Module 20 — Python + SQL
Storing data permanently and querying it with SQLite, straight from Python.
Prerequisite: Module 19 — Python Projects
After this lesson, you will be able to
- Connect to a SQLite database from Python and create a table
- Insert, select, update, and delete records using SQL
- Build a simple CRUD (Create, Read, Update, Delete) workflow
Concept & Syntax
Files are fine for small amounts of data, but once you need to search, filter, sort, or reliably update
records, a database is the right tool. SQL (Structured Query Language) is the
language used to talk to relational databases. SQLite is a lightweight, file-based database
engine built into Python's standard library via the sqlite3 module — no separate server required,
which makes it perfect for learning and for small applications.
import sqlite3
conn = sqlite3.connect("app.db")
cursor = conn.cursor()
cursor.execute("SELECT * FROM students WHERE grade > ?", (80,))
conn.commit()
conn.close()Examples
Connecting and creating a table Very Easy
import sqlite3
conn = sqlite3.connect("school.db")
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY,
name TEXT,
grade INTEGER
)
""")
conn.commit()
conn.close()connect() opens (or creates) the database file. IF NOT EXISTS prevents an error if the table is already there. commit() saves the change to disk.
Inserting records safely Easy
import sqlite3
conn = sqlite3.connect("school.db")
cursor = conn.cursor()
cursor.execute("INSERT INTO students (name, grade) VALUES (?, ?)", ("Ada", 92))
conn.commit()
conn.close()The ? placeholders are filled in safely from the tuple — always do this instead of building SQL strings with +, which opens the door to SQL injection bugs.
Selecting with WHERE and ORDER BY Intermediate
import sqlite3
conn = sqlite3.connect("school.db")
cursor = conn.cursor()
cursor.execute("SELECT name, grade FROM students WHERE grade > ? ORDER BY grade DESC", (80,))
for row in cursor.fetchall():
print(row)
conn.close()WHERE filters rows; ORDER BY grade DESC sorts results from highest to lowest grade. fetchall() returns every matching row as a list of tuples.
Updating and deleting Real World
import sqlite3
conn = sqlite3.connect("school.db")
cursor = conn.cursor()
cursor.execute("UPDATE students SET grade = ? WHERE name = ?", (95, "Ada"))
cursor.execute("DELETE FROM students WHERE grade < ?", (50,))
conn.commit()
conn.close()UPDATE changes existing rows matching WHERE; DELETE removes rows matching WHERE. Both need commit() to actually save.
Visual Explanation
💡 Why does this work?
Read the concept explanation above again slowly, line by line — every keyword there maps directly onto a line of code in the examples. If it still doesn't click, re-run the traced example (if this lesson has one) one step at a time.
🔍 What happens internally? (advanced)
Under the hood, Python compiles your source into bytecode and runs it on a virtual machine (the CPython interpreter). Variables are names in a namespace dictionary pointing at objects in memory — which is exactly what the memory panel in the tracer above is showing you.
Common Mistakes
Building SQL strings by directly inserting user input opens a serious security hole called SQL injection.
name = input("Name: ")
cursor.execute(f"SELECT * FROM students WHERE name = '{name}'")name = input("Name: ")
cursor.execute("SELECT * FROM students WHERE name = ?", (name,))Forgetting conn.commit() means INSERT/UPDATE/DELETE changes are lost when the connection closes.
cursor.execute("INSERT INTO students (name, grade) VALUES (?, ?)", ("Sam", 88))
conn.close() # forgot commit — the insert is lost!cursor.execute("INSERT INTO students (name, grade) VALUES (?, ?)", ("Sam", 88))
conn.commit()
conn.close()- Always use ? placeholders for values in a query — never build SQL with string formatting.
- Call conn.commit() after any INSERT, UPDATE, or DELETE.
- Close the connection (or use a with block) once you're done with the database.
- Use CREATE TABLE IF NOT EXISTS so setup code can safely run more than once.
Exercise & Challenge
Exercise 1 — Fill in the blank
cursor.execute(____________________, (100,))Complete the execute() call to safely select all students with a grade of exactly 100, using a placeholder rather than string formatting.
🏆 Challenge
Build a tiny CRUD script for a 'books' table (id, title, author) that creates the table, inserts two books, then selects and prints every book ordered by title.
- Use CREATE TABLE IF NOT EXISTS
- Insert at least 2 rows using ? placeholders
- SELECT with ORDER BY title, and print each row
💡 Need a hint?
cursor.execute("SELECT * FROM books ORDER BY title")
🔍 Show a sample solution
import sqlite3
conn = sqlite3.connect("library.db")
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, title TEXT, author TEXT)")
cursor.execute("INSERT INTO books (title, author) VALUES (?, ?)", ("Dune", "Herbert"))
cursor.execute("INSERT INTO books (title, author) VALUES (?, ?)", ("Emma", "Austen"))
conn.commit()
cursor.execute("SELECT * FROM books ORDER BY title")
for row in cursor.fetchall():
print(row)
conn.close()Quiz
Answer every question, then submit to see your score and explanations.
1. What module does Python's standard library provide for SQLite?
2. Why should you use ? placeholders instead of building SQL with f-strings?
3. What must you call after an INSERT/UPDATE/DELETE to actually save the change?
4. What does WHERE grade > 80 ORDER BY grade DESC do?
Summary
- sqlite3 connects Python to a lightweight, file-based SQL database with no separate server.
- Use ? placeholders for values — never build SQL strings with user input directly.
- commit() saves INSERT/UPDATE/DELETE changes; without it, they can be lost.
- WHERE filters rows and ORDER BY sorts them — the foundation of every useful query.