Every Python database driver implements the same interface, so what you learn against one works
against the others. This post uses sqlite3 for the runnable examples — it ships with
Python and needs no server — and points out where MySQL and PostgreSQL differ.
DB-API: the interface every driver shares
import sqlite3
connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.execute("CREATE TABLE accounts (id INTEGER PRIMARY KEY, kind TEXT, balance REAL)")
cursor.execute("INSERT INTO accounts VALUES (1, 'Checking', 1250.00)")
connection.commit()
cursor.execute("SELECT kind, balance FROM accounts")
print(cursor.fetchall()) # Output: [('Checking', 1250.0)]
print([d[0] for d in cursor.description]) # Output: ['kind', 'balance']
connection.close()
Connect, get a cursor, execute, fetch, commit, close. PEP 249 specifies that shape, so
mysql-connector-python, psycopg and sqlite3 all offer it —
the code changes only at the connect() call.
fetchall() loads every row into memory. Use fetchone() in a loop, or
iterate the cursor directly, when the result set is large.
cursor.description carries the column names, which is how a generic result-printer or a
CSV export knows its header without being told. Do not reach for cursor.rowcount after a
SELECT — it is specified only for INSERT, UPDATE and
DELETE, and several drivers return -1 for a query.
Connecting to MySQL
import os
import mysql.connector
connection = mysql.connector.connect(
host=os.environ.get("DB_HOST", "localhost"),
user=os.environ["DB_USER"],
password=os.environ["DB_PASSWORD"], # never a literal in source
database="bank",
autocommit=False,
)
Credentials come from the environment, never from source. A password committed to git is committed forever — rewriting history does not remove it from anyone's clone, and rotating the credential is the only real fix.
autocommit=False is the default and worth stating anyway, because it is what makes the
transaction section below possible.
Parameterised queries, always
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE accounts (id INTEGER, kind TEXT, balance REAL)")
db.execute("INSERT INTO accounts VALUES (1, 'Checking', 1250.0)")
db.commit()
evil = "Checking'; DROP TABLE accounts; --"
# Safe: the value is sent separately from the SQL
rows = db.execute("SELECT * FROM accounts WHERE kind = ?", (evil,)).fetchall()
print(rows) # Output: []
print(db.execute("SELECT COUNT(*) FROM accounts").fetchone()) # Output: (1,)
The table survived. The driver never substitutes the value into the SQL string — it sends the statement and the parameters separately, so the database parses the query before it has ever seen your data. There is nothing for an injected quote to escape from.
The unsafe version is any f-string or % or + that puts a value into SQL
text. It is not "unsafe unless you escape it"; it is unsafe, and the parameterised form is shorter
anyway.
One trap worth knowing: the placeholder differs by driver. sqlite3 uses
?, MySQL and psycopg use %s. It is a driver detail, not a Python format
string — never use % formatting on a query that contains one.
Transactions
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance REAL)")
db.executemany("INSERT INTO accounts VALUES (?, ?)", [(1, 1000.0), (2, 50.0)])
db.commit()
def transfer(db, source, target, amount):
try:
db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, source))
db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, target))
balance = db.execute("SELECT balance FROM accounts WHERE id = ?", (source,)).fetchone()[0]
if balance < 0:
raise ValueError("insufficient funds")
db.commit()
return "ok"
except Exception:
db.rollback()
return "rolled back"
print(transfer(db, 2, 1, 500.0)) # Output: rolled back
print(db.execute("SELECT balance FROM accounts ORDER BY id").fetchall())
# Output: [(1000.0,), (50.0,)]
Both balances are unchanged. That is the point of a transaction: two updates that must both happen or neither, with no window in which the money exists in one account and not the other.
rollback() in the except is not optional. Without it the connection is left
mid-transaction, holding locks, and the next operation on it inherits the mess.
A context manager instead of remembering
import sqlite3
from contextlib import contextmanager
@contextmanager
def transaction(db):
try:
yield db
db.commit()
except Exception:
db.rollback()
raise
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE accounts (id INTEGER, balance REAL)")
with transaction(db):
db.execute("INSERT INTO accounts VALUES (1, 100.0)")
try:
with transaction(db):
db.execute("INSERT INTO accounts VALUES (2, 200.0)")
raise RuntimeError("something failed")
except RuntimeError:
pass
print(db.execute("SELECT COUNT(*) FROM accounts").fetchone()) # Output: (1,)
One row, not two — the second insert was rolled back by the exception. Commit-on-success and
rollback-on-failure become structural rather than something each caller has to remember, which is the
same argument as with open(...) in
Files.
Note the bare raise after the rollback: swallowing the exception here would hide a
failure behind a clean-looking rollback.
Rows as dicts
import sqlite3
db = sqlite3.connect(":memory:")
db.row_factory = sqlite3.Row # MySQL: connection.cursor(dictionary=True)
db.execute("CREATE TABLE accounts (id INTEGER, kind TEXT, balance REAL)")
db.execute("INSERT INTO accounts VALUES (1, 'Checking', 1250.0)")
row = db.execute("SELECT * FROM accounts").fetchone()
print(row["kind"]) # Output: Checking
print(row["balance"]) # Output: 1250.0
By default a row is a tuple and you index it by position, which breaks the moment someone adds a
column to the SELECT. Named access survives that. The mechanism differs per driver — a
row_factory here, cursor(dictionary=True) in MySQL — but every driver has
one.
Connection pooling
from mysql.connector import pooling
pool = pooling.MySQLConnectionPool(pool_name="bank", pool_size=5, host="localhost",
user="app", password="…", database="bank")
connection = pool.get_connection() # borrow
try:
...
finally:
connection.close() # returns it to the pool, does not close it
Opening a connection costs a TCP handshake and authentication — a few milliseconds, which is enormous next to a query taking microseconds. A pool opens them once and lends them out.
close() on a pooled connection returns it rather than closing it, which reads wrong and
is what the API does. Forgetting it exhausts the pool and the next
get_connection() blocks — a hang rather than an error, and a genuinely unpleasant thing to
diagnose.
When to use an ORM
SQLAlchemy and Django's ORM map rows to objects, generate the SQL, and handle connection pooling and migrations. That is worth a lot on an application with fifty tables and worth very little on a script with three queries.
Use raw DB-API when the queries are few and you want to see exactly what runs. Use an ORM when you have a real schema, relationships to traverse and migrations to manage — and even then, know how to read the SQL it emits, because the day it generates something slow you will need to.
Next: Machine Learning.