Basic module usage¶
The basic Psycopg usage is common to all the database adapters implementing
the DB-API protocol. Other database adapters, such as the builtin
psycopg2, have roughly the same pattern of interaction.
Main objects in Psycopg 3¶
Here is an interactive session showing some of the basic commands:
# Note: the module name is psycopg, not psycopg3 import psycopg # Connect to an existing database with psycopg.connect("dbname=test user=postgres") as conn: # Open a cursor to perform database operations with conn.cursor() as cur: # Execute a command: this creates a new table cur.execute(""" CREATE TABLE test ( id serial PRIMARY KEY, num integer, data text) """) # Pass data to fill a query placeholders and let Psycopg perform # the correct conversion (no SQL injections!) cur.execute( "INSERT INTO test (num, data) VALUES (%s, %s)", (100, "abc'def")) # Query the database and obtain data as Python objects. cur.execute("SELECT * FROM test") cur.fetchone() # will return (1, 100, "abc'def") # You can use `cur.fetchmany()`, `cur.fetchall()` to return a list # of several records, or even iterate on the cursor for record in cur: print(record) # Make the changes to the database persistent conn.commit()
In the example you can see some of the main objects and methods and how they relate to each other:
Connectionclass encapsulates a database session. It allows to:
Cursorallows interaction with the database:
Using these objects as context managers (i.e. using
with) will make sure to close them and free their resources at the end of the block (notice that this is different from psycopg2).
The pattern above is familiar to
psycopg2 users. However, Psycopg 3 also
exposes a few simple extensions which make the above pattern leaner:
# In Psycopg 2 cur = conn.cursor() cur.execute(...) # In Psycopg 3 cur = conn.execute(...)
# In Psycopg 2 cur.execute(...) record = cur.fetchone() cur.execute(...) for record in cur: ... # In Psycopg 3 record = cur.execute(...).fetchone() for record in cur.execute(...): ...
Using them together, in simple cases, you can go from creating a connection to using a result in a single expression:
print(psycopg.connect(DSN).execute("SELECT now()").fetchone()) # 2042-07-12 18:15:10.706497+01:00
Connection can be used as a context manager:
with psycopg.connect() as conn: ... # use the connection # the connection is now closed
When the block is exited, if there is a transaction open, it will be committed. If an exception is raised within the block the transaction is rolled back. In both cases the connection is closed.
Adapting pyscopg to your program¶
The above pattern of use only shows the default behaviour of the adapter. Psycopg can be customised in several ways, to allow the smoothest integration between your Python program and your PostgreSQL database:
If you want to customise the objects that the cursor returns, instead of receiving tuples, you can specify your row factories.