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

>>> elem = cur.fetchone()[0]
>>> elem

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