psycopg2.extras
– Miscellaneous goodies for Psycopg 2¶
This module is a generic place used to hold little helper functions and classes until a better place in the distribution is found.
Connection and cursor subclasses¶
A few objects that change the way the results are returned by the cursor or
modify the object behavior in some other way. Typically cursor
subclasses
are passed as cursor_factory argument to connect()
so that the
connection’s cursor()
method will generate objects of this
class. Alternatively a cursor
subclass can be used one-off by passing it
as the cursor_factory argument to the cursor()
method.
If you want to use a connection
subclass you can pass it as the
connection_factory argument of the connect()
function.
Dictionary-like cursor¶
The dict cursors allow to access to the attributes of retrieved records using an interface similar to the Python dictionaries instead of the tuples.
>>> dict_cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
>>> dict_cur.execute("INSERT INTO test (num, data) VALUES(%s, %s)",
... (100, "abc'def"))
>>> dict_cur.execute("SELECT * FROM test")
>>> rec = dict_cur.fetchone()
>>> rec['id']
1
>>> rec['num']
100
>>> rec['data']
"abc'def"
The records still support indexing as the original tuple:
>>> rec[2]
"abc'def"
Real dictionary cursor¶
namedtuple
cursor¶
New in version 2.3.
Logging cursor¶
Note
Queries that are executed with cursor.executemany()
are not logged.
Replication support objects¶
See Replication protocol support for an introduction to the topic.
The following replication types are defined:
- psycopg2.extras.REPLICATION_LOGICAL¶
- psycopg2.extras.REPLICATION_PHYSICAL¶
The individual messages in the replication stream are represented by
ReplicationMessage
objects (both logical and physical type):
Additional data types¶
JSON adaptation¶
New in version 2.5.
Changed in version 2.5.4: added jsonb
support. In previous versions jsonb
values are returned
as strings. See the FAQ for a workaround.
Psycopg can adapt Python objects to and from the PostgreSQL json
and jsonb
types. With PostgreSQL 9.2 and following versions adaptation is
available out-of-the-box. To use JSON data with previous database versions
(either with the 9.1 json extension, but even if you want to convert text
fields to JSON) you can use the register_json()
function.
The Python json
module is used by default to convert Python objects
to JSON and to parse data from the database.
In order to pass a Python object to the database as query argument you can use
the Json
adapter:
curs.execute("insert into mytable (jsondata) values (%s)",
[Json({'a': 100})])
Reading from the database, json
and jsonb
values will be automatically
converted to Python objects.
Note
If you are using the PostgreSQL json
data type but you want to read
it as string in Python instead of having it parsed, your can either cast
the column to text
in the query (it is an efficient operation, that
doesn’t involve a copy):
cur.execute("select jsondata::text from mytable")
or you can register a no-op loads()
function with
register_default_json()
:
psycopg2.extras.register_default_json(loads=lambda x: x)
Note
You can use register_adapter()
to adapt any Python
dictionary to JSON, either registering Json
or any subclass or factory
creating a compatible adapter:
psycopg2.extensions.register_adapter(dict, psycopg2.extras.Json)
This setting is global though, so it is not compatible with similar
adapters such as the one registered by register_hstore()
. Any other
object supported by JSON can be registered the same way, but this will
clobber the default adaptation rule, so be careful to unwanted side
effects.
If you want to customize the adaptation from Python to PostgreSQL you can
either provide a custom dumps()
function to Json
:
curs.execute("insert into mytable (jsondata) values (%s)",
[Json({'a': 100}, dumps=simplejson.dumps)])
or you can subclass it overriding the dumps()
method:
class MyJson(Json):
def dumps(self, obj):
return simplejson.dumps(obj)
curs.execute("insert into mytable (jsondata) values (%s)",
[MyJson({'a': 100})])
Customizing the conversion from PostgreSQL to Python can be done passing a
custom loads()
function to register_json()
. For the builtin data types
(json
from PostgreSQL 9.2, jsonb
from PostgreSQL 9.4) use
register_default_json()
and register_default_jsonb()
. For example, if you
want to convert the float values from json
into
Decimal
you can use:
loads = lambda x: json.loads(x, parse_float=Decimal)
psycopg2.extras.register_json(conn, loads=loads)
Or, if you want to use an alternative JSON module implementation, such as the faster UltraJSON, you can use:
psycopg2.extras.register_default_json(loads=ujson.loads, globally=True)
psycopg2.extras.register_default_jsonb(loads=ujson.loads, globally=True)
Hstore data type¶
New in version 2.3.
The hstore
data type is a key-value store embedded in PostgreSQL. It has
been available for several server versions but with the release 9.0 it has
been greatly improved in capacity and usefulness with the addition of many
functions. It supports GiST or GIN indexes allowing search by keys or
key/value pairs as well as regular BTree indexes for equality, uniqueness etc.
Psycopg can convert Python dict
objects to and from hstore
structures.
Only dictionaries with string/unicode keys and values are supported. None
is also allowed as value but not as a key. Psycopg uses a more efficient hstore
representation when dealing with PostgreSQL 9.0 but previous server versions
are supported as well. By default the adapter/typecaster are disabled: they
can be enabled using the register_hstore()
function.
Composite types casting¶
New in version 2.4.
Using register_composite()
it is possible to cast a PostgreSQL composite
type (either created with the CREATE TYPE
command or implicitly defined
after a table row type) into a Python named tuple, or into a regular tuple if
collections.namedtuple()
is not found.
>>> cur.execute("CREATE TYPE card AS (value int, suit text);")
>>> psycopg2.extras.register_composite('card', cur)
<psycopg2.extras.CompositeCaster object at 0x...>
>>> cur.execute("select (8, 'hearts')::card")
>>> cur.fetchone()[0]
card(value=8, suit='hearts')
Nested composite types are handled as expected, provided that the type of the composite components are registered as well.
>>> cur.execute("CREATE TYPE card_back AS (face card, back text);")
>>> psycopg2.extras.register_composite('card_back', cur)
<psycopg2.extras.CompositeCaster object at 0x...>
>>> cur.execute("select ((8, 'hearts'), 'blue')::card_back")
>>> cur.fetchone()[0]
card_back(face=card(value=8, suit='hearts'), back='blue')
Adaptation from Python tuples to composite types is automatic instead and requires no adapter registration.
Note
If you want to convert PostgreSQL composite types into something different
than a namedtuple
you can subclass the CompositeCaster
overriding
make()
. For example, if you want to convert your type
into a Python dictionary you can use:
>>> class DictComposite(psycopg2.extras.CompositeCaster):
... def make(self, values):
... return dict(zip(self.attnames, values))
>>> psycopg2.extras.register_composite('card', cur,
... factory=DictComposite)
>>> cur.execute("select (8, 'hearts')::card")
>>> cur.fetchone()[0]
{'suit': 'hearts', 'value': 8}
Range data types¶
New in version 2.5.
Psycopg offers a Range
Python type and supports adaptation between them and
PostgreSQL range
types. Builtin range
types are supported out-of-the-box;
user-defined range
types can be adapted using register_range()
.
The following Range
subclasses map builtin PostgreSQL range
types to
Python objects: they have an adapter registered so their instances can be
passed as query arguments. range
values read from database queries are
automatically casted into instances of these classes.
Note
Python lacks a representation for infinity
date so Psycopg converts
the value to date.max
and such. When written into the database these
dates will assume their literal value (e.g. 9999-12-31
instead of
infinity
). Check Infinite dates handling for an example of
an alternative adapter to map date.max
to infinity
. An
alternative dates adapter will be used automatically by the DateRange
adapter and so on.
Custom range
types (created with CREATE TYPE
... AS RANGE
) can be
adapted to a custom Range
subclass:
UUID data type¶
New in version 2.0.9.
Changed in version 2.0.13: added UUID array support.
>>> psycopg2.extras.register_uuid()
<psycopg2._psycopg.type object at 0x...>
>>> # Python UUID can be used in SQL queries
>>> import uuid
>>> my_uuid = uuid.UUID('{12345678-1234-5678-1234-567812345678}')
>>> psycopg2.extensions.adapt(my_uuid).getquoted()
"'12345678-1234-5678-1234-567812345678'::uuid"
>>> # PostgreSQL UUID are transformed into Python UUID objects.
>>> cur.execute("SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid")
>>> cur.fetchone()[0]
UUID('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11')
Networking data types¶
By default Psycopg casts the PostgreSQL networking data types (inet
,
cidr
, macaddr
) into ordinary strings; array of such types are
converted into lists of strings.
Changed in version 2.7: in previous version array of networking types were not treated as arrays.
>>> psycopg2.extras.register_inet()
<psycopg2._psycopg.type object at 0x...>
>>> cur.mogrify("SELECT %s", (Inet('127.0.0.1/32'),))
"SELECT E'127.0.0.1/32'::inet"
>>> cur.execute("SELECT '192.168.0.1/24'::inet")
>>> cur.fetchone()[0].addr
'192.168.0.1/24'
Fast execution helpers¶
The current implementation of executemany()
is (using an extremely
charitable understatement) not particularly performing. These functions can
be used to speed up the repeated execution of a statement against a set of
parameters. By reducing the number of server roundtrips the performance can be
orders of magnitude better than using executemany()
.
Note
execute_batch()
can be also used in conjunction with PostgreSQL
prepared statements using PREPARE
, EXECUTE
, DEALLOCATE
.
Instead of executing:
execute_batch(cur,
"big and complex SQL with %s %s params",
params_list)
it is possible to execute something like:
cur.execute("PREPARE stmt AS big and complex SQL with $1 $2 params")
execute_batch(cur, "EXECUTE stmt (%s, %s)", params_list)
cur.execute("DEALLOCATE stmt")
which may bring further performance benefits: if the operation to perform
is complex, every single execution will be faster as the query plan is
already cached; furthermore the amount of data to send on the server will
be lesser (one EXECUTE
per param set instead of the whole, likely
longer, statement).