Posted on

Access Postgre DB using Psycopg library

Psycopg has a very good documentation here.

The main entry points of Psycopg are:

  • The function connect() creates a new database session and returns a new connection instance.
  • The class connection encapsulates a database session. It allows to:
    • create new cursor instances using the cursor() method to execute database commands and queries,
    • terminate transactions using the methods commit() or  rollback().
  • The class cursor allows interaction with the database:
    • send commands to the database using methods such as execute() and executemany(),
    • retrieve data from the database by iteration or using methods such as fetchone(), fetchmany(), fetchall().

Transactions control

In Psycopg transactions are handled by the connection class. By default, the first time a command is sent to the database (using one of the cursors created by the connection), a new transaction is created. The following database commands will be executed in the context of the same transaction – not only the commands issued by the first cursor, but the ones issued by all the cursors created by the same connection. Should any command fail, the transaction will be aborted and no further command will be executed until a call to the rollback() method.

The connection is responsible for terminating its transaction, calling either the commit() or rollback() method. Committed changes are immediately made persistent into the database. Closing the connection using the close() method or destroying the connection object (using del or letting it fall out of scope) will result in an implicit rollback.

It is possible to set the connection in autocommit mode: this way all the commands executed will be immediately committed and no rollback is possible. A few commands (e.g. CREATE DATABASE, VACUUM, CALL on stored procedures using transaction control…) require to be run outside any transaction: in order to be able to run these commands from Psycopg, the connection must be in autocommit mode: you can use the autocommit property.

Thread and process safety

The Psycopg module and the connection objects are thread-safe: many threads can access the same database either using separate sessions and creating a connection per thread or using the same connection and creating separate cursor

>>> import psycopg2

# Connect to an existing database
>>> conn = psycopg2.connect("dbname=test user=postgres")

# Open a cursor to perform database operations
>>> cur = conn.cursor()

# Execute a command: this creates a new table
>>> cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")

# Pass data to fill a query placeholders and let Psycopg perform
# the correct conversion (no more 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()
(1, 100, "abc'def")

# Make the changes to the database persistent
>>> conn.commit()

# Close communication with the database
>>> cur.close()
>>> conn.close()

Leave a comment