Psycopg: PostgreSQL adapter for Python

PostgreSQL is one the most popular open source object-relational database system & heavily being used in industry. On the other hand python is widely used multiple programming paradigm language. If you want to work in PostgreSQL using python, the best choice would be Psycopg. Psycopg is the most popular PostgreSQL adapter for python. It fully implements Python DB API 2.0. Psycopg2 is designed for multi-threaded applications and maintaines its own connection pool.
One of nice feature that psycopg2 driver supports, it matches Python objects to the PostgreSQL data types e.g list to the array, tuples to records, and dictionary to hstore. The following tutorials will demonstrate basic usage of psycopg.

# Python 3.5
# Connecting to PostgreSQL database from python 

# importing psycopg2 module
import psycopg2

try:
    conn = psycopg2.connect("dbname='tempdb' user='postgres' host='localhost' password='postgres'")
except Exception as ex:
    print("Error: unable to connect to the database",ex )

# we got connection 
conn

<connection object at 0x7f93202a5e88; dsn: 'user=postgres host=localhost dbname=tempdb password=xxx', closed: 0>

After connecting to database, we need to create cursor by the connection.cursor() method. And they are bound to the connection for the entire lifetime.

# Creating cursor
cur = conn.cursor()

Now after having the cursor defined we can execute a query by cursor.execute() function.

# Let's fetch 5 rows from test table
cur.execute("SELECT * FROM test LIMIT 5;")

To get the results returned by query there are multiple functions available in psycopg2 based on return values e.g fetchall()(), fetchone() & fetchmany()

# Let's get all the data retrieved by query
cur.fetchall()

[(1, 122, 5.0), (1, 185, 5.0), (1, 231, 5.0), (1, 292, 5.0), (1, 316, 5.0)]

As you can see above psycopg2 automatically converts data into python built-in list.

Now Let's create a new table in given database.

# Creating new table using SQL query

tablename = "ratings"
USER_ID_COLNAME = 'userId'
MOVIE_ID_COLNAME = 'movieId'
RATING_COLNAME = 'rating'

try:
    query = "CREATE TABLE {0} ( {1} integer, {2} integer,{3} real);".format(tablename,
                                                        USER_ID_COLNAME,
                                                        MOVIE_ID_COLNAME,
                                                        RATING_COLNAME)
    cur.execute(query)
except Exception as ex:
        print("Failed to create table: ",ex)

print("Created table:  ", tablename)

Created table:   ratings

Table created sucessfully .! Now let's add some data. psycopg2 supports PostgreSQL's COPY command.

# Adding data from csv file using COPY method of PostgreSQL
# datafile is sample.csv

tablename = "ratings"
with open('sample.csv') as datafile:
    try:
        cur.copy_from(datafile,tablename )
        conn.commit()
    except Exception as ex:
        print("Failed to copy file in database: ",ex)

Now let's fetch added data

# Running select query and feching data 
cur.execute("SELECT * FROM ratings LIMIT 5;")
rows = cur.fetchall()
rows

[(1, 122, 5.0), (1, 185, 5.0), (1, 231, 5.0), (1, 292, 5.0), (1, 316, 5.0)]

Let's count the the number of rows in table. Here we will use fetchone()

# count the number of rows

tablename = "ratings"
query = "SELECT count(*) FROM {0}".format(tablename)
cur.execute(query)
count = int(cur.fetchone()[0])
count

Similarly let's count number of tables

# counting no of tables in schema
query = " SELECT COUNT(table_name) FROM information_schema.tables WHERE table_schema = 'public' "                                                                         
cur.execute(query)
count = int(cur.fetchone()[0])
count

Let's delete perticular table.

# deleting ratings table 
RATINGS_TABLE = 'ratings'
cur.execute('DROP TABLE IF EXISTS {0} CASCADE'.format(RATINGS_TABLE))
cur.commit()

PostgreSQL does not have an autocommit option which means all the queries will have to be executed within a transaction. This functionality ensures data integrity and allows for appropriate error handling but there are queries that can not be run from within a transaction. Let's see example.

try:
    cur.execute("DROP DATABASE tempd")
except Exception as ex:
    print("Failed to drop our test database!",ex)

Failed to drop our test database! DROP DATABASE cannot run inside a transaction block

PostgreSQL can not drop databases within a transaction, if you want to drop database within a transaction then you need to change the isolation level of the database as follows.

conn.set_isolation_level(0)

This was short introduction to psycopg, I hope you have enjoyed. See you next time.

Psycopg: PostgreSQL adapter for Python

References