Python PostgreSQL Tutorial : CRUD Operation In Python PostgreSQL

Hey friends, welcome to Python PostgreSQL Tutorial. In this tutorial you will learn what is postgreSQL, how to create table, how to insert, update and delete the table’s records. After going through this post you will be having a complete idea about using postgreSQL database for your python Application. So without wasting time let’s gets started.

I have uploaded MySQL tutorial, you can check it.

Python MySQL Tutorial : Using MySQL Database with Python

Contents

1 What is PostgreSQL?
2 What is CRUD?
3 Python PostgreSQL Using psycopg2 Module
- 3.1 What Is psycopg2 ?
- 3.2 Installing psycopg2
4 Creating Database In PostgreSQL
5 Python PostgreSQL : Performing Basic CRUD Operation

What is PostgreSQL?

PostgreSQL is a general purpose and object-relational database management system, the most advanced open source database system.
It has more than 15 years off active development phase and a proven architecture that has earned it a strong reputation for reliability, data integrity and correctness.
It can run on various operating systems like Windows, Linux, Solaris, Mac OS X etc.
PostgreSQL is the first database management system that implements multi-version concurrency control (MVCC) feature, even before Oracle.
The original name of PostgreSQL is Postgres therefore, sometimes PostgreSQL is referred as Postgres.

PostgreSQL Features

User-defined types
Sophisticated locking mechanism
Table inheritance
Views, rules, subquery
Foreign key referential integrity
Nested transactions (savepoints)
Multi-version concurrency control (MVCC) etc

PostgreSQL Benefits

Consistency
Compactness
Validation
Performance

PostgreSQL Users

Many companies are using PostgreSQL for making their products. Some of them are following –

Apple
Fujitsu
Red Hat
Cisco
Juniper Network etc.

What is CRUD?

Here i am using a term CRUD, and i am pretty sure you are thinking What is CRUD ? CRUD is nothing but an abbreviation for the basic operations that we perform in any database. And the operations are following –

Create
Read
Update
Delete

Have you checked – Python SQLite3 Tutorial to Perform Basic Database Operation

Python PostgreSQL Using psycopg2 Module

As we know python has various database drivers for PostgreSQL. Currently, the psycopg is the most popular PostgreSQL database adapter for the Python language.

What Is psycopg2 ?

psycopg2 is a library for python to connect to database from server.
The current version of the psycopg is 2 or psycopg2.
The psycopg2 has many useful features such as client-side and server-side cursors, asynchronous notification and communication, COPY command support, etc.

Installing psycopg2

To install psycopg2, you have to run following command. So open your command prompt and run the following command.


pip install psycopg2

pip install psycopg2

You will see following output on running the above command.

So now our module is installed successfully, and now we have to learn how to perform CRUD operation in psycopg2 using python. So let’s move ahead.

Creating Database In PostgreSQL

For working on database, the first thing to do is creating database. So in postgreSQL, create a database.

Now go to pgAdmin 4 and create a database. I have created a database named sample_db, you can see it in the below image.

Python PostgreSQL : Performing Basic CRUD Operation

In this section, we will see the important part of this tutorial. So let’s see what we are going to do.

Creating A New Project

First of all you have to create a new python project. So go to your python IDE(whatever you use) and create a project and inside this project create a python file. I am assuming that you already know creating python project, if no then check it first – creating project in python.

Give a look on my project.

And now, we will start performing operations on database. But before performing any operation, we have to connect with our database. So let’s see how to do that.

Connecting To PostgreSQL Database

Now write the following code for connecting to an existing database.
Remember one thing – if the database does not exist, then it will be created and finally a database object will be returned.


import psycopg2   # import psycopg module

try:
     # connect to database
    con = psycopg2.connect(user = "postgres",
                                  password = "password",
                                  host = "127.0.0.1",
                                  port = "5432",
                                  database = "sample_db")
    cur = con.cursor()

    # Print PostgreSQL version
    cur.execute("SELECT version();")
    record = cur.fetchone()
    print("\nYou are connected to - ", record,"\n")

except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)

finally:
    #closing database connection.
            if(con):
            cur.close()
            con.close()
            print("PostgreSQL connection is closed")

import psycopg2 # import psycopg module

try:

# connect to database

con = psycopg2.connect(user = "postgres",

password = "password",

host = "127.0.0.1",

port = "5432",

database = "sample_db")

cur = con.cursor()

# Print PostgreSQL version

cur.execute("SELECT version();")

record = cur.fetchone()

print("\nYou are connected to - ", record,"\n")

except (Exception, psycopg2.Error) as error :

print ("Error while connecting to PostgreSQL", error)

finally:

#closing database connection.

if(con):

cur.close()

con.close()

print("PostgreSQL connection is closed")

What We Did ?

So now we will understand what we have done in above code. So let’s start –

