Topic: Python with SQLite Database

Topics Covered

What is a Database?
What is SQLite?
Difference Between File Handling and Database
Basic SQL Commands
Python with SQLite
Create Table
Insert Data
Fetch Data
Update Data
Delete Data
Complete SQLite CRUD Program
Practice Questions
Day 4 Summary

1. What is a Database?

A database is used to store data in an organized way.

In Day 3, we stored data using files.

Example:

ram.txt
sita.txt
hari.txt

But file handling becomes difficult when we have lots of data.

Example:

If we want to store student details:

Name: Ram
Age: 18
Address: Kathmandu

Using files is possible, but searching, updating, and deleting data becomes hard.

So we use a database.

A database stores data in tables.

Example:

id	name	age	address
1	Ram	18	Kathmandu
2	Sita	19	Pokhara
3	Hari	20	Biratnagar

2. What is SQLite?

SQLite is a small and simple database.

It stores data inside a single file.

Example:

school.db

SQLite is good for learning because:

It is simple.
It does not need server setup.
It works directly with Python.
Python already has SQLite built in.

We do not need to install anything extra.

We only need to import:

import sqlite3

3. File Handling vs Database

File Handling

In file handling, we store data in text files.

Example:

ram.txt

Inside the file:

This is okay for small programs.

But it is not good for storing many users or many student records.

Database

In a database, data is stored in tables.

Example:

id	username	password
1	ram	12345
2	sita	abc123

Database makes it easier to:

Add data
View data
Search data
Update data
Delete data

These operations are called CRUD.

4. What is CRUD?

CRUD means:

Letter	Meaning	Example
C	Create	Add new student
R	Read	View students
U	Update	Edit student data
D	Delete	Remove student

In database:

CRUD	SQL Command
Create	`INSERT`
Read	`SELECT`
Update	`UPDATE`
Delete	`DELETE`

5. What is SQL?

SQL means Structured Query Language.

SQL is used to talk with the database.

We use SQL commands to create tables, insert data, read data, update data, and delete data.

Example:

SELECT * FROM students;

This means:

Show all data from the students table.

6. Basic SQL Commands

1. CREATE TABLE

Used to create a new table.

Example:

CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    age INTEGER,
    address TEXT
);

Explanation:

id INTEGER PRIMARY KEY AUTOINCREMENT

This creates an ID that increases automatically.

name TEXT

This stores text data.

age INTEGER

This stores number data.

address TEXT

This stores text data.

2. INSERT

Used to add data into a table.

Example:

INSERT INTO students (name, age, address)
VALUES ('Ram', 18, 'Kathmandu');

This adds one student into the students table.

3. SELECT

Used to read data from a table.

Example:

SELECT * FROM students;

This shows all students.

Example:

SELECT name, age FROM students;

This shows only name and age.

4. WHERE

Used to filter data.

Example:

SELECT * FROM students
WHERE id = 1;

This shows only the student whose ID is 1.

5. UPDATE

Used to change existing data.

Example:

UPDATE students
SET age = 20
WHERE id = 1;

This changes the age of the student whose ID is 1.

Important:

Always use WHERE while updating.

Without WHERE, all rows may be updated.

6. DELETE

Used to remove data.

Example:

DELETE FROM students
WHERE id = 1;

This deletes the student whose ID is 1.

Important:

Always use WHERE while deleting.

Without WHERE, all data may be deleted.

7. Python with SQLite

To use SQLite in Python, we use:

import sqlite3

Basic structure:

import sqlite3

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

cursor = connection.cursor()

cursor.execute("SQL command here")

connection.commit()

connection.close()

8. Meaning of Important Lines

`sqlite3.connect()`

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

This connects Python to the SQLite database.

If school.db does not exist, Python creates it automatically.

`cursor`

cursor = connection.cursor()

A cursor is a helper that runs SQL commands.

Simple explanation:

Database is like a notebook.

Connection opens the notebook.

Cursor is like a pen that writes or reads inside the notebook.

`execute()`

cursor.execute("SELECT * FROM students")

