HSHimanshu Sharma
Python Notes

Python database programming: the DB-API, SQLite, queries and safe parameters

Himanshu Sharma · · 8 min read

Python database programming: the DB-API, SQLite, queries and safe parameters Advertisement

Programs lose their data when they stop, unless it is stored somewhere. A file works for small things, but once data has structure, many users or relationships (customers and their orders), you want a database. Python can talk to almost every database through a common pattern called the DB-API, so the same steps work for SQLite, MySQL, PostgreSQL and Oracle. This post teaches those steps using SQLite, because it ships with Python and needs no installation, so every example below was run for real. The differences for other databases are explained near the end.

Why a database instead of files?

Files Database
Simple to start Needs a little setup
You write your own searching and sorting Fast queries with SQL and indexes
Hard to keep consistent when several programs write Transactions, constraints and locking
No relationships Tables linked by keys
Whole file often rewritten Only the changed rows are written

Use a database when you must search, update parts of the data, keep it consistent or share it. The MySQL course teaches the SQL language itself; this post is about calling it from Python.

The standard steps

Every DB-API program follows the same sequence:

  1. Connect to the database.
  2. Create a cursor.
  3. Execute SQL statements.
  4. Fetch the results (for queries) or commit the changes (for inserts, updates and deletes).
  5. Close the cursor and the connection.
import sqlite3
con = sqlite3.connect(":memory:")       # a temporary database in memory
cur = con.cursor()
cur.execute("CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT)")
cur.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total REAL)")

sqlite3.connect("shop.db") would create or open a file instead; ":memory:" is perfect for experiments and tests. The connection represents the database and controls transactions. A cursor runs statements and holds the result of the last query.

Inserting data safely: parameters

cur.execute("INSERT INTO customers (name, city) VALUES (?, ?)", ("Asha", "Pune"))
cur.executemany("INSERT INTO customers (name, city) VALUES (?, ?)",
                [("Ben", "Delhi"), ("Chen", "Pune")])
con.commit()
cur.executemany("INSERT INTO orders (customer_id, total) VALUES (?, ?)",
                [(1, 250.0), (1, 120.5), (2, 80.0)])
con.commit()

The ? marks are placeholders; the values go in a separate tuple. sqlite3.paramstyle is "qmark". Other drivers use %s (MySQL and PostgreSQL drivers) or :name / :1 (Oracle); the principle is identical. executemany runs one statement for many rows, which is faster than a Python loop of execute calls.

Why placeholders matter: SQL injection

Never build SQL by pasting values into the string. Compare what happens with a hostile name:

name = "x' OR '1'='1"
unsafe = "SELECT name FROM customers WHERE name = '%s'" % name
print(unsafe)
print(con.execute(unsafe).fetchall())
# SELECT name FROM customers WHERE name = 'x' OR '1'='1'
# [('Asha',), ('Ben',), ('Chen',)]

print(con.execute("SELECT name FROM customers WHERE name = ?", (name,)).fetchall())
# []

The pasted version turned the input into part of the query: the condition became ... OR '1'='1', which is always true, so every row came back. The parameterised version treated the whole text as a value, found no such name, and returned nothing. The same attack could delete tables or read passwords. The rule is absolute: values always go through placeholders. (Table and column names cannot be parameters; if you must choose them dynamically, check them against a fixed allow-list.)

Reading data

cur.execute("SELECT * FROM customers")
print(cur.fetchone())         # (1, 'Asha', 'Pune')
print(cur.fetchmany(1))       # [(2, 'Ben', 'Delhi')]
print(cur.fetchall())         # [(3, 'Chen', 'Pune')]
print(cur.fetchone())         # None  (no more rows)
Method Returns
fetchone() The next row, or None
fetchmany(n) A list of up to n rows
fetchall() A list of all remaining rows

You can also loop directly over the cursor, which is memory-friendly for big results:

for row in cur.execute("""SELECT c.name, SUM(o.total)
                          FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
                          GROUP BY c.id ORDER BY c.id"""):
    print(row)
# ('Asha', 370.5)
# ('Ben', 80.0)
# ('Chen', None)

Chen has no orders, so SUM gives None (SQL NULL). Column names are in cur.description:

print([d[0] for d in cur.execute("SELECT id, name FROM customers").description])    # ['id', 'name']

Rows as dictionaries

Plain tuples are easy to mix up. Setting row_factory lets you use names:

