7.1 Python with SQLite — Complete Database Tutorial

7.1 Python with SQLite

SQLite is a lightweight, file-based relational database that is included with Python through the built-in sqlite3 module.

Unlike database servers such as MySQL or PostgreSQL, SQLite does not require a separate database server for basic applications. The database is normally stored in a single .db file.

1. Learning Objectives

By the end of this lesson, you will be able to:

• Understand SQLite and relational databases.
• Import Python's sqlite3 module.
• Create a SQLite database.
• Create database tables.
• Insert records.
• Retrieve records using SQL.
• Filter and sort database records.
• Update existing records.
• Delete records.
• Use parameterized SQL queries.
• Commit and rollback transactions.
• Use primary keys and constraints.
• Work with multiple related tables.
• Use aggregate functions.
• Perform SQL joins.
• Connect SQLite with pandas.
• Build a small database-driven Python application.

2. What Is SQLite?

SQLite is a relational database engine that stores structured data in database files.

A SQLite database can contain multiple tables, indexes, views, and other database objects.

Python Program
      |
      v
sqlite3 Module
      |
      v
SQLite Database
      |
      v
database.db

3. SQLite vs Server-Based Databases

4. Common SQLite Use Cases

• Desktop applications
• Small web applications
• Prototypes
• Educational projects
• Testing and development
• Local data storage
• Data-analysis experiments
• Embedded applications
• Automation scripts

5. Import the sqlite3 Module

Python includes the sqlite3 module in its standard library.

import sqlite3

Therefore, no separate package installation is normally required.

6. Create or Open a SQLite Database

import sqlite3

connection = sqlite3.connect(
    "school.db"
)

If school.db does not exist, SQLite creates it. If it already exists, SQLite opens the existing database.

7. Connection and Cursor

Two objects are commonly used when working with SQLite:

• Connection: Represents the connection to the database.
• Cursor: Executes SQL statements and retrieves results.

import sqlite3

connection = sqlite3.connect(
    "school.db"
)

cursor = connection.cursor()

8. Create a Database Table

SQL is used to define the structure of a relational table.

sql = """
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    course TEXT
)
"""

cursor.execute(sql)

connection.commit()

The IF NOT EXISTS clause prevents an error if the table has already been created.

9. Understanding the Table Structure

10. Why Is commit() Important?

Database modifications generally need to be committed so that the transaction becomes persistent.

connection.commit()

Operations such as INSERT, UPDATE, and DELETE should normally be committed after successful execution.

11. Insert a Record

sql = """
INSERT INTO students
(name, age, course)
VALUES (?, ?, ?)
"""

cursor.execute(
    sql,
    (
        "Alex Morgan",
        20,
        "Computer Science"
    )
)

connection.commit()

The question marks are parameter placeholders. This is preferable to constructing SQL by directly concatenating user input.

12. Insert Multiple Records

students = [
    (
        "Alex Morgan",
        20,
        "Computer Science"
    ),
    (
        "Emma Carter",
        21,
        "Data Science"
    ),
    (
        "Noah Wilson",
        19,
        "Information Technology"
    )
]

cursor.executemany(
    """
    INSERT INTO students
    (name, age, course)
    VALUES (?, ?, ?)
    """,
    students
)

connection.commit()

executemany() is convenient when the same SQL operation needs to be performed for multiple records.

13. Retrieve All Records

cursor.execute(
    "SELECT * FROM students"
)

rows = cursor.fetchall()

for row in rows:

    print(row)

fetchall() returns all available result rows.

14. Retrieve One Record

cursor.execute(
    "SELECT * FROM students"
)

row = cursor.fetchone()

print(row)

fetchone() retrieves one row from the current result set.

15. Filter Records with WHERE

cursor.execute(
    """
    SELECT *
    FROM students
    WHERE age > ?
    """,
    (20,)
)

rows = cursor.fetchall()

for row in rows:

    print(row)

