Converting Arguments and Results

The PostgreSQL protocol provides both arguments and results in string formats. However, Python uses objects of many different types, not just strings. pg-purepy can automatically convert between Python objects and their PostgreSQL string representation, and vice-versa, provided it knows how to. A converter is a class that tells the protocol machine how to convert objects back and forth.

Built-in converters

pg-purepy comes with several builtin converters, for many Python stdlib types and many PostgreSQL core types.

“Fundamental” built-in types

  • All Postgres integer types are mapped to Python int. This includes the standard types such as int2, int4, int8, but also the more esoteric types such as oid.

  • Both Postgres float types are mapped to Python float.

  • bytea is mapped to Python bytes.

  • Booleeans are mapped to Python bool.

Date/Time types

  • TIMESTAMP WITH TIMEZONE and TIMESTAMP WITHOUT TIMEZONE are mapped to Arrow instances. The server timezone and UTC are used for timezones respectively, so it’s all handled automatically.

    Note

    I use Arrow over the vanilla datetime objects because I don’t like datetime. Write your own converter if you disagree with me.

  • DATE is mapped to datetime.date.

  • TIME WITHOUT TIMEZONE is mapped to datetime.time. TIME WITH TIMEZONE isn’t supported.

Enumeration types

You can add support for your own custom enumeration types using EnumConverter.

class pg_purepy.conversion.EnumConverter(oid, enum_klass, *, use_member_values=False, lowercase_names=True)

Bases: Converter

A converter that lets you use Python enums for PostgreSQL enums.

__init__(oid, enum_klass, *, use_member_values=False, lowercase_names=True)
Parameters:

  • oid (int) – The PostgreSQL object ID of the enum type.

  • enum_klass (Type[Enum]) – The Python-side enumeration class.

  • use_member_values (bool) – If True and the enum values are all strings, then the member values will be used rather than the names.

  • lowercase_names (bool) – If use_member_values is False, and this is True, then the member names will be lowercased. This is the general PostgreSQL enum convention.

Array Types

All built-in types have an array converter included, that will turn lists or tuples (or other ordered sequences) into PostgreSQL arrays.

If you want to convert your own types to/from arrays, you need to register a separate array converter.

class pg_purepy.conversion.ArrayConverter(oid, subconverter, quote_inner=False)

Bases: Converter

Converts arrays to Python lists. This requires a subconverter which will be called to convert every value in the array.

__init__(oid, subconverter, quote_inner=False)
Parameters:

  • oid (int) – The OID of the array type (not the base type!)

  • subconverter (Converter) – The converter for individual elements inside the array.

  • quote_inner (bool) – When converting to PostgreSQL, if inner elements should be quoted.

hstore

The postgresql key-value type (known as hstore) can be added as a converter.

async with ... as pool:
    await pool.add_converter_using(get_hstore_converter)

Custom Converters

If you need to convert a type that isn’t supported by default, you can create a custom Converter.

class pg_purepy.conversion.abc.Converter

Bases: object

Base class for all conversion classes. Implement this to create a custom converter.

oid: int

The OID of the PostgreSQL type this converter uses.

abstractmethod from_postgres(context, data)

Converts data from the PostgreSQL string representation to a Python type.

Parameters:

  • context (ConversionContext) – The conversion context this converter was invoked in.

  • data (str) – The raw string data.

Return type:

Any

Returns:

Any Python object that resulted from the conversion.

abstractmethod to_postgres(context, data)

Converts data from the Python type to the PostgreSQL string representation.

Parameters:

  • context (ConversionContext) – The conversion context this converter was invoked in.

  • data (Any) – The Python object that needs to be converted.

Return type:

str

Returns:

The string data that will be used in a query string.

The conversion context is passed to conversion functions, and contains attributes that may be useful for your conversion.

class pg_purepy.conversion.abc.ConversionContext(client_encoding, timezone=tzutc())

Bases: object

A conversion context contains information that might be needed to convert from the PostgreSQL string representation to the real representation.

client_encoding: str

The encoding of the client.

timezone: tzinfo

The timezone of the server.

Then, you can register converters with a method depending on your API.

AsyncPostgresConnection.add_converter(converter)

Registers a Converter with this connection.

SansIOClient.add_converter(converter)

Adds a new Converter to this protocol.

The high-level API has its own API for converters. See Converters.