Databases and SQL
Files are fine for small data. Real apps keep data in a database and ask it questions in SQL.
Run this room's code on your own computer in VS Code. You set this up in Module 07.
Learn it
SQL commands create, add, read and change rows in database tables.
A database stores data in tables of rows and columns, and lets many programs read and change it safely. SQL (Structured Query Language) is the language for asking a database questions.
Python includes SQLite, a small database that lives in a single file, with the sqlite3 module. Nothing to install.
The four main SQL commands are CREATE TABLE (make a table), INSERT (add rows), SELECT (read rows) and UPDATE/DELETE (change or remove rows). Always commit() to save changes.
import sqlite3 con = sqlite3.connect("game.db") cur = con.cursor() cur.execute("DROP TABLE IF EXISTS scores") cur.execute("CREATE TABLE scores (name TEXT, score INTEGER)") cur.execute("INSERT INTO scores VALUES ('Nova', 128)") cur.execute("INSERT INTO scores VALUES ('Kai', 95)") con.commit() for row in cur.execute("SELECT name, score FROM scores"): print(row) con.close()
('Nova', 128)
('Kai', 95)Try it yourself
+10 XPAdd a third player, then select only the players with a score over 100, highest first.
Create a free account to start this lab
Sign in to write, run and check your code here. Your progress, XP and code are saved to your account. No card needed.
Capture the flag
+10 XPWhich SQL command reads rows from a table?
Create a free account to start this lab
Sign in to answer the flag question. Your progress, XP and code are saved to your account. No card needed.
Inventory database
+20 XPCreate inventory.db with a table items (name TEXT, qty INTEGER). Insert 4 items, update one quantity with UPDATE items SET qty = ? WHERE name = ?, delete one item, then print all remaining rows ordered by name.
Create a free account to start this lab
Sign in to do this homework and have it checked. Your progress, XP and code are saved to your account. No card needed.