This runs SQL commands.

`commit()`

connection.commit()

This saves changes.

We use commit() after:

Create table
Insert data
Update data
Delete data

For SELECT, commit is not necessary.

`close()`

connection.close()

This closes the database connection.

9. Create Table Using Python

import sqlite3

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

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

connection.commit()
connection.close()

print("Table created successfully")

Explanation:

CREATE TABLE IF NOT EXISTS students

This creates the students table only if it does not already exist.

10. Insert Data Using Python

import sqlite3

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

cursor.execute("""
INSERT INTO students (name, age, address)
VALUES (?, ?, ?)
""", ("Ram", 18, "Kathmandu"))

connection.commit()
connection.close()

print("Student inserted successfully")

Explanation:

VALUES (?, ?, ?)

The question marks are placeholders.

The actual values are passed here:

("Ram", 18, "Kathmandu")

This is safer than writing values directly inside SQL.

11. Fetch Data Using Python

import sqlite3

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

cursor.execute("SELECT * FROM students")

students = cursor.fetchall()

for student in students:
    print(student)

connection.close()

Output example:

(1, 'Ram', 18, 'Kathmandu')
(2, 'Sita', 19, 'Pokhara')

12. `fetchone()` vs `fetchall()`

`fetchone()`

Gets only one row.

student = cursor.fetchone()

Example output:

(1, 'Ram', 18, 'Kathmandu')

`fetchall()`

Gets all rows.

students = cursor.fetchall()

Example output:

[
    (1, 'Ram', 18, 'Kathmandu'),
    (2, 'Sita', 19, 'Pokhara')
]

13. Update Data Using Python

import sqlite3

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

cursor.execute("""
UPDATE students
SET name = ?, age = ?, address = ?
WHERE id = ?
""", ("Ram Sharma", 20, "Biratnagar", 1))

connection.commit()
connection.close()

print("Student updated successfully")

Explanation:

This updates the student whose ID is 1.

14. Delete Data Using Python

import sqlite3

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

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

connection.commit()
connection.close()

print("Student deleted successfully")

Important:

(1,)

This is a single-value tuple.

Do not write:

(1)

Because (1) is just a number, not a tuple.

15. Complete SQLite CRUD Program

Save the file as:

student_database.py

import sqlite3

def connect_db():
    connection = sqlite3.connect("school.db")
    return connection

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

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

    connection.commit()
    connection.close()

    print("Table created successfully")

def insert_student():
    name = input("Enter student name: ")
    age = int(input("Enter student age: "))
    address = input("Enter student address: ")

    connection = connect_db()
    cursor = connection.cursor()

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

    connection.commit()
    connection.close()

    print("Student inserted successfully")

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

    cursor.execute("SELECT * FROM students")

    students = cursor.fetchall()

    connection.close()

    if len(students) == 0:
        print("No students found")
    else:
        print("\n--- Student List ---")

        for student in students:
            print("ID:", student[0])
            print("Name:", student[1])
            print("Age:", student[2])
            print("Address:", student[3])
            print("--------------------")

def search_student():
    student_id = int(input("Enter student ID: "))

    connection = connect_db()
    cursor = connection.cursor()

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

    student = cursor.fetchone()

    connection.close()

    if student:
        print("ID:", student[0])
        print("Name:", student[1])
        print("Age:", student[2])
        print("Address:", student[3])
    else:
        print("Student not found")

def update_student():
    student_id = int(input("Enter student ID to update: "))

    new_name = input("Enter new name: ")
    new_age = int(input("Enter new age: "))
    new_address = input("Enter new address: ")

    connection = connect_db()
    cursor = connection.cursor()

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

    connection.commit()

    if cursor.rowcount == 0:
        print("Student not found")
    else:
        print("Student updated successfully")

    connection.close()

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

    connection = connect_db()
    cursor = connection.cursor()

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

    connection.commit()

    if cursor.rowcount == 0:
        print("Student not found")
    else:
        print("Student deleted successfully")

    connection.close()

