Cheat Sheet for Basic Python for Postgres

Combining Postgres with Python is an effective solution for complex data-driven tasks thanks to Python's straightforward programming experience and extensive libraries.

In real-world applications, effective data management is essential. Whether you're working with user profiles, financial transactions, product inventories, or any other structured data, you'll often need to create, read, update, or delete records. These four operations—collectively known as CRUD—are the foundation of all database interactions. Mastering CRUD with Python and Postgres lays the foundation for building web applications, automating data workflows, and developing robust backend services for use cases like e-commerce platforms, automation scripts, and mobile apps.

This tutorial shows how I set up a Postgres database using Supabase and connected a Python application to it. I show how to perform create, update, and delete operations, and how to make sure your database operations are safe, efficient, and maintainable.

Setting Up the Environment and Project

If you don't have Python installed on your machine, go to the official Python website and download Python 3.10 or later.

Create a new directory for your project by executing the following commands from your terminal or shell:

mkdir python-postgres-crud
cd python-postgres-crud

To keep your project isolated, create a virtual environment by running the following command:

python3 -m venv venv

Now activate the virtual environment. If you're on Windows, run the command below:

.\venv\Scripts\activate

On macOS/Linux, run the following command:

source venv/bin/activate

You also need a dedicated adapter to interact with Postgres using Python. Run the following command to install psycopg2, the most commonly used library for this purpose:

pip install psycopg2-binary

With your project created, you're ready to start setting up the database.

Setting Up a Postgres Database

Instead of setting up a local Postgres instance, I decided to use Supabase because it provides a fully managed, hosted Postgres database out of the box, simplifying the setup time and database management. This allows us to focus entirely on writing and deploying the application code without worrying about infrastructure details. First, visit Supabase and create a free account if you don't have one yet.

Note: If you prefer to work with a local database, you can install Postgres locally instead. However, this tutorial assumes you're using Supabase.

Once you're logged in, go to your dashboard and click New Project. Fill out the form by providing a project name (eg python-tutorial), setting a strong database password (make sure to save it), and selecting a region close to your location. After you submit the form, Supabase will take a minute or two to provision your project.

After your project is ready, click Connect on the top bar. Then scroll down to Session pooler and click View parameters. Here you'll find your connection details.

Copy your connection details (host, database, and user) and keep them somewhere safe because you'll need them later to connect your Python application to your database.

Connecting Python to Your Supabase Database

Inside your project directory, create a file named db_connect.py and add the following code to establish a connection to the database (make sure to replace YOUR_HOST, YOUR_USER, and YOUR_PASSWORD with your credentials):

import psycopg2

def create_connection():
    try:
        conn = psycopg2.connect(
            host="YOUR_HOST",
            database="postgres",
            user="YOUR_USER",
            password="YOUR_PASSWORD",
            port=5432
        )
        print("Connection to the database successful!")
        return conn
    except Exception as e:
        print(f"Failed to connect to database: {e}")
        return None

If the connection is successful, the code will generate a success message. If the connection fails, an error message will display.

To test the connection, create a new file named test_connection.py and add the following code:

from db_connect import create_connection

conn = create_connection()

if conn:
    conn.close()

Run this code from your terminal or shell:

python test_connection.py

This test verifies your database credentials, connection parameters, and network accessibility, confirming that your application can successfully communicate with the Supabase database. If everything is set up correctly, you'll see Connection to the database successful! printed in the console.

Preparing Your Database

Before your Python code can interact with any data, you'll need to set up a database table. In this example, you'll create a users table to store user information, including names and email addresses.

From the left navigation in the Supabase dashboard, navigate to SQL Editor and paste the following SQL command, then click Run to create a users table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This command defines a table where each user will have a unique ID, a name, a unique email address, and an automatically set creation timestamp. After you run the query, open Table Editor from the left navigation to ensure your table has been created successfully.

Inserting Records

To insert records, create a new file named insert_user.py and add the following code:

from db_connect import create_connection

conn = create_connection()
cur = conn.cursor()

try:
    cur.execute(
        """
        INSERT INTO users (name, email)
        VALUES (%s, %s)
        """,
        ("Alice", "[e-mail]")
    )
    conn.commit()
    print("User inserted successfully!")
except Exception as e:
    print(f"Failed to insert user: {e}")

cur.close()
conn.close()

This code uses the create_connection() function to create the connection, which is the communication channel between your Python code and the database. The conn.cursor() function creates a cursor that is used to execute SQL commands. The cur.execute() method executes the SQL insert, and the conn.commit() function saves the change to the database. If the operation is successful, you will get a success message, and a new user with the name Alice and email [e-mail] will be created.

Run the code by executing the following:

python insert_user.py

You should see an output similar to the following:

Connection to the database successful!
User inserted successfully!

Now, if you check your database from Table Editor, you should see that the user named Alice has been added to your table.

Adding Multiple Records

When you need to insert many records at once—such as during initial setup or data imports—you can do a bulk insert. To do so, create a new file named insert_many_users.py and add the following code:

from db_connect import create_connection

# Number of users to insert
x = 20

# Generate dummy users
users = [
    (f"User{i}", f"user{i}@example.com")
    for i in range(1, x + 1)
]

# Connect to the database
conn = create_connection()
cur = conn.cursor()

try:
    # Bulk insert using executemany
    cur.executemany(
        "INSERT INTO users (name, email) VALUES (%s, %s)",
        users
    )
    conn.commit()
    print(f"✅ Inserted {cur.rowcount} users successfully!")
except Exception as e:
    conn.rollback()
    print(f"❌ Failed to insert users: {e}")
finally:
    cur.close()
    conn.close()

This code specifies the number of users (twenty) through the variable x before it uses a for loop to fill the dummy users array. The code establishes a database connection before running an SQL query to insert all users simultaneously through the executemany method. If the operation succeeds, it prints a success message after committing changes. If something goes wrong, it rolls back the transaction and prints an error message.

Run the code by executing the following:

python insert_many_users.py

You should see an output similar to the following:

Connection to the database successful!
✅ Inserted 20 users successfully!

Now, if you check your database from Table Editor, you should see that twenty new user records have been added to the table.

Updating Records

Most applications experience data changes through time because users perform updates to their email addresses, name changes, and preference modifications. Updating operations enable you to modify existing database records without needing to delete or recreate them. To update the records in the example, create a file named update_user.py and add the following code:

from db_connect import create_connection

conn = create_connection()
cur = conn.cursor()

try:
    cur.execute(
        """
        UPDATE users
        SET email = %s
        WHERE id = %s
        """,
        ("[e-mail]", 1)
    )
    conn.commit()
    print("User updated successfully!")
except Exception as e:
    print(f"Failed to update user: {e}")

cur.close()
conn.close()

It's important that you use a WHERE clause when performing updates so you don't accidentally modify all your records. You can use the primary key (id) or a unique field (eg email) to precisely identify the record you want to update. Avoid using other fields that are not unique (eg name) as they can contain duplicate values and lead to unintended changes.

The code is similar to the previous one for creating a connection and handling errors, but this time you're running an update SQL query to update the email of the record that has the id 1.

Run the code by executing the following:

python update_user.py

You should see an output similar to the following:

Connection to the database successful!
User updated successfully!

Now, if you check your database from Table Editor, you should see that the record has been updated with a new email: [e-mail].

Deleting Records

In real-world applications, you may need to remove records, for example, when you delete a user account or clean up test data. While deleting data permanently is sometimes necessary (eg complying with privacy regulations or removing irrelevant test entries), many applications use "soft deletes" instead. Soft deletes mark records as deleted (eg with an is_deleted flag) rather than actually removing them, allowing for later recovery and preserving historical data. That said, to perform a permanent delete using SQL, create a new file named delete_user.py and add the following code:

from db_connect import create_connection

conn = create_connection()
cur = conn.cursor()

try:
    cur.execute(
        """
        DELETE FROM users
        WHERE id = %s
        """,
        (1,)
    )
    conn.commit()
    print("User deleted successfully!")
except Exception as e:
    print(f"Failed to delete user: {e}")

cur.close()
conn.close()

Again, this is similar to the previous code for creating a connection and handling errors, but you're running an update SQL query to delete a record that has the id 1, the email [e-mail], and the name Alice.

Run the code by executing the following:

python delete_user.py

You should see an output similar to the following:

Connection to the database successful!
User deleted successfully!

Now, if you check your database from Table Editor, you should see that the record with the email [e-mail] has been deleted.

Best Practices and Error Handling

When you're building a production-grade application, it's not just about making your code work—it's about making sure it works reliably, securely, and efficiently.

Parameterized Queries

To begin with, always use parameterized queries. These help defend your database from SQL injection, which can happen if user inputs are directly interpolated into SQL commands. Consider this vulnerable code:

cur.execute(f"DELETE FROM users WHERE email = '{email}'")

If the variable email contains malicious SQL code, your entire database could be compromised. For example, an attacker can put something like '; DROP TABLE users; –, and this will turn the SQL command into DELETE FROM users WHERE email = ''; DROP TABLE users; --'. This query will first delete users with empty email addresses, then completely remove your users table, which will delete all your user data.

Instead, use this:

cur.execute("DELETE FROM users WHERE email = %s", (email,))

This way, the input is safely handled by the database driver.

Using Context Managers

You should also use context managers with the with statement when dealing with connections and cursors. This approach ensures proper resource cleanup, even if your code crashes midway. This prevents potential resource leaks, such as leaving database connections open unintentionally. Here's how it works:

from contextlib import closing
import psycopg2

with closing(psycopg2.connect(...)) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM users")
        result = cur.fetchall()
        print(result)

Transactions

If you perform multiple operations that should succeed or fail as a group, wrap them in a transaction. Transactions help preserve data integrity. An example is a user registration where you want to create a new user and update their preferences at the same time. Here's how it works:

try:
    cur.execute("INSERT INTO users (username, email) VALUES (%s, %s)", (username, email))
    user_id = cur.fetchone()[0]
    cur.execute("INSERT INTO user_preferences (user_id, preference) VALUES (%s, %s)", (user_id, preference))
    conn.commit()
except Exception as e:
    conn.rollback()
    print("User registration failed:", e)

Exception Handling

You also need to consider how you'll handle exceptions specifically. Use targeted exception blocks like psycopg2.IntegrityError to give more accurate feedback to users. If you try to insert a duplicate email, catching this specific error allows you to tell the user exactly what went wrong rather than presenting a vague or generic message. Here's how it works:

try:
    cur.execute("INSERT INTO users(email) VALUES(%s)", (email,))
    conn.commit()
except psycopg2.IntegrityError:
    conn.rollback()
    print(f"The email '{email}' already exists.")

Fetching Data Efficiently

There are also a few ways you can improve performance. Avoid fetching more data than you need. If you're only displaying a user's name and email in your app, don't query every column from the database.

Instead of writing a query like this:

SELECT * FROM users;

You should write a more specific query like this:

SELECT name, email FROM users;

Pagination

When your data set grows large, pagination becomes essential. Loading thousands of records at once can crash your app or slow it down. Fetch a limited number of rows at a time:

SELECT * FROM users ORDER BY id LIMIT 50 OFFSET 100;

This retrieves fifty records starting from the 101st entry. It's great for building paginated UIs.

Indexing

Indexing also helps speed up searches and filtering. For example, if you frequently look up users by email, create an index:

CREATE INDEX idx_users_email ON users(email);

The use of indexes speeds up read operations but results in slower insert and update operations. The selection of your indexed columns depends on the actual behavior of your application. You should never run UPDATE or DELETE without a WHERE clause because this can unintentionally affect all rows in your table. You can even enforce safety nets in development environments by limiting update/delete operations with application-level checks.

Foreign Key Constraints and Cascading Deletes

A foreign key establishes a link between tables by ensuring that each value in one table points to an existing record in another table, such as linking each post to its corresponding user. The ON DELETE CASCADE feature provides a safe deletion method by automatically deleting related records in child tables whenever a parent record gets deleted.

For example, you can create a posts table that references the user's id as a foreign key, and on delete, you do cascade deletes. Here's what it will look like:

CREATE TABLE posts (

id SERIAL PRIMARY KEY,
user_id uuid REFERENCES users(id) ON DELETE CASCADE,
content TEXT
);

Use cascading deletes with caution, as they can unintentionally remove large amounts of related data. Always design your deletion logic with a clear understanding of the relationships in your schema.

Conclusion

The foundational skills explained in this tutorial are your "cheat sheet" that will allow you to interact with a Postgres database directly from Python. I explained how to do basic CRUD operations using Python and Postgres. I also covered how to implement transactions, handle exceptions properly, avoid SQL injection, and write optimized queries to ensure that your application not only works but remains fast and secure as it scales.

You can find the code used in this tutorial in the following GitHub repository.