SQL and SQLite from Python
Meet databases from zero: tables, rows and columns, SQLite (a whole database in one file, built into Python), and the four things you do with data: create, read, update and delete. You'll write SQL with Python's sqlite3 module, learn why SQL is never built from strings, and look inside a database from the terminal. Then you'll build a plant shop's catalog.
Updated
Databases in Plain Words
- What a Database Is, and Why SQLite (Apprentice): 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.
- Tables and Rows: CREATE TABLE and INSERT (Apprentice): CREATE TABLE IF NOT EXISTS gives each column a type and rules such as NOT NULL, DEFAULT and CHECK that refuse bad rows, and INSERT adds rows with ? placeholders, one at a time with execute or many with executemany, with lastrowid giving the new row's id.
Reading and Changing Rows
- Reading Rows: SELECT, WHERE and ORDER BY (Apprentice): SELECT asks a table for the columns you name, WHERE keeps only the rows that pass its test (with AND, OR and LIKE), ORDER BY sorts them and LIMIT caps how many come back, and in Python fetchone and fetchall read the answer, with sqlite3.Row giving rows you read by column name.
- Changing Rows: UPDATE and DELETE (Apprentice): UPDATE changes the columns SET names and DELETE removes rows, each only in the rows its WHERE picks, rowcount says how many rows that was so an API can tell a change from a missing row, and a with block commits changes that belong together or rolls them all back.
- Placeholders, and Why SQL Is Never Built from Strings (Journeyman): SQL built with an f-string lets whatever a person types become part of the query, so a name such as x' OR '1'='1 turns one search into "every row", which is SQL injection, and ? or :name placeholders stop it by sending each value separately from the SQL.
Exercise: Plant Shop Catalog
- Exercise: Plant Shop Catalog (Apprentice): Move Fern & Fig Plant Shop's catalog into SQLite by writing six functions with hand-written SQL, to make the table with its rules, add a plant, list the pet-safe plants cheapest first, search by part of a name, change a price and remove a plant, every value passed with a placeholder, and let pytest check each one on a fresh database in memory.