Adapting basic Python types#

Many standard Python types are adapted into SQL and returned as Python objects when a query is executed.

Converting the following data types between Python and PostgreSQL works out-of-the-box and doesn’t require any configuration. In case you need to customise the conversion you should take a look at Data adaptation configuration.

Booleans adaptation#

Python bool values True and False are converted to the equivalent PostgreSQL boolean type:

>>> cur.execute("SELECT %s, %s", (True, False))
# equivalent to "SELECT true, false"

Changed in version 3.2: numpy.bool_ values can be dumped too.

Numbers adaptation#

Strings adaptation#

Binary adaptation#

Python types representing binary objects (bytes, bytearray, memoryview) are converted by default to bytea fields. By default data received is returned as bytes.

If you are storing large binary data in bytea fields (such as binary documents or images) you should probably use the binary format to pass and return values, otherwise binary data will undergo ASCII escaping, taking some CPU time and more bandwidth. See Binary parameters and results for details.

Date/time types adaptation#

Dates and times limits in Python#

PostgreSQL date and time objects can represent values that cannot be represented by the Python datetime objects:

dates and timestamps after the year 9999, the special value “infinity”;
dates and timestamps before the year 1, the special value “-infinity”;
the time 24:00:00.

Loading these values will raise a DataError.

If you need to handle these values you can define your own mapping (for instance mapping every value greater than datetime.date.max to date.max, or the time 24:00 to 00:00) and write a subclass of the default loaders implementing the added capability; please see this example for a reference.

DateStyle and IntervalStyle limits#

Loading timestamp with time zone in text format is only supported if the connection DateStyle is set to ISO format; time and time zone representation in other formats is ambiguous.

Furthermore, at the time of writing, the only supported value for IntervalStyle is postgres; loading interval data in text format with a different setting is not supported.

If your server is configured with different settings by default, you can obtain a connection in a supported style using the options connection parameter; for example:

>>> conn = psycopg.connect(options="-c datestyle=ISO,YMD")
>>> conn.execute("show datestyle").fetchone()[0]
# 'ISO, YMD'

These GUC parameters only affects loading in text format; loading timestamps or intervals in binary format is not affected by DateStyle or IntervalStyle.

JSON adaptation#

Psycopg can map between Python objects and PostgreSQL json/jsonb types, allowing to customise the load and dump function used.

Because several Python objects could be considered JSON (dicts, lists, scalars, even date/time if using a dumps function customised to use them), Psycopg requires you to wrap the object to dump as JSON into a wrapper: either psycopg.types.json.Json or Jsonb.

from psycopg.types.json import Jsonb

thing = {"foo": ["bar", 42]}
conn.execute("INSERT INTO mytable VALUES (%s)", [Jsonb(thing)])

By default Psycopg uses the standard library json.dumps and json.loads functions to serialize and de-serialize Python objects to JSON. If you want to customise how serialization happens, for instance changing serialization parameters or using a different JSON library, you can specify your own functions using the psycopg.types.json.set_json_dumps() and set_json_loads() functions, to apply either globally or to a specific context (connection or cursor).

from functools import partial
from psycopg.types.json import Jsonb, set_json_dumps, set_json_loads
import ujson

# Use a faster dump function
set_json_dumps(ujson.dumps)

# Return floating point values as Decimal, just in one connection
set_json_loads(partial(json.loads, parse_float=Decimal), conn)

conn.execute("SELECT %s", [Jsonb({"value": 123.45})]).fetchone()[0]
# {'value': Decimal('123.45')}

If you need an even more specific dump customisation only for certain objects (including different configurations in the same query) you can specify a dumps parameter in the Json/Jsonb wrapper, which will take precedence over what is specified by set_json_dumps().

from uuid import UUID, uuid4

class UUIDEncoder(json.JSONEncoder):
    """A JSON encoder which can dump UUID."""
    def default(self, obj):
        if isinstance(obj, UUID):
            return str(obj)
        return json.JSONEncoder.default(self, obj)

