Connecting to PostgreSQL using Python

​

Connecting to PostgreSQL using Python

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

​

Setting up the Python virtual environment and installing a PostgreSQL package

1. Log in to your account using SSH.
2. At the command prompt, type the following command:
   cd ~
   
3. To create a virtual environment, type one of the following commands:
   • If you are running Python 3.x, type the following command:
     python3 -m venv sqlenv
     
   • If you are running Python 2.x, type the following command:
     virtualenv sqlenv
     

   📘 Note 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.

4. To activate the virtual environment, type the following command:
   source sqlenv/bin/activate
   

   📘 Note 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.

5. To update pip in the virtual environment, type the following command:
   pip install -U pip
   
6. 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

#!/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()

​

Code sample using the legacy pg module

#!/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()

​

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.

​

Related articles

• Using virtualenv and pip
• Connecting to PostgreSQL using psql
• Connecting to PostgreSQL using PHP