SQLite — a database in one file

You will be able to

• ✓ Map tables to lists of dicts
• ✓ Explain INSERT, SELECT and WHERE
• ✓ Know when to graduate from CSV to SQLite

A table is rows with a schema

CREATE TABLE reading (id INTEGER, device TEXT, celsius REAL) declares columns and types. Each INSERT adds a row. SELECT device, celsius FROM reading WHERE celsius >= 35 is a filter you already wrote with a for-loop. SQL is that filter plus an engine that can use indexes instead of scanning every row.

Primary keys (id) let you point at one row forever. Foreign keys let a booking row point at a student row. That is how real school ERPs stop duplicating a name in twenty files.

SQL you will actually type

INSERT INTO reading (device, celsius) VALUES ('lab-dht-04', 31) writes. SELECT * FROM reading WHERE device = 'lab-dht-04' ORDER BY id DESC LIMIT 50 reads a dashboard. UPDATE student SET house = 'Blue' WHERE roll = 19 edits. DELETE is permanent — prefer a status column (active = 0) for school records.

Never build SQL by gluing user text: "... WHERE name = '" + name + "'". That is SQL injection. Use placeholders: cursor.execute("... WHERE name = ?", (name,)). pandas and web frameworks fail interviews on this one point.

CSV vs SQLite vs “a real server”

Use CSV when a human must open the file in Excel and the data is a snapshot. Use SQLite when the program is the owner: sensors appending every minute, a pygame high-score table, a local LMS cache. Use Postgres or MySQL when many users write at once over the network — that is campus ERP, not a laptop lab tool.

You can always export SQLite back to CSV for a principal. You cannot cheaply turn a folder of CSVs into safe concurrent writes.

Transactions

A transaction is “all of these writes, or none.” Transferring a book from one student to another is two updates. If the power dies after the first, a transaction rolls back. sqlite3 gives you this with a connection; CSV does not.

Words that matter

Schema

The declared columns and types of a table.

Query

A SELECT (or similar) that asks the engine for a subset.

Index

A lookup structure so WHERE on a column is not a full scan.

Placeholder

A ? in SQL filled by the driver — never concatenate user text.

Common mistakes

On a full Python install — sqlite3 (stdlib)

Python’s standard library talks to SQLite. No pip. One file you can copy with the project.

No pip needed.

Create a readings table, insert two rows, select heat days.

import sqlite3

con = sqlite3.connect("lab.db")
con.execute(
    "CREATE TABLE IF NOT EXISTS reading (device TEXT, celsius REAL)"
)
con.execute("INSERT INTO reading VALUES (?, ?)", ("lab-dht-04", 31))
con.execute("INSERT INTO reading VALUES (?, ?)", ("lab-dht-04", 37))
con.commit()

rows = con.execute(
    "SELECT celsius FROM reading WHERE celsius >= 35"
).fetchall()
print(rows)
con.close()

Example program — The same table as a list of dicts

WHERE celsius >= 35 is this loop. Databases make it fast and durable.

Your turn — SELECT house Blue

students is a table. Collect names where house is Blue. Print so Kabir appears.

Self-assessment

Check your understanding before you mark the lesson complete.