SQLite with Python

Store, query, update and delete data with SQLite — the file-based relational database built right into Python’s standard library.

"SQLite is a database that lives in a single file. No server to install, no service to run. Python ships it built-in — and it powers millions of apps, from mobile phones to web browsers."

— Shurai

SQLite vs Other Databases

📄 SQLite

Single .db file
No server needed
Built into Python (import sqlite3)
Perfect for apps, prototypes, testing
Used by Android, iOS, Firefox, Chrome

🔩 PostgreSQL / MySQL

Dedicated server process
Handles many concurrent users
Better for high-traffic production apps
Need pip install + configuration

The SQLite Workflow — 4 Steps

connectconn = sqlite3.connect("mydb.db") — opens/creates the file cursorcur = conn.cursor() — the object that runs SQL executecur.execute("SQL...") — run a query commitconn.commit() — save changes to disk (required for INSERT/UPDATE/DELETE)

Create a Table & Insert Data

python

import sqlite3

conn = sqlite3.connect("school.db")   # creates file if needed
cur  = conn.cursor()

# CREATE TABLE (IF NOT EXISTS = safe to run multiple times)
cur.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        name    TEXT    NOT NULL,
        grade   TEXT,
        score   REAL
    )
""")

# INSERT multiple rows at once
# ⚠️  Always use ? placeholders — never f-strings (SQL injection!)
data = [
    ("Riya",   "A", 92.0),
    ("Arjun",  "B", 75.5),
    ("Sneha",  "A", 88.0),
    ("Vikram", "C", 60.0),
]
cur.executemany(
    "INSERT INTO students (name, grade, score) VALUES (?, ?, ?)",
    data
)
conn.commit()   # SAVE — without this, data is lost!
print("Inserted 4 students")

⚠️ SQL Injection — Never use f-strings in SQL

Writing f"SELECT * FROM users WHERE name = '{name}'" is dangerous. If a user inputs ' OR '1'='1, it changes the meaning of your query entirely. Always use ? placeholders and pass values as a tuple: cur.execute("... WHERE name = ?", (name,)).

SELECT — Query Your Data

python

# All students
cur.execute("SELECT * FROM students")
for row in cur.fetchall():
    print(row)    # (1, 'Riya', 'A', 92.0), ...

# Filtered — students scoring 80 or above
cur.execute("SELECT name, score FROM students WHERE score >= ?", (80,))
print(cur.fetchall())   # [('Riya', 92.0), ('Sneha', 88.0)]

# Ordered
cur.execute("SELECT name, score FROM students ORDER BY score DESC")
print(cur.fetchall())   # highest score first

# Count + aggregate
cur.execute("SELECT COUNT(*), AVG(score), MAX(score) FROM students")
print(cur.fetchone())   # (4, 78.875, 92.0)

UPDATE and DELETE

python

# UPDATE — change one value
cur.execute("UPDATE students SET grade = ? WHERE name = ?", ("A+", "Riya"))
conn.commit()

# DELETE — remove rows matching a condition
cur.execute("DELETE FROM students WHERE score < ?", (65,))
conn.commit()
print(f"Rows deleted: {cur.rowcount}")

Cleaner Code — Context Manager + Row Objects

python — best practice pattern

import sqlite3

with sqlite3.connect("school.db") as conn:
    conn.row_factory = sqlite3.Row   # ← rows as dicts!
    cur = conn.cursor()

    cur.execute("SELECT * FROM students ORDER BY score DESC")
    for row in cur.fetchall():
        # access by column name instead of row[0], row[1], ...
        print(f"  {row['name']:8}  {row['grade']}  {row['score']}")
# connection closes automatically when the "with" block exits

output

  Riya      A+  92.0
  Sneha      A  88.0
  Arjun      B  75.5

"Always use the context manager (with sqlite3.connect(...) as conn) and always use ? placeholders. These two habits alone prevent 90% of SQLite bugs."

— Shurai

🧠 Quiz — Q1

What is the key advantage of SQLite over databases like PostgreSQL?

🧠 Quiz — Q2

Why must you call conn.commit() after INSERT, UPDATE, or DELETE?

🧠 Quiz — Q3

Why should you always use ? placeholders instead of f-strings in SQL queries?

🧠 Quiz — Q4

What does setting conn.row_factory = sqlite3.Row give you?