Follow One Question Through the Database

Someone asks, “What can people borrow?” You already wrote the query that answers that question:

SELECT name FROM items;

Here is the schema it uses again:

Schema for items

The query names two parts of this schema. name is the column you want back, and items is the table that supplies the rows.

On your own computer, imagine that this catalog is stored in a file named lending.db. Python opens a connection to that file, and SQLite follows the query to the requested names:

Python connection
        ↓
lending.db
        ↓
items table
        ↓
name column
        ↓
Cordless drill, Folding table, Projector

The connection is the route Python uses while the program is running. The rows live in lending.db. Calling connection.close() ends that route, but it does not remove the file or its rows.

Later, Python can open a new connection to the same file and run the same query. SQLite still finds the items table and returns its names.

The connection that returned Projector may be gone, but the Projector row is still in lending.db. A new connection can ask the same file for the names again. In the next chapter, you will create the items table instead of finding it ready for you.