← Back to Home

SQLite3 Defaults: 8 Decisions Python Makes For You

sqlite3pythondatabase

I tested this on one machine: a temporary directory, two concurrent connections, and a batch of rows with deliberately wrong types, then wrote down each decision sqlite3 made without asking me. Environment: CPython 3.13.14, SQLite 3.53.1, Windows 11. This is the binding layer, not the backup layer, so it pairs with my earlier post on SQLite hot backups: that one asks whether a copied database is consistent, this one asks what the Python wrapper does when you say nothing. No affiliate links in this post.

PythonSqlite
Technologies referenced · icons from Simple Icons (CC0)

The short version

TL;DR: an INTEGER column does not guarantee integers, because affinity rewrites well-formed literals before they hit the page; the implicit transaction covers INSERT, UPDATE, DELETE and REPLACE only, so SELECT and DDL fall outside it; that is why CREATE TABLE survives a rollback; a lock wait defaults to five seconds, not zero; WAL stops readers and writers from blocking each other; rowcount is -1 for SELECT; executescript commits your pending work before it runs; and the default date adapters have been deprecated since 3.12.

// FLOW_01The five gates one INSERT passes through5 NODES01STEP_01bind parameters02STEP_02affinity rewrite03STEP_03implicit transaction04STEP_04lock wait05STEP_05commit to diskEND_OF_FLOW
The five gates one INSERT passes through · diagram drawn from this article

An INTEGER column does not guarantee an integer

SQLite is dynamically typed: a value carries its own type, a column does not. The declared type is only a suggestion, which the docs call type affinity. I created five columns and pushed the same strings into each:

CREATE TABLE t(a INTEGER, b REAL, c NUMERIC, d TEXT, e BLOB);

Reading each value back with typeof() gave these results:

The datatype3 page is explicit: if a string can be represented losslessly as an integer, NUMERIC affinity converts it, and the docs use '500.0' becoming integer 500 as their own example. This changes how mixed types sort. In ORDER BY x, NULL comes first, numbers 3 and 10 sit in the middle, the strings '10' and 'apple' come after, and BLOBs always land last. The number 10 and the string '10' are two different sort classes.

There is one exception worth knowing: INTEGER PRIMARY KEY is an alias for rowid, and every other column accepts any type. Pushing '123' into the rowid column gave me integer 123; pushing 'abc' was rejected outright with sqlite3.IntegrityError: datatype mismatch.

Who opened the transaction: the implicit BEGIN behind isolation_level

On default settings isolation_level is an empty string and autocommit is LEGACY_TRANSACTION_CONTROL (-1). In that mode Python opens a transaction implicitly before INSERT, UPDATE, DELETE and REPLACE, and only those four. Here is the in_transaction trace I measured:

A pure SELECT on a fresh connection leaves in_transaction at False. The docs spell this out: the implicit BEGIN happens only when the statement is INSERT, UPDATE, DELETE or REPLACE and no transaction is already active.

Knowing who opened the transaction explains the counterintuitive result in the next section. A related question, whether a schema change gets detected by your migration tool at all, is separate, and I logged four blind spots in my Alembic autogenerate test.

CREATE TABLE survived my rollback

If DDL never opens a transaction, then a CREATE TABLE lands in autocommit mode. I ran it both ways:

Same statement, two outcomes, and the only difference is who opened the transaction. If you want to undo DDL, you have to open an explicit transaction yourself. One more piece of history: before Python 3.6, sqlite3 committed implicitly before DDL, and that behaviour is gone, so older posts about "DDL auto-commits" should be reread as "DDL does not join the transaction".

There is a collision point here too. Run BEGIN while the implicit transaction is open and you get sqlite3.OperationalError: cannot start a transaction within a transaction. Only after a rollback, and with no new DML, does an explicit BEGIN succeed.

database is locked: five seconds by default, not immediately

"SQLite fails instantly with database is locked" is the most repeated claim about this module, and for Python users it is wrong. There are two layers:

I pitted two connections against each other. Connection A held a write lock with BEGIN IMMEDIATE; connection B wrote with timeout=0 and failed in 0.5 milliseconds with sqlite3.OperationalError: database is locked. With PRAGMA busy_timeout=3000 on B, and A releasing the lock after 0.6 seconds, B waited 658 milliseconds and then the write succeeded. Whether you see an error depends on whether you can afford to wait, and by default you have five seconds.

One place where locks surprise people: if connection A has an uncommitted DML, even a single INSERT nobody committed, connection B's CREATE TABLE queues for the same write lock, because DDL writes the schema. In my test B simply timed out with database is locked. Finding the connection that forgot to commit fixes this faster than raising the timeout.

WAL changes the read-write relationship. The default journal_mode is delete; after switching to WAL, B's reads returned normally while A held the write lock. The cost is two companion files: -wal measured 12,392 bytes and -shm 32,768 bytes before close, both disappeared after close, and the main file grew from 4,096 to 8,192 bytes as the WAL was folded back in.

rowcount and lastrowid: three different answers

Cursor.rowcount is meaningful only for INSERT, UPDATE, DELETE and REPLACE; for everything else, SELECT included, it is -1. Four measurements:

Use lastrowid right after execute on a single INSERT; before the first successful insert its value is None. The docs also note that WITHOUT ROWID tables do not record lastrowid at all.

executescript commits before it runs

Under legacy transaction control, executescript() does something the docs state plainly: if a transaction is pending, it commits first, then runs the script. The consequence is that rows you were about to roll back are already saved:

con.execute("INSERT INTO t(x) VALUES('pending')")
con.executescript("CREATE TABLE t2(x); INSERT INTO t2 VALUES(1);")
con.rollback()   # the pending row is still there

