python postgresql

Learn how to effectively utilize Python with the powerful PostgreSQL database. PostgreSQL, a robust relational database management system (RDBMS), boasts of features like foreign keys, joins, views, triggers, stored procedures, and much more. By the end of this guide, you’ll be familiar with the essentials of integrating PostgreSQL with Python.

Relevant Course: Master SQL Databases with Python

Setting Up PostgreSQL for Python

Install PostgreSQL

To commence, ensure you have both the PostgreSQL DBMS and the psycopg2 Python module. For those on an Ubuntu system, you can swiftly install the PostgreSQL using the following:

sudo apt-get install postgresql postgresql-contrib

To confirm the successful installation and running status of PostgreSQL, employ:

sudo /etc/init.d/postgresql status

If you’re not greeted with the expected screen, the commands below might come in handy:

sudo service postgresql start
sudo service postgresql restart

Install psycopg2

Psycopg serves as the bridge connecting Python to PostgreSQL. To get it on board, use:

sudo apt-get install python-psycopg2

Next, set up a database and its user, also referred to as a role, with these commands:

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

A quick reload ensures everything is in order:

sudo /etc/init.d/postgresql reload

Python and PostgreSQL in Action

Creating Tables and Populating Them

Run the below script to create a database table and fill it with some initial data:

#!/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 as e:
    if con:
        con.rollback()
    print(f'Error: {e}')
    sys.exit(1)
finally:   
    if con:
        con.close()

It’s vital to execute all SQL queries for changes to take effect:

con.commit()

Reading Data

Fetch data using the SELECT SQL command. The method returns a list for every data 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 is None:
            break
        print(f"Product: {row[1]}\t\tPrice: {row[2]}")
except psycopg2.DatabaseError as e:
    if con:
        con.rollback()
    print(f'Error: {e}')
    sys.exit(1)
finally:   
    if con:
        con.close()

Updating Data

Modify data within a PostgreSQL table using the following:

#!/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 as e:
    if con:
        con.rollback()
    print(f'Error: {e}')
    sys.exit(1)
finally:   
    if con:
        con.close()

Deleting Data

Erase data from your PostgreSQL table using:

#!/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 as e:
    if con:
        con.rollback()
    print(f'Error: {e}')
    sys.exit(1)
finally:   
    if con:
        con.close()

Navigate through more Python database interactions: Back | Next