Performing CRUD operations with Python and PostgreSQL

PostgreSQL is one of the most commonly used relational databases. You’re often required to perform database operations on PostgreSQL databases from within a Python application. For instance, if you want to develop a Python application that uses a PostgreSQL database to store data, you need to know how to create, read, update and delete (CRUD) data in a PostgreSQL via Python. In this article, you will see how to perform various database operations on PostgreSQL databases using Python.

Before you execute the scripts in this article, we’re assuming you have an instance of a PostgreSQL database running on your localhost or any server that you have permissions to access. To see how to download and intall PostgreSQL for various operating systems, take a look at this link.

Installing Required Libraries

We’ll be using the Python psycopg2 module to perform database operations on our PostgreSQL database. To download the module, execute the following script on your command terminal.

pip install psycopg2

Performing CRUD Operations on PostgreSQL via Python

Connecting to PostgreSQL Server

To connect to PostgreSQL server, use the connect() function from the psycopg2 module. You have to pass values for the database, user, password, host and port attribute of the connect() function. The following script initializes values for different attributes.

database_name = "postgres"
user_name = "postgres"
password = "abc123"
host_ip = "127.0.0.1"
host_port ="5432"

To connect to PostgreSQL, execute the following script. The psycopg2.connect() function returns a connection object which you can use to perform various queries on the PostgreSQL server.

my_con = psycopg2.connect(
            database = database_name,
            user = user_name,
            password = password,
            host = host_ip,
            port = host_port
)

Creating and Editing a Database (CRUD)

To create a database, you need to get the cursor from the connection object. The cursor object is used to create queries on the database. The following script returns your cursor object.

my_con.autocommit = True
cursor = my_con.cursor()

Next, to create a database, you need to write the query that creates the database and pass that query to the execute() method of the cursor object as shown in the following script. Executing the following script should create a database named car_db on the PostgreSQL database server. The car_db database is a dummy database that will contain one table holding fictional information about different cars.

query = "CREATE DATABASE car_db"
cursor.execute(query)

Creating a Table

To create a table in database, you first have to connect to the database. Previously we created a connection to PostgreSQL server, now we need to connect to the car_db database as shown below.

database_name = "car_db"
user_name = "postgres"
password = "abc123"
host_ip = "127.0.0.1"
host_port ="5432"

my_db_con = psycopg2.connect(
            database = database_name,
            user = user_name,
            password = password,
            host = host_ip,
            port = host_port
)

The next step is to write the query that creates a table within the cars database.

The following script contains a query that creates a table named cars with 6 columns: id, name, model, number, color, company.

create_table_query = """
CREATE TABLE IF NOT EXISTS cars (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  model INTEGER,
  number TEXT,
  color TEXT,
  company TEXT
);
"""

To create the actual table, you need to create a cursor object using the connection to the car_db database. Next, you need to pass the query that creates the cars table to the execute() method of the cursor object as shown below. Notice, with “autocommit” attribute set to True, you don’t have to commit your queries again and again after executing them.

my_db_con.autocommit = True
cursor = my_db_con.cursor()
cursor.execute(create_table_query)

Now that the cars table has been created, let’s insert some records into the table.

To insert multiple records, you need to create a list of tuples where each tuple contains one record. In the following script we create a list of 5 tuples (5 records). The records are fictional and contain different information about cars.

After making a list of tuples we create a query for inserting records. The insert query in the following script contains a placeholder for 5 records.

Inserting Records

cars = [
    ("Aqua", 2009, "ABC123", "Red", "Toyota"),
    ("700s", 2015, "XXXX22", "Black", "BMW"),
    ("Vezel", 2018, "XXX111", "White", "Honda"),
    ("200C", 2001, "MMMM11", "Black", "Mercedez"),
    ("Vitz", 2010, "XXXX", "Red", "Toyota"),
]

car_records = ", ".join(["%s"] * len(cars))

insert_query = (
    f"INSERT INTO cars (name, model, number, color, company) VALUES {car_records}"
)

The string query containing the placeholder, car_records, and actual list of records, cars, is passed to the execute() method of the cursor() object in order to insert records as shown in the following script.

cursor.execute(insert_query, cars)

Code More, Distract Less: Support Our Ad-Free Site

You might have noticed we removed ads from our site - we hope this enhances your learning experience. To help sustain this, please take a look at our Python Developer Kit and our comprehensive cheat sheets. Each purchase directly supports this site, ensuring we can continue to offer you quality, distraction-free tutorials.

Reading Records

To select a record, you simply have to write a SELECT query and store it in a string variable. The query is then passed to the execute() method of the cursor object. To return actual records, you need to call the fetchall() method of the cursor object. The following script fetches all the records from the cars table and displays them on the console.

select_cars_query = "SELECT * FROM cars"
cursor.execute(select_cars_query)

cars = cursor.fetchall()

for car in cars:
    print(car)

Output:

(1, 'Aqua', 2009, 'ABC123', 'Red', 'Toyota')
(2, '700s', 2015, 'XXXX22', 'Black', 'BMW')
(3, 'Vezel', 2018, 'XXX111', 'White', 'Honda')
(4, '200C', 2001, 'MMMM11', 'Black', 'Mercedez')
(5, 'Vitz', 2010, 'XXXX', 'Red', 'Toyota')

