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

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

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.

  • You will see following output on running the above command.
Python PostgreSQL
Python PostgreSQL

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
Python PostgreSQL


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.
Python PostgreSQL
Python PostgreSQL

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.

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.
Python PostgreSQL
Python PostgreSQL

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.

Python PostgreSQL
Python PostgreSQL

  • Now you can see our table is created and the table name is student.
Python PostgreSQL
Python PostgreSQL
  • Now we will insert some records into student table. So write the following code.

  • Now run the above code, you will get following output.
Python PostgreSQL
Python PostgreSQL
  • Now let’s check whether our record is inserted or not. So open your postgreSQL database.

    Python PostgreSQL
    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.

  • 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.

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

Delete Operation

  • 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 🙂

Leave a Comment