Python

Python PostgreSQL CRUD Operations Example

Hello in this tutorial, we will understand how to perform SQL – Crud operations to the PostgreSQL database via python programming.

1. Introduction

To connect with PostgreSQL in python programming we have the following modules that are commonly available and known to the developer world as all these modules adhere to the Python database API specification

  • Psycopg2
  • py-PostgreSQL
  • ocpgdb
  • PyGreSQL
  • pg8000

1.1 Setting up Python

If someone needs to go through the Python installation on Windows, please watch this link. You can download the Python from this link.

1.2 Setting up Psycopg2

Once the python is successfully installed on your system you can install the psycopg2 using a simple pip command. You can fire the below command from the command prompt and it will successfully download the module from pypi.org and install it.

Installation command

pip install psycopg2

1.3 Setting up Postgres database

To start with the tutorial, I am hoping that you have the Postgres up and running in your localhost environment. For easy setup, I have the database up and running on the Docker environment. You can execute the below command to get the container running on docker in minutes.

Docker command

docker run -d -p 5433:5432 -e POSTGRES_PASSWORD=password --name postgres postgres

If everything goes well the container would be started successfully as shown in Fig. 1. You can use the docker ps -a command to confirm that container is started successfully. For further information on docker basics, you can navigate to this tutorial.

Python PostgreSQL CRUD - container on docker
Fig. 1: Postgres container on Docker

2. Python PostgreSQL CRUD Operations Example

Before going any deeper in the practical let me walk you through a simple architecture diagram where it shows that wherein the psycopg2 module fits in the picture.

Python PostgreSQL CRUD - connection
Fig. 2: Python postgresql connection

For the psycopg2 module to work we will supply the following attributes to the PostgreSQL from the python application i.e.

  • user – Identity user to work with the PostgreSQL database. Default PostgreSQL user is postgres
  • password – Credential of the identity user
  • host – Address of the database server. If running on localhost then you can either use localhost or 127.0.0.1
  • port – The port number. Default PostgreSQL port is 5432
  • database – Database to which you want to connect. It can be left blank

Let us dive in with the programming stuff now.

2.1 Creating a configuration file

Add the following code to the environment file wherein we will specify the connection and database details. You are free to change these details as per your configuration setup.

local.env

[DB]
username = postgres
password = password
host = localhost
port = 5433
database =

2.2 Creating a python script to read the configuration

Add the following code to the python script which will read the configuration file created above and return the config object to be used later while connecting to the database. The script will import the configparser module for reading the configuration file. Remember to give the correct path where the local.env is created.

readdbconfig.py

import configparser
def read_db_params():
    # reading the env file
    config = configparser.ConfigParser()
    config.read('config/local.env')
    return config

2.3 Connecting to the database

Add the following code to the python script which will connect to the postgresql with the help of the psycopg2 module. If the connection is successful the script will print the database version and if not will throw an error and print the exception message on the console. The python script consists of –

  • connect() method for creating a connection to the postgresql
  • cursor() method to interact with the database and perform operations
  • execute() method to run a database query
  • fetchone() method to return a single row
  • close() method to close the cursor and connector object once the work gets done and avoid connection leak issues

connecttodb.py

# python postgresql
import psycopg2
from psycopg2 import Error
from readdbconfig import *
def connect(database_name):
    try:
        # method will read the env file and return the config object
        params = read_db_params()
        db_name = params.get('DB', 'database') if not database_name else database_name
        # connect to database
        # reading the database parameters from the config object
        conn = psycopg2.connect(
            user=params.get('DB', 'username'),
            password=params.get('DB', 'password'),
            host=params.get('DB', 'host'),
            port=params.get('DB', 'port'),
            database=db_name
        )
        return conn
    except(Exception, Error) as error:
        print(error)
def print_version(conn):
    # creating a cursor to perform database operations
    cursor = conn.cursor()
    try:
        # execute the sql query
        cursor.execute('SELECT version();')
        # fetch result
        record = cursor.fetchone()
        print('PostgreSQL version = {}'.format(record))
    except(Exception, Error) as error:
        print(error)
    finally:
        if conn is not None:
            cursor.close()
            conn.close()
            print('\nDatabase connection closed.')
