Working with Results

Fetch Methods

cur.execute("SELECT id, username, email FROM users")

# fetchone() - Get single row
row = cur.fetchone()  # Returns tuple or None

# fetchall() - Get all rows
rows = cur.fetchall()  # Returns list of tuples

# fetchmany(n) - Get n rows
batch = cur.fetchmany(100)  # Returns list of up to n tuples

# Iterate directly (memory efficient)
for row in cur:
    print(row)

Named Tuples for Readable Results

from psycopg2.extras import NamedTupleCursor

with conn.cursor(cursor_factory=NamedTupleCursor) as cur:
    cur.execute("SELECT id, username, email FROM users")
    for user in cur:
        # Access by name instead of index
        print(f"User: {user.username}, Email: {user.email}")

Dictionary Cursor

from psycopg2.extras import DictCursor, RealDictCursor

# DictCursor - access by index or name
with conn.cursor(cursor_factory=DictCursor) as cur:
    cur.execute("SELECT * FROM users WHERE id = %s", (1,))
    user = cur.fetchone()
    print(user['username'])  # By name
    print(user[1])           # By index

# RealDictCursor - returns actual dict
with conn.cursor(cursor_factory=RealDictCursor) as cur:
    cur.execute("SELECT * FROM users")
    users = cur.fetchall()
    # Returns list of dicts
    for user in users:
        print(user)  # {'id': 1, 'username': 'john', ...}

Accessing Column Metadata

cur.execute("SELECT id, username, email FROM users")

# Get column names
columns = [desc[0] for desc in cur.description]
print(columns)  # ['id', 'username', 'email']

# Create dicts manually
rows = cur.fetchall()
result = [dict(zip(columns, row)) for row in rows]

Server-Side Cursors for Large Results

# For very large result sets, use server-side cursor
with conn.cursor(name='large_cursor') as cur:
    cur.itersize = 1000  # Fetch 1000 rows at a time
    cur.execute("SELECT * FROM large_table")

    for row in cur:
        process(row)  # Memory-efficient iteration

Processing Query Results

Processing Query Results

Working with Results

Fetch Methods

Named Tuples for Readable Results

Dictionary Cursor

Accessing Column Metadata

Server-Side Cursors for Large Results