docs/advanced/adapt.rst
.. currentmodule:: psycopg.adapt
.. _adaptation:
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 :ref:query-parameters. Using the objects
described in this page is useful if you intend to customise the
adaptation rules.
Adaptation configuration is performed by changing the
~psycopg.abc.AdaptContext.adapters object of objects implementing the
~psycopg.abc.AdaptContext protocol, for instance ~psycopg.Connection
or ~psycopg.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
~psycopg.Connection.connect().
.. image:: ../pictures/adapt.svg :align: center
The !adapters attributes are AdaptersMap instances, and contain the
mapping from Python types and ~psycopg.abc.Dumper classes, and from
PostgreSQL OIDs to ~psycopg.abc.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 ~psycopg.abc.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 ~psycopg.abc.Dumper.oid attribute.
Loaders (objects implementing the ~psycopg.abc.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.
.. versionchanged:: 3.3
you can call `~AdaptersMap.register_loader()` on `!Cursor.adapters`
after a query has returned a result to change the loaders used.
.. _adapt-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 ~psycopg.Cursor, a
~psycopg.adapt.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 ~psycopg.abc.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
select a binary or a text dumper class (identified by their
~psycopg.abc.Dumper.format attribute). When using the %s
"~PyFormat.AUTO" format, if the same type has both a text and a binary
dumper registered, the last one registered by
~AdaptersMap.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 :sql:integer
or :sql:bigint depends on the number size...) In these cases the
mechanism provided by ~psycopg.abc.Dumper.get_key() and
~psycopg.abc.Dumper.upgrade() is used to create more specific dumpers.
The query is executed. Upon successful request, the result is received as a
~psycopg.pq.PGresult.
For every OID returned by the query, the !Transformer will instantiate a
~psycopg.abc.Loader. All the values with the same OID will be converted by
the same loader instance.
~psycopg.abc.Loader.format attribute).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 ~psycopg.pq.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).
.. _adapt-example-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 ~xml.etree.ElementTree is very simple, using the
psycopg.adapt.Loader base class and implementing the
~psycopg.abc.Loader.load() method:
.. __: https://www.postgresql.org/docs/current/datatype-xml.html
.. code:: python
>>> 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 ~psycopg.abc.Dumper.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::
You can use a `~psycopg.types.TypesRegistry`, exposed by
any `~psycopg.abc.AdaptContext`, to obtain information on builtin types, in
the form of a `TypeInfo` object::
# Global types registry
>>> psycopg.adapters.types["text"]
<TypeInfo: text (oid: 25, array oid: 1009)>
# Types registry on a connection
>>> conn.adapters.types["integer"]
<TypeInfo: int4 (oid: 23, array oid: 1007)>
The same method can be used to get information about extension types if
they have been registered on that context using the
`~psycopg.types.TypeInfo`\.\ `~psycopg.types.TypeInfo.register()` method::
>>> (t := psycopg.types.TypeInfo.fetch(conn, "hstore"))
<TypeInfo: hstore (oid: 770082, array oid: 770087)>
>>> t.register() # globally
>>> psycopg.adapters.types["hstore"]
<TypeInfo: hstore (oid: 770082, array oid: 770087)>
.. _adapt-example-null-str:
.. versionchanged:: 3.2
The `dump()` method can also return `!None`, which will be stored as
:sql:`NULL` in the database.
If you prefer to store missing values as :sql:NULL, in the database, but
your input may contain empty strings, you can subclass the stock string dumper
to return !None upon empty or whitespace-only strings::
>>> from psycopg.types.string import StrDumper
>>> class NullStrDumper(StrDumper):
... def dump(self, obj):
... if not obj or obj.isspace():
... return None
... return super().dump(obj)
>>> conn.adapters.register_dumper(str, NullStrDumper)
>>> conn.execute("select %s, %s, %s, %s", ("foo", "", "bar", " ")).fetchone()
('foo', None, 'bar', None)
.. _adapt-example-float:
Normally PostgreSQL :sql:numeric values are converted to Python
~decimal.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
:sql: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 :sql:numeric values as Python !float, you can register on
:sql:numeric the same Loader class used to load
:sql:float4/:sql:float8 values. Because the PostgreSQL textual
representation of both floats and decimal is the same, the two loaders are
compatible.
.. code:: python
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.
.. _adapt-example-inf-date:
Suppose you want to work with the "infinity" date which is available in PostgreSQL but not handled by Python:
.. code:: 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):
.. code:: python
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))