SQLite is a whole database in one file, and Python ships with the driver. No server, no install.
python
import sqlite3 from pathlib import Path Path("shop.db").unlink(missing_ok=True) # start empty each time you press Run conn = sqlite3.connect("shop.db") # creates the file if it isn't there with conn: # commits at the end of the block conn.execute("CREATE TABLE IF NOT EXISTS products (name TEXT, price REAL)") conn.executemany( "INSERT INTO products (name, price) VALUES (?, ?)", [("Notebook", 3.50), ("Pen", 1.20), ("Backpack", 34.00)], ) for name, price in conn.execute("SELECT name, price FROM products WHERE price < ? ORDER BY price", (5,)): print(f"{name}: {price:.2f}") conn.close()
output
Pen: 1.20 Notebook: 3.50
Three things in that example carry the weight:
with conn:commits when the block ends, and rolls back if anything inside it raised. Without it (or an explicitconn.commit()), your inserts vanish when the program exits.?placeholders pass values separately from the SQL. More on why below.(5,), with the comma. Parameters are always a tuple, and(5)is just the number 5.
Never build SQL with f-strings
This is the mistake that turns into a security incident. Put user input straight into the SQL text and the input gets to write SQL too:
python
import sqlite3 conn = sqlite3.connect(":memory:") # a throwaway database in memory with conn: conn.execute("CREATE TABLE users (name TEXT, is_admin INTEGER)") conn.executemany("INSERT INTO users VALUES (?, ?)", [("ada", 1), ("linus", 0)]) typed = "nobody' OR '1'='1" # what someone types into your search box unsafe = conn.execute(f"SELECT name FROM users WHERE name = '{typed}'").fetchall() safe = conn.execute("SELECT name FROM users WHERE name = ?", (typed,)).fetchall() print("f-string:", unsafe) print("placeholder:", safe)
output
f-string: [('ada',), ('linus',)]
placeholder: []The f-string version returned every user, because the input closed the quote and added a condition that is always true. With ?, the whole input is treated as one name to look for, and nobody is called that. This is SQL injection, and it's still in the OWASP Top 10.
Rows as dictionaries
By default each row is a tuple. Set row_factory and you can use column names instead:
python
import sqlite3 conn = sqlite3.connect(":memory:") conn.row_factory = sqlite3.Row with conn: conn.execute("CREATE TABLE products (name TEXT, price REAL)") conn.execute("INSERT INTO products VALUES (?, ?)", ("Pen", 1.20)) row = conn.execute("SELECT * FROM products").fetchone() print(row["name"], row["price"]) print(dict(row))
output
Pen 1.2
{'name': 'Pen', 'price': 1.2}When the app grows past a handful of tables, the Databases lesson picks up with SQLAlchemy.