Concepts / Querying Data from SQLite with SELECT Statements

Querying Data from SQLite with SELECT Statements

Use the browser to inspect results, but close it completely before running Python code against the same database.

  • Programming
Interactive lab

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

  1. 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.
  2. Writes go into the writer's private copy of the pages; other sessions keep reading the last committed data.
  3. A second session that tries to write while the first holds the write lock gets "database is locked".
  4. 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.
  5. 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.

Educational simulation

Loading the simulation…

The Verification Problem

When a Python program reads from or writes to SQLite, the Database Browser for SQLite can help you verify what is actually in the database. The important discipline is to treat Python execution and browser inspection as separate stages. A common beginner assumption is that the browser can remain open during a Python run as long as nobody is actively typing in it. That assumption can produce database lock errors even when the Python program itself appears correct.

Key point

Use the browser to inspect results, but close it completely before running Python code against the same database.

What do you think happens?

You have opened the SQLite database in the browser but are not typing or editing anything. You now run a Python program that uses the same database. What is the safest expectation?

  • The Python program will always be able to use the database because the browser is idle

  • The browser should be closed before the Python program runs

  • The database must be deleted and recreated

  • The Python program should be run repeatedly until it succeeds

Reveal answer
Answer:

The browser should be closed before the Python program runs

The recommended workflow separates browser verification from Python execution. Leaving the browser open can cause lock-related failures, and unsaved browser changes are especially important to close before running Python.

Lock States During Database Use

SQLite uses a series of 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 browser moves to a reserved lock, indicating that a write is pending. When you save, it 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 finishes, the browser releases the lock.

Database openedshared lockBrowser changereserved lockSaveexclusive lockLock releasedafter save completesmake a changepress savewriting completes
How does the database move between available reading, pending writing, exclusive writing, and released states?

Tracing a Verification Run

Worked example

Checking a Python Database Operation

A Python program is intended to read from or write to a SQLite database. How can you verify the result without creating a lock conflict?

  1. 1
    Prepare the run

    Close the Database Browser before starting the Python program. This removes the browser from the database-access sequence.

  2. 2
    Execute Python

    Run the Python program while the browser is closed. The program can now perform its database operation without the browser being left open against the same file.

  3. 3
    Inspect the result

    After Python finishes, open the database in the Database Browser and inspect the database contents to verify what the program read or wrote.

  4. 4
    Prepare the next run

    Close the browser again before making another Python run. Repeating this separation prevents the browser from interfering with the next execution.

Result

The browser acts as a verification stage between Python runs rather than as a second program that remains connected during execution.

In this workflow, a SELECT-based read is checked by inspecting the database contents in the browser after the Python program has run. If the Python program also writes data, the same inspection stage lets you verify whether the expected database contents are present. The browser is therefore used after execution to confirm the result, not kept open during execution.

SQLite databasestored dataSELECT queryread operationPython programreceives dataDatabase Browserverificationquery readsreturns datainspect after run
How does database information move through a Python read and return to the browser for verification?

The Safe Debugging Cycle

Close browserbefore PythonRun Pythonread or writeOpen browserafter PythonInspect resultsverify contentsClose browserbefore next runthenafter executionverifyfinish inspection
What sequence should you follow when alternating between running Python and checking SQLite results?
  1. 1
    Close the Database Browser completely before running Python.
  2. 2
    Run the Python program.
  3. 3
    Open the browser after Python finishes.
  4. 4
    Inspect the database contents to verify the read or write result.
  5. 5
    Close the browser completely before the next Python run.

Diagnosing Lock Errors

  • Common mistake

    Leaving the Database Browser open while running Python

    What it looks like

    The browser remains open while the Python program attempts to use the same SQLite database.

    Why it fails

    The browser may hold a lock, and the Python program can fail with a lock-related exception.

    Do this instead

    Close the browser completely before running Python.

  • Common mistake

    Running Python with unsaved browser changes

    What it looks like

    A change has been made in the browser, but the database has not been saved and closed.

    Why it fails

    The browser has a pending or active write state, so another program may not be able to use the database.

    Do this instead

    Save the browser changes and close the database before running Python.

  • Common mistake

    Treating a lock error as proof that the Python logic is wrong

    What it looks like

    The program reports that the database is locked, so the query or program is immediately rewritten.

    Why it fails

    The source identifies browser access and uncleanly terminated processes as possible causes of the lock.

    Do this instead

    First close the Database Browser completely. If it is already closed, consider whether a Python process may still hold the lock because it crashed or hung.

Edge case

If the browser is already closed and the error remains, the lock may be held by a Python process that crashed or hung while holding it. This possibility is part of the diagnosis, so inspect the running Python process rather than assuming the database contents or query are automatically at fault.

Practice the Workflow

EASY

Imagine that you have just inspected a SQLite database in the Database Browser and now need to run a Python program that reads from the same database. Describe the exact order of actions you will take, including when you open the browser, when you inspect the results, and when you close it.

Hints
  • The browser should not remain open during Python execution.
  • Inspection happens after the Python run.
  • The browser must be closed again before the next Python run.
MEDIUM

A Python program reports database is locked. The Database Browser is still open, and it contains an unsaved change. Identify the immediate action and explain why it addresses the problem.

Hints
  • Look for the program that may currently hold the database lock.
  • Unsaved browser changes are relevant.
  • The recommended workflow separates browser verification from Python execution.

Key Takeaways

  1. 1
    Use the Database Browser for SQLite to verify what a Python program read or wrote.
  2. 2
    SQLite locking prevents multiple programs from modifying the database simultaneously.
  3. 3
    Opening the database creates a shared reading lock; changing and saving data moves through reserved and exclusive lock states.
  4. 4
    Close the browser before every Python run, especially when the browser has unsaved changes.
  5. 5
    If a lock error occurs, close the browser first and then consider whether a Python process crashed or hung while holding the lock.

At a glance

  • A SELECT-based database read should be verified after the Python program finishes, using the Database Browser.
  • The browser and Python program should not be left connected to the same database during the same execution stage.
  • SQLite lock states explain why unsaved browser changes or an open browser can block another program.
  • The safe cycle is close the browser, run Python, open the browser, inspect results, and close it again.
  • For a lock error, close the browser completely first; if the error remains, investigate a Python process that did not terminate cleanly.

Related Topics

Related concepts

More concepts starting with Q