Psycopg 3 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.
Psycopg 3 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 explicit cast.
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"]) ... psycopg.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
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.
The behaviour with builtin data should be as expected; if you have customised the way to adapt data, or you have your own extension types, you should look at the new objects involved in adaptation.
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.
psycopg2, using the syntax with connection,
only the transaction is closed, not the connection. This behaviour is
surprising for people used to several other Python classes wrapping resources,
such as files.
In psycopg3, using with connection will close the
connection at the end of the
with block, making handling the connection
resources more familiar.
In order to manage transactions as blocks you can use the
Connection.transaction() method, which allows for finer control, for
instance to use nested transactions.
See Transaction blocks for details.
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
CALL procedure_name(...) instead.
What’s new in Psycopg 3¶
to be completed
Several data types are adapted out-of-the-box: uuid, network, range, bytea, array of any supported type are dealt with automatically.
Access to the low-level libpq functions.