Database Programming with Python

# Database Programming with Python

Welcome to Chapter 25! Databases store persistent data. Python's built-in sqlite3 module lets you work with databases without installing anything extra.

---

1. Learning Objectives #

• Understand relational databases and SQL basics.

• Use SQLite with Python's sqlite3 module.

• Perform CRUD operations.

• Use parameterized queries to prevent SQL injection.

• Build a student management system.

---

2. SQLite Basics #

```python id="py25_ex1" import sqlite3

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

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

conn.commit() print("✅ Table created!") conn.close()

---

## 3. CRUD Operations

### Create (INSERT)

python id="py25_ex2" conn = sqlite3.connect("school.db") cursor = conn.cursor()

# Insert one record cursor.execute( "INSERT INTO students (name, age, grade, gpa) VALUES (?, ?, ?, ?)", ("Alice", 20, "A", 3.9) )

# Insert multiple records students = [ ("Bob", 22, "B", 3.5), ("Charlie", 21, "A", 3.8), ("Diana", 23, "C", 3.0) ] cursor.executemany( "INSERT INTO students (name, age, grade, gpa) VALUES (?, ?, ?, ?)", students )

conn.commit() print(f"✅ {cursor.rowcount} students added!") conn.close()

### Read (SELECT)

python id="py25ex3" conn = sqlite3.connect("school.db") cursor = conn.cursor()

# Fetch all cursor.execute("SELECT * FROM students") allstudents = cursor.fetchall() for s in allstudents: print(s)

# Fetch with conditions cursor.execute("SELECT name, gpa FROM students WHERE gpa > ? ORDER BY gpa DESC", (3.5,)) topstudents = cursor.fetchall() for name, gpa in top_students: print(f" {name}: {gpa}")

conn.close()

### Update

python id="py25_ex4" conn = sqlite3.connect("school.db") cursor = conn.cursor()

cursor.execute( "UPDATE students SET gpa = ? WHERE name = ?", (3.95, "Alice") ) conn.commit() print(f"✅ Updated {cursor.rowcount} record(s)") conn.close()

### Delete

python id="py25_ex5" conn = sqlite3.connect("school.db") cursor = conn.cursor()

cursor.execute("DELETE FROM students WHERE name = ?", ("Diana",)) conn.commit() print(f"🗑️ Deleted {cursor.rowcount} record(s)") conn.close()

---

## 4. Using Context Manager

python id="py25_ex6" import sqlite3

# Automatically commits on success, rolls back on error with sqlite3.connect("school.db") as conn: cursor = conn.cursor() cursor.execute("SELECT * FROM students") for row in cursor.fetchall(): print(row) # Connection auto-closed

---

## 5. Row Factory for Dict-like Access

python id="py25ex7" conn = sqlite3.connect("school.db") conn.rowfactory = sqlite3.Row # Returns dict-like rows

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

for row in cursor.fetchall(): print(f"Name: {row['name']}, GPA: {row['gpa']}")

conn.close()

---

## 6. Mini Project: Student Management System

python id="py25project" import sqlite3

DBNAME = "studentmgmt.db"

def initdb(): with sqlite3.connect(DBNAME) as conn: conn.execute(""" CREATE TABLE IF NOT EXISTS students ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER, course TEXT, gpa REAL ) """)

def addstudent(): name = input(" Name: ") age = int(input(" Age: ")) course = input(" Course: ") gpa = float(input(" GPA: ")) with sqlite3.connect(DBNAME) as conn: conn.execute( "INSERT INTO students (name, age, course, gpa) VALUES (?, ?, ?, ?)", (name, age, course, gpa) ) print(f" ✅ {name} added!")

def viewstudents(): with sqlite3.connect(DBNAME) as conn: conn.rowfactory = sqlite3.Row rows = conn.execute("SELECT * FROM students ORDER BY name").fetchall() if not rows: print(" 📭 No students!") return print(f"\n {'ID':<5}{'Name':<15}{'Age':<6}{'Course':<15}{'GPA':<6}") print(" " + "-" * 47) for r in rows: print(f" {r['id']:<5}{r['name']:<15}{r['age']:<6}{r['course']:<15}{r['gpa']:<6}")

def searchstudent(): name = input(" Search name: ") with sqlite3.connect(DBNAME) as conn: conn.rowfactory = sqlite3.Row rows = conn.execute( "SELECT * FROM students WHERE name LIKE ?", (f"%{name}%",) ).fetchall() if rows: for r in rows: print(f" {r['name']} | Age: {r['age']} | GPA: {r['gpa']}") else: print(" ❌ No matches!")

def deletestudent(): sid = int(input(" Student ID to delete: ")) with sqlite3.connect(DBNAME) as conn: result = conn.execute("DELETE FROM students WHERE id = ?", (sid,)) if result.rowcount: print(" 🗑️ Deleted!") else: print(" ❌ Not found!")

initdb() print("=" * 45) print(" 🎓 STUDENT MANAGEMENT SYSTEM") print("=" * 45)

while True: print("\n 1. Add 2. View 3. Search 4. Delete 5. Exit") choice = input(" Choose: ") if choice == "1": addstudent() elif choice == "2": viewstudents() elif choice == "3": searchstudent() elif choice == "4": deletestudent() elif choice == "5": print(" 👋 Goodbye!") break ``

Q2: ? in SQL queries prevents: A) Errors B) SQL injection C) Duplicates D) Nulls Answer: B

Q3: fetchall() returns: A) One row B) All rows as list C) Count D) Boolean Answer: B

Q4: conn.commit() does: A) Reads data B) Saves changes C) Closes connection D) Creates table Answer: B

Q5: AUTOINCREMENT does: A) Auto-deletes B) Auto-increments ID C) Auto-sorts D) Auto-updates Answer: B

Q6: cursor.rowcount gives: A) Total rows B) Affected rows C) Column count D) Table count Answer: B

Q7: sqlite3.Row enables: A) Faster queries B) Dict-like access C) Auto-commit D) Encryption Answer: B

Q8: executemany() is for: A) One query B) Multiple inserts C) Creating tables D) Deleting tables Answer: B

Q9: SQL LIKE '%text%' does: A) Exact match B) Contains match C) Starts with D) Ends with Answer: B

1. 1. What is SQL injection? Malicious SQL inserted via user input. Prevent with parameterized queries (?).

• Use parameterized queries (?) to prevent SQL injection.

• CRUD: INSERT, SELECT, UPDATE, DELETE.

• Use context managers (with) for automatic cleanup.

• sqlite3.Row` provides dict-like access to results.

---

10. Next Chapter Recommendation #

In Chapter 26: Web Scraping with Python, you'll learn to extract data from websites using BeautifulSoup! 🚀