How to connect to PostgreSQL using Python

This article describes how to connect to a PostgreSQL database using Python.

Connecting to PostgreSQL using Python

Before you can access PostgreSQL databases using Python, you must install one (or more) of the following packages in a virtual environment:

• psycopg2: This package contains the psycopg2 module.
• PyGreSQL: This package contains the pgdb module.

Both of these packages support Python's portable SQL database API. This means that if you switch from one module to another, you can reuse almost all of your existing code (the code sample below demonstrates how to do this).

Setting up the Python virtual environment and installing a PostgreSQL package

To set up the Python virtual environment and install a PostgreSQL package, follow these steps:

1. Log in to your account using SSH.
2. To create a virtual environment, type the following commands:

   cd ~
   virtualenv sqlenv
   

   • The virtualenv command creates a virtual environment named sqlenv, and subsequent commands in this procedure assume that the environment is named sqlenv. You can use any environment name you want, but make sure you replace all occurrences of sqlenv with your own environment name.
   • If you are running an alternate Python version (for example, you manually configured a newer Python version for your account as described in this article), you can specify that version for the virtual environment. For example, to install a user-configured version of Python 3.8 in the virtual environment, you could use the following command:

     virtualenv -p /home/username/bin/python3.8 sqlenv
     

3. To activate the virtual environment, type the following command:
   

   source sqlenv/bin/activate

   The command prompt now starts with (sqlenv) to indicate that you are working in a Python virtual environment. All of the following commands in this procedure assume that you are working within the virtual environment. If you log out of your SSH session (or deactivate the virtual environment by using the deactivate command), make sure you reactivate the virtual environment before following the steps below and running the sample code.

4. To update pip in the virtual environment, type the following command:
   

   pip install -U pip

5. Type the command for the package you want to install:
   

   • To install the psycopg2 package, type the following command:

     pip install psycopg2

   • To install the PyGreSQL package, type the following command:
     

     pip install pygresql

Code sample using Python's portable SQL database API

After you install a PostgreSQL package in the virtual environment, you are ready to work with actual databases. The following sample Python code demonstrates how to do this, as well as just how easy it is to switch between the different SQL package implementations using the portable SQL database API. The sample code works with Python 2.7 and Python 3.x.

In your own code, replace username with the PostgreSQL database username, password with the database user's password, and dbname with the database name:

#!/usr/bin/python

from __future__ import print_function

hostname = 'localhost'
username = 'username'
password = 'password'
database = 'dbname'

# Simple routine to run a query on a database and print the results:
def doQuery( conn ) :
    cur = conn.cursor()

    cur.execute( "SELECT fname, lname FROM employee" )

    for firstname, lastname in cur.fetchall() :
        print( firstname, lastname )


print( "Using psycopg2:" )
import psycopg2
myConnection = psycopg2.connect( host=hostname, user=username, password=password, dbname=database )
doQuery( myConnection )
myConnection.close()

print( "Using PyGreSQL (pgdb):" )
import pgdb
myConnection = pgdb.connect( host=hostname, user=username, password=password, database=database )
doQuery( myConnection )
myConnection.close()

This example creates a series of Connection objects that opens the same database using different PostgreSQL modules. Because both of these modules use the portable SQL database API interface, they are able to use the code in the doQuery() function without any modifications.

When you have a Connection object associated with a database, you can create a Cursor object. The Cursor object enables you to run the execute() method, which in turn enables you to run raw SQL statements (in this case, a SELECT query on a table named employee).

Code sample using the legacy pg module

The PyGreSQL package also includes a legacy pg module that you can use to connect to PostgreSQL. Although it is easy to use, it does not implement Python's portable SQL database API.

The following code sample demonstrates how to use the pg module to connect to a PostgreSQL database. Replace username with the PostgreSQL database username, password with the database user's password, and dbname with the database name:

#!/usr/bin/python

from __future__ import print_function

import pg

conn = pg.DB( host="localhost", user="username", passwd="password", dbname="dbname" )

result = conn.query( "SELECT fname, lname FROM employee" )

for firstname, lastname in result.getresult() :
    print( firstname, lastname )

conn.close()

This example creates a Connection object that opens the PostgreSQL database using the specified parameters. Once you have a Connection object associated with the database, you can query the database directly using raw SQL statements (in this case, a SELECT query on a table named employee). The getresult() method reads the result data returned by the query. Finally, the close() method closes the connection to the database.

More Information

• For more information about Python's portable SQL database API, please visit https://www.python.org/dev/peps/pep-0249.
• For more information about the psycopg2 package, please visit https://pypi.python.org/pypi/psycopg2.
• For more information about the PyGreSQL package, please visit https://pypi.python.org/pypi/PyGreSQL.