Counting after the rollback, the pending row is still present, because it was committed before the script ran. in_transaction also flipped from True to False. If you need the script inside your transaction, wrap it explicitly, or simply do not call executescript while work is pending.

Cross-thread use: ProgrammingError and three concurrency patterns

With the default check_same_thread=True, another thread touching the connection raises an error that even names both thread ids:

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 30876 and this is thread id 31088.

Three workable patterns, each with its own price:

With a shared connection, remember the earlier fact: one thread's uncommitted transaction makes other writers queue, and row counts depend on which connection looks. My uncommitted row was visible to its own connection (count 1), invisible to the concurrent one (count 0), and a third connection's write hit database is locked; after rollback the count returned to zero.

Dates go in but do not come back: detect_types and deprecated adapters

With the default detect_types=0, a datetime is converted to ISO text by an adapter: I stored 2026-11-21 09:30:00, typeof reported text, and it came back as a str. To read datetimes back, pass detect_types=sqlite3.PARSE_DECLTYPES at connect time, and reads return datetime and date objects.

Two newer traps:

One more line worth copying into your notes: the default timestamp converter ignores the UTC offset in the database and always returns a naive datetime. Register an offset-aware converter if the offset matters.

Number traps in the SQL layer and the limits of STRICT

Below the binding layer, SQL expressions have their own surprises:

The error text for a constraint violation is sqlite3.IntegrityError: UNIQUE constraint failed: t.id, and the documented fix is UPSERT: INSERT INTO t VALUES('a',2) ON CONFLICT(id) DO UPDATE SET n=excluded.n. In my test the conflicting row was updated rather than rejected.

STRICT tables, available since 3.37, get sold as the strict typing rescue. I inserted '123' into an INTEGER column of a STRICT table and it was still stored as integer 123. STRICT requires the value to be convertible to the declared type; it does not forbid conversion. If you need byte-exact storage, declare BLOB or manage types yourself.

Binding errors point in the wrong direction

A mismatch between placeholders and arguments produces a misleading message:

sqlite3.OperationalError: table t has 2 columns but 1 values were supplied

Nothing is wrong with a two-column table; you supplied one bound parameter. Wrong types also report elsewhere: passing a dict to qmark placeholders gives sqlite3.ProgrammingError: Binding 1 has no name, but you supplied a dictionary (which has only names)., while passing a dict as a positional argument gives sqlite3.ProgrammingError: Error binding parameter 1: type 'dict' is not supported.

What actually works, measured:

Troubleshooting: the errors I actually hit

Where these findings stop

Every result here comes from CPython 3.13.14 with SQLite 3.53.1 on Windows 11, cross-checked against the official docs. Not covered: how behaviour changes once the autocommit default flips to False in a future release, interactive mode, load_extension, and whether affinity conversion differs on Linux or older SQLite builds. The docs state that the autocommit default "will change to False in a future Python release", so transaction behaviour is worth retesting when you upgrade a major version. For backup and copy scenarios, use this together with my SQLite hot backup post; for the sibling problem of configuration values being rewritten silently, read the YAML safe_load test.

FAQ

Q: If the column is declared INTEGER, why do I get an integer back instead of the string I inserted?

Because the declared type is only affinity, and the value decides its own storage class. '123' is a well-formed literal that converts losslessly, so INTEGER affinity stores it as integer 123 and typeof() reports integer. Declare TEXT or BLOB if you need the string preserved byte for byte.

Q: Python already waits five seconds, so do I still need to set busy_timeout?

It depends on how long your writers queue. Five seconds is plenty for a small single-machine database, but a long uncommitted transaction on the other side can eat all of it and still produce database is locked. I look for uncommitted work first, then set PRAGMA busy_timeout to two or three times my measured worst case instead of guessing a large number.

Q: Since CREATE TABLE will not roll back, how do I write a safe migration?

Put the whole migration in an explicit transaction: BEGIN, then your DDL and data changes, then commit() or rollback(). Do not issue BEGIN while an implicit transaction is open, and do not call executescript with work pending, because it commits first. Verify by creating the table, rolling back, and querying sqlite_master, which beats reading your own code.

Q: SQLite 3.37 added STRICT tables, so does that stop silent type rewriting?

No. STRICT requires the value to be convertible to the declared type; it does not forbid conversion. I inserted '123' into an INTEGER column of a STRICT table and got integer 123 back. What STRICT does buy you is an earlier, clearer error for values like 'abc' that cannot convert at all.

Last word

Each default in sqlite3 has a reason, but together they mean one thing: between the SQL you write and the bytes on disk sits a binding layer whose decisions never raise and never log. Verifying it is cheap, a temporary database, two connections and a batch of mistyped rows, and ten minutes gets your own schema audited. Waiting until silent type rewriting has polluted production data costs far more. My DuckDB test and the Alembic autogenerate checks belong to the same thread, decisions the database layer makes without telling you.

👉 Join Xiaomi MiMo Platform: Leading AI model platform with cost-effective inference

👉 Join Aliyun AI: Top AI products with exclusive coupons for business innovation

📌 This article was AI-assisted generated and human-reviewed | TechPassive — An AI-driven content testing site focused on real tool reviews

🔗 Recommended Tools

These are carefully selected tools. Using our affiliate links supports us to keep producing quality content:

As an Amazon Associate, I earn from qualifying purchases at no extra cost to you.

☁️ DigitalOcean Cloud ⚡ Vultr VPS 🤖 QoderWork CN (Refer & Earn) ☁️ Aliyun AI Products 📚 WordPress Books 🧠 AI & LLM Books 🏗️ System Design Books 🐳 Docker & K8s Books 🐧 Linux Admin Books 🐍 Python Books 🗄️ Database Books 🚀 DevOps & SRE Books 🤖 Xiaomi MiMo Platform
← Back to Home