16. Sort Records with ORDER BY

cursor.execute(
    """
    SELECT *
    FROM students
    ORDER BY age DESC
    """
)

rows = cursor.fetchall()

for row in rows:

    print(row)

DESC sorts from highest to lowest, while ASC sorts from lowest to highest.

17. Limit the Number of Results

cursor.execute(
    """
    SELECT *
    FROM students
    ORDER BY age DESC
    LIMIT 2
    """
)

rows = cursor.fetchall()

This retrieves only the first two records after sorting.

18. Update a Record

cursor.execute(
    """
    UPDATE students
    SET course = ?
    WHERE id = ?
    """,
    (
        "Artificial Intelligence",
        1
    )
)

connection.commit()

19. Delete a Record

cursor.execute(
    """
    DELETE FROM students
    WHERE id = ?
    """,
    (3,)
)

connection.commit()

As with UPDATE, always carefully verify the WHERE condition.

20. Delete a Table

cursor.execute(
    """
    DROP TABLE IF EXISTS students
    """
)

connection.commit()

21. Close the Database Connection

connection.close()

Close database connections when they are no longer required.

22. Use a Context Manager

A context manager can make transaction handling safer and cleaner.

import sqlite3

with sqlite3.connect(
    "school.db"
) as connection:

    cursor = connection.cursor()

    cursor.execute(
        """
        INSERT INTO students
        (name, age, course)
        VALUES (?, ?, ?)
        """,
        (
            "Olivia Brown",
            22,
            "Data Science"
        )
    )

The connection context manager commits successfully completed transactions and rolls back when an exception causes the block to fail.

23. Parameterized SQL Queries

Never construct SQL queries by directly inserting untrusted input into SQL strings.

Avoid:

name = input(
    "Enter name: "
)

sql = (
    "SELECT * FROM students "
    + "WHERE name = '"
    + name
    + "'"
)

cursor.execute(sql)

Instead, use parameters:

name = input(
    "Enter name: "
)

cursor.execute(
    """
    SELECT *
    FROM students
    WHERE name = ?
    """,
    (name,)
)

24. Understanding SQL Injection

SQL injection occurs when untrusted input is improperly incorporated into SQL statements and changes the intended meaning of the query.

The solution is not simply to remove a few special characters. Use the database driver's parameter-binding mechanism.

cursor.execute(
    """
    SELECT *
    FROM students
    WHERE name = ?
    """,
    (user_input,)
)

25. Primary Keys

A primary key uniquely identifies a row in a table.

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    course TEXT
)

Here, id acts as the unique identifier for each student record.

26. NOT NULL Constraint

NOT NULL prevents a column from containing NULL values.

name TEXT NOT NULL

This is useful when a particular field is mandatory.

27. UNIQUE Constraint

A UNIQUE constraint prevents duplicate values in a column.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE,
    email TEXT UNIQUE
)

28. DEFAULT Values

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    status TEXT DEFAULT 'Pending'
)

If a status is not supplied during insertion, SQLite can use the defined default value.

29. Common SQLite Storage Classes

30. Aggregate Functions

SQL provides functions for calculating summaries from multiple records.

cursor.execute(
    """
    SELECT
        COUNT(*),
        AVG(age),
        MIN(age),
        MAX(age)
    FROM students
    """
)

result = cursor.fetchone()

print(result)

Common aggregate functions include:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

31. GROUP BY

GROUP BY groups records before aggregate calculations are performed.

cursor.execute(
    """
    SELECT
        course,
        COUNT(*) AS total_students
    FROM students
    GROUP BY course
    """
)

rows = cursor.fetchall()

for row in rows:

    print(row)

32. HAVING

HAVING filters grouped results.

cursor.execute(
    """
    SELECT
        course,
        COUNT(*) AS total_students
    FROM students
    GROUP BY course
    HAVING COUNT(*) > 1
    """
)

Remember the distinction:

