PYTHON INTERACTION WITH SQLITE — PRACTICAL
ASSIGNMENT-1 (Solved)
Generated: 2025-08-24 09:29
1) Write a Python program to create SQLite database connection to a
database that resides in the memory.
Explanation: In SQLite, the special database path ':memory:' creates a temporary database that lives only for
the duration of the connection. It's very fast and useful for testing because nothing is written to disk.
Code:
import sqlite3
# Connect to an in-memory SQLite database
conn = sqlite3.connect(':memory:')
# Simple smoke test: create a table and query SQLite version
cur = conn.cursor()
cur.execute("CREATE TABLE demo(id INTEGER PRIMARY KEY, note TEXT)")
cur.execute("SELECT sqlite_version()")
version = cur.fetchone()[0]
print("Connected to in-memory DB. SQLite version:", version)
print("Tables:", [r[0] for r in cur.execute("SELECT name FROM sqlite_master WHERE type='table'")])
conn.close()
Output:
Connected to in-memory DB. SQLite version: 3.40.1
Tables: ['demo']
2) Write a Python program to connect a database and create a SQLite table
within the database.
Explanation: Connecting to a file-backed SQLite database is as simple as passing a filesystem path. If the file
doesn't exist, SQLite creates it. The example creates a table 'students'.
Code:
import sqlite3
conn = sqlite3.connect(r"/mnt/data/college.db")
cur = conn.cursor()
cur.execute("""
CREATE TABLE IF NOT EXISTS students(
roll_no INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
grade TEXT
)""")
conn.commit()
print("Database is at:", r"/mnt/data/college.db")
print("Tables now:", [r[0] for r in cur.execute("SELECT name FROM sqlite_master WHERE type='table'")])
conn.close()
Output:
Database is at: /mnt/data/college.db
Tables now: ['students']
3) Write a Python program to list the tables of a given SQLite database file.
Explanation: Query the 'sqlite_master' metadata table to list all user tables in the database.
Code:
import sqlite3
conn = sqlite3.connect(r"/mnt/data/college.db")
cur = conn.cursor()
tables = [r[0] for r in cur.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")]
print("Tables:", tables)
conn.close()
Output:
Tables: ['students']
4) Write a Python program to create a table and insert some records in that
table. Finally select all rows from the table and display the records.
Explanation: We'll add another table 'courses', insert three rows using parameterized SQL (which prevents SQL
injection), and then fetch all rows.
Code:
import sqlite3
conn = sqlite3.connect(r"/mnt/data/college.db")
cur = conn.cursor()
cur.execute("""
CREATE TABLE IF NOT EXISTS courses(
code TEXT PRIMARY KEY,
title TEXT NOT NULL,
credits INTEGER NOT NULL
)""")
rows_to_insert = [
("CS101", "Intro to CS", 4),
("DB201", "Databases", 3),
("PY111", "Python Basics", 2)
]
cur.executemany("INSERT OR REPLACE INTO courses(code, title, credits) VALUES(?,?,?)", rows_to_insert)
conn.commit()
all_rows = list(cur.execute("SELECT * FROM courses ORDER BY code"))
print("All rows in courses:")
for r in all_rows:
print(r)
conn.close()
Output:
All rows in courses:
('CS101', 'Intro to CS', 4)
('DB201', 'Databases', 3)
('PY111', 'Python Basics', 2)
5) Write a Python program to insert values to a table from user input.
Explanation: In scripts we usually wrap input as a function argument; here we'll simulate user input by a list. We
use placeholders (?) and executemany to safely insert.
Code:
import sqlite3
# Simulated 'user inputs'
new_students = [
(3551, "Gaurav Sharma", 20, "A"),
(3552, "Anita Patel", 19, "B"),
(3553, "Rahul Mehta", 21, "A")
]
conn = sqlite3.connect(r"/mnt/data/college.db")
cur = conn.cursor()
cur.executemany("INSERT OR REPLACE INTO students(roll_no, name, age, grade) VALUES(?,?,?,?)", new_students)
conn.commit()
print("Inserted rows:", len(new_students))
for r in cur.execute("SELECT * FROM students ORDER BY roll_no"):
print(r)
conn.close()
Output:
Inserted rows: 3
(3551, 'Gaurav Sharma', 20, 'A')
(3552, 'Anita Patel', 19, 'B')
(3553, 'Rahul Mehta', 21, 'A')
6) Write a Python program to count the number of rows of a given SQLite
table.
Explanation: Use COUNT(*) to get the number of records in a table. We'll count rows in 'students'.
Code:
import sqlite3
conn = sqlite3.connect(r"/mnt/data/college.db")
cur = conn.cursor()
cur.execute("SELECT COUNT(*) FROM students")
count = cur.fetchone()[0]
print("Row count in students:", count)
conn.close()
Output:
Row count in students: 3
7) Write a Python program to update a specific column value of a given
table and select all rows before and after updating the said table.
Explanation: We'll change the grade of roll_no=3552 from 'B' to 'A' and print rows before and after.
Code:
import sqlite3
conn = sqlite3.connect(r"/mnt/data/college.db")
cur = conn.cursor()
print("Before update:")
for r in cur.execute("SELECT * FROM students ORDER BY roll_no"):
print(r)
cur.execute("UPDATE students SET grade=? WHERE roll_no=?", ("A", 3552))
conn.commit()
print("\nAfter update:")
for r in cur.execute("SELECT * FROM students ORDER BY roll_no"):
print(r)
conn.close()
Output:
Before update:
(3551, 'Gaurav Sharma', 20, 'A')
(3552, 'Anita Patel', 19, 'B')
(3553, 'Rahul Mehta', 21, 'A')
After update:
(3551, 'Gaurav Sharma', 20, 'A')
(3552, 'Anita Patel', 19, 'A')
(3553, 'Rahul Mehta', 21, 'A')
8) Write a Python program to update all the values of a specific column of a
given SQLite table.
Explanation: We'll give everyone a temporary grade 'P' to demonstrate updating an entire column.
Code:
import sqlite3
conn = sqlite3.connect(r"/mnt/data/college.db")
cur = conn.cursor()
cur.execute("UPDATE students SET grade='P'")
conn.commit()
for r in cur.execute("SELECT * FROM students ORDER BY roll_no"):
print(r)
conn.close()
Output:
(3551, 'Gaurav Sharma', 20, 'P')
(3552, 'Anita Patel', 19, 'P')
(3553, 'Rahul Mehta', 21, 'P')
9) Write a Python program to delete a specific row from a given SQLite
table.
Explanation: We'll delete the student with roll_no=3553 and show the remaining rows.
Code:
import sqlite3
conn = sqlite3.connect(r"/mnt/data/college.db")
cur = conn.cursor()
cur.execute("DELETE FROM students WHERE roll_no=?", (3553,))
conn.commit()
for r in cur.execute("SELECT * FROM students ORDER BY roll_no"):
print(r)
conn.close()
Output:
(3551, 'Gaurav Sharma', 20, 'P')
(3552, 'Anita Patel', 19, 'P')
10) Write a Python program to alter a given SQLite table.
Explanation: SQLite supports a subset of ALTER TABLE. We can add a new column easily. Below we add a
NOT NULL 'dept' column with a default value, then update a few rows.
Code:
import sqlite3
conn = sqlite3.connect(r"/mnt/data/college.db")
cur = conn.cursor()
# Add new column 'dept' with default value 'BCA'
cur.execute("ALTER TABLE students ADD COLUMN dept TEXT NOT NULL DEFAULT 'BCA'")
conn.commit()
# Update two rows to show variety
cur.execute("UPDATE students SET dept=? WHERE roll_no=?", ("CS", 3551))
cur.execute("UPDATE students SET dept=? WHERE roll_no=?", ("IT", 3552))
conn.commit()
for r in cur.execute("SELECT * FROM students ORDER BY roll_no"):
print(r)
conn.close()
Output:
(3551, 'Gaurav Sharma', 20, 'P', 'CS')
(3552, 'Anita Patel', 19, 'P', 'IT')
