Python Database PostgreSQL

In this article you will learn how to use the PostgreSQL database with Python. PostgreSQL is an relational database management system (rdbms). PostgreSQL supports foreign keys, joins, views, triggers, stored procedures and much more.

Related course
Master SQL Databases with Python

Installation

Install PostgreSQL
For this tutorial you will need the PostgreSQL dbms and the psycopg2 module.
On an Ubuntu system you can install the PostgreSQL database system with this command:

sudo apt-get install postgresql postgresql-contrib

Test if the PostgreSQL database system is up and running with this command:

sudo /etc/init.d/postgresql status

PostreSQL database status

If you do not see the above screen, try one of these commands:

sudo service postgresql start
sudo service postgresql restart

Install psycopg2
Psycopg is a PostgreSQL database adapter for Python.
This command installs the module:

sudo apt-get install python-psycopg2

We create a database and database user (also called a role)

sudo -u postgres createuser -D -A -P pythonspot
sudo -u postgres createdb -O pythonspot testdb

Reload the database:

sudo /etc/init.d/postgresql reload

Python PostgreSQL

Create table and Insert data
Run this program :

#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

try:
    con = psycopg2.connect("host='localhost' dbname='testdb' user='pythonspot' password='password'")   
    cur = con.cursor()
    cur.execute("CREATE TABLE Products(Id INTEGER PRIMARY KEY, Name VARCHAR(20), Price INT)")
    cur.execute("INSERT INTO Products VALUES(1,'Milk',5)")
    cur.execute("INSERT INTO Products VALUES(2,'Sugar',7)")
    cur.execute("INSERT INTO Products VALUES(3,'Coffee',3)")
    cur.execute("INSERT INTO Products VALUES(4,'Bread',5)")
    cur.execute("INSERT INTO Products VALUES(5,'Oranges',3)")
    con.commit()
except psycopg2.DatabaseError, e:
    if con:
        con.rollback()
    
    print 'Error %s' % e    
    sys.exit(1)
    
finally:   
    if con:
        con.close()

It will create a database table (this datastructure holds the data). Data is inserted into the table using:

cur.execute("INSERT INTO Products VALUES(5,'Oranges',3)")

The line below is mandatory, it executes all sql queries:

con.commit()

The program pgadmin3 can be used to graphically explore the database. In this example we see the data we have inserted with the program.

Read data
Data can be read using the SELECT SQL query. A list is returned for every row:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

try:
    con = psycopg2.connect("host='localhost' dbname='testdb' user='pythonspot' password='password'")   
    cur = con.cursor()
    cur.execute("SELECT * FROM Products")

    while True:
        row = cur.fetchone()

        if row == None:
            break

        print("Product: " + row[1] + "\t\tPrice: " + str(row[2]))

except psycopg2.DatabaseError, e:
    if con:
        con.rollback()
    
    print 'Error %s' % e    
    sys.exit(1)
    
finally:   
    if con:
        con.close()

Update data
PostgreSQL table data can be updated with this code:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

try:
    con = psycopg2.connect("host='localhost' dbname='testdb' user='pythonspot' password='password'")   
    cur = con.cursor()
    cur.execute("UPDATE Products SET Price=%s WHERE Id=%s", (10, 4))      
    con.commit()
except psycopg2.DatabaseError, e:
    if con:
        con.rollback()
    
    print 'Error %s' % e    
    sys.exit(1)
    
finally:   
    if con:
        con.close()

Delete data
Delete data from a PostgreSQL table with this code:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

try:
    con = psycopg2.connect("host='localhost' dbname='testdb' user='pythonspot' password='password'")   
    cur = con.cursor()
    cur.execute("DELETE FROM Products WHERE Id=" + str(4))      
    con.commit()
except psycopg2.DatabaseError, e:
    if con:
        con.rollback()
    
    print 'Error %s' % e    
    sys.exit(1)
    
finally:   
    if con:
        con.close()

BackNext