# What a Database Is, and Why SQLite

A JSON file loses changes when two people save at once and has to be read whole to find one item, so an API keeps its data in a database of tables, rows and columns, and Python's built-in sqlite3 module opens a whole SQLite database stored in one file.

Apprentice · SQLite · SQL · Database · Updated November 19, 2026

By Gregor Dzierzon

## After this lesson you can

- Explain what a database's tables, rows, columns and primary key are, and why an API keeps its data in a database instead of a JSON file
- Open a SQLite database with sqlite3.connect, run SQL with execute, save changes with commit and close the connection

## Key takeaways

- A database keeps data in tables, like labeled spreadsheets, with a column for each fact and a row for each item.
- A primary key is a column whose value is different in every row, such as an id, so one value always means one row.
- A JSON file loses a change when two people save at once and must be read whole to find one item, which a database is built to handle.
- SQLite is a whole database in one file, and Python's sqlite3 module opens it with no server and nothing to install.
- sqlite3.connect opens a database, execute runs one SQL statement, commit saves the changes and close lets the file go.

Every app that remembers something keeps it somewhere: the orders a shop took today, the accounts people signed up for, the items in a to-do list. Up to now, this course's APIs kept their data in a Python list, which is gone the moment the server stops. In this module your API's data moves into a database, so it's still there tomorrow.

The example for this chapter is Fern & Fig, a small plant shop. Its catalog (each plant's name, the light it needs, how often to water it, its price, whether it's safe for pets, and a care tip) lives in a file, `plants.json`, and the shop's API reads that file and writes it back.

## The catalog in a JSON file

A JSON file works for one person. Trouble starts when two people use it at once. Ana and Ben both open the catalog at the shop's counter: Ana lowers the Snake Plant's price and saves, and a moment later Ben adds a Boston Fern and saves. Each of them saves the whole list as they last read it:

```python
import json

saved = '[{"name": "Snake Plant", "price": 24.0}]'

ana = json.loads(saved)
ben = json.loads(saved)

ana[0]["price"] = 22.0
saved = json.dumps(ana)

ben.append({"name": "Boston Fern", "price": 18.0})
saved = json.dumps(ben)

print(saved)
```

```text
[{"name": "Snake Plant", "price": 24.0}, {"name": "Boston Fern", "price": 18.0}]
```

Ben's save wrote his old copy over Ana's, so the Snake Plant is back to $24.00 and nobody was told. And as the catalog grows, finding one plant means reading every plant in the file, while nothing stops someone saving a plant with no name.

A **database** is a program built for these jobs: it takes many changes at once without losing any, finds one item among thousands quickly, and refuses data that breaks its rules.

## Tables, rows and columns

Picture a spreadsheet with a labeled column for each fact and a line for each plant. A database keeps its data the same way, in **tables**: each **column** holds one fact (the name, the price), and each **row** is one item (one plant). A database usually holds several tables, one for each kind of thing: plants, customers, orders.

![The plants table drawn as a labeled grid: three columns, id, name and price, across the top and two rows below them, 1, Snake Plant, 24.0 and 2, Spider Plant, 12.5. One row is outlined and labeled row, the price column is outlined and labeled column, and the id column is labeled primary key](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/11/874290ee66f242e7b46fcdcbbb286df9.webp)

The `id` column is the table's **primary key**: a value that's different in every row, like a receipt number. Two plants might share a name, but plant 2 is always exactly one row.

You talk to a database in **SQL** (read "S-Q-L", or "sequel"), a language made for asking about tables: "give me the plants under $20", "add this plant", "change that price". Most databases run as a server, a separate program your app connects to: PostgreSQL and SQL Server are two that bigger apps use. **SQLite** is different: the whole database is one file on disk, and Python comes with the `sqlite3` module that opens it, so there's nothing to install. It's a good fit for learning and for small apps, and the SQL you learn here carries over to the servers.

## Connect, execute, commit, close

Four steps talk to a SQLite database from Python:

