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
Top comments (0)