types – Types information and adapters

The psycopg.types package exposes the concrete implementation of Loader and Dumper to manage builtin objects, together with objects to describe PostgreSQL types and wrappers to help or customise the types conversion.

Types information

The TypeInfo object describes simple information about a PostgreSQL data type, such as its name, oid and array oid. Subclasses may hold more information, for instance the components of a composite type.

You can use TypeInfo.fetch() to query information from a database catalog, which is then used by helper functions, such as register_hstore(), to register adapters on types whose OID is not known upfront or to create more specialised adapters.

The TypeInfo object doesn’t instruct Psycopg to convert a PostgreSQL type into a Python type: this is the role of a Loader. However it can extend the behaviour of other adapters: if you create a loader for MyType, using the TypeInfo information, Psycopg will be able to manage seamlessly arrays of MyType or ranges and composite types using MyType as a subtype.

See also

Data adaptation configuration describes how to convert from Python objects to PostgreSQL types and back.

from psycopg.adapt import Loader
from psycopg.types import TypeInfo

t = TypeInfo.fetch(conn, "mytype")
t.register(conn)

for record in conn.execute("SELECT mytypearray FROM mytable"):
    # records will return lists of "mytype" as string

class MyTypeLoader(Loader):
    def load(self, data):
        # parse the data and return a MyType instance

conn.adapters.register_loader("mytype", MyTypeLoader)

for record in conn.execute("SELECT mytypearray FROM mytable"):
    # records will return lists of MyType instances
class psycopg.types.TypeInfo(name, oid, array_oid, alt_name='', delimiter=',')

Hold information about a PostgreSQL base type.

classmethod fetch(conn, name)
async classmethod fetch(aconn, name)

Query a system catalog to read information about a type.

Parameters
Returns

a TypeInfo object (or subclass) populated with the type information, None if not found.

If the connection is async the function will behave as a coroutine and the caller will need to await on it to get the result:

t = await TypeInfo.fetch(aconn, "mytype")
register(context=None)

Register the type information, globally or in the specified context.

Parameters

context (Optional[AdaptContext]) – the context where the type is registered, for instance a Connection or Cursor. None registers the TypeInfo globally.

Registering the TypeInfo in a context allows the adapters of that context to look up type information: for instance it allows to recognise automatically arrays of that type and load them from the database as a list of the base type.

For recursive types, Psycopg offers a few TypeInfo subclasses which can be used to extract more complete information, such as CompositeInfo, RangeInfo, MultirangeInfo.

TypeInfo objects are collected in TypesRegistry instances, which help type information lookup. Every AdaptersMap exposes its type map on its types attribute.

class psycopg.types.TypesRegistry(template=None)

Container for the information about types in a database.

TypeRegistry instances are typically exposed by AdaptersMap objects in adapt contexts such as Connection or Cursor (e.g. conn.adapters.types).

The global registry, from which the others inherit from, is available as psycopg.adapters.types.

__getitem__(key: Union[str, int]) psycopg.types.TypeInfo
__getitem__(key: Tuple[Type[psycopg._typeinfo.T], int]) psycopg._typeinfo.T

Return info about a type, specified by name or oid

Parameters

key (Union[str, int, Tuple[type, int]]) – the name or oid of the type to look for.

Raise KeyError if not found.

Return type

TypeInfo

>>> import psycopg

>>> psycopg.adapters.types["text"]
<TypeInfo: text (oid: 25, array oid: 1009)>

>>> psycopg.adapters.types[23]
<TypeInfo: int4 (oid: 23, array oid: 1007)>
get(key: Union[str, int]) Optional[psycopg.types.TypeInfo]
get(key: Tuple[Type[psycopg._typeinfo.T], int]) Optional[psycopg._typeinfo.T]

Return info about a type, specified by name or oid

Parameters

key (Union[str, int, Tuple[type, int]]) – the name or oid of the type to look for.

Unlike __getitem__, return None if not found.

Return type

Optional[TypeInfo]

get_oid(name)

Return the oid of a PostgreSQL type by name.

Parameters

key – the name of the type to look for.

Return the array oid if the type ends with “[]

Raise KeyError if the name is unknown.

Return type

int

>>> psycopg.adapters.types.get_oid("text[]")
1009
get_by_subtype(cls, subtype)

Return info about a TypeInfo subclass by its element name or oid.

Parameters
  • cls (Type[~T]) – the subtype of TypeInfo to look for. Currently supported are RangeInfo and MultirangeInfo.

  • subtype (Union[int, str]) – The name or OID of the subtype of the element to look for.

Return type

Optional[~T]

Returns

The TypeInfo object of class cls whose subtype is subtype. None if the element or its range are not found.

JSON adapters

See JSON adaptation for details.

class psycopg.types.json.Json(obj, dumps=None)
class psycopg.types.json.Jsonb(obj, dumps=None)

Wrappers to signal to convert obj to a json or jsonb PostgreSQL value.

Any object supported by the underlying dumps() function can be wrapped.

If a dumps function is passed to the wrapper, use it to dump the wrapped object. Otherwise use the function specified by set_json_dumps().

psycopg.types.json.set_json_dumps(dumps, context=None)

Set the JSON serialisation function to store JSON objects in the database.

Parameters
  • dumps (Callable[[Any], str]) – The dump function to use.

  • context (Connection or Cursor) – Where to use the dumps function. If not specified, use it globally.

By default dumping JSON uses the builtin json.dumps. You can override it to use a different JSON library or to use customised arguments.

If the Json wrapper specified a dumps function, use it in precedence of the one set by this function.

psycopg.types.json.set_json_loads(loads, context=None)

Set the JSON parsing function to fetch JSON objects from the database.

Parameters
  • loads (Callable[[bytes], Any]) – The load function to use.

  • context (Connection or Cursor) – Where to use the loads function. If not specified, use it globally.

By default loading JSON uses the builtin json.loads. You can override it to use a different JSON library or to use customised arguments.