Data adaptation configuration¶

The adaptation system is at the core of psycopg3 and allows to customise the way Python objects are converted to PostgreSQL when a query is performed and how PostgreSQL values are converted to Python objects when query results are returned.

Note

For a high-level view of the conversion of types between Python and PostgreSQL please look at Passing parameters to SQL queries. Using the objects described in this page is useful if you intend to customise the adaptation rules.

The TypeInfo object allows to query type information from a database, which can be used by the adapters: for instance to make them able to decode arrays of base types or composite types.
The Dumper is the base object to perform conversion from a Python object to a bytes string understood by PostgreSQL. The string returned shouldn’t be quoted: the value will be passed to the database using functions such as PQexecParams() so quoting and quotes escaping is not necessary.
The Loader is the base object to perform the opposite operation: to read a bytes string from PostgreSQL and create a Python object.

Dumper and Loader are abstract classes: concrete classes must implement the dump() and load() methods. psycopg3 provides implementation for several builtin Python and PostgreSQL types.

Psycopg provides adapters for several builtin types, which can be used as the base to build more complex ones: they all live in the psycopg3.types package.

Dumpers and loaders configuration¶

Dumpers and loaders can be registered on different scopes: globally, per Connection, per Cursor, so that adaptation rules can be customised for specific needs within the same application: in order to do so you can use the context parameter of Dumper.register() and Loader.register().

When a Connection is created, it inherits the global adapters configuration; when a Cursor is created it inherits its Connection configuration.

Note

register() is a class method on the base class, so if you subclass Dumper or Loader you should call the .register() on the class you created.

Example: handling infinity date¶

Suppose you want to work with the “infinity” date which is available in PostgreSQL but not handled by Python:

>>> conn.execute("'infinity'::date").fetchone()
Traceback (most recent call last):
   ...
psycopg3.DataError: Python date doesn't support years after 9999: got infinity

One possibility would be to store Python’s datetime.date.max to PostgreSQL infinity. For this, let’s create a subclass for the dumper and the loader and register them in the working scope (globally or just on a connection or cursor):

from datetime import date

from psycopg3.oids import postgres_types as builtins
from psycopg3.types import DateLoader, DateDumper

class InfDateDumper(DateDumper):
    def dump(self, obj):
        if obj == date.max:
            return b"infinity"
        else:
            return super().dump(obj)

class InfDateLoader(DateLoader):
    def load(self, data):
        if data == b"infinity":
            return date.max
        else:
            return super().load(data)

InfDateDumper.register(date, cur)
InfDateLoader.register(builtins["date"].oid, cur)

cur.execute("SELECT %s::text, %s::text", [date(2020, 12, 31), date.max]).fetchone()
# ('2020-12-31', 'infinity')
cur.execute("select '2020-12-31'::date, 'infinity'::date").fetchone()
# (datetime.date(2020, 12, 31), datetime.date(9999, 12, 31))

TODO

Example: numeric to float

Dumpers and loaders life cycle¶

Registering dumpers and loaders will instruct psycopg3 to use them in the queries to follow, in the context where they have been registered.

When a query is performed on a Cursor, a Transformer object is created as a local context to manage conversions during the query, instantiating the required dumpers and loaders and dispatching the values to convert to the right instance.

The Transformer copies the adapters configuration from the Cursor, thus inheriting all the changes made to the global configuration, the current Connection, the Cursor.
For every Python type passed as query argument, the Transformer will instantiate a Dumper. Usually all the objects of the same type will be converted by the same dumper; certain dumpers may be used in more than one instance, because the same Python type maps to more than one PostgreSQL type (for instance, a Python int might be better dumped as a PostgreSQL integer, bigint, smallint according to its value).
According to the placeholder used (%s, %b, %t), psycopg3 may pick a binary or a text dumper. When using the %s “AUTO” format, if the same type has both a text and a binary dumper registered, the last one registered (using Dumper.register()) will be selected.
For every OID returned by the query, the Transformer will instantiate a Loader. All the values with the same OID will be converted by the same loader.
Recursive types (e.g. Python lists, PostgreSQL arrays and composite types) will use the same adaptation rules.