Updating Records

To update records, you again have to pass the update query to the execute() method of the cursor object. For example, the following script conditionally updates the color of all the cars to Blue if the car models are greater than or equal to 2010.

update_car_colors = """
UPDATE
  cars
SET
  color = 'Blue'
WHERE
  model >= 2010
"""

cursor.execute(update_car_colors)

Let’s check to see if the colors of our cars were updated. Run this script to select all the car records, where the select_cars_query string was defined earlier.

cursor.execute(select_cars_query)

cars = cursor.fetchall()

for car in cars:
    print(car)

The following output shows that the colors of cars having model numbers greater than or equal to 2010 were successfully updated to Blue.

Output:

(1, 'Aqua', 2009, 'ABC123', 'Red', 'Toyota')
(4, '200C', 2001, 'MMMM11', 'Black', 'Mercedez')
(2, '700s', 2015, 'XXXX22', 'Blue', 'BMW')
(3, 'Vezel', 2018, 'XXX111', 'Blue', 'Honda')
(5, 'Vitz', 2010, 'XXXX', 'Blue', 'Toyota')

Deleting Records

Deleting a record is similar to updating a record. You just have to pass the delete query to the execute() method of the cursor object. The following script deletes all the records from the cars table where the value for the colors column is “Red”.

delete_car_records = "DELETE FROM cars WHERE color = 'Red'"

cursor.execute(delete_car_records)

Let’s see if the records were deleted. Select all the records from the cars table using the script below. Recall the select_cars_query string was defined earlier.

cursor.execute(select_cars_query)

cars = cursor.fetchall()

for car in cars:
    print(car)

The output below shows that no red cars exist in cars table, indicating our record was successfully deleted.

Output:

(4, '200C', 2001, 'MMMM11', 'Black', 'Mercedez')
(2, '700s', 2015, 'XXXX22', 'Blue', 'BMW')
(3, 'Vezel', 2018, 'XXX111', 'Blue', 'Honda')
(5, 'Vitz', 2010, 'XXXX', 'Blue', 'Toyota')

This is just one sample of how you can use Python to interact with databases. We have several Python SQL tutorials, including

• how to import a SQL data query into a Pandas DataFrame and
• how to add and read database data with Python sqlite3.

We also have application-specific Python/SQL tutorials showing you how to

• copy data from Excel to a sqlite3 database with Python and
• convert sqlite data types for Python.

We find these tutorials really helpful, and we hope you do, too. For more tutorials like these, join us using the form below:

Code More, Distract Less: Support Our Ad-Free Site

You might have noticed we removed ads from our site - we hope this enhances your learning experience. To help sustain this, please take a look at our Python Developer Kit and our comprehensive cheat sheets. Each purchase directly supports this site, ensuring we can continue to offer you quality, distraction-free tutorials.

Complete Code for Python PostgreSQL CRUD Operations

For convenience, I’ve included the the complete code for connecting and performing create, read, update and delete operation on PostgreSQL database using Python below:

# pip install psycopg2

# Making Connection with PostgreSQL Server
import psycopg2

database_name = "postgres"
user_name = "postgres"
password = "abc123"
host_ip = "127.0.0.1"
host_port ="5432"

my_con = psycopg2.connect(
            database = database_name,
            user = user_name,
            password = password,
            host = host_ip,
            port = host_port
)

my_con.autocommit = True
cursor = my_con.cursor()

## Creating Cars Database

query = "CREATE DATABASE car_db"
cursor.execute(query)

database_name = "car_db"
user_name = "postgres"
password = "abc123"
host_ip = "127.0.0.1"
host_port ="5432"

my_db_con = psycopg2.connect(
            database = database_name,
            user = user_name,
            password = password,
            host = host_ip,
            port = host_port
)

## Creating cars table inside cars_db

create_table_query = """
CREATE TABLE IF NOT EXISTS cars (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  model INTEGER,
  number TEXT,
  colour TEXT,
  company TEXT
);
"""

my_db_con.autocommit = True
cursor = my_db_con.cursor()
cursor.execute(create_table_query)

## Inserting Records in cars table

cars = [
    ("Aqua", 2009, "ABC123", "Red", "Toyota"),
    ("700s", 2015, "XXXX22", "Black", "BMW"),
    ("Vezel", 2018, "XXX111", "White", "Honda"),
    ("200C", 2001, "MMMM11", "Black", "Mercedez"),
    ("Vitz", 2010, "XXXX", "Red", "Toyota"),
]

car_records = ", ".join(["%s"] * len(cars))

insert_query = (
    f"INSERT INTO cars (name, model, number, colour, company) VALUES {car_records}"
)


cursor.execute(insert_query, cars)

## Reading Records from cars Table

select_cars_query = "SELECT * FROM cars"
cursor.execute(select_cars_query)

cars = cursor.fetchall()

for car in cars:
    print(car)

## Updating records in cars table

update_car_colours = """
UPDATE
  cars
SET
  colour = 'Blue'
WHERE
  model >= 2010
"""

cursor.execute(update_car_colours)

## Deleting Records from cars table

delete_car_records = "DELETE FROM cars WHERE colour = 'Red'"

cursor.execute(delete_car_records)