First of all i have imported psycopg2 module. By importing psycopg2 module, we can use classes and method of this module to communicate with postgreSQL.
The next thing we have done is that created a connection. connect( ) method is used to create a connection to a PostgreSQL database instance. This returns a PostgreSQL Connection Object.
Now we have passed some arguments to connect( ) method such as user, password, host etc.
Then we have created a cursor object. con.cursor( ) create a cursor object which allows us to execute PostgreSQL command through Python source code.
Then we have fetched version of postgreSQL using execute( ) method. cur.fetchone( ) method is used to fetch query result.
We have placed all our code in the try-except block to catch the database exceptions and error that may occur during this process.
At last we have closed the connection.
Now let’s see whether our database is connecting or not.

Congrats our database is connecting successfully

Create Operation

In create operation, we will see how to create table in our database. So write the following program and run it.


import psycopg2
try:
    con = psycopg2.connect(user = "postgres",
                                  password = "password",
                                  host = "127.0.0.1",
                                  port = "5432",
                                  database = "sample_db")
    cur = con.cursor()

    cur.execute('''CREATE TABLE Student
          (ROll INT PRIMARY KEY     NOT NULL,
          NAME           TEXT    NOT NULL,
          AGE            INT     NOT NULL,
          ADDRESS        CHAR(50)
                   );''')

    
    con.commit()
    
    print("Table created successfully")

except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)

finally:
    #closing database connection.
        if(con):
            cur.close()
            con.close()
            print("PostgreSQL connection is closed")

import psycopg2

try:

con = psycopg2.connect(user = "postgres",

password = "password",

host = "127.0.0.1",

port = "5432",

database = "sample_db")

cur = con.cursor()

cur.execute('''CREATE TABLE Student

(ROll INT PRIMARY KEY NOT NULL,

NAME TEXT NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR(50)

);''')

con.commit()

print("Table created successfully")

except (Exception, psycopg2.Error) as error :

print ("Error while connecting to PostgreSQL", error)

finally:

#closing database connection.

if(con):

cur.close()

con.close()

print("PostgreSQL connection is closed")

Now you can see our table is created and the table name is student.

Now we will insert some records into student table. So write the following code.


import psycopg2
try:
    con = psycopg2.connect(user = "postgres",
                                  password = "password",
                                  host = "127.0.0.1",
                                  port = "5432",
                                  database = "sample_db")
    cur = con.cursor()

    cur.execute("INSERT INTO Student (ROLL,NAME,AGE,ADDRESS) \
          VALUES (1, 'DAINA', 20, 'Mumbai')");
    con.commit()
    print("Record inserted successfully")

except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)

finally:
    #closing database connection.
        if(con):
            cur.close()
            con.close()
            print("PostgreSQL connection is closed")

import psycopg2

try:

con = psycopg2.connect(user = "postgres",

password = "password",

host = "127.0.0.1",

port = "5432",

database = "sample_db")

cur = con.cursor()

cur.execute("INSERT INTO Student (ROLL,NAME,AGE,ADDRESS) \

VALUES (1, 'DAINA', 20, 'Mumbai')");

con.commit()

print("Record inserted successfully")

except (Exception, psycopg2.Error) as error :

print ("Error while connecting to PostgreSQL", error)

finally:

#closing database connection.

if(con):

cur.close()

con.close()

print("PostgreSQL connection is closed")

Now run the above code, you will get following output.

Now let’s check whether our record is inserted or not. So open your postgreSQL database.
Python PostgreSQL
You can see records are inserted into table successfully.

Django Database API – Saving Data in SQLite using Models

Read Operation

In read operation, we will fetch the records from database. So write the following code.


import psycopg2
try:
    con = psycopg2.connect(user = "postgres",
                                  password = "password",
                                  host = "127.0.0.1",
                                  port = "5432",
                                  database = "sample_db")
    cur = con.cursor()

    cur.execute("SELECT roll, name, age,  address from Student")
    records = cur.fetchall()
    print("     STUDENT INFORMATION       ")
    print("-----------------------------------")
    for row in records:
        print("     ROLL = ", row[0])

        print("     NAME = ", row[1])

        print("     AGE = ", row[2])

        print("     ADDRESS = ", row[3])
        "\n"

    con.commit()

except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)

finally:
    #closing database connection.
        if(con):
            cur.close()
            con.close()
            print("\nPostgreSQL connection is closed")

import psycopg2

try:

con = psycopg2.connect(user = "postgres",

password = "password",

host = "127.0.0.1",

port = "5432",

database = "sample_db")

cur = con.cursor()

cur.execute("SELECT roll, name, age, address from Student")

records = cur.fetchall()

print(" STUDENT INFORMATION ")

print("-----------------------------------")

for row in records:

print(" ROLL = ", row[0])

print(" NAME = ", row[1])

print(" AGE = ", row[2])

