summaryrefslogtreecommitdiffstats
path: root/docs/advanced/adapt.rst
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--docs/advanced/adapt.rst269
1 files changed, 269 insertions, 0 deletions
diff --git a/docs/advanced/adapt.rst b/docs/advanced/adapt.rst
new file mode 100644
index 0000000..4323b07
--- /dev/null
+++ b/docs/advanced/adapt.rst
@@ -0,0 +1,269 @@
+.. currentmodule:: psycopg.adapt
+
+.. _adaptation:
+
+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 :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.
+
+
+.. _adapt-example-xml:
+
+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 `~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 that it is possible to use a `~psycopg.types.TypesRegistry`, exposed by
+any `~psycopg.abc.AdaptContext`, to obtain information on builtin types, or
+extension types if they have been registered on that context using the
+`~psycopg.types.TypeInfo`\.\ `~psycopg.types.TypeInfo.register()` method.
+
+
+.. _adapt-example-float:
+
+Example: PostgreSQL numeric to Python 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:
+
+Example: handling infinity 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))
+
+
+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 `~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 `!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`` "`~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
+ `!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 `~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).