Getting the Values from the Database and Error Handling in Python: Complete Database Programming Guide

The first time I connected Python to a real production database, I made every mistake in the book — I didn’t close connections, I didn’t handle a single exception, and one bad query took down an entire batch job at 2 AM. Since then, I’ve written enough database code to know that fetching values correctly and handling errors gracefully aren’t optional extras — they’re the difference between a script that works on my machine and one that survives contact with real-world data. This guide walks through how I retrieve data from databases in Python and how I’ve learned to handle the errors that inevitably come with it.

Setting the Stage: DB-API 2.0

Almost every database library in Python — sqlite3, psycopg2 for PostgreSQL, mysql-connector-python, pyodbc — follows the same underlying specification called PEP 249, also known as the Python Database API (DB-API) 2.0. Once I understood this, working with different databases stopped feeling like learning a new language every time. The core pattern is always: connect, get a cursor, execute a query, fetch results, handle exceptions, close the connection.

import sqlite3

connection = sqlite3.connect('example.db')
cursor = connection.cursor()
cursor.execute('SELECT id, name, email FROM users')
rows = cursor.fetchall()

for row in rows:
    print(row)

connection.close()

The Three Ways to Fetch Data

Every DB-API-compliant cursor gives me three fetch methods, and picking the right one matters for both memory usage and clarity.

import sqlite3

connection = sqlite3.connect('example.db')
cursor = connection.cursor()
cursor.execute('SELECT id, name FROM users')

# Fetch one row at a time
first_row = cursor.fetchone()
print(first_row)  # (1, 'Alice') or None if no rows

# Fetch a limited batch
cursor.execute('SELECT id, name FROM users')
batch = cursor.fetchmany(5)
print(batch)

# Fetch everything remaining
cursor.execute('SELECT id, name FROM users')
all_rows = cursor.fetchall()
print(all_rows)

connection.close()

• fetchone() returns a single tuple, or None if there’s nothing left. I use this when I only need one result, like checking whether a username already exists.
• fetchmany(size) returns up to size rows, useful for processing huge result sets in manageable chunks without loading everything into memory at once.
• fetchall() returns every remaining row as a list of tuples. Convenient, but dangerous on very large tables — I’ve watched a script eat several gigabytes of RAM because I called fetchall() on a multi-million-row table without thinking.

Iterating a Cursor Directly

Something a lot of people don’t realize: the cursor itself is iterable, and this is often the most memory-efficient approach because it streams rows rather than materializing them all at once (behavior depends on the driver, but most implement it this way internally).

cursor.execute('SELECT id, name FROM users')
for row in cursor:
    print(row)

This pattern is my default now unless I have a specific reason to use fetchall().

Getting Column Names Along with Values

Raw tuples are fine until you need to remember that row[2] is the email column. I usually map results to dictionaries using cursor.description:

import sqlite3

connection = sqlite3.connect('example.db')
cursor = connection.cursor()
cursor.execute('SELECT id, name, email FROM users')

columns = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()

results = [dict(zip(columns, row)) for row in rows]
print(results)

Output:

[{'id': 1, 'name': 'Alice', 'email': 'alice@example.com'}, ...]

For SQLite specifically, there’s an even cleaner built-in way using row_factory:

import sqlite3

connection = sqlite3.connect('example.db')
connection.row_factory = sqlite3.Row
cursor = connection.cursor()
cursor.execute('SELECT id, name, email FROM users')

for row in cursor.fetchall():
    print(row['name'], row['email'])

sqlite3.Row objects support both index-based and key-based access, which I find genuinely convenient for readability.

Using Parameterized Queries (Not String Formatting)

This is the single most important habit I’ve built around database code, and it ties directly into error handling and security. I never, ever build SQL queries with string concatenation or f-strings when user input is involved.

# DANGEROUS - never do this
user_input = "1 OR 1=1"
cursor.execute(f"SELECT * FROM users WHERE id = {user_input}")

# SAFE - parameterized query
user_id = 1
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

Parameterized queries protect against SQL injection because the database driver treats the parameter as pure data, never as executable SQL syntax. Different drivers use different placeholder styles (? for sqlite3, %s for psycopg2/MySQL), so I always check the specific library’s paramstyle.

Error Handling: The Exception Hierarchy

DB-API 2.0 defines a standard hierarchy of exceptions that every compliant library implements, which means the error-handling patterns I write for SQLite largely transfer to PostgreSQL or MySQL with minimal changes.

Exception
 └── Warning
 └── Error
      ├── InterfaceError
      └── DatabaseError
           ├── DataError
           ├── OperationalError
           ├── IntegrityError
           ├── InternalError
           ├── ProgrammingError
           └── NotSupportedError

Here’s how I typically structure error handling in a real script:

import sqlite3

connection = None
try:
    connection = sqlite3.connect('example.db')
    cursor = connection.cursor()
    cursor.execute("INSERT INTO users (id, name, email) VALUES (?, ?, ?)",
                   (1, 'Bob', 'bob@example.com'))
    connection.commit()

