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 abytes
string understood by PostgreSQL. The string returned shouldn’t be quoted: the value will be passed to the database using functions such asPQexecParams()
so quoting and quotes escaping is not necessary.The
Loader
is the base object to perform the opposite operation: to read abytes
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 theCursor
, thus inheriting all the changes made to the global configuration, the currentConnection
, theCursor
.For every Python type passed as query argument, the
Transformer
will instantiate aDumper
. 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 Pythonint
might be better dumped as a PostgreSQLinteger
,bigint
,smallint
according to its value).For every OID returned by the query, the
Transformer
will instantiate aLoader
. 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 theformat
. They may implementoid
(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
, orTransformer
) – 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.
-
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
orstr
) – The type to manage.context (
Connection
,Cursor
, orTransformer
) – Where the dumper should be used. IfNone
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 aformat
.- Parameters
oid (int) – The type that will be managed by this dumper.
context (
Connection
,Cursor
, orTransformer
) – 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.
-
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
, orTransformer
) – Where the loader should be used. IfNone
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
, orTransformer
) – The context where the transformer should operate.
TODO: finalise the interface of this object