Psycopg2 Tutorial
Installing Psycopg2 for Python Beginners
This article does not seek to fully address the installation of psycopg2 or management of Python packages, but begins by taking a moment to review basic Python needs in order to follow this tutorial including running the code.
Python is available on major operating systems such as Windows, macOS and distros of Linux.
Python is also preinstalled on many operating systems, but in some cases the default is Python 2.
Unless there is a specific reason to use Python 2 (such as legacy installers) it should be avoided and Python 3 should be used. However, the range of "Python 3" versions has become significant with differences across minor versions. Since the default Python 3 on operating systems also varies, a best practice is to avoid using the default Python interpreters on operating systems.
Therefore, rely on a Python interpreter which is installed and configured independent of the OS installs. Python made available by an environment manager or an IDE are great ways to accomplish this, in addition to the direct install. This article encourages the use of a Python managers but makes no recommendations in particular.
With a Python 3 interpreter the recommended method of package installation is pip. The default pip install will rely on the Python Package Index (PyPI) to install the psycogp2 library, featured below for convenience:
pip install psycopg2
Readers of this article are also encouraged to recognize that psycopg3 is now available, and that this article id distinctly psycopg2 only.
Access PostgreSQL with Python
PostgreSQL is a popular Open Source database that has Application Programming Interfaces for both popular and less used programming languages.
Python is very popular and used for many purposes including interacting with PostgreSQL.
There are a few fantastic drivers for Python but Psycopg2 is the most popular. Recently Psycopg3 has been released but 2 remains dominant in use and this tutorial only covers Psycopg2 for Python 3.
Psycopg2 is a DB API 2.0 compliant PostgreSQL driver that is actively developed. It is designed for multi-threaded applications and manages its own connection pool.
This article discusses basics:
- Connect to PostgreSQL with Psycopg2
- Executing basic queries
- Transaction scopes
- Use of Python dictionaries
This article presumes Python, Psycopg2, PostgreSQL are available, with a user dbuser capable of creating databases and tables. Substitute your own connection parameters accordingly. The test platform for this article is Python 3.10.12, Psycopg2 2.9.3 (relying on libpq 12.15), and PostgreSQL 14.8.
The following code snippets make use of Python 3 idioms, but are intended to be simple and illustrative.
Import Psycopg2 and Connect
#!/usr/bin/env python
#
# Small script to show PostgreSQL and Pyscopg together
#
import psycopg2
try:
conn = psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")
except:
print("I am unable to connect to the database")
The above will import the driver and try to connect to the database. If the connection fails a print statement to STDOUT will occur. If there is no output to your Python interpreter then the connection was successful.
Simple Queries and Cursors
The next step is to define a cursor to work with. It is important to note that Psycopg2 cursors are not the same as cursors used in PL/pgSQL.
We begin with the same simple connection method as before. We then create a cursor from the connection using a Python Context Manager. The cursor has the execute method that can send a statement to the database. When the with context manager approach is used, some aspects of handling the transaction are not explicit. For example, when the syntactic with block is done the cursor is closed.
#!/usr/bin/env python
#
# Small script to show Pyscopg2 cursor
#
import psycopg2
try:
conn = psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")
except:
print("I am unable to connect to the database")
# we use a context manager to scope the cursor session
with conn.cursor() as curs:
try:
# simple single row system query
curs.execute("SELECT version()")
# returns a single row as a tuple
single_row = curs.fetchone()
# use an f-string to print the single tuple returned
print(f"{single_row}")
# simple multi row system query
curs.execute("SELECT query, backend_type FROM pg_stat_activity")
# a default install should include this query and some backend workers
many_rows = curs.fetchmany(5)
# use the * unpack operator to print many_rows which is a Python list
print(*many_rows, sep = "\n")
# a more robust way of handling errors
except (Exception, psycopg2.DatabaseError) as error:
print(error)
Rely on the inline comments for some line by line documentation.
The other portion of this section is the example fetch methods. They illustrate selecting a single, or a parameter driven number of results from the given SQL statements executed. The first fetchone returns a Python tuple, and that the fetchmany (and fetchall) return a list of tuples that represent the rows returned from the database. Both can be used to loop through results as best fit for the application.
Transactions
Transactions are an important feature. The configuration of database isolation should be considered carefully in order to get the optimal balance of consistency and performance. This tutorial does not cover this subject in depth, but highlights a key distinction between data transactions and operations which impact the database catalog.
Operations such as CREATE DATABASE cannot be executed within a transaction block. See PostgreSQL docs, and keep this in mind when using Python context managers, which handle session transaction COMMIT and ROLLBACK.
In order to complete data catalog operations, the AUTOCOMMIT setting allows for commands to be submitted accordingly.
#!/usr/bin/env python
#
# Small script to show Pyscopg2 cursor
#
import psycopg2
conn = psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")
print(f"Autocommit: {conn.autocommit} and Isolation Level: {conn.isolation_level}")
# change the behavior of commit
conn.autocommit = True
print(f"Autocommit: {conn.autocommit} and Isolation Level: {conn.isolation_level}")
with conn.cursor() as cur:
try:
cur.execute("CREATE DATABASE dbtest")
except (Exception, psycopg2.DatabaseError) as error:
print(error)
conn.close()
# to use the new database we create a new connection
dbtest_conn = psycopg2.connect("dbname='dbtest' user='dbuser' host='localhost' password='dbpass'")
with dbtest_conn:
with dbtest_conn.cursor() as dbtest_curs:
try:
# create a table with a multi-line Python string surrounded by 3 double quotes
dbtest_curs.execute("""
CREATE TABLE postgresqldotorg (
page_id SERIAL PRIMARY KEY,
page_name VARCHAR(255) NOT NULL
)
""")
except (Exception, psycopg2.DatabaseError) as error:
print(error)
with dbtest_conn.cursor() as dbtest_curs:
try:
dbtest_curs.execute("INSERT INTO postgresqldotorg ( page_name ) VALUES ( 'psycopg2_tutorial' )")
dbtest_curs.execute("SELECT * FROM postgresqldotorg")
dbtest_row = dbtest_curs.fetchone()
print(f"{dbtest_row}")
dbtest_curs.execute("INSERT INTO postgresqldotorg ( page_name ) VALUES ( 'psycopg3_tutorial' )")
dbtest_curs.execute("INSERT INTO postgresqldotorg ( page_name ) VALUES ( 'postgresqltutorial.com' )")
dbtest_curs.execute("INSERT INTO postgresqldotorg ( page_name ) VALUES ( 'psycopg2.org' )")
dbtest_curs.execute("SELECT * FROM postgresqldotorg")
dbtest_rows = dbtest_curs.fetchall()
print(f"{dbtest_rows}")
except (Exception, psycopg2.DatabaseError) as error:
print(error)
Access with Python Dictionaries
A convenient feature of psycopg is the ability to access data by the column name.
The snippet below illustrates accessing the results of the SELECT statement by column.
#!/usr/bin/env python
#
# Small script to show Pyscopg2 cursor with dictionary
#
import psycopg2
# note the extras import
import psycopg2.extras
try:
conn = psycopg2.connect("dbname='dbtest' user='dbuser' host='localhost' password='dbpass'")
except:
print("I am unable to connect to the database")
# pass the DictCursor factory to the cursor
with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as curs:
try:
curs.execute("SELECT * FROM postgresqldotorg")
pg_rows = curs.fetchall()
# loop on the results
for pg_row in pg_rows:
# access the column by name
print(f"{pg_row['page_name']}")
except (Exception, psycopg2.DatabaseError) as error:
print(error)
Resources
This article was originally published as Accessing PostgreSQL with Python and Psycopg2: The combination of Python and PostgreSQL is potent, particularly when you use the Psycopg2 driver. By Joshua D. Drake. August 26, 2005