As a consequence it is possible to perform certain choices only once per query (e.g. looking up the connection encoding) and then call a fast-path operation for each value to convert.

Querying will fail if a Python object for which there isn’t a Dumper registered (for the right Format) is used as query parameter. If the query returns a data type whose OID doesn’t have a Loader, the value will be returned as a string (or bytes string for binary types).

Objects involved in types adaptation¶

TODO

move to API section

class psycopg3.adapt.Format(value)¶

Enum representing the format wanted for a query argument.

The value AUTO allows psycopg3 to choose the best value for a certain value.

AUTO = 's'¶: Automatically chosen (%s placeholder).

TEXT = 't'¶: Text parameter (%t placeholder).

BINARY = 'b'¶: Binary parameter (%b placeholder).

class psycopg3.adapt.Dumper(cls, context=None)¶

Convert Python object of the type cls to PostgreSQL representation.

This is an abstract base class: subclasses must implement the dump() method and specify the format. They may implement oid (as attribute or property) in order to override the oid type oid; if not PostgreSQL will try to infer the type from the context, but this may fail in some contexts and may require a cast.

Parameters

cls (type) – The type that will be managed by this dumper.
context (Connection, Cursor, or Transformer) – The context where the transformation is performed. If not specified the conversion might be inaccurate, for instance it will not be possible to know the connection encoding or the server date format.

format: pq.Format¶: The format this class dumps, TEXT or BINARY. This is a class attribute.

abstract dump(obj)¶

Convert the object obj to PostgreSQL representation.

Return type: Union[bytes, bytearray, memoryview]

The format returned by dump shouldn’t contain quotes or escaped values.

quote(obj)¶

Convert the object obj to escaped representation.

Return type: Union[bytes, bytearray, memoryview]

By default return the dump() value quoted and sanitised, so that the result can be used to build a SQL string. This works well for most types and you won’t likely have to implement this method in a subclass.

Tip

This method will be used by Literal to convert a value client-side.

This method only makes sense for text dumpers; the result of calling it on a binary dumper is undefined. It might scratch your car, or burn your cake. Don’t tell me I didn’t warn you.

oid: int¶: The oid to pass to the server, if known.

todo

Document how to find type OIDs in a database.

classmethod register(cls, context=None)¶

Configure context to use this dumper to convert object of type cls.

Return type: None

You should call this method on the Dumper subclass you create, passing the Python type you want to dump as cls.

If two dumpers of different format are registered for the same type, the last one registered will be chosen by default when the query doesn’t specify a format (i.e. when the value is used with a %s “AUTO” placeholder).

Parameters

cls (type or str) – The type to manage.
context (Connection, Cursor, or Transformer) – Where the dumper should be used. If None the dumper will be used globally.

If cls is specified as string it will be lazy-loaded, so that it will be possible to register it without importing it before. In this case it should be the fully qualified name of the object (e.g. "uuid.UUID").

class psycopg3.adapt.Loader(oid, context=None)¶

Convert PostgreSQL objects with OID oid to Python objects.

This is an abstract base class: subclasses must implement the load() method and specify a format.

Parameters

oid (int) – The type that will be managed by this dumper.
context (Connection, Cursor, or Transformer) – The context where the transformation is performed. If not specified the conversion might be inaccurate, for instance it will not be possible to know the connection encoding or the server date format.

format: Format¶: The format this class can load, TEXT or BINARY. This is a class attribute.

abstract load(data)¶

Convert a PostgreSQL value to a Python object.

Return type: Any

classmethod register(oid, context=None)¶

Configure context to use this loader to convert values with OID oid.

Return type: None

You should call this method on the Loader subclass you create, passing the OID of the type you want to load as oid parameter.

Parameters

oid (int) – The PostgreSQL OID to manage.
context (Connection, Cursor, or Transformer) – Where the loader should be used. If None the loader will be used globally.

class psycopg3.adapt.Transformer(context=None)¶

An object that can adapt efficiently between Python and PostgreSQL.

The life cycle of the object is the query, so it is assumed that stuff like the server version or connection encoding will not change. It can have its state so adapting several values of the same type can be optimised.

Parameters: context (Connection, Cursor, or Transformer) – The context where the transformer should operate.

TODO: finalise the interface of this object