• WHERE filters rows before grouping.
• HAVING filters groups after aggregation.

33. Working with Multiple Tables

Relational databases normally divide related information into separate tables.

students
----------------
id
name
course_id


courses
----------------
id
course_name

This avoids unnecessary repetition and creates relationships between entities.

34. Foreign Keys

A foreign key references a key in another table.

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    course_name TEXT NOT NULL
)

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    course_id INTEGER,
    FOREIGN KEY (
        course_id
    )
    REFERENCES courses(id)
)

35. Enable Foreign-Key Enforcement

When using SQLite, applications should explicitly enable foreign key enforcement when they rely on foreign-key constraints.

connection.execute(
    "PRAGMA foreign_keys = ON"
)

This should be done for each database connection where foreign-key enforcement is required.

36. INNER JOIN

A join combines related records from multiple tables.

cursor.execute(
    """
    SELECT
        students.name,
        courses.course_name
    FROM students
    INNER JOIN courses
        ON students.course_id =
           courses.id
    """
)

rows = cursor.fetchall()

for row in rows:

    print(row)

37. Database Transactions

A transaction groups related database operations into a logical unit of work.

try:

    cursor.execute(
        """
        INSERT INTO students
        (name, age, course)
        VALUES (?, ?, ?)
        """,
        (
            "Liam Davis",
            20,
            "Computer Science"
        )
    )

    cursor.execute(
        """
        INSERT INTO students
        (name, age, course)
        VALUES (?, ?, ?)
        """,
        (
            "Mia Taylor",
            21,
            "Data Science"
        )
    )

    connection.commit()

except Exception:

    connection.rollback()

    raise

If an operation fails before the commit, the application can roll back the transaction.

38. commit() vs rollback()

39. Access Columns by Name

By default, SQLite query results are returned as tuples.

cursor.execute(
    "SELECT * FROM students"
)

row = cursor.fetchone()

print(row[0])
print(row[1])

A row factory can provide more readable access:

connection.row_factory = (
    sqlite3.Row
)

cursor = connection.cursor()

cursor.execute(
    "SELECT * FROM students"
)

row = cursor.fetchone()

print(
    row["name"]
)

print(
    row["course"]
)

40. Retrieve the Generated Primary Key

cursor.execute(
    """
    INSERT INTO students
    (name, age, course)
    VALUES (?, ?, ?)
    """,
    (
        "James Anderson",
        20,
        "Data Science"
    )
)

student_id = (
    cursor.lastrowid
)

connection.commit()

print(
    student_id
)

lastrowid is useful when a newly inserted row's identifier is required for subsequent operations.

41. Read SQLite Data with pandas

SQLite becomes particularly useful for data science when combined with pandas.

import pandas as pd

query = """
SELECT *
FROM students
"""

df = pd.read_sql_query(
    query,
    connection
)

print(df)

This converts SQL query results directly into a pandas DataFrame.

42. SQL Filtering Before DataFrame Analysis

query = """
SELECT
    name,
    age,
    course
FROM students
WHERE age > ?
"""

df = pd.read_sql_query(
    query,
    connection,
    params=(20,)
)

print(df)

Filtering in SQL can reduce the amount of data transferred into Python.

43. Write a DataFrame to SQLite

df = pd.DataFrame(
    {
        "name": [
            "Alex Morgan",
            "Emma Carter"
        ],
        "age": [
            20,
            21
        ],
        "course": [
            "Data Science",
            "Computer Science"
        ]
    }
)

df.to_sql(
    "students",
    connection,
    if_exists="replace",
    index=False
)

44. Append Data to an Existing Table

df.to_sql(
    "students",
    connection,
    if_exists="append",
    index=False
)

append adds the DataFrame's rows to the existing table.

45. Inspect Tables in the Database

cursor.execute(
    """
    SELECT name
    FROM sqlite_master
    WHERE type = 'table'
    ORDER BY name
    """
)