uuid_dumps = partial(json.dumps, cls=UUIDEncoder)
obj = {"uuid": uuid4()}
cnn.execute("INSERT INTO objs VALUES %s", [Json(obj, dumps=uuid_dumps)])
# will insert: {'uuid': '0a40799d-3980-4c65-8315-2956b18ab0e1'}

Lists adaptation#

Python list objects are adapted to PostgreSQL arrays and back. Only lists containing objects of the same type can be dumped to PostgreSQL (but the list may contain None elements).

Note

If you have a list of values which you want to use with the IN operator… don’t. It won’t work (neither with a list nor with a tuple):

>>> conn.execute("SELECT * FROM mytable WHERE id IN %s", [[10,20,30]])
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg.errors.SyntaxError: syntax error at or near "$1"
LINE 1: SELECT * FROM mytable WHERE id IN $1
                                          ^

What you want to do instead is to use the ‘= ANY()’ expression and pass the values as a list (not a tuple).

>>> conn.execute("SELECT * FROM mytable WHERE id = ANY(%s)", [[10,20,30]])

This has also the advantage of working with an empty list, whereas IN () is not valid SQL.

UUID adaptation#

Python uuid.UUID objects are adapted to PostgreSQL UUID type and back:

>>> conn.execute("select gen_random_uuid()").fetchone()[0]
UUID('97f0dd62-3bd2-459e-89b8-a5e36ea3c16c')

>>> from uuid import uuid4
>>> conn.execute("select gen_random_uuid() = %s", [uuid4()]).fetchone()[0]
False  # long shot

Network data types adaptation#

Objects from the ipaddress module are converted to PostgreSQL network address types:

IPv4Address, IPv4Interface objects are converted to the PostgreSQL inet type. On the way back, inet values indicating a single address are converted to IPv4Address, otherwise they are converted to IPv4Interface
IPv4Network objects are converted to the cidr type and back.
IPv6Address, IPv6Interface, IPv6Network objects follow the same rules, with IPv6 inet and cidr values.

>>> conn.execute("select '192.168.0.1'::inet, '192.168.0.1/24'::inet").fetchone()
(IPv4Address('192.168.0.1'), IPv4Interface('192.168.0.1/24'))

>>> conn.execute("select '::ffff:1.2.3.0/120'::cidr").fetchone()[0]
IPv6Network('::ffff:102:300/120')

Enum adaptation#

New in version 3.1.

Psycopg can adapt Python Enum subclasses into PostgreSQL enum types (created with the CREATE TYPE ... AS ENUM (...) command).

In order to set up a bidirectional enum mapping, you should get information about the PostgreSQL enum using the EnumInfo class and register it using register_enum(). The behaviour of unregistered and registered enums is different.

If the enum is not registered with register_enum():
- Pure Enum classes are dumped as normal strings, using their member names as value. The unknown oid is used, so PostgreSQL should be able to use this string in most contexts (such as an enum or a text field).
  
  Changed in version 3.1: In previous version dumping pure enums is not supported and raise a “cannot adapt” error.
- Mix-in enums are dumped according to their mix-in type (because a class MyIntEnum(int, Enum) is more specifically an int than an Enum, so it’s dumped by default according to int rules).
- PostgreSQL enums are loaded as Python strings. If you want to load arrays of such enums you will have to find their OIDs using types.TypeInfo.fetch() and register them using register().
If the enum is registered (using EnumInfo.fetch() and register_enum()):
- Enums classes, both pure and mixed-in, are dumped by name.
- The registered PostgreSQL enum is loaded back as the registered Python enum members.

class psycopg.types.enum.EnumInfo(name: str, oid: int, array_oid: int, labels: Sequence[str])#

Manage information about an enum type.

EnumInfo is a subclass of TypeInfo: refer to the latter’s documentation for generic usage, especially the fetch() method.

labels#: After fetch(), it contains the labels defined in the PostgreSQL enum type.

enum#: After register_enum() is called, it will contain the Python type mapping to the registered enum.

