Writing Data to SQLite with INSERT Statements
The central rule is simple: do not leave the database open in the Database Browser while a Python program is running and modifying that same database.
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 Verification Stops at the Lock
A Python program can contain a correct INSERT operation and still fail when it tries to modify a SQLite database. The problem may not be the data operation itself. The Database Browser for SQLite may still have the same database open, or it may have unsaved changes. SQLite file locking prevents multiple programs from modifying the database simultaneously, so verification requires a deliberate sequence: run Python, close Python access, open the browser to inspect the result, and close the browser before the next write.
The central rule is simple: do not leave the database open in the Database Browser while a Python program is running and modifying that same database.
The Write Path
Think of a Python INSERT statement as the start of a write path. Python requests a change, SQLite manages that change in the database file, and the Database Browser later displays the stored result. The browser is useful for checking whether the program really wrote the expected data, but it should be used as a separate verification step rather than kept open during the Python write.
Database Browser view: existing rows
The browser shows the database before the Python write is verified.
Database Browser view: existing rows plus the row written by Python
After Python finishes and the browser is reopened, the browser can be used to inspect the written result.
The before-and-after view represents two separate inspection moments. First, the browser may show the earlier contents of the database. Python then performs the write while the browser is closed. Only after that run should the browser be opened again to inspect whether the database now contains the expected result.
SQLite Lock States
SQLite uses a series of lock states to manage access to the database file. When the Database Browser opens a database, it holds a shared lock that allows reading. When a change is made in the browser, the browser upgrades to a reserved lock, indicating that a write operation is pending. When the change is saved, the browser acquires an exclusive lock while writing the change to disk. During that exclusive-lock period, no other program can read or write the database. After saving completes, the browser releases the lock.
A Safe Verification Cycle
Checking a Python Database Write
A learner wants to use the Database Browser to check whether a Python program correctly wrote data with an INSERT operation.
- 1Close the browser
Before running Python, fully close the Database Browser and make sure the database is not left open there.
- 2Run Python
Run the Python program that reads or writes the SQLite database.
- 3Open the browser
After the Python run, open the database in the Database Browser to inspect the result.
- 4Verify the data
Use the browser to check whether the program produced the expected database contents.
- 5Close the browser again
Close the browser before the next Python run so the next program execution does not compete with the browser for access.
Browser verification and Python execution are separated into alternating steps, reducing lock conflicts during debugging.
The browser is not a live dashboard that should remain open throughout development. Treat it as a verification tool used between Python runs.
Diagnosing a Locked Database
Common mistake Leaving the Database Browser open while running Python.
What it looks likeThe learner keeps the database visible in the browser and starts a Python program that modifies the same database.
Why it failsSQLite file locking prevents multiple programs from modifying the database simultaneously.
Do this insteadFully close the browser before running the Python program.
Common mistake Assuming that an inactive browser is harmless.
What it looks likeThe learner is not typing in the browser, so they assume Python can use the database freely.
Why it failsOpening the database in the browser can hold a shared lock, and browser changes can move it into reserved or exclusive locking states.
Do this insteadSeparate browser verification from Python execution instead of relying on the browser being idle.
Common mistake Running Python while browser changes are unsaved.
What it looks likeThe learner changes data in the browser but starts Python before saving and closing the database.
Why it failsThe database can remain locked while the browser has pending or saved changes.
Do this insteadSave the browser changes and fully close the database before running Python.
Common mistake Stopping after closing the browser when the error remains.
What it looks likeThe browser is closed, but Python still reports that the database is locked.
Why it failsA Python process may have crashed or hung while holding a lock.
Do this insteadIf the browser is already closed, check whether a Python process failed to terminate cleanly.
Practice the Workflow
A Python program reports database is locked immediately after you inspected the database in the Database Browser. Describe the order of actions you would take before trying the Python program again.
Hints
- Check whether the Database Browser is still open.
- Remember that unsaved browser changes also matter.
- If the browser is already closed, consider whether a Python process crashed or hung.
What do you think happens?
What should happen first after Python finishes writing data if you want to verify the result safely?
Reveal answer
Open the browser to inspect the result.
The safe workflow runs Python with the browser closed, then opens the browser to inspect the result, and closes it again before the next Python run.
Working Rule
- 1Use the Database Browser for SQLite to verify what a Python program wrote or read.
- 2SQLite locks protect the database from simultaneous modification by multiple programs.
- 3A browser-open database can interfere with a Python write, even when the browser is not actively being edited.
- 4Use the cycle close browser, run Python, open browser, verify results, and close browser again.
- 5When a lock error appears, close the browser completely first; if it is already closed, check for a Python process that crashed or hung while holding the lock.
Key Takeaways
- The Database Browser is a verification tool for checking Python database reads and writes.
- SQLite moves through lock states that control reading and writing access.
- The browser and a Python writer should not use the same database at the same time.
- A safe debugging cycle alternates between Python execution and browser inspection.
- Closing the browser completely is the first step when a database lock error occurs.