tables = cursor.fetchall()

for table in tables:

    print(table)

46. Inspect a Table's Schema

cursor.execute(
    """
    PRAGMA table_info(students)
    """
)

columns = cursor.fetchall()

for column in columns:

    print(column)

This is useful when developing scripts that need to understand an existing SQLite database.

47. Database Indexes

An index can improve the performance of queries that frequently search or sort using particular columns.

cursor.execute(
    """
    CREATE INDEX IF NOT EXISTS
    idx_students_course
    ON students(course)
    """
)

connection.commit()

48. Mini Project — Student Database

We will build a simple Python application that stores student information in SQLite.

Database Design

students
--------------------------------
id
name
age
course
email

49. Step 1 — Create the Database

import sqlite3

connection = sqlite3.connect(
    "student_database.db"
)

cursor = connection.cursor()

50. Step 2 — Create the Students Table

cursor.execute(
    """
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER,
        course TEXT,
        email TEXT UNIQUE
    )
    """
)

connection.commit()

51. Step 3 — Insert Student Records

students = [

    (
        "Alex Morgan",
        20,
        "Computer Science",
        "alex@example.com"
    ),

    (
        "Emma Carter",
        21,
        "Data Science",
        "emma@example.com"
    ),

    (
        "Noah Wilson",
        19,
        "Information Technology",
        "noah@example.com"
    )

]

cursor.executemany(
    """
    INSERT OR IGNORE INTO students
    (name, age, course, email)
    VALUES (?, ?, ?, ?)
    """,
    students
)

connection.commit()

52. Step 4 — Display Students

cursor.execute(
    """
    SELECT
        id,
        name,
        age,
        course,
        email
    FROM students
    ORDER BY name
    """
)

rows = cursor.fetchall()

for row in rows:

    print(row)

53. Step 5 — Search Students

search_term = input(
    "Enter student name: "
)

cursor.execute(
    """
    SELECT *
    FROM students
    WHERE name LIKE ?
    """,
    (
        f"%{search_term}%",
    )
)

rows = cursor.fetchall()

for row in rows:

    print(row)

The % wildcard allows the search term to match part of the name.

54. Step 6 — Update Student Information

student_id = 1

new_course = (
    "Artificial Intelligence"
)

cursor.execute(
    """
    UPDATE students
    SET course = ?
    WHERE id = ?
    """,
    (
        new_course,
        student_id
    )
)

connection.commit()

55. Step 7 — Delete a Student

student_id = 3

cursor.execute(
    """
    DELETE FROM students
    WHERE id = ?
    """,
    (student_id,)
)

connection.commit()

56. Complete Mini Project

import sqlite3


DATABASE = (
    "student_database.db"
)


