Connecting Python to SQLite Databases
Treat the Database Browser for SQLite and the Python program as alternating users of the database file, not as tools that should normally modify it at the same time.
Try it: Transactions and Locking
How a transaction groups statements so they commit or roll back together, why a session's uncommitted changes are invisible to other sessions, and how SQLite's locks let many readers in but only one writer — producing "database is locked".
How it works
- BEGIN starts a transaction but takes no lock yet. The first read takes a SHARED lock; the first write takes the single RESERVED (write) lock.
- Writes go into the writer's private copy of the pages; other sessions keep reading the last committed data.
- A second session that tries to write while the first holds the write lock gets "database is locked".
- COMMIT needs every other reader to be gone (EXCLUSIVE lock); if one is still in a read transaction, COMMIT fails with "database is locked" and the transaction stays open. ROLLBACK discards the changes.
- Outside BEGIN, every statement is its own transaction and commits at once (autocommit).
Default run (3 steps): Two sessions, A and B, share one database file. Committed: 1 Ana 100, 2 Ben 50. Locks: A NONE, B NONE. Run a statement in either session. Example: A runs BEGIN and UPDATE, then B tries to UPDATE. … B needs the RESERVED (write) lock, but A already holds RESERVED — only one writer at a time → Error: database is locked.
Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Models SQLite's default rollback-journal mode with two connections to one file and no busy timeout (WAL mode lets readers and a writer overlap). Locks are shown per session; pages and the journal are not drawn. Every statement's outcome was checked against two real sqlite3 connections.
Loading the simulation…
Why a Correct Program Can Fail
A Python program can contain correct database logic and still fail because another program is using the same SQLite database file. A common example is leaving the Database Browser for SQLite open while running Python code that modifies the database. The browser may appear idle, but the database can still be locked. The reliable solution is to separate execution from verification: run Python with the browser closed, then open the browser to inspect the result, and close it again before the next Python run.
Treat the Database Browser for SQLite and the Python program as alternating users of the database file, not as tools that should normally modify it at the same time.
Following the Database Lock
SQLite uses lock states to manage access to its database file. When the Database Browser opens the file, it holds a shared lock that allows reading. If you make a change in the browser, the lock becomes a reserved lock, indicating that a write is pending. When you save, the browser acquires an exclusive lock while writing the change to disk. During that exclusive period, no other program can read or write the database. After saving finishes, the browser releases the lock.
| State | What it means | What the browser is doing |
|---|---|---|
| Shared lock | Reading is allowed | The database is open for reading |
| Reserved lock | A write operation is pending | A change has been made but is not yet saved |
| Exclusive lock | Other programs cannot read or write | Changes are being written to disk |
| Released | The lock is no longer held by the browser | Saving has completed or the database has been closed |
The lock states described in the source material
When Two Programs Collide
SQLite file locking prevents multiple programs from modifying the database simultaneously. The important point is that the browser does not need to be actively editing at the exact moment Python runs. If the browser is still open, or if it has unsaved changes, it may still hold a lock that prevents Python from completing its database operation.
Example origin: generated. Imagine that you edit a database row in the Database Browser and leave the browser open without saving and closing the database. You then run a Python program intended to write another change. The Python code may fail with an error such as database is locked because the browser still has the database open for its own operation.
Moving Between Python and Verification
Checking One Python Database Operation
A Python program is intended to write data to an SQLite database. How can you verify the result without causing a lock error?
- 1Prepare
Close the Database Browser completely before starting the Python program.
- 2Execute
Run the Python program while the browser is closed. The program can now perform its database operation without the browser holding the file open.
- 3Inspect
After Python finishes, open the database in the Database Browser for SQLite.
- 4Compare
Inspect the rows and values shown in the browser and compare them with the change the Python program was supposed to make.
- 5Reset
Close the browser completely before running Python again.
The browser provides verification, while closing it between runs prevents the browser and Python from competing for access to the same database.
Use a repeatable alternation: close the browser, run Python, open the browser, inspect the result, and close the browser again. This workflow keeps execution and verification separate and makes it easier to identify whether a problem is in the program's result or in database access.
Recovering from Lock Errors
Common mistake Leaving the Database Browser open while running Python
What it looks likeThe browser remains open after verification, and Python is started to modify the same database.
Why it failsThe browser may still hold a lock, so Python cannot complete its database operation.
Do this insteadClose the Database Browser completely before running Python.
Common mistake Forgetting to save and close after making a browser change
What it looks likeA value is changed in the browser, but the database is left open with the change still pending.
Why it failsThe browser can hold a reserved or other active lock while the change has not been fully saved and closed.
Do this insteadSave the change and fully close the database before running Python.
Common mistake Assuming that correct Python code cannot produce a lock error
What it looks likeThe code looks correct, so the browser is left open during execution.
Why it failsLock errors can result from simultaneous access even when the program's database logic is correct.
Do this insteadCheck which program still has the database open before changing the code.
If an error says database is locked or unable to open database file, first close the Database Browser completely. If the browser is already closed, the lock may be held by a Python process that crashed or failed to terminate cleanly. In that case, investigate the remaining Python process before trying the operation again.
Practice the Alternating Workflow
You have just inspected an SQLite database in the Database Browser and now need to run a Python program that writes to the same database. Describe the exact order of actions you should take before, during, and after the Python run.
Hints
- Identify which application must be closed before Python starts.
- Include the browser step used to verify the Python result.
- Remember what must happen before a second Python run.
What do you think happens?
The Database Browser is open but no one is typing in it. What should you do before running Python code that modifies the same SQLite database?
Reveal answer
Close the browser completely before running Python.
The browser can still hold the database open or retain unsaved changes. Separating browser verification from Python execution avoids lock conflicts.
Key Takeaways
- 1Use the Database Browser for SQLite to inspect and verify the rows and values produced by Python.
- 2SQLite lock states control access to the database file, and an exclusive lock prevents other programs from reading or writing during a save.
- 3Close the browser before running Python, especially after making or saving browser changes.
- 4Use the safe cycle of Python execution, browser verification, browser closure, and the next Python run.
- 5For a lock error, first check for an open browser, unsaved browser changes, or a Python process that did not terminate cleanly.
At a glance
- The Database Browser for SQLite is useful for verifying Python database reads and writes.
- SQLite file locking prevents simultaneous modifications by different programs.
- A browser that is open or contains unsaved changes can cause Python lock errors.
- The safe debugging workflow alternates between Python execution and browser verification, with the browser closed before every Python run.