SQLite3 Defaults: 8 Decisions Python Makes For You
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.
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.
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:
'123'into the INTEGER column is stored as integer 123'1e3'into INTEGER is stored as integer 1000, because exponent notation still counts as a well-formed literal'12.0'into NUMERIC is stored as integer 12, since an exact conversion prefers integers- the integer
7into REAL is stored as real 7.0 '1e3'into BLOB stays text, because BLOB affinity converts nothing'abc'stays text in all five columns, because it is not a well-formed literal
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:
- after CREATE TABLE: False
- after INSERT: True
- after SELECT: True, because the earlier DML opened it, not the query
- after rollback: False
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:
CREATE TABLE t2followed byrollback(): t2 is still there, the rollback did nothingBEGINfirst, create t2 inside the explicit transaction, thenrollback(): t2 is gone
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:
- the SQLite C API ships no busy handler by default, so it returns SQLITE_BUSY immediately, and at that layer the claim holds
- Python's
connect(timeout=)defaults to 5.0 seconds, the docs say "Default five seconds", andPRAGMA busy_timeoutread back 5000 in my test
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:
executemanywith three rows: rowcount 3, butlastrowidis None, and the docs confirm lastrowid is not updated after executemany- after SELECT: rowcount -1, and the only way to know the count is len(fetchall())
- DELETE of two rows: rowcount 2, usable
- UPDATE matching nothing: rowcount 0, which is exactly the signal for "nothing changed"
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:
check_same_thread=Falsewith one shared connection: the docs warn that writes may need serialising by the caller, and in my test a thread lock made concurrent writes work- one connection per thread: no ProgrammingError, but now you handle lock contention between connections, back to the busy_timeout section
- a connection pool with a single writer: writes converge on one connection while readers stay parallel
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:
- the default adapters have been deprecated since Python 3.12, and on 3.13.14 each datetime insert emitted a DeprecationWarning; the docs suggest register_adapter instead
- a DATE column holding a full datetime is stored in timestamp format, and the date converter then fails with
ValueError: invalid literal for int() with base 10: b'21 09:30:00', so the declared type and the written format must match - a TIMESTAMP column holding a non-date string fails at read time under PARSE_DECLTYPES with
ValueError: not enough values to unpack (expected 2, got 1), because the converter does not validate and the bad data explodes on retrieval
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:
SELECT 1/2returns 0, because integer division applies when both operands are integers;1/2.0returns 0.5SELECT '10'+2returns 12, since arithmetic coerces text to a number;'abc'+2returns 2, because unparseable text counts as 0SELECT 0.1+0.2returns 0.30000000000000004, IEEE 754 double precision, same as Python
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:
?with a sequence, or:namewith a dict, and a repeated:xfills several places with one value- numbered placeholders work, so
VALUES(?1, ?1)fills two columns from one value - None binds as NULL (typeof returns null), True binds as integer 1, a float binds as real
- dict and set are unsupported and raise ProgrammingError immediately
Troubleshooting: the errors I actually hit
- database is locked in 0.5 ms: the real behaviour with
timeout=0or a bare C API call. Check for a connection holding an uncommitted transaction first, since DDL takes the write lock too, and only then raise busy_timeout; doing it in the other order turns a rare failure into a reliable one - cannot start a transaction within a transaction: you called BEGIN while the implicit transaction was open. Test
in_transactionfirst, or manage exactly one source of transactions - CREATE TABLE will not roll back: DDL is outside the implicit transaction, so open your own BEGIN
- invalid literal for int() with base 10: b'21 09:30:00': a DATE column received a full datetime. Align declared type with written format, or use TIMESTAMP consistently
- Error binding parameter 1: type 'dict' is not supported: the binding layer takes no containers. Serialise to JSON or unpack into tuple fields
- table t has 2 columns but 1 values were supplied: count placeholders and arguments before you go reading table definitions
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.