PYTHON / DATABASES WITH PYTHON
Creating tables and inserting rows safely
Design a SQLite table whose constraints reject bad data, then insert rows using executemany, lastrowid, ON CONFLICT, and IntegrityError handling.
What you will learn
- Declare NOT NULL, UNIQUE, CHECK and REFERENCES in CREATE TABLE, not just in Python
- Use CREATE TABLE IF NOT EXISTS so setup code is safe to run again
- Insert with an explicit column list and read generated keys from cursor.lastrowid
- Handle collisions with try/except sqlite3.IntegrityError or ON CONFLICT DO UPDATE
Understanding Creating tables and inserting rows safely
A CREATE TABLE statement is not just storage layout, it is a contract the database engine enforces on every writer: your script, a migration, a colleague's admin tool, the sqlite3 command line. That is why validation belongs in the schema as well as in your Python code. Write the statement as CREATE TABLE IF NOT EXISTS so the same setup function can run on a fresh file and on an existing one without raising OperationalError, and keep one column per single fact so constraints can talk about individual values.
The constraints worth reaching for are small and specific. INTEGER PRIMARY KEY makes the column an alias for SQLite's internal rowid, so the engine hands you a fresh id automatically. NOT NULL blocks the most common bad row, a missing required field. UNIQUE on one column or on a column pair defines what "the same row" means, CHECK encodes a range or an enumeration, DEFAULT fills in a value you would otherwise forget, and REFERENCES makes a child row impossible unless its parent exists — but only if you run PRAGMA foreign_keys = ON on that connection, since SQLite leaves the check off by default.
On the insert side, always name the columns you are filling: INSERT INTO books (author_id, title, year) VALUES (?, ?, ?) keeps working when someone adds a column later, while a bare VALUES list silently shifts data or fails. Pass a list of tuples to executemany for batches, take cursor.lastrowid when you need the id you just generated for child rows, and treat sqlite3.IntegrityError as ordinary control flow rather than a crash: it is the engine telling you the row would break a rule you asked it to enforce. A failed statement is undone on its own, so the rows inserted before it are still pending in the transaction and you decide whether to commit them.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")
con.execute("""
CREATE TABLE IF NOT EXISTS authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
)
""")
con.execute("""
CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY,
author_id INTEGER NOT NULL REFERENCES authors(id),
title TEXT NOT NULL,
year INTEGER CHECK (year BETWEEN 1450 AND 2100),
in_print INTEGER NOT NULL DEFAULT 1,
UNIQUE (author_id, title)
)
""")
cur = con.execute("INSERT INTO authors (name) VALUES (?)", ("Ursula K. Le Guin",))
author_id = cur.lastrowid
print("author id:", author_id)
con.executemany(
"INSERT INTO books (author_id, title, year) VALUES (?, ?, ?)",
[(author_id, "A Wizard of Earthsea", 1968),
(author_id, "The Dispossessed", 1974)],
)
try:
con.execute("INSERT INTO books (author_id, title, year) VALUES (?, ?, ?)",
(author_id, "The Dispossessed", 1974))
except sqlite3.IntegrityError as exc:
print("rejected duplicate:", exc)
try:
con.execute("INSERT INTO books (author_id, title, year) VALUES (?, ?, ?)",
(999, "Ghost Book", 1990))
except sqlite3.IntegrityError as exc:
print("rejected orphan:", exc)
con.commit()
for title, year, in_print in con.execute(
"SELECT title, year, in_print FROM books ORDER BY year"):
print(title, year, in_print)
con.close()Constraints declared in CREATE TABLE, not checks written in Python, are what actually guarantee that only valid rows can exist.
Worked examples
Constraints filter a batch of messy input
Bad rows are rejected by the engine while the good ones in the same loop still land.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("""
CREATE TABLE readings (
id INTEGER PRIMARY KEY,
sensor TEXT NOT NULL,
celsius REAL NOT NULL CHECK (celsius > -273.15)
)
""")
candidates = [
("kitchen", 21.5),
(None, 19.0),
("attic", -400.0),
("attic", 4.25),
]
for sensor, celsius in candidates:
try:
con.execute("INSERT INTO readings (sensor, celsius) VALUES (?, ?)",
(sensor, celsius))
print("stored", sensor, celsius)
except sqlite3.IntegrityError as exc:
print("skipped", sensor, celsius, "->", type(exc).__name__)
print("rows kept:", con.execute("SELECT count(*) FROM readings").fetchone()[0])Example explained
Line 1sensor TEXT NOT NULL turns the Python None into an IntegrityError instead of a NULL row.
Line 2CHECK (celsius > -273.15) rejects -400.0 even though -400.0 is a perfectly valid Python float.
Line 3The loop uses execute per row precisely so one bad row does not abort the whole batch, unlike executemany.
Line 4count(*) is 2: each failed statement was undone on its own, leaving the two valid inserts pending.
Re-runnable seeding with ON CONFLICT
An upsert makes an insert idempotent, so running the seed twice updates instead of duplicating or crashing.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("""
CREATE TABLE IF NOT EXISTS settings (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
)
""")
def seed(pairs):
con.executemany(
"""INSERT INTO settings (key, value) VALUES (?, ?)
ON CONFLICT(key) DO UPDATE SET value = excluded.value""",
pairs,
)
seed([("theme", "light"), ("lang", "en")])
seed([("theme", "dark"), ("lang", "en")])
print("rows:", con.execute("SELECT count(*) FROM settings").fetchone()[0])
for row in con.execute("SELECT key, value FROM settings ORDER BY key"):
print(row)Example explained
Line 1key TEXT PRIMARY KEY creates the unique index that ON CONFLICT(key) needs as its conflict target.
Line 2excluded.value refers to the value the failed INSERT was trying to write, so the update reuses the same bound parameter.
Line 3The second seed() call changes theme to 'dark' and leaves lang alone rather than raising IntegrityError.
Line 4Swap DO UPDATE for DO NOTHING when the existing row should win.
Declared types are only hints
SQLite stores a string in an INTEGER column unless you add a typeof() check.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE loose (qty INTEGER)")
con.execute("CREATE TABLE tight (qty INTEGER NOT NULL CHECK (typeof(qty) = 'integer'))")
con.execute("INSERT INTO loose (qty) VALUES (?)", ("twelve",))
print("loose:", con.execute("SELECT qty, typeof(qty) FROM loose").fetchone())
try:
con.execute("INSERT INTO tight (qty) VALUES (?)", ("twelve",))
except sqlite3.IntegrityError:
print("tight rejected the string")
con.execute("INSERT INTO tight (qty) VALUES (?)", (12,))
print("tight:", con.execute("SELECT qty, typeof(qty) FROM tight").fetchone())Example explained
Line 1INTEGER on loose.qty is an affinity: SQLite tries to convert 'twelve' to a number, fails, and stores text anyway.
Line 2typeof(qty) reports the storage class of the stored value, which is how you can see the mismatch.
Line 3CHECK (typeof(qty) = 'integer') turns the affinity hint into a real rule, so the same insert now raises IntegrityError.
Line 4The integer 12 passes the check and comes back as a Python int.
Important notes
SQLite column types are affinities, not guarantees. If you need real type enforcement, add CHECK (typeof(col) = ...) or declare the table STRICT (SQLite 3.37 and later).
A constraint violation rolls back only the failing statement. Rows inserted earlier in the same open transaction are still pending, so decide deliberately whether to commit or roll back after catching IntegrityError.
Common mistakes
Writing INSERT INTO books VALUES (?, ?, ?) with no column list: it breaks with 'table books has 5 columns but 3 values were supplied', and once you do supply every value your data silently shifts the day someone adds a column.
Declaring REFERENCES authors(id) but never running PRAGMA foreign_keys = ON: the clause is inert on that connection, orphan child rows are inserted without complaint, and the corruption surfaces much later in a JOIN that loses rows.
Using plain CREATE TABLE in setup code: it works on the first run and then raises sqlite3.OperationalError: table books already exists every time afterwards, so people start deleting the database file to make the script work.
Try it yourself
Change, predict, then run
Create a tasks table with id INTEGER PRIMARY KEY, title TEXT NOT NULL UNIQUE, status TEXT NOT NULL DEFAULT 'todo' CHECK (status IN ('todo','doing','done')), insert two rows with executemany, then prove that inserting a duplicate title and a row with status 'later' each raise sqlite3.IntegrityError.
Open the Python workspaceCheck your understanding
Your books table declares author_id INTEGER NOT NULL REFERENCES authors(id), yet inserting a row with author_id = 999 when no such author exists succeeds without error. What is the most likely reason?
- Foreign key enforcement is per-connection and disabled unless you execute PRAGMA foreign_keys = ON
- sqlite3 only validates foreign keys when you call con.commit(), and the commit has not happened yet
- REFERENCES is only enforced when the parent column is declared UNIQUE instead of PRIMARY KEY
- Passing the value through a ? placeholder bypasses constraint checking for that column
Show answer
SQLite compiles foreign key support in but leaves it off for backwards compatibility, so each new connection must enable it with PRAGMA foreign_keys = ON before a transaction is open. Deferring the check to commit time (option 2) only happens for constraints explicitly declared DEFERRABLE INITIALLY DEFERRED, and even those need the pragma; placeholders and the choice of PRIMARY KEY versus UNIQUE have no effect on whether constraints run.