Free lesson · Python Basic to Advance

Db API Overview

Hard9 min readpython

DB-API Overview

What the DB-API Is

The Python Database API Specification (PEP 249, often called DB-API 2.0) is the standard interface that database drivers implement. Whether you're talking to SQLite, PostgreSQL, MySQL, Oracle, or SQL Server, the shape of the code is the same: open a connection, get a cursor, execute SQL, fetch results, close. The driver does the protocol-specific work; your code uses a uniform API.

import sqlite3    # could be psycopg, pymysql, oracledb, pyodbc - same shape

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()

cursor.execute("CREATE TABLE users (id INTEGER, name TEXT)")
cursor.execute("INSERT INTO users VALUES (?, ?)", (1, "alice"))
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
print(rows)    # [(1, 'alice')]

connection.commit()
cursor.close()
connection.close()

This code uses the DB-API. Connect opens a connection. Cursor returns an object that executes statements and holds results. Execute runs SQL with parameters. Fetchall returns the result rows. The same code pattern works with every compliant driver - switching from sqlite3 to psycopg or pymysql is mostly a matter of changing the import and connection string.

The DB-API is what makes Python's database story sane. One pattern works everywhere.

Why a Standard Matters

Before PEP 249, every database library invented its own API. PEP 249 standardized:

connect(...) - module-level function returning a Connection
Connection.cursor() - returns a Cursor
Cursor.execute(sql, params) - runs one statement
Cursor.executemany(sql, seq_of_params) - runs the same statement many times
Cursor.fetchone() / fetchmany(n) / fetchall() - retrieve results
Connection.commit() / rollback() - transaction control
Connection.close() / Cursor.close() - cleanup
Exception hierarchy - Error, Warning, InterfaceError, DatabaseError, etc.

# Any DB-API driver follows this shape
import some_driver

conn = some_driver.connect(...)
cur = conn.cursor()
cur.execute(sql, params)
rows = cur.fetchall()
conn.commit()

This code shows the universal pattern. Connect, cursor, execute, fetch. Every compliant driver follows it. Switching databases is a matter of changing the import and connection arguments - the code in between is largely the same.

For most application code, programming to the DB-API and using a specific driver works. Higher-level libraries (SQLAlchemy, ORM frameworks) build on top of DB-API drivers.

Connections

A connection represents a session with the database. Open one at the start, close at the end:

import sqlite3

# Open
conn = sqlite3.connect("mydata.db")

# Use it via cursors (covered below)
# ...

# Close
conn.close()

This code opens and closes a connection. The connect call takes driver-specific arguments - a file path for SQLite, a host plus credentials for client-server databases. Always close connections to release resources. The recommended form uses a context manager - covered below.

Connections hold real resources (TCP sockets, file handles, server-side state). Leaks tie up server capacity.

Cursors

A cursor is the object that runs queries and holds results. Multiple cursors per connection are usually fine:

import sqlite3
conn = sqlite3.connect(":memory:")

cur = conn.cursor()

# Execute a statement
cur.execute("CREATE TABLE items (id INTEGER, name TEXT)")
cur.execute("INSERT INTO items VALUES (?, ?)", (1, "apple"))
cur.execute("INSERT INTO items VALUES (?, ?)", (2, "banana"))

# Read results
cur.execute("SELECT * FROM items")
for row in cur:
    print(row)
# (1, 'apple')
# (2, 'banana')

cur.close()

This code shows the cursor lifecycle. Execute runs SQL - for select statements, results are buffered or streamed depending on the driver. Iterating the cursor yields rows. Close releases the cursor's resources. Many drivers reuse a single cursor across calls - but explicit close is a good habit.

The cursor pattern dates back decades - it's familiar across many languages and databases.

Fetch Methods

Three ways to retrieve results:

import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("SELECT 1 UNION SELECT 2 UNION SELECT 3")

# One row at a time
row = cur.fetchone()
print(row)    # (1,)
row = cur.fetchone()
print(row)    # (2,)

# A batch of rows
cur.execute("SELECT 1 UNION SELECT 2 UNION SELECT 3")
rows = cur.fetchmany(2)
print(rows)    # [(1,), (2,)]

# All remaining rows
cur.execute("SELECT 1 UNION SELECT 2 UNION SELECT 3")
all_rows = cur.fetchall()
print(all_rows)    # [(1,), (2,), (3,)]

This code shows the fetch methods. Fetchone returns a single row or None when exhausted. Fetchmany takes a count and returns up to that many. Fetchall returns everything remaining. For large results, fetchone or fetchmany prevents loading the entire result set into memory.

