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.

TODO

  • Example: infinity date customisation

  • 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 Trasformer 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).

  • 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: Any) → Union[bytes, bytearray, memoryview]

Convert the object obj to PostgreSQL representation.

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

quote(obj: Any) → Union[bytes, bytearray, memoryview]

Convert the object obj to escaped representation.

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.

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

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: Union[bytes, bytearray, memoryview]) → Any

Convert a PostgreSQL value to a Python object.

classmethod register(oid, context=None)

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

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