def create_database():

    connection = (
        sqlite3.connect(
            DATABASE
        )
    )

    cursor = (
        connection.cursor()
    )

    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            age INTEGER,
            course TEXT,
            email TEXT UNIQUE
        )
        """
    )

    connection.commit()

    connection.close()


def add_student(
    name,
    age,
    course,
    email
):

    with sqlite3.connect(
        DATABASE
    ) as connection:

        connection.execute(
            """
            INSERT INTO students
            (name, age, course, email)
            VALUES (?, ?, ?, ?)
            """,
            (
                name,
                age,
                course,
                email
            )
        )


def get_students():

    with sqlite3.connect(
        DATABASE
    ) as connection:

        cursor = (
            connection.execute(
                """
                SELECT *
                FROM students
                ORDER BY name
                """
            )
        )

        return cursor.fetchall()


def update_course(
    student_id,
    course
):

    with sqlite3.connect(
        DATABASE
    ) as connection:

        connection.execute(
            """
            UPDATE students
            SET course = ?
            WHERE id = ?
            """,
            (
                course,
                student_id
            )
        )


def delete_student(
    student_id
):

    with sqlite3.connect(
        DATABASE
    ) as connection:

        connection.execute(
            """
            DELETE FROM students
            WHERE id = ?
            """,
            (student_id,)
        )


create_database()

add_student(
    "Alex Morgan",
    20,
    "Computer Science",
    "alex@example.com"
)

add_student(
    "Emma Carter",
    21,
    "Data Science",
    "emma@example.com"
)

update_course(
    1,
    "Artificial Intelligence"
)

for student in get_students():

    print(student)

57. CRUD Operations

The four fundamental database operations are commonly represented by the acronym CRUD.

58. Essential SQL Commands

59. SQLite vs Excel for Automation

Both Excel and SQLite are useful, but they solve different problems.

60. SQLite in a Data Science Workflow

SQLite Database
       ↓
SQL Query
       ↓
pandas DataFrame
       ↓
Data Cleaning
       ↓
EDA
       ↓
Visualization
       ↓
Report

This workflow connects database skills with the NumPy, pandas, visualization, and EDA concepts learned earlier.

61. SQLite in an Automation Workflow

External Data
      ↓
Python Automation
      ↓
Validation
      ↓
SQLite Database
      ↓
SQL Analysis
      ↓
pandas
      ↓
Excel / PDF / Email

SQLite therefore provides a persistent storage layer between data collection and reporting.

62. SQLite Best Practices

1. Use parameterized queries.
2. Commit changes deliberately.
3. Close connections when finished.
4. Use transactions for related operations.
5. Validate external input.
6. Define appropriate constraints.
7. Use meaningful table and column names.
8. Create indexes for appropriate frequently queried columns.
9. Back up important database files.
10. Avoid storing unnecessary sensitive information.
11. Separate database logic from application logic.
12. Test database operations independently.

63. Common Mistakes

64. Python SQLite Interview Questions

65. Examination Questions — MCQs

66. Practical Examination Tasks

67. Mini Project — Automated Data Store

Extend the automation concepts from the previous module.

Public Data
     ↓
Python requests
     ↓
Validation
     ↓
SQLite
     ↓
pandas
     ↓
Excel
     ↓
PDF
     ↓
Email

SQLite acts as the persistent database layer between the data collection process and the reporting system.

68. Coding Challenge

Build a Product Inventory Database with the following columns:

products
--------------------------------
id
name
category
price
quantity
created_at

Requirements

1. Create the SQLite database.
2. Create the products table.
3. Insert at least ten products.
4. Display all products.
5. Search products by category.
6. Find products costing more than a specified amount.
7. Update product quantities.
8. Delete a product.
9. Calculate total inventory value.
10. Load the data into pandas.
11. Export the DataFrame to Excel.

69. Real-World Interview Scenario

Expected Approach

1. Use SQLite or an appropriate database for persistent storage.
2. Design normalized tables where appropriate.
3. Add primary keys and relevant constraints.
4. Insert validated records into the database.
5. Use SQL queries for filtering and aggregation.
6. Load selected results into pandas for analysis.
7. Generate Excel/PDF reports only when required.
8. Schedule the collection process.
9. Maintain backups and logs.

70. Python SQLite Quick Reference

71. Self-Assessment Checklist

You should now be able to:

• ☐ Explain what SQLite is.
• ☐ Explain the difference between SQLite and a server-based database.
• ☐ Create a SQLite database with Python.
• ☐ Create tables.
• ☐ Define primary keys.
• ☐ Use constraints.
• ☐ Insert records.
• ☐ Retrieve records.
• ☐ Filter and sort records.
• ☐ Update records safely.
• ☐ Delete records safely.
• ☐ Use parameterized queries.
• ☐ Explain SQL injection.
• ☐ Use transactions.
• ☐ Use commit() and rollback().
• ☐ Work with multiple related tables.
• ☐ Use foreign keys.
• ☐ Perform SQL joins.
• ☐ Use aggregate functions.
• ☐ Use SQLite with pandas.
• ☐ Build a database-driven Python application.