types – Types information and adapters

The psycopg.types package exposes:

• objects to describe PostgreSQL types, such as TypeInfo, TypesRegistry, to help or customise the types conversion;
• concrete implementations of Loader and Dumper protocols to handle builtin data types;
• helper objects to represent PostgreSQL data types which don’t have a straightforward Python representation, such as Range.

The TypeInfo object describes simple information about a PostgreSQL data type, such as its name, oid and array oid. TypeInfo 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.

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: str, oid: int, array_oid: int, *, regtype: str = '', delimiter: str = ',')

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:

• conn (Connection or AsyncConnection) – the connection to query
• name (str or Identifier) – the name of the type to query. It can include a schema name.

RETURNS:

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

If the connection is async, fetch() 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: Optional[AdaptContext] = 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.

In order to get information about dynamic PostgreSQL types, Psycopg offers a few TypeInfo subclasses, whose fetch() method can extract more complete information about the type, such as CompositeInfo, RangeInfo, MultirangeInfo, EnumInfo.

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: Optional[TypesRegistry] = 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]) → TypeInfo

__getitem__(key: Tuple[Type[T], int]) → T

Return info about a type, specified by name or oid

PARAMETERS:

key – the name or oid of the type to look for.

Raise KeyError if not found.

>>> 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[TypeInfo]

get(key: Tuple[Type[T], int]) → Optional[T]

Return info about a type, specified by name or oid

PARAMETERS:

key – the name or oid of the type to look for.

Unlike __getitem__, return None if not found.

get_oid(name: str) → int

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.

>>> psycopg.adapters.types.get_oid("text[]")
1009

get_by_subtype(cls: Type[T], subtype: Union[int, str]) → Optional[T]

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

PARAMETERS:

• cls – the subtype of TypeInfo to look for. Currently supported are RangeInfo and MultirangeInfo.
• subtype – The name or OID of the subtype of the element to look for.

RETURNS:

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

See JSON adaptation for details.

class psycopg.types.json.Json(obj: Any, dumps: Optional[Callable[[Any], Union[str, bytes]]] = None)

class psycopg.types.json.Jsonb(obj: Any, dumps: Optional[Callable[[Any], Union[str, bytes]]] = 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: Callable[[Any], Union[str, bytes]], context: Optional[AdaptContext] = 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: Callable[[Union[str, bytes]], Any], context: Optional[AdaptContext] = 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.