CRUD operations on a PostgreSQL database in python using Psycopg2

There are many databases out there written in different languages. Relational databases are well known for storing data in tables with a strict schema. The data items stored in a relational database have a pre-defined relation between them.

PostgreSQL is a powerful, open-source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. You can interact with a PostgreSQL database using numerous GUIs or a command line or terminal. We will be connecting to a PostgreSQL database with python using Psycopg2.

We can use various python libraries to connect to PostgreSQL like SQLAlchemy however the most popular is Psycopg2. SQLAlchemy generates SQL queries while mapping the database schema to Python objects. Psycopg2 takes your hand-crafted SQL queries and executes them against the database. In other words, SQLAlchemy is an ORM (Object-Relational Mapper) while psycopg2 is a database driver for PostgreSQL.

We will be using a common example throughout this tutorial. Assume you are working in a school and your job involves managing the database for holding all the student’s records. The table you are managing has just three columns: Roll number, Student name and Class that the student belongs to. Now that we know what we are dealing with let’s go over the schema for the table.

Schema:

This tutorial assumes that you have installed PostgreSQL on your system.We will be using the default postgres database that comes with the PostgreSQL installation. If you wish to use a different database, make sure you create a new database and switch to it before proceeding further.

Connecting to the PostgreSQL database

Let’s start by installing the psycopg2 module for us to use. In your command prompt run the following command:

pip install psycopg2

In order to operate on any database through python we will first need to connect to it. We can connect to our PostgreSQL database using the connect method provided by psycopg2.

Here, we will be connecting to our PostgreSQL database and call the connection as conn. In the end we will also have to close our connection with the database using conn.close()

In your python file write the following:

Code:

import psycopg2

DB_NAME = "postgres"
DB_HOST = "localhost"
DB_USER = "postgres"
DB_PASSWORD = "password"
DB_PORT = "5432"
 
conn = psycopg2.connect(dbname=DB_NAME, host=DB_HOST, user=DB_USER, password=DB_PASSWORD, port=DB_PORT)        
 
print("Connection to database successful!")
 
conn.close()
print("Connection with the database terminated")

Note that here you will have to make the appropriate changes to the variables DB_NAME, DB_HOST, DB_USER, DB_PASSWORD and DB_PORT if you are using a different database.

Once you run this, you should see the output:

CRUD operations on PostgreSQL

If you’ve ever worked with databases, you’ve probably heard of CRUD operations. CRUD meaning: CREATE, READ, UPDATE and DELETE are the four basic RDBMS programming operations. So let's perform CRUD operations on our database.

1. Create

Let’s start by creating a table in our database. As discussed before, we will be dealing with a student record table that will have columns for roll number, name and class where the roll number will be the primary key.

We will be using the cursor object provided by psycopg2 to run our PostgreSQL commands in our python file. A cursor is an object that is used to make the connection for executing SQL queries. It acts as the middleware between a PostgreSQL database connection and the query we want to run. We can create the cursor object as:

```

cursor = conn.cursor()

```

where conn is the connection to the database that we have made in the previous section. Now we can use the execute method to run the sql code to create a table.