```python
import sqlite3

conn = sqlite3.connect("shop.db")
conn.execute("CREATE TABLE IF NOT EXISTS plants (id INTEGER PRIMARY KEY, name TEXT, price REAL)")
conn.execute("INSERT INTO plants (name, price) VALUES (?, ?)", ("Snake Plant", 24.0))
conn.execute("INSERT INTO plants (name, price) VALUES (?, ?)", ("Spider Plant", 12.5))
conn.commit()

row = conn.execute("SELECT COUNT(*) FROM plants").fetchone()
print(row)
print(row[0], "plants")
conn.close()
```

```text
(2,)
2 plants
```

- `sqlite3.connect("shop.db")` opens the file, making it when it isn't there yet, and gives back a **connection**, `conn`: your line to the database. `sqlite3.connect(":memory:")` makes a database in memory instead, gone when the program ends, which is handy for practice and tests.
- `conn.execute(...)` runs one SQL statement. Here they make the `plants` table, add two rows, and count them. The `?` marks (read "question mark") hold places for the values that follow in brackets. *Tables and Rows: CREATE TABLE and INSERT* takes these statements apart.
- `conn.commit()` saves the changes into the file. Until then, they're only a draft that this connection can see.
- `execute` hands back a **cursor**, the object that steps through a statement's answer, and `fetchone()` takes the answer's next row as a tuple: `(2,)`. `row[0]` is the count.
- `conn.close()` lets go of the file.

Run the program a second time and it prints `4 plants`: the first run's two plants were still in `shop.db`. That's the whole point of a database.

## Looking inside with python -m sqlite3

Python 3.12 and later can open a database file in the terminal, so you can look at what your code saved. In VS Code's terminal, in the folder that holds `shop.db` (on a Mac, type `python3`):

```bash
python -m sqlite3 shop.db
```

It prints a few lines about itself, including the version of SQLite inside your Python, and then waits at a `sqlite>` prompt for SQL. After one run of the program above:

```text
sqlite> SELECT * FROM plants;
(1, 'Snake Plant', 24.0)
(2, 'Spider Plant', 12.5)
sqlite> .quit
```

`SELECT * FROM plants;` (read "select star from plants": `*` means every column) shows each row as a tuple, and `.quit` leaves the shell. What you type there runs on the real file, so use it to look, and make changes through your code.

## Do it: start the plant catalog in memory

Before moving the real catalog, Fern & Fig wants to try a database in memory.

- `plants`: a list of plants to add, each a tuple of a name and a price, such as `("Boston Fern", 18.0)`.

**Your job:** write the function `start_catalog(plants)`.

- Open a new database in memory with `sqlite3.connect(":memory:")`.
- Make the table `plants` with three columns: `id INTEGER PRIMARY KEY`, `name TEXT` and `price REAL`.
- Add each plant in the list, in order, with an `INSERT` that uses `?` placeholders. Leave `id` out: SQLite numbers the rows 1, 2, 3 for you.
- Return the connection.

The tests read your table back through the connection you return. An empty list still makes the table, with no rows in it.

Worked through: `start_catalog([("Boston Fern", 18.0), ("Parlor Palm", 29.0)])` makes the table and adds two rows, so on the connection it returns, `SELECT COUNT(*) FROM plants` gives `(2,)`, and `SELECT id, name, price FROM plants` gives these rows:

```text
[(1, 'Boston Fern', 18.0), (2, 'Parlor Palm', 29.0)]
```

Practiced live on the lesson page: write the Python right there and check it.

## Check yourself

2 questions to try on the lesson page.

## So what

Every API that remembers anything keeps its data in a database, and with tables, rows, a primary key and four steps in Python, you've opened your first one.

## Key terms

- [Database](https://www.ask-codey.com/glossary/database)
- [Table](https://www.ask-codey.com/glossary/table)
- [Row](https://www.ask-codey.com/glossary/row)
- [Column](https://www.ask-codey.com/glossary/column)
- [Primary Key](https://www.ask-codey.com/glossary/primary-key)
- [SQLite](https://www.ask-codey.com/glossary/sqlite)

## Related

- [Tables and Rows: CREATE TABLE and INSERT](https://www.ask-codey.com/lessons/fastapi-sql-create-and-insert)
- [Reading Rows: SELECT, WHERE and ORDER BY](https://www.ask-codey.com/lessons/fastapi-sql-select)

Source: https://www.ask-codey.com/lessons/fastapi-sqlite-what-a-database-is