# driver code
if __name__ == '__main__':
    # ops method
    print_version(connect(''))

If everything goes well the following output will be shown in the IDE console. If not the exception message will be shown.

Logs

PostgreSQL version = ('PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit',)

Database connection closed.

2.4 Create table

Add the following code to the python script which will create a table in the employee database. The python script will create a table if it does not exists. If it does the script will throw an error. Ensure that the database already exists.

createtable.py

from connecttodb import *
# create table method
def create_table(conn):
    # creating a cursor to perform a sql operation
    cursor = conn.cursor()
    # sql query
    query = '''
    CREATE TABLE employee (id INT NOT NULL, first_name VARCHAR(255), 
    last_name VARCHAR(100), email VARCHAR(150), 
    gender VARCHAR(50), phone VARCHAR(100), 
    PRIMARY KEY (id));
    '''
    try:
        # execute the command
        cursor.execute(query)
        # commit the changes
        conn.commit()
        print('Table created successfully')
    except(Exception, Error) as error:
        print(error)
    finally:
        if conn is not None:
            cursor.close()
            conn.close()
            print('\nDatabase connection closed')
# driver code
if __name__ == '__main__':
    # connect to database and create table
    create_table(connect('employee'))

If everything goes well the following output will be shown in the IDE console. If not the exception message will be shown.

Logs

Table created successfully

Database connection closed.

2.5 Insert data into the table

Add the following code to the python script which will insert the default employee-related data to the employee table. The python script will check if default data is present in the table or not. If present it will skip the insert operation. If not the default data will be inserted.

insertdata.py

from connecttodb import *
from helper import *
# insert data
def insert(conn):
    # creating a cursor to perform a sql operation
    cursor = conn.cursor()
    # sql query
    query = '''
    INSERT INTO employee (id, first_name, last_name, email, gender, phone) VALUES (%s, %s, %s, %s, %s, %s);
    '''
    try:
        count = get_records_count(cursor)
        if count > 0:
            print('Default data present. Skipping insert')
        else:
            data = [
                (1, 'Marga', 'Cronchey', 'mcronchey0@pen.io', 'F', '314-289-7265'),
                (2, 'Theda', 'Mushrow', 'tmushrow1@whitehouse.gov', 'F', '804-163-9834'),
                (3, 'Marielle', 'Bonicelli', 'mbonicelli2@sitemeter.com', 'F', '624-922-2416'),
                (4, 'Locke', 'Watkinson', 'lwatkinson3@accuweather.com', 'M', '456-260-1052'),
                (5, 'Blakelee', 'Wilcot', 'bwilcot4@twitpic.com', 'M', '608-344-4090')
            ]
            # execute the command
            cursor.executemany(query, data)
            # commit the changes
            conn.commit()
            print('{} records inserted'.format(cursor.rowcount))
    except(Exception, Error) as error:
        print(error)
    finally:
        if conn is not None:
            cursor.close()
            conn.close()
            print('\nDatabase connection closed')
# driver code
if __name__ == '__main__':
    # connect to database and insert data into the table
    insert(connect('employee'))

If everything goes well the following output will be shown in the IDE console.

Logs

5 records inserted

Database connection closed.

2.6 Get all data from the table

Add the following code to the python script which will get all the employee-related data from the employee table. The python script will check if any existing data is present in the table or not. If present it will print the data on the console. If not the following message – No data present in db will be printed on the console.

getall.py

from connecttodb import *
from helper import *
# get all records
def get_all(conn):
    # creating a cursor to perform a sql operation
    cursor = conn.cursor()
    # sql query
    query = '''SELECT * FROM employee;'''
    try:
        count = get_records_count(cursor)
        if count == 0:
            print('No data present in db')
        else:
            # execute the command
            cursor.execute(query)
            records = cursor.fetchall()
            print('EMPLOYEE INFORMATION')
            print('-------------------------------------')
            for record in records:
                full_name = record[1] + " " + record[2]
                print('Id = {}, Name = {}, Email = {}, Gender = {}, Phone = {}'.format(record[0], full_name,
                                                                                       record[3], record[4], record[5]))
    except(Exception, Error) as error:
        print(error)
    finally:
        if conn is not None:
            cursor.close()
            conn.close()
            print('\nDatabase connection closed')