def menu():
    create_table()

    while True:
        print("\n===== Student Management System =====")
        print("1. Insert Student")
        print("2. View Students")
        print("3. Search Student")
        print("4. Update Student")
        print("5. Delete Student")
        print("6. Exit")

        choice = input("Enter your choice: ")

        if choice == "1":
            insert_student()

        elif choice == "2":
            view_students()

        elif choice == "3":
            search_student()

        elif choice == "4":
            update_student()

        elif choice == "5":
            delete_student()

        elif choice == "6":
            print("Program closed")
            break

        else:
            print("Invalid choice. Please try again")

menu()

16. Explanation of Complete Program

`connect_db()`

def connect_db():
    connection = sqlite3.connect("school.db")
    return connection

This function connects to the database.

We created a separate function so we do not need to write this again and again:

sqlite3.connect("school.db")

`create_table()`

This function creates the students table.

CREATE TABLE IF NOT EXISTS students

If the table already exists, it does not create another table.

`insert_student()`

This function adds a new student.

It takes input from user:

name = input("Enter student name: ")
age = int(input("Enter student age: "))
address = input("Enter student address: ")

Then inserts the data:

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

`view_students()`

This function shows all students.

SELECT * FROM students

This means:

Select all columns from students table.

`search_student()`

This function searches one student by ID.

SELECT * FROM students
WHERE id = ?

This means:

Find the student whose ID matches the given ID.

`update_student()`

This function updates student details.

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

This means:

Change name, age, and address of the student whose ID matches.

`delete_student()`

This function deletes a student.

DELETE FROM students
WHERE id = ?

This means:

Delete the student whose ID matches.

17. Important SQL Keywords

Keyword	Meaning
`CREATE TABLE`	Creates a table
`INSERT INTO`	Adds data
`SELECT`	Reads data
`UPDATE`	Changes data
`DELETE`	Removes data
`WHERE`	Filters data
`ORDER BY`	Sorts data
`LIMIT`	Limits result

18. More SQL Examples

Show all students

SELECT * FROM students;

Show only name and age

SELECT name, age FROM students;

Search student by address

SELECT * FROM students
WHERE address = 'Kathmandu';

Sort students by age

SELECT * FROM students
ORDER BY age;

Show only first 5 students

SELECT * FROM students
LIMIT 5;

Delete all students carefully

DELETE FROM students;

This deletes all records, so use carefully.

19. Why We Use `?` in Python SQLite

We write:

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

This is better than:

cursor.execute(f"""
INSERT INTO students (name, age, address)
VALUES ('{name}', {age}, '{address}')
""")

Why?

Because ? is safer.

It prevents many SQL errors and also helps protect from SQL injection.

For students, explain simply:

The question mark is an empty space. Python safely fills that empty space with the value we provide.

20. Common Mistakes

Mistake 1: Forgetting `commit()`

Wrong:

cursor.execute("INSERT INTO students (name, age, address) VALUES (?, ?, ?)", ("Ram", 18, "Kathmandu"))
connection.close()

Correct:

cursor.execute("INSERT INTO students (name, age, address) VALUES (?, ?, ?)", ("Ram", 18, "Kathmandu"))
connection.commit()
connection.close()

Without commit(), data may not be saved.

Mistake 2: Forgetting `WHERE` in UPDATE

Wrong:

UPDATE students
SET age = 20;

This changes age for all students.

Correct:

UPDATE students
SET age = 20
WHERE id = 1;

Mistake 3: Forgetting `WHERE` in DELETE

Wrong:

DELETE FROM students;

This deletes all students.

Correct:

DELETE FROM students
WHERE id = 1;

Mistake 4: Wrong Single Tuple

Wrong:

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

Correct:

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

For one value, we need comma:

(student_id,)

21. Day 4 Practice Questions

SQL Practice

Create a table named products.
Add columns: id, name, price, quantity.
Insert three products.
Show all products.
Search product by ID.
Update product price.
Delete one product.

Python SQLite Practice

Create a product management system.

Requirements:

Create product table.
Add product.
View all products.
Search product.
Update product.
Delete product.
Use menu system.

