ReviseAlgo Logo

Database Access

Python DB-API

PEP 249 standard database interface — common API across all database drivers (sqlite3, psycopg2, mysql-connector, etc.).

Interview: Database standards — understanding the DB-API lets you work with any database driver.

Last Updated: June 12, 2026 • 7 min read

The DB-API (PEP 249) is Python's standard interface for database access. Every database driver (sqlite3, psycopg2, mysql-connector-python, cx_Oracle, etc.) implements this interface, making it easy to switch databases with minimal code changes.

Core Interface

  • connect() — create a connection to the database
  • connection.cursor() — create a cursor for executing queries
  • cursor.execute(query, params) — execute a single SQL statement
  • cursor.executemany(query, seq) — execute for each parameter set
  • cursor.fetchone(), fetchall(), fetchmany(n) — retrieve results
  • connection.commit() / connection.rollback() — transaction control

Parameter Styles

  • qmark: WHERE id = ? — used by sqlite3
  • format: WHERE id = %s — used by psycopg2, mysql-connector
  • pyformat: WHERE id = %(id)s — named parameters
  • numeric: WHERE id = :1 — used by cx_Oracle
  • The style depends on the database driver — check module.paramstyle

Common Drivers

  • sqlite3: Built-in, for SQLite databases
  • psycopg2: PostgreSQL (pip install psycopg2-binary)
  • mysql-connector-python: MySQL (pip install mysql-connector-python)
  • pyodbc: ODBC connections (SQL Server, Access)
  • cx_Oracle / oracledb: Oracle databases

Interview Insight

The DB-API standardizes database access — same connect/cursor/execute/commit pattern works across all databases. The main difference between drivers is the parameter style (qmark vs format). This abstraction makes it easy to switch databases or support multiple backends.

Use Cases

Multi-database support — same code works with SQLite, PostgreSQL, MySQL

Application portability — switch databases without rewriting data access code

Standardized error handling — common exception hierarchy across drivers

Connection management — consistent connect/close patterns

Database abstraction layers — building ORM-like wrappers

Common Mistakes

Using wrong parameter style for the driver — check module.paramstyle

Not closing connections — use context managers or try/finally

Not handling database-specific exceptions — catch OperationalError, IntegrityError

Forgetting cursor.description — needed to get column names from query results

Assuming all drivers support the same features — check module.apilevel and threadsafety