PostgreSQL & Python

These notes describe how to use the psycopg2 Python library to interact with a PostgreSQL database. The psycopg2 library can be installed with pip install psycopg2 at the command line.

Using Psycopg2

By convention, psycopg2 is imported as pg2.

import psycopg2 as pg2

Next, connect to the database. By convention, that is conn.

secret = # your password
conn = pg2.connect(database='dvdrental', user='postgres', password=secret)

Now, the connection is established. The next step is retrieving the cursor. A cursor is a control structure that enables traversal over database records. It is like a pointer or iterator for SQL data retrieval.

cur = conn.cursor()

Those steps are all that is required to begin executing SQL.

cur.execute('SELECT * FROM payment')

The cursor has a couple methods available to return rows of data. These methods include:

  • fetchall - returns all the rows. The return type is a list of tuples, with types appropriate to the data.
  • fetchmany - takes as a parameter the number of rows to return. The return type is a list of tuples, with types appropriate to the data.
  • fetchone - returns the first row of the data. The return type is a single tuple.
data = cur.fetchmany(10)

Then, the data can be conveniently accessed using tuple unpacking, as shown below.

data[0][4]

Once the data reading process is complete, close the connection.

conn.close()

The best practice is to always manually type out the entire SQL query, as opposed to relying on string manipulation in Python, to ensure that the query being executed is what the user intends.