Hide navigation sidebar
Hide table of contents sidebar
Back to top
Toggle table of contents sidebar

# Data adaptation configuration

The adaptation system is at the core of Psycopg 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.

- Adaptation configuration is performed by changing the `adapters` object of objects implementing the `AdaptContext` protocol, for instance `Connection` or `Cursor`.

- Every context object derived from another context inherits its adapters mapping: cursors created from a connection inherit the connection’s configuration.

  By default, connections obtain an adapters map from the global map exposed as `psycopg.adapters`: changing the content of this object will affect every connection created afterwards. You may specify a different template adapters map using the `context` parameter on `connect()`.

- The `adapters` attributes are `AdaptersMap` instances, and contain the mapping from Python types and `Dumper` classes, and from PostgreSQL OIDs to `Loader` classes. Changing this mapping (e.g. writing and registering your own adapters, or using a different configuration of builtin adapters) affects how types are converted between Python and PostgreSQL.

  - Dumpers (objects implementing the `Dumper` protocol) are the objects used to perform the conversion from a Python object to a bytes sequence in a format understood by PostgreSQL. The string returned shouldn’t be quoted: the value will be passed to the database using functions such as :pq:`PQexecParams()` so quoting and quotes escaping is not necessary. The dumper usually also suggests to the server what type to use, via its `oid` attribute.

  - Loaders (objects implementing the `Loader` protocol) are the objects used to perform the opposite operation: reading a bytes sequence from PostgreSQL and creating a Python object out of it.

  - Dumpers and loaders are instantiated on demand by a `Transformer` object when a query is executed.

Note

Changing adapters in a context only affects that context and its children objects created afterwards; the objects already created are not affected. For instance, changing the global context will only change newly created connections, not the ones already existing.

## Writing a custom adapter: XML

Psycopg doesn’t provide adapters for the XML data type, because there are just too many ways of handling XML in Python. Creating a loader to parse the PostgreSQL xml type to `ElementTree` is very simple, using the `psycopg.adapt.Loader` base class and implementing the `load()` method:

    >>> import xml.etree.ElementTree as ET
    >>> from psycopg.adapt import Loader

    >>> # Create a class implementing the `load()` method.
    >>> class XmlLoader(Loader):
    ...     def load(self, data):
    ...         return ET.fromstring(data)

    >>> # Register the loader on the adapters of a context.
    >>> conn.adapters.register_loader("xml", XmlLoader)

    >>> # Now just query the database returning XML data.
    >>> cur = conn.execute(
    ...     """select XMLPARSE (DOCUMENT '<?xml version="1.0"?>
    ...            <book><title>Manual</title><chapter>...</chapter></book>')
    ...     """)

    >>> elem = cur.fetchone()[0]
    >>> elem
    <Element 'book' at 0x7ffb55142ef0>

The opposite operation, converting Python objects to PostgreSQL, is performed by dumpers. The `psycopg.adapt.Dumper` base class makes it easy to implement one: you only need to implement the `dump()` method:

    >>> from psycopg.adapt import Dumper

    >>> class XmlDumper(Dumper):
    ...     # Setting an OID is not necessary but can be helpful
    ...     oid = psycopg.adapters.types["xml"].oid
    ...
    ...     def dump(self, elem):
    ...         return ET.tostring(elem)

    >>> # Register the dumper on the adapters of a context
    >>> conn.adapters.register_dumper(ET.Element, XmlDumper)

    >>> # Now, in that context, it is possible to use ET.Element objects as parameters
    >>> conn.execute("SELECT xpath('//title/text()', %s)", [elem]).fetchone()[0]
    ['Manual']

Note that it is possible to use a `TypesRegistry`, exposed by any `AdaptContext`, to obtain information on builtin types, or extension types if they have been registered on that context using the `TypeInfo`.`register()` method.

## Example: PostgreSQL numeric to Python float

Normally PostgreSQL `numeric` values are converted to Python `Decimal` instances, because both the types allow fixed-precision arithmetic and are not subject to rounding.

Sometimes, however, you may want to perform floating-point math on `numeric` values, and `Decimal` may get in the way (maybe because it is slower, or maybe because mixing `float` and `Decimal` values causes Python errors).

If you are fine with the potential loss of precision and you simply want to receive `numeric` values as Python `float`, you can register on `numeric` the same `Loader` class used to load `float4`/`float8` values. Because the PostgreSQL textual representation of both floats and decimal is the same, the two loaders are compatible.

    conn = psycopg.connect()

    conn.execute("SELECT 123.45").fetchone()[0]
    # Decimal('123.45')

    conn.adapters.register_loader("numeric", psycopg.types.numeric.FloatLoader)

    conn.execute("SELECT 123.45").fetchone()[0]
    # 123.45

In this example the customised adaptation takes effect only on the connection `conn` and on any cursor created from it, not on other connections.

## 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("SELECT 'infinity'::date").fetchone()
    Traceback (most recent call last):
       ...
    DataError: date too large (after year 10K): 'infinity'

One possibility would be to store Python’s `datetime.date.max` as 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

    # Subclass existing adapters so that the base case is handled normally.
    from psycopg.types.datetime import DateLoader, DateDumper

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

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

    # The new classes can be registered globally, on a connection, on a cursor
    cur.adapters.register_dumper(date, InfDateDumper)
    cur.adapters.register_loader("date", InfDateLoader)

    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))

## Dumpers and loaders life cycle

Registering dumpers and loaders will instruct Psycopg 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 adaptation during the query, instantiating the required dumpers and loaders and dispatching the values to perform the wanted conversions from Python to Postgres and back.

- The `Transformer` copies the adapters configuration from the `Cursor`, thus inheriting all the changes made to the global `psycopg.adapters` 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 instance.

  - According to the placeholder used (`%s`, `%b`, `%t`), Psycopg 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 by `register_dumper()` will be used.

  - Sometimes, just looking at the Python type is not enough to decide the best PostgreSQL type to use (for instance the PostgreSQL type of a Python list depends on the objects it contains, whether to use an `integer` or `bigint` depends on the number size…) In these cases the mechanism provided by `get_key()` and `upgrade()` is used to create more specific dumpers.

- The query is executed. Upon successful request, the result is received as a `PGresult`.

- 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 instance.

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