Asking questions against a database

Simple example: San Francisco street trees

The city of San Francisco maintains a public dataset of every street tree in the city. We’ll start by loading the current snapshot into a SQLite database and asking questions against it.

Give your coding agent this prompt:

Run `uvx sqlite-utils insert --help` to learn how sqlite-utils works, then use it to load https://raw.githubusercontent.com/simonw/sf-tree-history/refs/heads/main/Street_Tree_List.csv into a SQLite database called trees.db with TreeID as the primary key.

Once the database is created, browse it with Datasette:

uvx datasette trees.db

Now try asking your coding agent questions about the trees. Here are some to get started:

  • What are the ten most common tree species in San Francisco?

  • How many trees were planted each year? What was the peak year for planting?

  • Which streets have the most trees?

  • How many trees are cared for by the city (DPW) vs. private owners?

Bring your own data

Once you’ve tried the trees example, the next step is to use the same pattern on a dataset you care about.

Good candidates include:

  • A CSV you downloaded from a government open data portal

  • An Excel spreadsheet from a records request

Start with a prompt like this:

I have a dataset in the current directory. Figure out what format it is, load it into a SQLite database, and then help me explore it.

Use `uvx sqlite-utils --help` if needed.

Once the database is built, browse it with Datasette:

uvx datasette data.db

Then start asking questions.