psycopg.types.enum.register_enum(info: EnumInfo, context: Optional[AdaptContext] = None, enum: Optional[Type[E]] = None, *, mapping: Optional[Union[Mapping[E, str], Sequence[Tuple[E, str]]]] = None)#

Parameters:

info – The object with the information about the enum to register.
context – The context where to register the adapters. If None, register it globally.
enum – Python enum type matching to the PostgreSQL one. If None, a new enum will be generated and exposed as EnumInfo.enum.
mapping – Override the mapping between enum members and info labels.

After registering, fetching data of the registered enum will cast PostgreSQL enum labels into corresponding Python enum members.

If no enum is specified, a new Enum is created based on PostgreSQL enum labels.

Example:

>>> from enum import Enum, auto
>>> from psycopg.types.enum import EnumInfo, register_enum

>>> class UserRole(Enum):
...     ADMIN = auto()
...     EDITOR = auto()
...     GUEST = auto()

>>> conn.execute("CREATE TYPE user_role AS ENUM ('ADMIN', 'EDITOR', 'GUEST')")

>>> info = EnumInfo.fetch(conn, "user_role")
>>> register_enum(info, conn, UserRole)

>>> some_editor = info.enum.EDITOR
>>> some_editor
<UserRole.EDITOR: 2>

>>> conn.execute(
...     "SELECT pg_typeof(%(editor)s), %(editor)s",
...     {"editor": some_editor}
... ).fetchone()
('user_role', <UserRole.EDITOR: 2>)

>>> conn.execute(
...     "SELECT ARRAY[%s, %s]",
...     [UserRole.ADMIN, UserRole.GUEST]
... ).fetchone()
[<UserRole.ADMIN: 1>, <UserRole.GUEST: 3>]

If the Python and the PostgreSQL enum don’t match 1:1 (for instance if members have a different name, or if more than one Python enum should map to the same PostgreSQL enum, or vice versa), you can specify the exceptions using the mapping parameter.

mapping should be a dictionary with Python enum members as keys and the matching PostgreSQL enum labels as values, or a list of (member, label) pairs with the same meaning (useful when some members are repeated). Order matters: if an element on either side is specified more than once, the last pair in the sequence will take precedence:

# Legacy roles, defined in medieval times.
>>> conn.execute(
...     "CREATE TYPE abbey_role AS ENUM ('ABBOT', 'SCRIBE', 'MONK', 'GUEST')")

>>> info = EnumInfo.fetch(conn, "abbey_role")
>>> register_enum(info, conn, UserRole, mapping=[
...     (UserRole.ADMIN, "ABBOT"),
...     (UserRole.EDITOR, "SCRIBE"),
...     (UserRole.EDITOR, "MONK")])

>>> conn.execute("SELECT '{ABBOT,SCRIBE,MONK,GUEST}'::abbey_role[]").fetchone()[0]
[<UserRole.ADMIN: 1>,
 <UserRole.EDITOR: 2>,
 <UserRole.EDITOR: 2>,
 <UserRole.GUEST: 3>]

>>> conn.execute("SELECT %s::text[]", [list(UserRole)]).fetchone()[0]
['ABBOT', 'MONK', 'GUEST']

A particularly useful case is when the PostgreSQL labels match the values of a str-based Enum. In this case it is possible to use something like {m: m.value for m in enum} as mapping:

>>> class LowercaseRole(str, Enum):
...     ADMIN = "admin"
...     EDITOR = "editor"
...     GUEST = "guest"

>>> conn.execute(
...     "CREATE TYPE lowercase_role AS ENUM ('admin', 'editor', 'guest')")

>>> info = EnumInfo.fetch(conn, "lowercase_role")
>>> register_enum(
...     info, conn, LowercaseRole, mapping={m: m.value for m in LowercaseRole})

>>> conn.execute("SELECT 'editor'::lowercase_role").fetchone()[0]
<LowercaseRole.EDITOR: 'editor'>