Offensive Security Researcher & Quantum Cryptography Analyst

PostgreSQL Database Access Using Psycopg2 in Python: Complete Connection and Query Execution Guide

Abdul Wahab Junaid
PostgreSQL Database access using psycopg2 in python

PostgreSQL Database access using psycopg2 in python

PostgreSQL was the first “real” database I used beyond SQLite, and psycopg2 was my introduction to it from Python. What I appreciated almost immediately, coming from SQL Server’s ODBC-based setup, was how much simpler the installation and connection process felt — psycopg2 speaks PostgreSQL’s wire protocol directly, no separate system-level driver required. Here’s everything I’ve learned working with it in real projects.

Installing Psycopg2

pip install psycopg2-binary

I specifically use psycopg2-binary for development and most production use, since it bundles precompiled binary dependencies (like libpq, PostgreSQL’s own client library) and avoids needing a C compiler and PostgreSQL development headers installed on the system. The plain psycopg2 package (without -binary) compiles from source against your system’s PostgreSQL libraries — the PostgreSQL project’s own documentation notes this can be preferable for certain production deployments where you want tighter control over the exact libpq version being used, but psycopg2-binary is the pragmatic default for most projects.

Establishing a Connection

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="mydatabase",
    user="myuser",
    password="mypassword"
)

cursor = conn.cursor()

Alternatively, a connection string (DSN — Data Source Name) works too:

conn = psycopg2.connect("host=localhost port=5432 dbname=mydatabase user=myuser password=mypassword")

# or as a URI
conn = psycopg2.connect("postgresql://myuser:mypassword@localhost:5432/mydatabase")

I generally prefer the keyword-argument form for readability in scripts, and the URI form when credentials come from a single environment variable (many hosting platforms, like Heroku historically, provide a DATABASE_URL in exactly this format).

Executing Queries

cursor.execute("SELECT id, name, email FROM users WHERE active = %s", (True,))

rows = cursor.fetchall()
for row in rows:
    print(row)

Note psycopg2‘s parameter placeholder is %s — regardless of the actual data type being passed — which trips people up coming from other drivers (like pyodbc‘s ? or SQLAlchemy’s named :param style). It’s not Python’s old-style % string formatting despite looking similar; psycopg2 intercepts these placeholders itself and handles proper escaping internally, so never use actual Python % string formatting to build the query yourself.

# NEVER do this — looks similar but is a genuine SQL injection risk:
name = "Alice"
cursor.execute("SELECT * FROM users WHERE name = '%s'" % name)

# Always do this instead — psycopg2 handles escaping safely:
cursor.execute("SELECT * FROM users WHERE name = %s", (name,))

Fetching Results

cursor.execute("SELECT id, name FROM users")

one = cursor.fetchone()      # single row tuple, or None
many = cursor.fetchmany(5)   # up to 5 rows
# re-execute since the cursor's position has moved past available rows
cursor.execute("SELECT id, name FROM users")
everything = cursor.fetchall()  # all remaining rows as a list of tuples

for row in cursor:  # lazy, memory-friendly iteration
    print(row)

By default, rows come back as plain tuples, accessed by positional index (row[0], row[1]). For more readable code, I usually switch to a dictionary-like cursor.

import psycopg2.extras

cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cursor.execute("SELECT id, name, email FROM users")

for row in cursor.fetchall():
    print(row["name"], row["email"])  # access by column name instead of index

RealDictCursor returns each row as a dictionary-like object, which I find dramatically improves code readability, especially in larger codebases where remembering “column 3 is the email field” by position alone becomes error-prone.

Inserting, Updating, and Deleting

cursor.execute(
    "INSERT INTO users (name, email, active) VALUES (%s, %s, %s)",
    ("Alice Johnson", "alice@example.com", True)
)
conn.commit()

cursor.execute("UPDATE users SET active = %s WHERE id = %s", (False, 42))
conn.commit()

cursor.execute("DELETE FROM users WHERE active = %s", (False,))
conn.commit()

Like most DB-API 2.0 drivers, psycopg2 requires an explicit conn.commit() to persist changes — forgetting it is one of the most common early mistakes, resulting in inserted data that mysteriously “disappears” (it was never actually committed to the database in the first place).

Getting the Auto-Generated ID After an Insert

PostgreSQL’s RETURNING clause is a genuinely nice feature I lean on constantly — it lets me get back the auto-generated primary key from an INSERT in the same round trip, without a separate query.

cursor.execute(
    "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id",
    ("Bob Smith", "bob@example.com")
)
new_id = cursor.fetchone()[0]
conn.commit()
print(f"New user ID: {new_id}")

Using Context Managers

psycopg2 supports with blocks for both connections and cursors, though the exact semantics are worth understanding precisely: a with conn: block automatically commits on success or rolls back on an exception, but does not close the connection itself — you still need to close the connection explicitly (or wrap the whole thing in its own outer context management pattern).

import psycopg2

conn = psycopg2.connect("postgresql://myuser:mypassword@localhost/mydatabase")

try:
    with conn:  # handles commit/rollback automatically
        with conn.cursor() as cursor:
            cursor.execute("INSERT INTO users (name) VALUES (%s)", ("Carol",))
finally:
    conn.close()  # still needs to be closed explicitly

This distinction — with conn managing transactions, not connection lifetime — is subtle and catches people off guard coming from libraries where with closes everything uniformly.