For large queries, prefer fetchone() in a loop or fetchmany() with a batch size - fetchall() materializes everything at once.

Iterating the Cursor

Most drivers let you iterate the cursor directly, equivalent to repeated fetchone():

import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("SELECT 1 UNION SELECT 2 UNION SELECT 3")

for row in cur:
    print(row)
# (1,)
# (2,)
# (3,)

This code iterates a cursor. Each iteration produces one row. Memory-friendly - only one row is in scope at a time. The driver may still buffer behind the scenes, but the Python side doesn't materialize the full result. For large query results, this is the right form.

`executemany` for Bulk Operations

For inserting or updating many rows, executemany is much faster than a loop of execute calls:

import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE items (id INTEGER, name TEXT)")

# Slow - one round-trip per insert
for item in items:
    cur.execute("INSERT INTO items VALUES (?, ?)", item)

# Fast - one call, many rows
items = [(1, "apple"), (2, "banana"), (3, "cherry")]
cur.executemany("INSERT INTO items VALUES (?, ?)", items)

conn.commit()

This code shows executemany. The same SQL runs for each tuple of parameters. Drivers can optimize this - batching the statements together for fewer round-trips. For client-server databases, the difference can be ten times faster or more. Useful for bulk loading, batch updates, and any case where many rows share the same statement shape.

Parameters

Parameters are passed separately from the SQL string. The placeholder syntax depends on the driver:

Driver	Placeholder style
`sqlite3`	`?` (qmark)
`psycopg`	`%s` (format)
`psycopg` (named)	`%(name)s` (pyformat)
`pymysql`	`%s` (format)
`oracledb`	`:name` (named)

# sqlite3 - qmark style
cur.execute("SELECT * FROM users WHERE id = ?", (user_id,))

# psycopg - format style
cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))

# psycopg - named (pyformat) style
cur.execute("SELECT * FROM users WHERE id = %(id)s", {"id": user_id})

This code shows parameter styles. Each driver implements one or two of the styles defined by the DB-API. The principle is the same: parameters are passed separately from the SQL, which prevents injection and lets the driver handle quoting. Topic 25.4 covers parameterized queries in depth.

The placeholder difference is one of the few non-portable parts of switching drivers. Higher-level libraries like SQLAlchemy abstract over this.

Context Managers

Most drivers support context managers - with blocks for connection and cursor lifecycle:

import sqlite3

# Connection as context manager - auto commits on success, rolls back on exception
with sqlite3.connect("mydata.db") as conn:
    cur = conn.cursor()
    cur.execute("INSERT INTO items VALUES (?, ?)", (1, "apple"))
    # commits at end of block

# Cursor as context manager - auto-closes
with sqlite3.connect(":memory:") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT 1")
        print(cur.fetchone())

This code uses context managers. Connection with manages the transaction - commits on success, rolls back on exception. Cursor with manages the cursor's lifecycle. The behavior varies by driver - sqlite3 commits but doesn't close; psycopg closes cursors but doesn't close connections. Read each driver's docs for the exact semantics.

Behavior varies by driver - read the driver's docs. The general pattern is exception-safe cleanup.

Exception Hierarchy

The DB-API defines a standard exception hierarchy:

Exception
└── Warning
└── Error
    ├── InterfaceError      # driver/interface issues
    └── DatabaseError       # database errors
        ├── DataError       # bad data (out of range, etc.)
        ├── OperationalError    # connection lost, timeout, etc.
        ├── IntegrityError      # constraint violation
        ├── InternalError       # internal database error
        ├── ProgrammingError    # SQL syntax errors, wrong column count
        └── NotSupportedError   # unsupported operation

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

This text shows the exception tree. All database errors inherit from Error. Specific types help categorize: integrity error for constraint violations, operational error for connection problems, programming error for SQL mistakes. Useful for handling specific cases - retry on operational error, surface integrity error to the user as a validation problem, treat programming error as a bug.

import sqlite3

try:
    cur.execute("INSERT INTO users VALUES (?, ?)", (1, "alice"))
except sqlite3.IntegrityError:
    # duplicate key, foreign key violation, NOT NULL violation
    print("constraint violation")
except sqlite3.OperationalError:
    # connection lost, table not found, etc.
    print("operational issue")
except sqlite3.Error:
    # catch-all DB error
    print("database error")

