Secure PostgreSQL Access with Psycopg

1. The Python Database API and psycopg

Before diving into code, it's useful to understand the context. Most Python database libraries, including the one we will use, adhere to a standard called the Python Database API Specification v2.0 (PEP 249). This specification defines a common interface for interacting with databases, meaning that the fundamental concepts you learn here—like connections, cursors, and execution methods—are transferable to other databases (like SQLite or MySQL) in Python.

For PostgreSQL, the definitive library is psycopg. We will be using the latest version, psycopg 3. It's a complete rewrite of the popular psycopg2, designed for modern Python with better performance and features. It's essential to be aware of the version, as many online tutorials still use the older psycopg2, which has significant differences in handling connections and transactions.

To begin, you will need to install it:

pip install "psycopg[binary]"

The [binary] option installs a pre-compiled version, which is typically the fastest and easiest way to get started.

2. Connection and Transaction Management

Interacting with a database from an application involves a clear lifecycle:

1. Connect: Establish a session with the database server.
2. Transact: Execute one or more SQL commands as an atomic unit.
3. Close: Terminate the session and release resources.

psycopg models this with two main objects:

• Connection: Represents the physical connection to the database. It manages the underlying network socket and transaction state.
• Cursor: A control structure that enables you to execute SQL commands within a transaction and traverse the result set. You can think of it as a cursor in a text editor, pointing to your current position within the results.

The modern, idiomatic way to manage these resources in Python is with with statements (context managers). This ensures that connections are properly closed and transactions are correctly committed or rolled back, even in the case of errors.

The official psycopg documentation provides the canonical example of this pattern. Study it carefully to understand the roles of connect, cursor, execute, and commit.

3. The Threat of SQL Injection

Now, we address one of the most critical security topics in web and application development: SQL injection. Before we see how psycopg solves this, it's vital to understand the problem itself.

SQL injection occurs when an attacker can manipulate an application's SQL queries by inserting malicious SQL code into user-supplied input. A naive developer might construct a query using simple string formatting:

# DO NOT DO THIS. THIS IS A DANGEROUS EXAMPLE.
user_id = "105 OR 1=1" # Malicious input from a web form
query = f"SELECT * FROM users WHERE id = '{user_id}';"



# The resulting query becomes:
# SELECT * FROM users WHERE id = '105 OR 1=1';
# This condition is always true, potentially returning ALL users.

This is a catastrophic vulnerability. The core problem is mixing code (the SQL statement) with data (the user input) in a way that allows the data to be misinterpreted as code.

The following video provides an excellent conceptual overview of SQL injection and introduces the standard defense: prepared statements (also called parameterized queries).

4. Safe Queries with Parameterization

psycopg implements this defense mechanism through server-side parameter binding. You write your SQL query with placeholders (%s) and pass the data as a separate argument to the execute() method. The library then safely handles sending the query and the data to the PostgreSQL server, ensuring the data cannot be interpreted as SQL code.

This is the only correct way to pass data into your queries.

5. Your Task: A Python Database Connector

It's time to put this into practice. You will create a Python script that connects to your database and executes one of the complex queries you wrote in the last lesson, but this time using secure parameterization.

1. Create a New File: In your project repository, create a new file named db.py. This will house your database interaction logic.

2. Write a Connector Function: Implement a Python function that retrieves all chunks for a given book title. This directly builds toward your project's retrieval needs. Use the with statement patterns you've just learned.

   import os
   import psycopg
   from psycopg.rows import dict_row # Helpful for getting results as dictionaries
   
   
   
   
   # It's good practice to load connection details from environment variables
   # For now, you can hardcode it, but we'll improve this later.
   DB_CONN_STRING = "dbname=yourdb user=youruser password=yourpass host=localhost"
   
   def get_chunks_for_book(book_title: str) -> list[dict]:
       """
       Retrieves all text chunks for a given book title from the database.
   
       Args:
           book_title: The title of the book to search for.
   
       Returns:
           A list of dictionaries, where each dictionary represents a chunk.
           Returns an empty list if the book is not found or has no chunks.
       """
   
   
   
       # Your query from the previous lesson, adapted for parameterization.
       # This assumes a schema with books, chapters, and chunks tables.
       # Adjust it to match your exact schema.
       query = """
           SELECT
               b.title AS book_title,
               c.chapter_number,
               c.title AS chapter_title,
               k.chunk_index,
               k.content
           FROM books b
           JOIN chapters c ON b.id = c.book_id
           JOIN chunks k ON c.id = k.chapter_id
           WHERE b.title = %s
           ORDER BY c.chapter_number, k.chunk_index;
       """
       
       results = []
       try:
   
   
   
           # Use a context manager for the connection
           with psycopg.connect(DB_CONN_STRING, row_factory=dict_row) as conn:
   
   
   
               # Use a context manager for the cursor
               with conn.cursor() as cur:
                   cur.execute(query, (book_title,)) # Note the trailing comma!
                   results = cur.fetchall()
       except psycopg.Error as e:
           print(f"Database error: {e}")
   
   
   
           # In a real app, you'd have more sophisticated logging and error handling
       
       return results
   
   
   
   
   # Simple test block
   if __name__ == '__main__':
   
   
   
       # Replace 'Dune' with a book title that exists in your database
       dune_chunks = get_chunks_for_book('Dune')
       if dune_chunks:
           print(f"Found {len(dune_chunks)} chunks for the book 'Dune'.")
           print("First chunk:")
           print(dune_chunks[0])
           print("\nLast chunk:")
           print(dune_chunks[-1])
       else:
           print("No chunks found for 'Dune'.")
   

   Note: psycopg.rows.dict_row is a "row factory" that makes the cursor return results as dictionaries instead of tuples, which is often more readable as you can access columns by name (e.g., row['content']) instead of by index (row[4]).

3. Run and Verify: Execute your script from the terminal (python db.py). Verify that it correctly fetches the data for a book you have loaded into your database.

4. Commit and Reflect: Commit the db.py file to your repository. In your engineering journal, reflect on this process. What was the most important concept from this lesson? Why is cur.execute(query, (book_title,)) so fundamentally different and safer than cur.execute(query % book_title)?

Conclusion

You have now established the most critical link in your application stack: the connection between your Python code and your PostgreSQL database. This is a significant step from being a programmer who uses databases to an engineer who builds systems with them.

Key Takeaways:

• psycopg is the standard library for connecting Python to PostgreSQL. Always be mindful of using the modern psycopg 3.
• Connection and cursor lifecycles are best managed with with statements, which ensure resources are closed and transactions are handled atomically.
• Never use string formatting (%, +, f-strings) to pass data into SQL queries. This is a severe security vulnerability.
• Always use parameterized queries (cur.execute(sql, params)) to safely separate SQL code from data.

In the next lesson, we will take the functionality you've just built and expose it to the world. We'll dive into API development with FastAPI, creating HTTP endpoints that will allow other services (or a front-end) to request data from your Narrative Intelligence Engine. The get_chunks_for_book function you wrote today will be one of the first pieces of logic you'll wire up to an API endpoint.