PostgreSQL CRUD Tutorials in Python: A Step-by-Step Guide

PostgreSQL is a powerful open-source relational database management system (RDBMS). In this tutorial, we’ll explore how to perform CRUD (Create, Read, Update, Delete) operations using PostgreSQL in a Python application. We’ll cover each step and provide practical examples with detailed explanations to help you get started.

Prerequisites

Before we begin, make sure you have the following prerequisites:

PostgreSQL Database: PostgreSQL should be installed and running. You can download it from the official PostgreSQL website.
Python: Ensure you have Python installed on your system. You can download Python from the official Python website.
Psycopg2: Install the psycopg2 package, which is a PostgreSQL adapter for Python. You can install it using pip:
```
pip install psycopg2
```

Step 1: Connecting to PostgreSQL

To use PostgreSQL in a Python application, establish a connection to the database.

import psycopg2

# Create a connection
try:
    connection = psycopg2.connect(
        user="your_username",
        password="your_password",
        host="your_host",
        port="your_port",
        database="your_database_name"
    )

    print("Connected to PostgreSQL")

except psycopg2.Error as e:
    print(f"Error: {e}")

Replace "your_username", "your_password", "your_host", "your_port", and "your_database_name" with your PostgreSQL credentials and connection details.

Step 2: Creating a Table

Let’s create a simple users table to demonstrate CRUD operations.

try:
    cursor = connection.cursor()

    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(50) NOT NULL,
        email VARCHAR(100) NOT NULL
    )
    """

    cursor.execute(create_table_query)
    print("Table 'users' created successfully")

except psycopg2.Error as e:
    print(f"Error: {e}")

Step 3: Inserting Data

Now, let’s insert a new user into the users table.

try:
    cursor = connection.cursor()

    insert_query = "INSERT INTO users (username, email) VALUES (%s, %s)"
    user_data = ("john_doe", "[email protected]")

    cursor.execute(insert_query, user_data)
    connection.commit()
    print("Data inserted successfully")

except psycopg2.Error as e:
    print(f"Error: {e}")

Step 4: Querying Data

Retrieve data from the users table.

try:
    cursor = connection.cursor()

    select_query = "SELECT * FROM users"

    cursor.execute(select_query)

    for row in cursor:
        print(f"ID: {row[0]}, Username: {row[1]}, Email: {row[2]}")

except psycopg2.Error as e:
    print(f"Error: {e}")

Step 5: Updating Data

Update a user’s email in the users table.

try:
    cursor = connection.cursor()

    update_query = "UPDATE users SET email = %s WHERE username = %s"
    user_data = ("[email protected]", "john_doe")

    cursor.execute(update_query, user_data)
    connection.commit()
    print("Data updated successfully")

except psycopg2.Error as e:
    print(f"Error: {e}")

Step 6: Deleting Data

Delete a user from the users table.

try:
    cursor = connection.cursor()

    delete_query = "DELETE FROM users WHERE username = %s"
    user_data = ("john_doe",)

    cursor.execute(delete_query, user_data)
    connection.commit()
    print("Data deleted successfully")

except psycopg2.Error as e:
    print(f"Error: {e}")

Step 7: Error Handling and Cleanup

Proper error handling is essential when working with databases. Close the PostgreSQL connection when done.

finally:
    if 'cursor' in locals():
        cursor.close()

    if 'connection' in locals():
        connection.close()
        print("Connection closed")

Conclusion

In this tutorial, we’ve covered the basics of performing CRUD operations using PostgreSQL in a Python application. You’ve learned how to connect to PostgreSQL, create a table, insert data, query data, update records, and delete records. These fundamental skills will serve as a solid foundation for building more complex database-driven applications with PostgreSQL and Python.

If you want to learn more about MySQL, please use our PostgreSQL tutorials and PostgreSQL Reference.