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
Practice Python with interactive exercises

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] + "Price: " + 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()

Back Next