Connecting Python to PostgreSQL Database
Published: 2022-04-01 13:41:44

Hi. In this article, I'm going to explian how to connect a Python app to PostgreSQL database.
I should be quick to mention that 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:
`Log in to your account using SSH`

To create a virtual environment, type the following command:
   `virtualenv connectDB`

To activate the virtual environment, type the following command:
  `source connectDB/bin/activate`

The command prompt now starts (connectDB) to indicate that all the operations are now in the Python virtual environment.
We will then update pip before we use to install psycopg2.

To update pip, enter the following command:
     `pip install -U pip`

To install psycopg2 package, enter the following command:
    `pip install psycopg2`

To install pygresql package, type the following command:
    `pip install pygresql`

Having installed PostgreSQL packages in the virtual environment, we are ready to work with actual databases. Let's now demonstrate how to connect to a database with the following sample Python code and switch between the different SQL package implementations using the portable SQL database API. 

```
#!/usr/bin/python 
from __future__ import print_function 
hostname = 'localhost' 
username = 'username' 
password = 'password' 
database = 'dbname'
```

```
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).
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.
It is clear, from the above code sample, that Python's portable SQL database API makes it easy to switch between PostgreSQL modules in your code. We only twerk with import and connect to use a different module.
I really hope this was helpful