This code catches specific exception types. Integrity error for constraint violations - duplicate keys, missing required fields. Operational error for runtime issues - connection drops, missing tables. The generic Error parent catches anything else. Useful for retry logic, user-facing validation messages, and clear error reporting.

DB-API Drivers

A few common drivers:

Database	Driver	DB-API style
SQLite	`sqlite3` (stdlib)	qmark `?`
PostgreSQL	`psycopg` (v3)	format `%s`, named `%(name)s`
PostgreSQL	`psycopg2` (older)	format `%s`, named `%(name)s`
MySQL	`pymysql`	format `%s`
MySQL	`mysqlclient`	format `%s`
Oracle	`oracledb`	named `:name`
SQL Server	`pyodbc`	qmark `?`
DuckDB	`duckdb`	qmark `?` and `$n`

For most projects, picking a driver is a one-time decision. SQLAlchemy (Topics 25.6–25.7) wraps drivers and exposes a uniform interface.

A Realistic Example

A complete pattern combining the elements:

import sqlite3
from contextlib import closing

def get_users_by_role(role):
    with sqlite3.connect("users.db") as conn:
        with closing(conn.cursor()) as cur:
            cur.execute("""
                SELECT id, name, email
                FROM users
                WHERE role = ?
                ORDER BY name
            """, (role,))
            return cur.fetchall()

users = get_users_by_role("admin")
for user_id, name, email in users:
    print(f"{user_id}: {name} <{email}>")

This code shows a realistic database function. Connection wrapped in with, cursor wrapped in closing for explicit cleanup. Parameterized query for the role. Multi-line SQL for readability. Returns the full result list. The pattern works the same for any DB-API driver - switch sqlite3 to psycopg and the function still works.

This is the shape most direct-DB-API code takes - function-scoped connections, explicit cursors, parameterized queries, structured results.

When to Use the DB-API Directly

Direct DB-API use earns its place when:

The application has a small number of queries
Performance matters and you want full control over SQL
The team is comfortable writing SQL
The use case is reporting, analytics, or batch operations

Higher-level libraries (SQLAlchemy, ORM frameworks) often win when:

Many queries with similar shapes - boilerplate dominates
Schema is complex with many relationships
Multiple databases - the abstraction helps portability
Type safety and IDE assistance matter

For most production code, the choice is between direct SQL via the DB-API and SQLAlchemy. ORMs sit further up. Topics 25.6 and 25.7 cover SQLAlchemy.

Performance

DB-API overhead:

Connection setup: client-server connections are milliseconds; SQLite file open is microseconds
Per-statement: round-trip latency dominates client-server cases (executemany helps)
Result fetching: depends on driver and result size

For most application code, the database itself (query planning, I/O) is the bottleneck - not the Python side.

Interview Relevance

Interview: The DB-API is foundational:

PEP 249 as the standard

The connection / cursor / execute / fetch pattern

Parameter styles by driver (qmark, format, named)

executemany for bulk operations

The exception hierarchy

Context managers for safe cleanup

When to use direct DB-API vs higher-level libraries

A common interview moment: writing a function that fetches records from a database. The right form uses parameterized queries (not string concatenation), context managers for cleanup, and fetches appropriately for the result size.

Another moment: explaining why every driver looks similar. The answer involves PEP 249.

Module 30 covers Python interview preparation.

Common Pitfalls

Warning: String-concatenating SQL with user input. SQL injection vulnerability. Always use parameters - covered in depth in Topic 25.4.

Forgetting commit(). Inserts and updates aren't persisted. Many drivers default to manual commit.

Connection leaks. Closing matters. Use with blocks.

fetchall() on a million-row result. Loads everything into memory. Use fetchone() in a loop or fetchmany().

Mixing parameter styles for the driver. Each driver supports specific styles - ? for sqlite3, %s for psycopg.

Catching Exception instead of specific DB exceptions. Loses information about what went wrong.

Long-running cursors holding locks. Some databases lock data during cursor iteration. Close cursors when done.

Try It

Exercise: Practice with the DB-API:

Open a connection to sqlite3.connect(":memory:"). Create a table and insert a few rows.

Use fetchone, fetchmany, and fetchall on the same query. Note the differences.

Use executemany to insert 1,000 rows. Compare its speed with a loop of execute calls.

Trigger an IntegrityError (e.g., insert a duplicate primary key). Catch the specific exception.

Wrap a connection in a with block. Verify it commits on success and rolls back on exception.

Swap sqlite3 for psycopg (against a local Postgres). Note which parts of the code change.

Back to Python Basic to Advance