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.
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:
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")
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()
Leave a Reply: