How do I actually establish a connection to my database?

Getting the connection open is the first hurdle. You'll use psycopg2.connect(), and while you can pass a single connection string (a DSN), I usually prefer passing the parameters as keyword arguments. It's just cleaner to read and easier to manage when you move those values into environment variables later.

Here is how I typically set it up. I always wrap this in a try block because, in the real world, databases go down or credentials expire, and you don't want your whole app to crash without a helpful error message.

import psycopg2

try:
    conn = psycopg2.connect(
        dbname="bookstore_db",
        user="postgres",
        password="mysecretpassword",
        host="localhost",
        port="5432"
    )
    print("Connection successful!")
except psycopg2.OperationalError as e:
    print(f"Unable to connect to the database: {e}")

How do I run a query and actually get the results back?

A connection object by itself doesn't "do" much. To execute SQL, you need a cursor. Think of the cursor as your pointer or your "session" for a specific set of operations. You use it to execute the command, and then you use it to fetch the rows.

Let's say we have a table called books and we want to find all titles by a specific author. I'll use fetchall() here to grab everything, but if you're expecting thousands of rows, you'd want to use a loop or fetchmany() to avoid eating up all your RAM.

cur = conn.cursor()

cur.execute("SELECT title, stock FROM books WHERE author = 'George Orwell';")

# This returns a list of tuples
results = cur.fetchall()

for row in results:
    print(f"Book: {row[0]} | Stock: {row[1]}")

cur.close()

Why aren't my changes saving when I run an UPDATE or INSERT?

This is the most common "gotcha" for people new to psycopg2. By default, psycopg2 opens a transaction the moment you execute your first command. If you run an UPDATE or INSERT and then just close the connection, PostgreSQL will roll back those changes because you never explicitly told it to save them.

You have to call conn.commit(). If you don't, your data essentially vanishes into the void the moment the script ends. I've spent way too many hours debugging "missing data" only to realize I forgot this one line.

cur = conn.cursor()
cur.execute("UPDATE books SET stock = stock - 1 WHERE title = '1984';")

# Without this line, the update won't persist!
conn.commit()

cur.close()

Is it safe to use f-strings to put variables into my queries?

Absolutely not. Please, never do this. If you use f-strings or .format() to build your SQL queries, you are opening your application up to SQL Injection. A user could pass a string like '1984'; DROP TABLE books; -- as a search term and wipe out your entire database.

Instead, use placeholders. psycopg2 uses %s as the placeholder. You pass the variables as a second argument to execute() (as a tuple), and the library handles the escaping and quoting safely for you.

# The WRONG way (Dangerous!)
# cur.execute(f"SELECT * FROM books WHERE title = '{user_input}'")

# The RIGHT way (Safe)
book_title = "Animal Farm"
cur.execute("SELECT * FROM books WHERE title = %s;", (book_title,))
result = cur.fetchone()

📋 Practical Task

Exercise: Building a Book Inventory Stock-Up Tool

You need to create a script that allows a manager to add new stock to an existing book in the database. Your task is to write a Python script that does the following:

• Connects to a PostgreSQL database named bookstore_db.
• Prompts the user for a book title and the quantity to add.
• Executes an UPDATE statement to increase the stock column by the provided quantity for that specific title.
• Uses parameterized queries (the %s syntax) to prevent SQL injection.
• Commits the transaction so the changes are saved permanently.
• Closes the cursor and the connection properly.

Note: Assume the table books already exists with columns title (text) and stock (integer).