psycopg3 uses the common DBAPI structure of many other database adapter and
tries to behave as close as possible to
psycopg2. There are however a few
differences to be aware of.
psycopg3 sends the query and the parameters to the server separately,
instead of merging them client-side. PostgreSQL may behave slightly
differently in this case, usually throwing an error and suggesting to use an
cur.execute("select '[10,20,30]'::jsonb -> 1").fetchone() # returns (20,) cur.execute("select '[10,20,30]'::jsonb -> %s", ).fetchone() # raises an exception: # UndefinedFunction: operator does not exist: jsonb -> numeric cur.execute("select '[10,20,30]'::jsonb -> %s::int", ).fetchone() # returns (20,)
PostgreSQL will also reject the execution of several queries at once
(separated by semicolon), if they contain parameters. If parameters are used
you should use distinct
execute() calls; otherwise you may consider merging
the query client-side, using
Certain commands cannot be used with server-side binding, for instance
>>> cur.execute("SET timezone TO %s", ["utc"]) ... psycopg3.errors.SyntaxError: syntax error at or near "$1"
Sometimes PostgreSQL offers an alternative (e.g.
SELECT pg_notify()). If no alternative exist you can use
to compose the query client-side.
You cannot use
IN %s and pass a tuple, because
IN () is an SQL
construct. You must use
= any(%s) and pass a list. Note that this also
works for an empty list, whereas an empty tuple would have resulted in an
int is stricter¶
Operations involving numbers may fail if the number is passed to a function or
operator and PostgreSQL expects a
integer and not a
Examples may be
date + int,
jsonb -> int. In this case you
should use an
::int cast or the
Different adaptation system¶
The adaptation system has been completely rewritten, in order to address server-side parameters adaptation, but also to consider performance, flexibility, ease of customization.
Builtin data types should work as expected; if you have wrapped a custom data type you should check the Data adaptation configuration topic.
Copy is no more file-based¶
psycopg2 exposes a few copy methods to interact with
COPY. The interface doesn’t make easy to load
dynamically-generated data to the database.
There is now a single
copy() method, which is similar to
copy_expert() in accepting a free-form
COPY command and
returns an object to read/write data, block-wise or record-wise. The different
usage pattern also enables
COPY to be used in async interactions.
See Using COPY TO and COPY FROM for the details.
When the connection is used as context manager, at the end of the context
the connection will be closed. In
psycopg2 only the transaction is closed,
so a connection can be used in several contexts, but the behaviour is
surprising for people used to several other Python classes wrapping
resources, such as files.
callproc() is gone¶
cursor.callproc() is not implemented. The method has a simplistic
semantic which doesn’t account for PostgreSQL positional parameters,
procedures, set-returning functions. Use a normal
SELECT function_name(...) or
CALL procedure_name(...) instead.