We will first create the cursor object as discussed. Then using the execute method of the cursor we run a simple SQL code from within python. You can learn more about writing simple SQL queries from( https://www.sqltutorial.org/sql-cheat-sheet ).

conn.commit() is used right after the cursor's execute method and will commit any pending transactions to the database. It is essential for the changes to be reflected in the DB. By default, Psycopg2 opens a transaction before executing any commands. If commit() is not called, the effect of any data manipulation will be lost immediately.

Run the following code to create a table named students:

Code:

import psycopg2
 
DB_NAME = "postgres"
DB_HOST = "localhost"
DB_USER = "postgres"
DB_PASSWORD = "password"
DB_PORT = "5432"
 
conn = psycopg2.connect(dbname=DB_NAME, host=DB_HOST,
                        user=DB_USER, password=DB_PASSWORD, port=DB_PORT)
print("Connection to database successful!")

cursor = conn.cursor()
cursor.execute("""
CREATE TABLE students(
    roll_number INT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    class TEXT NOT NULL
)
""")
conn.commit()
print("Table created successfully")
 
conn.close()

Output:

Now the table students has been created in the postgres database.

If you are using a GUI tool for PostgreSQL like PGadmin4, you can see your table as:

It is also a good practice to use a cursor within a with block so that we don’t make any unexpected changes to our table. Using a with block will look like:

From now on in this tutorial we will be using the with block while working with a cursor.

While working on a real project you should always use a try-except block inside the with block. The queries must be executed in the try block and you must write the appropriate exception handling code. This is because if any exception occurs while you are running your SQL query, whether it is connection timing out, the table being deleted by another user or even a bad network (in case your DB is not locally hosted), then the entire program will crash due to this SQL exception. This might not seem that big of a problem as you are just running the commands at the moment, but for a real-world project like a web app, this may crash the whole application.

The above code with a try-except block will look like:

import psycopg2
 
DB_NAME = “postgres”
DB_HOST = “localhost”
DB_USER = “postgres”
DB_PASSWORD = “password”
DB_PORT = “5432”
 
conn = psycopg2.connect(dbname=DB_NAME, host=DB_HOST,
                        user=DB_USER, password=DB_PASSWORD, port=DB_PORT)
 
print(“Connection to database successful!”)
 
with conn.cursor() as cursor:
    try:
        cursor.execute(“””
        CREATE TABLE students(
        roll_number INT PRIMARY KEY NOT NULL,
        name TEXT NOT NULL,
        class TEXT NOT NULL
 
        “””)
        conn.commit()
        print(“table created successfully”)
    except:
        print(“An error occurred while creating the table")
 
conn.close()

Now since we already have the table students created in our database, we will get the output:

2. Add and Read from PostgreSQL Database

Now that we have created a table, the next operation according to CRUD is to read. However we first need to add something to the table to read it. So, let’s start by adding a student to the table.

To insert data into a table we use the INSERT command and you will begin to notice that we are just running simple SQL code within the execute method at this point. That’s exactly what psycopg2 allows us to do. We can run complex SQL queries in the execute method and now we are operating on a PostgreSQL database using python.

Code:

import psycopg2
 
DB_NAME = "postgres"
DB_HOST = "localhost"
DB_USER = "postgres"
DB_PASSWORD = "password"
DB_PORT = "5432"
 
conn = psycopg2.connect(dbname=DB_NAME, host=DB_HOST,
                        user=DB_USER, password=DB_PASSWORD, port=DB_PORT)
print("Connection to database successful!")
 
with conn.cursor() as cursor:
    cursor.execute("""
    INSERT INTO students (roll_number, name, class) VALUES(2, 'Yash', 'B2')
    """)
    conn.commit()
    print("Data added to the table successfully")
 
conn.close()

Output:

The record will be added to your table. If you look up your table in PGadmin, you can see it as:

Let’s read data from our table:

We use the SELECT statement of SQL to get all the columns from the students table. The data that the database returns is now inside of the cursor and we need to print it. To do so we use the fetchall() method. This will get all the records in the cursor and we will store it in a variable called table_contents.

Code:

import psycopg2
 
DB_NAME = "postgres"
DB_HOST = "localhost"
DB_USER = "postgres"
DB_PASSWORD = "password"
DB_PORT = "5432"
 
conn = psycopg2.connect(dbname=DB_NAME, host=DB_HOST,
                        user=DB_USER, password=DB_PASSWORD, port=DB_PORT)
print("Connection to database successful!")
 
with conn.cursor() as cursor:
    cursor.execute("""
    SELECT * FROM students
    """)
    conn.commit()
 
    table_contents = cursor.fetchall()
    print(table_contents)
 
conn.close()

Output:

All the records are displayed in their individual tuples in an outer list representing the table.

If we add another record to the table and then run the above code, we will see the output:

3. Update in PostgreSQL Database

Let’s say that Yash whose roll number is 2 was moved from class B2 to A1. So, we need to update this change in the table.

Here, we will use the UPDATE statement in SQL to change the class of the student with roll_number 2 to A1. We will use cursor.rowcount to give us the number of rows that are affected during the execution of the query.

To update a record, run the following code:

Code:

import psycopg2
 
DB_NAME = "postgres"
DB_HOST = "localhost"
DB_USER = "postgres"
DB_PASSWORD = "password"
DB_PORT = "5432"
 
conn = psycopg2.connect(dbname=DB_NAME, host=DB_HOST,
                        user=DB_USER, password=DB_PASSWORD, port=DB_PORT)
print("Connection to database successful!")
 
with conn.cursor() as cursor:
    cursor.execute("""
    UPDATE students SET class = 'A1' WHERE roll_number = 2   
    """)
    conn.commit()
 
    print("Updated successfully")
    print("Rows affected ", cursor.rowcount)
 
conn.close()

Output:

If you open your PGadmin4 you will see the updated record

4. Delete from PostgreSQL Database

Now let’s say Yash wants to leave this school and so we need to remove his record from the database.

Let’s use the DELETE statement in SQL to do so. In order to remove Yash’s record, knowing his roll number, run the following code:

Code:

import psycopg2
 
DB_NAME = "postgres"
DB_HOST = "localhost"
DB_USER = "postgres"
DB_PASSWORD = "password"
DB_PORT = "5432"
 
conn = psycopg2.connect(dbname=DB_NAME, host=DB_HOST,
                        user=DB_USER, password=DB_PASSWORD, port=DB_PORT)
print("Connection to database successful!")
 
with conn.cursor() as cursor:
    cursor.execute("""
    DELETE FROM students WHERE roll_number = 2   
    """)
    conn.commit()
 
    print("Record deleted successfully")
 
conn.close()

Output:

Here we are removing a record having roll_number = 2. Now if you open up your PGadmin and

refresh the table, you will see that record gone

Conclusion:

We learned how to perform all the operations that are needed to get started when working with a database. The PostgreSQL database used in this tutorial was hosted locally.However, if you have a database that is hosted on a cloud service like AWS or Google Cloud Platform, the only changes that you will have to make are the DB_NAME, DB_HOST, DB_USER, DB_PASSWORD and DB_PORT variables according to your host. The rest of the code will remain unaffected.

In the next tutorial, we will see how we can host a database on cloud using cloud services such as AWS , connect to the database and perform operations on it. Stay tuned for more!