except sqlite3.IntegrityError as e:
    print(f"Integrity constraint violated: {e}")
    if connection:
        connection.rollback()

except sqlite3.OperationalError as e:
    print(f"Operational error (e.g. table missing, locked db): {e}")

except sqlite3.Error as e:
    print(f"General database error: {e}")
    if connection:
        connection.rollback()

finally:
    if connection:
        connection.close()

Catching the most specific exception first matters here. An IntegrityError (like a duplicate primary key) usually means my data is wrong and needs different handling than an OperationalError (like a locked database file), which might just need a retry.

Using Context Managers for Automatic Cleanup

I used to forget to close connections constantly, especially when an exception was raised mid-script and skipped over my connection.close() line at the bottom. Context managers fixed this permanently.

import sqlite3

with sqlite3.connect('example.db') as connection:
    cursor = connection.cursor()
    cursor.execute('SELECT * FROM users')
    rows = cursor.fetchall()
    print(rows)

One subtlety worth knowing: for sqlite3, the with statement automatically commits or rolls back the transaction, but it does not automatically close the connection. I still explicitly close it, or nest it inside a try/finally, when I need that guarantee.

import sqlite3

connection = sqlite3.connect('example.db')
try:
    with connection:
        cursor = connection.cursor()
        cursor.execute("UPDATE users SET name = ? WHERE id = ?", ('Charlie', 1))
finally:
    connection.close()

Retrying Transient Errors

Some database errors are transient — a locked database, a dropped network connection — and worth retrying rather than failing immediately. Here’s a pattern I’ve used in production automation scripts:

import sqlite3
import time

def execute_with_retry(cursor, query, params=(), max_retries=3):
    for attempt in range(1, max_retries + 1):
        try:
            cursor.execute(query, params)
            return
        except sqlite3.OperationalError as e:
            if 'locked' in str(e) and attempt < max_retries:
                print(f"Database locked, retrying ({attempt}/{max_retries})...")
                time.sleep(0.5 * attempt)
            else:
                raise

Performance Considerations

Fetching data isn’t free, and the way I fetch matters at scale:

• fetchall() on large result sets loads everything into memory, which is O(n) space where n is the row count.
• Iterating the cursor or using fetchmany() keeps memory bounded, which matters a lot when processing tables with millions of rows.
• Batching INSERT statements with executemany() instead of looping over individual execute() calls dramatically reduces round-trip overhead:

users = [(2, 'Dana', 'dana@example.com'), (3, 'Eli', 'eli@example.com')]
cursor.executemany("INSERT INTO users (id, name, email) VALUES (?, ?, ?)", users)
connection.commit()

I’ve seen executemany() cut bulk-insert time by more than half compared to a naive loop, because it reduces the number of separate transaction/network round trips.

Common Mistakes I’ve Made

• Not closing connections, leading to “too many connections” errors under load.
• Catching bare Exception everywhere instead of specific DB-API exceptions, which hides bugs and makes debugging painful.
• Forgetting to commit after INSERT/UPDATE/DELETE statements — nothing persists until connection.commit() runs (unless autocommit mode is on).
• Building queries with string formatting, opening the door to SQL injection.
• Ignoring fetchone() returning None and then trying to index into it, causing a TypeError.

result = cursor.fetchone()
if result is None:
    print("No matching record found.")
else:
    print(result[0])

FAQs

What’s the difference between commit() and rollback()? commit() permanently saves all changes made in the current transaction. rollback() discards them, reverting the database to its state before the transaction began — essential when an error occurs mid-transaction.

Why did my INSERT not show up in the database? Almost always a missing connection.commit(). Some drivers default to autocommit off, meaning nothing is written until you explicitly commit.

How do I handle a database connection that drops mid-script? Wrap operations in try/except for OperationalError (or the equivalent in your driver), and consider a retry-with-backoff pattern for transient network issues.

Is fetchall() ever the wrong choice? Yes, for large result sets. Prefer iterating the cursor directly or using fetchmany() in chunks to keep memory usage predictable.

Should I use raw SQL or an ORM like SQLAlchemy? Depends on the project. I use raw DB-API code for small scripts and performance-critical paths, and reach for SQLAlchemy when a project grows complex enough that managing raw SQL becomes error-prone.

Summary

Getting values out of a database in Python is straightforward on the surface — connect, execute, fetch — but doing it well means understanding the fetch methods and their memory trade-offs, using parameterized queries religiously, and building error handling around the DB-API exception hierarchy rather than generic catch-alls. Combined with context managers and sensible retry logic, these habits have saved me from more production incidents than I can count.

References

• Python Official Documentation: sqlite3 — DB-API 2.0 interface for SQLite databases
• PEP 249 – Python Database API Specification v2.0: https://peps.python.org/pep-0249/
• Python Official Documentation: Errors and Exceptions