con.row_factory = sqlite3.Row
rows = con.execute("SELECT id, name FROM customers WHERE city = ?", ("Pune",)).fetchall()
print([dict(r) for r in rows], rows[0]["name"])
# [{'id': 1, 'name': 'Asha'}, {'id': 3, 'name': 'Chen'}] Asha

Transactions: commit and rollback

Changes made by INSERT, UPDATE and DELETE are not permanent until you commit. If something fails, rollback() undoes everything since the last commit. That makes a group of statements atomic: all of them happen or none do.

The cleanest form uses the connection as a context manager, which commits on success and rolls back on an exception:

try:
    with con:
        con.execute("UPDATE orders SET total = total + 1 WHERE id = 1")
        raise RuntimeError("fail midway")
except RuntimeError:
    pass
print(con.execute("SELECT total FROM orders WHERE id = 1").fetchone())     # (250.0,)

The update was rolled back, so the total is still 250. (The with form of an SQLite connection controls the transaction; it does not close the connection.) A real example is a bank transfer: debit one account and credit another inside one transaction, so a crash between the two statements can't lose money.

Errors

DB-API defines a family of exceptions, all derived from sqlite3.Error / DatabaseError:

try:
    with con:
        con.execute("INSERT INTO customers (name) VALUES (NULL)")
except sqlite3.IntegrityError as e:
    print("IntegrityError:", e)       # IntegrityError: NOT NULL constraint failed: customers.name

try:
    con.execute("SELECT * FROM nope")
except sqlite3.OperationalError as e:
    print("OperationalError:", e)     # OperationalError: no such table: nope
Exception Typical cause
IntegrityError A constraint was violated (NOT NULL, UNIQUE, foreign key)
OperationalError A problem with the database operation: missing table, locked file
ProgrammingError Misuse: wrong number of parameters, using a closed connection
DatabaseError The base class for database errors

Catch the specific exception you can handle (a duplicate key is a user-facing message), and log the others with log.exception.

Closing

con.close()
try:
    con.execute("SELECT 1")
except sqlite3.ProgrammingError as e:
    print("ProgrammingError:", e)      # ProgrammingError: Cannot operate on a closed database.

Close connections when finished, or you may leak resources and hold database locks. For a connection that needs closing automatically, use contextlib.closing(sqlite3.connect(...)).

Persistence

An in-memory database disappears when the program ends. A file database keeps its data:

import os, tempfile
p = os.path.join(tempfile.mkdtemp(), "shop.db")
c2 = sqlite3.connect(p); c2.execute("CREATE TABLE t(x)"); c2.execute("INSERT INTO t VALUES (1)")
c2.commit(); c2.close()
c3 = sqlite3.connect(p); print(c3.execute("SELECT x FROM t").fetchall(), os.path.exists(p)); c3.close()
# [(1,)] True

Other databases

The steps are identical; only the driver and the connection details change. These could not be run here since they need a server, so check each driver's documentation:

Database Typical driver Connect with Placeholder
SQLite sqlite3 (built in) file path ?
MySQL / MariaDB mysql-connector-python or PyMySQL host, user, password, database %s
PostgreSQL psycopg connection string %s
Oracle oracledb (successor to cx_Oracle) user, password, DSN :name

Install a driver with pip install ... in a virtual environment, and check that the install worked by importing it. Keep passwords out of the code: read them from environment variables or a configuration file that is not committed to version control. For MySQL concepts such as keys, joins and transactions, see the MySQL course, the MySQL cheat sheet and the SQL joins cheat sheet. The same ideas from PHP's side are in the PHP PDO lessons.

ORMs

Writing SQL by hand is fine for small programs. Larger applications often use an ORM (object-relational mapper) such as SQLAlchemy, which maps tables to Python classes and builds queries for you. Learn the DB-API first; an ORM uses it underneath.

Common mistakes

  • Building SQL strings with % or f-strings from user input.
  • Forgetting commit(), so changes vanish when the connection closes.
  • Not handling IntegrityError for duplicate or missing values.
  • Leaving connections open.
  • Using fetchall() on huge result sets instead of looping.
  • Using SELECT * in code that depends on column order.
  • Hard-coding passwords in source files.

Practice

Create a books table (id, title, author, year), insert five books, then write functions add_book, find_by_author and delete_book, all with placeholders. Wrap the inserting in a transaction and prove that an error rolls everything back. Finally, deliberately try the injection example against your own function and confirm it returns nothing.

Watch the lessons

Watch the video lessons: Episode 82: Python Database Connectivity, Episode 83: Python Database Programming (Oracle, Queries), Episode 85: Database Programming, Part 1 and Episode 86: Database Programming, Part 2.

Advertisement