🔷 Introduction

Most real-world applications need to store and manage data permanently. Instead of using files, developers rely on databases to organize, query, and scale data efficiently.

In this lesson, you will learn how to use SQLite, a lightweight database built into Python, to perform basic database operations.

You will learn:

✔ What a database is
✔ How to use SQLite with Python
✔ CRUD operations (Create, Read, Update, Delete)
✔ Executing SQL queries
✔ Fetching and displaying data

🟩 1. What is a Database?

A database is a structured collection of data.

Example:

SQLite stores data in a file (.db).

🟩 2. Using SQLite in Python

Python includes SQLite via the sqlite3 module.

🔹 Import and connect

import sqlite3

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

✔ conn → connection to database
✔ cursor → executes SQL commands

🟩 3. Creating a Table

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

🟩 4. Insert Data (CREATE)

cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("Ali", 22))
conn.commit()

✔ Use ? to prevent SQL injection

🟩 5. Read Data (SELECT)

cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()

for row in rows:
    print(row)

🟩 6. Update Data (UPDATE)

cursor.execute("UPDATE students SET age = ? WHERE name = ?", (23, "Ali"))
conn.commit()

🟩 7. Delete Data (DELETE)

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

🟩 8. Closing Connection

conn.close()

✔ Always close the database connection

🟩 9. Full Example

import sqlite3

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

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

cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("Sara", 25))

cursor.execute("SELECT * FROM students")
for row in cursor.fetchall():
    print(row)

conn.commit()
conn.close()

🟧 10. Exercises (Hidden Solutions)

Exercise 1 — Create a table

cursor.execute("""
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT
)
""")

Exercise 2 — Insert data

cursor.execute("INSERT INTO users (username) VALUES (?)", ("Ali",))

Exercise 3 — Select data

cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

Exercise 4 — Update data

cursor.execute("UPDATE users SET username = ? WHERE id = ?", ("Sara", 1))

Exercise 5 — Delete data

cursor.execute("DELETE FROM users WHERE id = ?", (1,))

🟦 Conclusion

In this lesson, you learned how to:

✔ Connect Python to a database
✔ Create tables
✔ Insert, read, update, and delete data
✔ Execute SQL queries
✔ Manage persistent data

These skills are essential for:

• Web applications (Django, Flask)
• APIs
• Data storage systems
• Backend development

🚀 Mini Project — Student Manager (SQLite CRUD App)

Project idea

Build a small Python application that manages students using SQLite.
The app should allow the user to:

• add a student
• view all students
• update a student
• delete a student

This is a classic CRUD application:

• C → Create
• R → Read
• U → Update
• D → Delete

🧩 What learners will practice

With this project, learners will practice:

• connecting to SQLite with sqlite3
• creating a table
• inserting records
• selecting and displaying records
• updating data
• deleting data
• using SQL safely with placeholders (?)
• organizing a menu-driven Python program

📁 Simple Project Structure

student_manager/
│
├── main.py
└── students.db

✅ Full Solution

import sqlite3


def connect_db():
    conn = sqlite3.connect("students.db")
    return conn


def create_table():
    conn = connect_db()
    cursor = conn.cursor()

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

    conn.commit()
    conn.close()


def add_student():
    name = input("Enter student name: ")
    age = input("Enter student age: ")

    try:
        age = int(age)
    except ValueError:
        print("Age must be a number.")
        return

    conn = connect_db()
    cursor = conn.cursor()

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

    conn.commit()
    conn.close()
    print("Student added successfully.")


def view_students():
    conn = connect_db()
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM students")
    students = cursor.fetchall()

    conn.close()

    if not students:
        print("No students found.")
        return

    print("\n--- Student List ---")
    for student in students:
        print(f"ID: {student[0]}, Name: {student[1]}, Age: {student[2]}")


def update_student():
    student_id = input("Enter student ID to update: ")
    new_name = input("Enter new name: ")
    new_age = input("Enter new age: ")

    try:
        student_id = int(student_id)
        new_age = int(new_age)
    except ValueError:
        print("ID and age must be numbers.")
        return

    conn = connect_db()
    cursor = conn.cursor()

    cursor.execute(
        "UPDATE students SET name = ?, age = ? WHERE id = ?",
        (new_name, new_age, student_id)
    )

    conn.commit()

    if cursor.rowcount == 0:
        print("No student found with that ID.")
    else:
        print("Student updated successfully.")

    conn.close()


def delete_student():
    student_id = input("Enter student ID to delete: ")

    try:
        student_id = int(student_id)
    except ValueError:
        print("ID must be a number.")
        return

    conn = connect_db()
    cursor = conn.cursor()

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

    if cursor.rowcount == 0:
        print("No student found with that ID.")
    else:
        print("Student deleted successfully.")

    conn.close()


def menu():
    print("\n=== Student Manager ===")
    print("1. Add student")
    print("2. View students")
    print("3. Update student")
    print("4. Delete student")
    print("5. Exit")


def main():
    create_table()

    while True:
        menu()
        choice = input("Choose an option: ")

        if choice == "1":
            add_student()
        elif choice == "2":
            view_students()
        elif choice == "3":
            update_student()
        elif choice == "4":
            delete_student()
        elif choice == "5":
            print("Goodbye.")
            break
        else:
            print("Invalid option. Please choose from 1 to 5.")


if __name__ == "__main__":
    main()

🔍 Explanation of the project

1. Database connection

def connect_db():
    conn = sqlite3.connect("students.db")
    return conn

This function opens a connection to the SQLite database.

If the file does not exist, SQLite creates it automatically.

2. Table creation

CREATE TABLE IF NOT EXISTS students

This ensures the table exists before the program starts working with data.

Each student has:

• id → unique identifier
• name → student name
• age → student age

3. Add student

The app asks the user for a name and age, then inserts the student into the database using:

INSERT INTO students (name, age) VALUES (?, ?)

Using ? placeholders is safer than building SQL strings manually.

4. View students

The program fetches all students:

SELECT * FROM students

Then prints them in a readable format.

5. Update student

The user enters a student ID, then the new values.
The app updates the row using:

UPDATE students SET name = ?, age = ? WHERE id = ?

6. Delete student

The user provides the student ID, and the app deletes the corresponding row:

DELETE FROM students WHERE id = ?

▶️ Example usage

=== Student Manager ===
1. Add student
2. View students
3. Update student
4. Delete student
5. Exit
Choose an option: 1
Enter student name: Sara
Enter student age: 24
Student added successfully.

Then:

Choose an option: 2

--- Student List ---
ID: 1, Name: Sara, Age: 24

🧠 Skills reinforced

This project helps learners reinforce:

• SQL basics
• Python + SQLite integration
• CRUD workflow
• user input validation
• persistent storage
• menu-driven application design

🛠 Suggested improvements

Your challenge is to extend the project by adding:

1. search student by name
2. sort students by age
3. prevent duplicate names
4. export students to CSV
5. split the app into modules
6. create a GUI version with Tkinter
7. add email field
8. validate age range