# driver code
if __name__ == '__main__':
    # connect to database and get all data
    get_all(connect('employee'))

If everything goes well the following output will be shown in the IDE console.

Logs

EMPLOYEE INFORMATION
-------------------------------------
Id = 1, Name = Marga Cronchey, Email = mcronchey0@pen.io, Gender = F, Phone = 314-289-7265
Id = 2, Name = Theda Mushrow, Email = tmushrow1@whitehouse.gov, Gender = F, Phone = 804-163-9834
Id = 3, Name = Marielle Bonicelli, Email = mbonicelli2@sitemeter.com, Gender = F, Phone = 624-922-2416
Id = 4, Name = Locke Watkinson, Email = lwatkinson3@accuweather.com, Gender = M, Phone = 456-260-1052
Id = 5, Name = Blakelee Wilcot, Email = bwilcot4@twitpic.com, Gender = M, Phone = 608-344-4090

Database connection closed.

2.7 Update a record in the table

Add the following code to the python script which will update an employee record into the table. The python script will check if the employee is present in the database. If present the record will be updated. If not it will print the following message – Employee id = 5 not found will be printed on the console.

update.py

from connecttodb import *
from helper import *
# update a record
def update(conn, eid):
    # creating a cursor to perform a sql operation
    cursor = conn.cursor()
    # sql query
    query = '''UPDATE employee SET gender = %s WHERE id = %s;'''
    try:
        record = get_by_id(cursor, eid)
        if record is None:
            print('Employee id = {} not found'.format(eid))
        else:
            # execute the command
            cursor.execute(query, ['F', eid])
            # commit the changes
            conn.commit()
            print('Employee id = {} updated successfully'.format(eid))
    except(Exception, Error) as error:
        print(error)
    finally:
        if conn is not None:
            cursor.close()
            conn.close()
            print('\nDatabase connection closed')
# driver code
if __name__ == '__main__':
    # connect to database and update a record
    update(connect('employee'), 5)

If everything goes well the following output will be shown in the IDE console. You can run the getall.py python script to fetch the records.

Logs

Employee id = 5 updated successfully

Database connection closed.

2.8 Delete a record in the table

Add the following code to the python script which will delete an employee record into the table. The python script will check if the employee is present in the database. If present the record will be deleted. If not it will print the following message – Employee id = 5 not found will be printed on the console.

delete.py

from connecttodb import *
from helper import *
# delete a record
def delete(conn, eid):
    # creating a cursor to perform a sql operation
    cursor = conn.cursor()
    # sql query
    query = '''DELETE FROM employee WHERE id = %s;'''
    try:
        record = get_by_id(cursor, eid)
        if record is None:
            print('Employee id = {} not found'.format(eid))
        else:
            # execute the command
            cursor.execute(query, [eid])
            # commit the changes
            conn.commit()
            print('Employee id = {} deleted successfully'.format(eid))
    except(Exception, Error) as error:
        print(error)
    finally:
        if conn is not None:
            cursor.close()
            conn.close()
            print('\nDatabase connection closed')
# driver code
if __name__ == '__main__':
    # connect to database and delete a record
    delete(connect('employee'), 5)

If everything goes well the following output will be shown in the IDE console. You can run the getall.py python script to fetch the records.

Logs

Employee id = 5 deleted successfully

Database connection closed.

Similarly, we can create other scripts like deleteall.py to delete all records from the employee database and many others. That is all for this tutorial and I hope the article served you with whatever you were looking for. Happy Learning and do not forget to share!

3. Summary

In this tutorial, we learned:

  • Introduction to psycopg2 module
  • Sample program to connect to the database with the help of psycopg2 module
  • Sample programs to perform CRUD operations

Now, you can check our Python PostgreSQL Tutorial Using Psycopg2.

You can download the source code of this tutorial from the Downloads section.

4. Download the Project

This was a python programming tutorial to connect to the PostgreSQL database with the help of the psycopg2 module and perform CRUD operations.

Download
You can download the full source code of this example here: Python PostgreSQL CRUD Operations Example

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button