Python の基本型に適応する#
多くの標準の Python 型は、クエリが実行されたときに SQL に適応され、Python オブジェクトを返します。
以下のデータ型の Python と PostgreSQL 間での変換は、はじめから動作するため、何の設定も必要ありません。変換をカスタマイズする必要がある場合には、データ適応の設定 を参照してください。
ブール値の適応#
Python の bool
値である True
と False
は、次のように同等の PostgreSQL のブール型 に変換されます。
>>> cur.execute("SELECT %s, %s", (True, False))
# "SELECT true, false" と同等
バージョン 3.2 で変更: numpy.bool_
の値もダンプできるようになりました。
数値の適応#
Python の
int
の値は、数値に応じて PostgreSQL のsmallint
、integer
、bigint
、numeric
のいずれかに変換できます。psycopg は利用できる最も小さなデータ型を選択します。PostgreSQL は自動的に大きな型にキャスト アップできる一方 (たとえば、PostgreSQL がinteger
を期待するところにsmallint
を渡すと喜んで受け取ってくれます)、小さな型に自動的にキャスト ダウンすることはないためです (たとえば、関数にinteger
引数がある場合、たとえ値が 1 だったとしても、それをbigint
の値に渡すと失敗します)。
Python の
float
の値は PostgreSQL のfloat8
に変換されます。
Python の
Decimal
の値は PostgreSQL のnumeric
に変換されます。
レスポンス時には、小さな型 (int2
、int4
、float4
) は、対応する Python のより大きな型に昇格されます。
注釈
性能上の理由や操作を簡単にするために、numeric
のデータを代わりに float
として受け取りたい場合もあるかもしれません。その場合、PostgreSQL の数値を Python の float にキャストする ためにアダプターを設定できます。この操作はもちろん、精度の劣化を引き起こしてしまいます。
バージョン 3.2 で変更: NumPy の integer と floating point の値もダンプできるようになりました。
文字列の適応#
Python の str
は PostgreSQL の文字列構文に変換され、PostgreSQL の text
や varchar
などの型は Python の str
に再度変換されます。
conn = psycopg.connect()
conn.execute(
"INSERT INTO menu (id, entry) VALUES (%s, %s)",
(1, "Crème Brûlée at 4.99€"))
conn.execute("SELECT entry FROM menu WHERE id = 1").fetchone()[0]
'Crème Brûlée at 4.99€'
PostgreSQL データベースには エンコーディングがあり、セッションにもエンコーディングがあります。これらは`!Connection.info.`encoding
属性で公開されています。データベースとコネクションが UTF-8 エンコーディングの場合、おそらく何も問題はないでしょう。それ以外のエンコーディングを使用している場合、アプリケーションがデータベースが処理できる non-ASCII 文字だけを扱うことを保証する必要があります。正しく扱わなかった場合、エンコード/デコードでエラーが発生してしまうかもしれません。
# エンコーディングは、データベース設定にしたがってコネクション時に設定されます
conn.info.encoding
'utf-8'
# Latin-9 エンコーディングは一部のヨーロッパ系言語のアクセント付き文字とユーロ記号を管理できます
conn.execute("SET client_encoding TO LATIN9")
conn.execute("SELECT entry FROM menu WHERE id = 1").fetchone()[0]
'Crème Brûlée at 4.99€'
# Latin-1 エンコーディングにはユーロ記号に対応する表現がありません
conn.execute("SET client_encoding TO LATIN1")
conn.execute("SELECT entry FROM menu WHERE id = 1").fetchone()[0]
# Traceback (most recent call last)
# ...
# UntranslatableCharacter: character with byte sequence 0xe2 0x82 0xac
# in encoding "UTF8" has no equivalent in encoding "LATIN1"
稀なケースでは、予期しないエンコーディングの文字列がデータベースに保存されているかもしれません。SQL_ASCII
クライアントエンコーディングを使用すると、データベースから送られてきたデータのデコードを無効化して、bytes
を返せます。
conn.execute("SET client_encoding TO SQL_ASCII")
conn.execute("SELECT entry FROM menu WHERE id = 1").fetchone()[0]
b'Cr\xc3\xa8me Br\xc3\xbbl\xc3\xa9e at 4.99\xe2\x82\xac'
代わりに、未知のエンコーディングのデータを bytea
にキャストして bytes として取得することもできます。これにより、他の文字列は変換せずにすみます。詳細は バイナリの適応 を参照してください。
PostgreSQL のテキストは 0x00
バイトを含めないことに注意してください。バイナリの 0 を含む Python 文字列を保存する必要がある場合、bytea
フィールドを使う必要があります。
バイナリの適応#
バイナリオブジェクトを表現する Python の型 (bytes
、bytearray
、memoryview
) はデフォルトで bytea
フィールドに変換されます。受信されたデータは、デフォルトでは bytes
として返されます。
大きなバイナリデータ (バイナリドキュメントや画像など) を bytea フィールドに保存する場合、おそらくバイナリ形式を使用して値を渡したり返したりする必要があるでしょう。そうしなればバイナリデータに ASCII エスケーピング が行われ、ある程度の CPU 時間とより大きなバンド幅が消費されてしまいます。詳細は、バイナリ パラメータと結果 を参照してください。
Date/time types adaptation#
Python
date
objects are converted to PostgreSQLdate
.Python
datetime
objects are converted to PostgreSQLtimestamp
(if they don't have atzinfo
set) ortimestamptz
(if they do).Python
time
objects are converted to PostgreSQLtime
(if they don't have atzinfo
set) ortimetz
(if they do).Python
timedelta
objects are converted to PostgreSQLinterval
.
PostgreSQL timestamptz
values are returned with a timezone set to the
connection TimeZone setting, which is available as a Python
ZoneInfo
object in the Connection.info
.timezone
attribute:
>>> conn.info.timezone
zoneinfo.ZoneInfo(key='Europe/London')
>>> conn.execute("select '2048-07-08 12:00'::timestamptz").fetchone()[0]
datetime.datetime(2048, 7, 8, 12, 0, tzinfo=zoneinfo.ZoneInfo(key='Europe/London'))
注釈
PostgreSQL timestamptz
doesn't store "a timestamp with a timezone
attached": it stores a timestamp always in UTC, which is converted, on
output, to the connection TimeZone setting:
>>> conn.execute("SET TIMEZONE to 'Europe/Rome'") # UTC+2 in summer
>>> conn.execute("SELECT '2042-07-01 12:00Z'::timestamptz").fetchone()[0] # UTC input
datetime.datetime(2042, 7, 1, 14, 0, tzinfo=zoneinfo.ZoneInfo(key='Europe/Rome'))
Check out the PostgreSQL documentation about timezones for all the details.
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).
注釈
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 PostgreSQLinet
type. On the way back,inet
values indicating a single address are converted toIPv4Address
, otherwise they are converted toIPv4Interface
IPv4Network
objects are converted to thecidr
type and back.IPv6Address
,IPv6Interface
,IPv6Network
objects follow the same rules, with IPv6inet
andcidr
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#
バージョン 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).バージョン 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 anint
than anEnum
, so it's dumped by default according toint
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 usingregister()
.
If the enum is registered (using
EnumInfo
.fetch()
andregister_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 ofTypeInfo
: refer to the latter's documentation for generic usage, especially thefetch()
method.- 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)#
Register the adapters to load and dump a enum type.
- パラメータ:
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 asEnumInfo.enum
.mapping -- Override the mapping between
enum
members andinfo
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 newEnum
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'>