Handling Transactions Explicitly

try:
    cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (100, 1))
    cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (100, 2))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"Transaction failed, rolled back: {e}")

This is a classic transactional pattern — transferring money between two accounts needs both updates to succeed together or neither to happen at all, and conn.rollback() undoes any partial changes if something fails partway through, preserving the database’s consistency.

Batch Operations for Performance

Inserting many rows one at a time, each with its own execute() call and network round trip, is slow. psycopg2 offers executemany(), and — for genuinely large batches — the even faster psycopg2.extras.execute_values().

import psycopg2.extras

data = [
    ("Alice", "alice@example.com"),
    ("Bob", "bob@example.com"),
    ("Carol", "carol@example.com"),
]

# Standard batch approach
cursor.executemany("INSERT INTO users (name, email) VALUES (%s, %s)", data)
conn.commit()

# Faster for large batches: constructs a single multi-row INSERT statement
psycopg2.extras.execute_values(
    cursor,
    "INSERT INTO users (name, email) VALUES %s",
    data
)
conn.commit()

execute_values() is significantly faster than executemany() for large datasets, because executemany() in psycopg2 (unlike some other drivers) still issues one round trip per row under the hood in older versions, while execute_values() explicitly rewrites the query into a single multi-row INSERT statement, dramatically reducing network round-trip overhead.

Using psycopg2 with pandas

import pandas as pd
import psycopg2

conn = psycopg2.connect("postgresql://myuser:mypassword@localhost/mydatabase")

df = pd.read_sql("SELECT * FROM sales WHERE year = %(year)s", conn, params={"year": 2026})
print(df.head())

This is one of my most common real-world uses of psycopg2 — as the connection object underlying a pandas query, letting me pull PostgreSQL data directly into a DataFrame for analysis.

Connection Pooling

For applications serving concurrent requests, psycopg2 includes basic built-in pooling via psycopg2.pool.

from psycopg2 import pool

connection_pool = pool.SimpleConnectionPool(
    minconn=1,
    maxconn=10,
    dsn="postgresql://myuser:mypassword@localhost/mydatabase"
)

conn = connection_pool.getconn()
try:
    cursor = conn.cursor()
    cursor.execute("SELECT 1")
    print(cursor.fetchone())
finally:
    connection_pool.putconn(conn)  # return the connection to the pool, don't close it

For larger applications, I’d typically reach for SQLAlchemy’s more sophisticated pooling on top of psycopg2, but the built-in pool module is genuinely useful for simpler multi-threaded scripts and small services that don’t need a full ORM layer.

Security Considerations

Always use parameterized queries (%s placeholders with a tuple/dict of values) — never string-format or concatenate user input directly into SQL, as covered above.

Use SSL for remote connections:

conn = psycopg2.connect(
    "postgresql://myuser:mypassword@remote-host:5432/mydatabase?sslmode=require"
)

Store credentials outside source code, using environment variables or a secrets manager rather than hardcoding them.

import os
import psycopg2

conn = psycopg2.connect(
    host=os.environ["DB_HOST"],
    dbname=os.environ["DB_NAME"],
    user=os.environ["DB_USER"],
    password=os.environ["DB_PASSWORD"],
)

Real-World Applications

Common Mistakes

Confusing %s in psycopg2 with Python’s old string formatting operator. They look identical but behave completely differently — psycopg2 intercepts and safely escapes these placeholders; never apply Python’s % operator to build the query string yourself.

Forgetting conn.commit() after write operations, leading to changes that appear to vanish.

Misunderstanding what with conn: actually does. It manages the transaction (commit/rollback), not the connection’s open/closed lifecycle — you still need conn.close() separately.

Using executemany() for very large batches when execute_values() would be dramatically faster, due to reduced round-trip overhead.

Not handling exceptions with an explicit conn.rollback(), leaving a transaction in a failed, half-completed state that can block subsequent queries on the same connection until explicitly rolled back.

Debugging Tips

Performance Considerations

FAQs

Should I use psycopg2 or psycopg2-binary? psycopg2-binary for most projects and development environments, since it avoids needing compiler toolchains and PostgreSQL dev headers; consider the source-compiled psycopg2 for production deployments where you want explicit control over the underlying libpq version.

Why is my inserted data not showing up? Almost always a missing conn.commit() — writes aren’t persisted until explicitly committed.

What’s the difference between fetchone(), fetchmany(), and fetchall()? fetchone() returns a single row (or None), fetchmany(n) returns up to n rows, and fetchall() returns every remaining row at once — choose based on result set size and memory constraints.

Is psycopg2 the only PostgreSQL driver for Python? No — asyncpg is a popular alternative for asynchronous applications, offering significantly better performance in asyncio-based codebases, though with a different API shape than the DB-API 2.0-style psycopg2. psycopg3 (the newer major version, often installed as the psycopg package) is also available with both sync and async support.

Summary

psycopg2 remains one of the most widely used and battle-tested ways to connect Python to PostgreSQL, offering a clean DB-API 2.0-compliant interface, direct wire-protocol communication (no separate system driver required, unlike SQL Server), and useful PostgreSQL-specific extras like RETURNING clauses and execute_values() for efficient bulk operations. The essentials to get right are consistent use of parameterized queries, explicit transaction management with commits and rollbacks, and reaching for connection pooling once an application serves meaningful concurrent load.

References

Exit mobile version