Product table:

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    quantity INTEGER NOT NULL
);

22. Product Management Practice Code Structure

Students should create functions like this:

def create_table():
    pass

def add_product():
    pass

def view_products():
    pass

def search_product():
    pass

def update_product():
    pass

def delete_product():
    pass

def menu():
    pass

This helps students practice functions and SQLite together.

23. Class Explanation Flow

You can teach in this order:

First revise file handling from Day 3.
Explain the problem with file handling.
Introduce database as a better storage system.
Explain table using Excel example.
Teach basic SQL commands.
Show SQLite connection in Python.
Explain cursor.
Create table.
Insert data.
Fetch data.
Update data.
Delete data.
Build complete CRUD app.
Give product management as practice.

24. Day 4 Final Summary

Today we learned:

Database

A database stores data in an organized way.

SQLite

SQLite is a simple database that stores data in a .db file.

SQL

SQL is the language used to talk with a database.

Cursor

Cursor is used to run SQL commands from Python.

CRUD

CRUD means:

Create
Read
Update
Delete

Important SQL Commands

CREATE TABLE
INSERT INTO
SELECT
UPDATE
DELETE
WHERE

Important Python SQLite Lines

import sqlite3

Used to use SQLite in Python.

sqlite3.connect("school.db")

Used to connect to database.

connection.cursor()

Used to create cursor.

cursor.execute()

Used to run SQL command.

connection.commit()

Used to save changes.

connection.close()

Used to close database.

Final Memory for Students

File handling stores data in files.

Database stores data in tables.

SQLite is a simple database.

SQL is used to talk with database.

Cursor runs SQL commands.

commit() saves changes.

CRUD means create, read, update, delete.

Topic: Python with SQLite Database

Topics Covered

1. What is a Database?

2. What is SQLite?

3. File Handling vs Database

File Handling

Database

4. What is CRUD?

5. What is SQL?

6. Basic SQL Commands

1. CREATE TABLE

2. INSERT

3. SELECT

4. WHERE

5. UPDATE

6. DELETE

7. Python with SQLite

8. Meaning of Important Lines

sqlite3.connect()

cursor

execute()

commit()

close()

9. Create Table Using Python

10. Insert Data Using Python

11. Fetch Data Using Python

12. fetchone() vs fetchall()

fetchone()

fetchall()

13. Update Data Using Python

14. Delete Data Using Python

15. Complete SQLite CRUD Program

16. Explanation of Complete Program

connect_db()

create_table()

insert_student()

view_students()

search_student()

update_student()

delete_student()

17. Important SQL Keywords

18. More SQL Examples

Show all students

Show only name and age

Search student by address

Sort students by age

Show only first 5 students

Delete all students carefully

19. Why We Use ? in Python SQLite

20. Common Mistakes

Mistake 1: Forgetting commit()

Mistake 2: Forgetting WHERE in UPDATE

Mistake 3: Forgetting WHERE in DELETE

Mistake 4: Wrong Single Tuple

21. Day 4 Practice Questions

SQL Practice

Python SQLite Practice

22. Product Management Practice Code Structure

23. Class Explanation Flow

24. Day 4 Final Summary

Database

SQLite

SQL

Cursor

CRUD

Important SQL Commands

Important Python SQLite Lines

Final Memory for Students

Comments

More from this blog

Arduino Height Measurement System

Arduino DHT11 Temperature & Humidity Monitoring System

Arduino Height Measurement System

Django Workshop Day 2

Day 1 Django Note

`sqlite3.connect()`

`cursor`

`execute()`

`commit()`

`close()`

12. `fetchone()` vs `fetchall()`

`fetchone()`

`fetchall()`

`connect_db()`

`create_table()`

`insert_student()`

`view_students()`

`search_student()`

`update_student()`

`delete_student()`

19. Why We Use `?` in Python SQLite

Mistake 1: Forgetting `commit()`

Mistake 2: Forgetting `WHERE` in UPDATE

Mistake 3: Forgetting `WHERE` in DELETE