print(" ADDRESS = ", row[3])

"\n"

con.commit()

except (Exception, psycopg2.Error) as error :

print ("Error while connecting to PostgreSQL", error)

finally:

#closing database connection.

if(con):

cur.close()

con.close()

print("\nPostgreSQL connection is closed")

Now let’s check the output.

Update Operation

Now for updating rows of a table, write the following code. We will also fetch all records after updating.


import psycopg2
try:
    con = psycopg2.connect(user = "postgres",
                                  password = "password",
                                  host = "127.0.0.1",
                                  port = "5432",
                                  database = "sample_db")
    cur = con.cursor()

    cur.execute("UPDATE Student set AGE = 25 ")
    con.commit()

    cur.execute("SELECT roll, name, age,  address from Student")
    records = cur.fetchall()
    print("     STUDENT INFORMATION       ")
    print("-----------------------------------")
    for row in records:
        print("     ROLL = ", row[0])

        print("     NAME = ", row[1])

        print("     AGE = ", row[2])

        print("     ADDRESS = ", row[3])
        "\n"
     print(" \n Age is updated \n") 

except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)

finally:
    #closing database connection.
        if(con):
            cur.close()
            con.close()
            print("\nPostgreSQL connection is closed")

import psycopg2

try:

con = psycopg2.connect(user = "postgres",

password = "password",

host = "127.0.0.1",

port = "5432",

database = "sample_db")

cur = con.cursor()

cur.execute("UPDATE Student set AGE = 25 ")

con.commit()

cur.execute("SELECT roll, name, age, address from Student")

records = cur.fetchall()

print(" STUDENT INFORMATION ")

print("-----------------------------------")

for row in records:

print(" ROLL = ", row[0])

print(" NAME = ", row[1])

print(" AGE = ", row[2])

print(" ADDRESS = ", row[3])

"\n"

print(" \n Age is updated \n")

except (Exception, psycopg2.Error) as error :

print ("Error while connecting to PostgreSQL", error)

finally:

#closing database connection.

if(con):

cur.close()

con.close()

print("\nPostgreSQL connection is closed")

Now let’s see the output and check whether our row is updated or not.

Delete Operation


import psycopg2
try:
    con = psycopg2.connect(user = "postgres",
                                  password = "password",
                                  host = "127.0.0.1",
                                  port = "5432",
                                  database = "sample_db")
    cur = con.cursor()

    cur.execute("DELETE from Student where roll= 1;")
    con.commit()
    print("Total number of rows deleted :", cur.rowcount)

    cur.execute("SELECT roll, name, age,  address from Student")
    records = cur.fetchall()
    print("     STUDENT INFORMATION       ")
    print("-----------------------------------")
    for row in records:
        print("     ROLL = ", row[0])

        print("     NAME = ", row[1])

        print("     AGE = ", row[2])

        print("     ADDRESS = ", row[3])
        "\n"


except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)

finally:
    #closing database connection.
        if(con):
            cur.close()
            con.close()
            print("\nPostgreSQL connection is closed")

import psycopg2

try:

con = psycopg2.connect(user = "postgres",

password = "password",

host = "127.0.0.1",

port = "5432",

database = "sample_db")

cur = con.cursor()

cur.execute("DELETE from Student where roll= 1;")

con.commit()

print("Total number of rows deleted :", cur.rowcount)

cur.execute("SELECT roll, name, age, address from Student")

records = cur.fetchall()

print(" STUDENT INFORMATION ")

print("-----------------------------------")

for row in records:

print(" ROLL = ", row[0])

print(" NAME = ", row[1])

print(" AGE = ", row[2])

print(" ADDRESS = ", row[3])

"\n"

except (Exception, psycopg2.Error) as error :

print ("Error while connecting to PostgreSQL", error)

finally:

#closing database connection.

if(con):

cur.close()

con.close()

print("\nPostgreSQL connection is closed")

You can see records of Student table has been deleted, but you are seeing that no records are remain in table, this is because we had insert only one record in the table.

Also Check – Django REST API Tutorial – A Beginner’s Guide

And we have done with all the CRUD operations in postgreSQL. So that’s all for this Python PostgreSQL Tutorial. I hope you found it helpful if you did, then please SHARE this post with your friends who are learning python.

And yes, if you have any query regarding this Python PostgreSQL Tutorial then your questions are welcome. Thank You 🙂

What is PostgreSQL?

PostgreSQL Features

PostgreSQL Benefits

PostgreSQL Users

What is CRUD?

Python PostgreSQL Using psycopg2 Module

What Is psycopg2 ?

Installing psycopg2

Creating Database In PostgreSQL

Python PostgreSQL : Performing Basic CRUD Operation

Creating A New Project

Connecting To PostgreSQL Database

Create Operation

Read Operation

Update Operation

Delete Operation

Leave a Comment Cancel reply