Introduction to SQLite and Python

SQLite is a lightweight, file-based database that comes built into Python via the sqlite3 module. No server setup required. You write SQL (Structured Query Language) to create tables, insert data, and query results.

import sqlite3

# Connect (creates file if it doesn't exist)
conn = sqlite3.connect("school.db")
cursor = conn.cursor()

# Create a table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        year INTEGER NOT NULL,
        grade REAL
    )
""")

conn.commit()  # save changes
print("Table created successfully!")
conn.close()   # always close the connection

Use ":memory:" instead of a filename to create a temporary in-memory database for testing.

CRUD Operations

CRUD stands for Create, Read, Update, Delete -- the four fundamental database operations. Python uses parameterised queries (with ? placeholders) to prevent SQL injection attacks.

import sqlite3

conn = sqlite3.connect("school.db")
cursor = conn.cursor()

# CREATE (Insert)
cursor.execute("INSERT INTO students (name, year, grade) VALUES (?, ?, ?)",
               ("Alice", 12, 92.5))
cursor.execute("INSERT INTO students (name, year, grade) VALUES (?, ?, ?)",
               ("Bob", 12, 87.0))

# Insert multiple rows at once
students = [("Charlie", 11, 78.5), ("Diana", 12, 95.0)]
cursor.executemany("INSERT INTO students (name, year, grade) VALUES (?, ?, ?)",
                   students)
conn.commit()

# READ (Select)
cursor.execute("SELECT * FROM students WHERE year = ?", (12,))
rows = cursor.fetchall()
for row in rows:
    print(row)  # (1, 'Alice', 12, 92.5)

# UPDATE
cursor.execute("UPDATE students SET grade = ? WHERE name = ?", (90.0, "Bob"))

# DELETE
cursor.execute("DELETE FROM students WHERE name = ?", ("Charlie",))
conn.commit()
conn.close()

Security: Never use f-strings or string concatenation in SQL queries. Always use ? placeholders to prevent SQL injection attacks.

Advanced Queries and Context Managers

SQL provides powerful clauses like ORDER BY, GROUP BY, and aggregate functions (COUNT, AVG, SUM). Using Python's with statement ensures the connection is properly closed.

import sqlite3

with sqlite3.connect("school.db") as conn:
    cursor = conn.cursor()

    # ORDER BY
    cursor.execute("SELECT name, grade FROM students ORDER BY grade DESC")
    print("Top students:")
    for name, grade in cursor.fetchall():
        print(f"  {name}: {grade}")

    # Aggregate functions
    cursor.execute("SELECT AVG(grade) FROM students WHERE year = 12")
    avg = cursor.fetchone()[0]
    print(f"Year 12 average: {avg:.1f}")

    cursor.execute("SELECT year, COUNT(*), AVG(grade) FROM students GROUP BY year")
    for year, count, avg in cursor.fetchall():
        print(f"Year {year}: {count} students, avg grade {avg:.1f}")

    # Use Row factory for named column access
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM students WHERE name = ?", ("Alice",))
    student = cursor.fetchone()
    print(f"{student['name']} is in Year {student['year']}")

Key Vocabulary

SQL

Structured Query Language - the standard language for creating, reading, updating, and deleting data in relational databases.

CRUD

The four basic operations: Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE).

Primary Key

A unique identifier for each row in a table. Often an auto-incrementing integer (id).

SQL Injection

A security vulnerability where malicious SQL is inserted into queries. Prevented by using parameterised queries (?).

Worked Examples

1

Build a simple to-do list with SQLite

import sqlite3

def create_todo_db():
    with sqlite3.connect("todos.db") as conn:
        conn.execute("""CREATE TABLE IF NOT EXISTS todos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            task TEXT NOT NULL,
            done BOOLEAN DEFAULT 0
        )""")

def add_task(task):
    with sqlite3.connect("todos.db") as conn:
        conn.execute("INSERT INTO todos (task) VALUES (?)", (task,))

def list_tasks():
    with sqlite3.connect("todos.db") as conn:
        rows = conn.execute("SELECT id, task, done FROM todos").fetchall()
        for id, task, done in rows:
            status = "DONE" if done else "TODO"
            print(f"[{status}] {id}. {task}")

def complete_task(task_id):
    with sqlite3.connect("todos.db") as conn:
        conn.execute("UPDATE todos SET done = 1 WHERE id = ?", (task_id,))

create_todo_db()
add_task("Study Python databases")
add_task("Complete practice questions")
complete_task(1)
list_tasks()

Pattern: Each function opens its own connection with with, ensuring clean resource management.

2

Query with filtering and sorting

import sqlite3

with sqlite3.connect("school.db") as conn:
    cursor = conn.cursor()

    # Find students with grade > 90, sorted by grade
    cursor.execute("""
        SELECT name, grade
        FROM students
        WHERE grade > ? AND year = ?
        ORDER BY grade DESC
    """, (90, 12))

    print("High achievers (Year 12, grade > 90):")
    for name, grade in cursor.fetchall():
        print(f"  {name}: {grade}")

    # Count students per year level
    cursor.execute("""
        SELECT year, COUNT(*) as count
        FROM students
        GROUP BY year
        HAVING count > 1
    """)
    for year, count in cursor.fetchall():
        print(f"Year {year}: {count} students")

HAVING filters groups after GROUP BY, while WHERE filters individual rows before grouping.

3

Create related tables with foreign keys

import sqlite3

with sqlite3.connect("school.db") as conn:
    conn.execute("PRAGMA foreign_keys = ON")  # enable FK support
    cursor = conn.cursor()

    cursor.execute("""CREATE TABLE IF NOT EXISTS subjects (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
    )""")

    cursor.execute("""CREATE TABLE IF NOT EXISTS enrolments (
        student_id INTEGER,
        subject_id INTEGER,
        FOREIGN KEY (student_id) REFERENCES students(id),
        FOREIGN KEY (subject_id) REFERENCES subjects(id)
    )""")

    # JOIN query - combine data from related tables
    cursor.execute("""
        SELECT s.name, sub.name
        FROM enrolments e
        JOIN students s ON e.student_id = s.id
        JOIN subjects sub ON e.subject_id = sub.id
    """)
    for student, subject in cursor.fetchall():
        print(f"{student} is enrolled in {subject}")

Foreign keys create relationships between tables. JOIN queries combine data from multiple related tables.

Knowledge Check

Select the correct answer for each question. Click "Check Answer" to see if you are right.

Question 1

What SQL statement is used to add a new row to a table?

SELECT CREATE UPDATE INSERT INTO

Question 2

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

They prevent SQL injection attacks They run faster f-strings do not work with sqlite3 They use less memory

Question 3

What does conn.commit() do?

Closes the database connection Saves all pending changes to the database file Creates a new table Rolls back the last change

Question 4

What does fetchall() return?

A single row as a tuple A dictionary of column names and values A list of tuples, one per matching row The number of matching rows

Question 5

What does AUTOINCREMENT do for a PRIMARY KEY column?

Creates a backup of the table Deletes old rows when new ones are added Sorts the table automatically Automatically assigns a unique, incrementing integer to each new row

Python and Databases