Python Database PostgreSQL – Python Tutorial

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.

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

python-database-postgresql — 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()

Installation

Python PostgreSQL

Leave a Reply Cancel reply