Interface Python with SQL

1. Introduction to Python-Database Interface

Python can connect to databases using various modules. The most common are:

• sqlite3: Built-in module for SQLite (lightweight, file-based database).
• mysql-connector-python: For MySQL database.
• psycopg2: For PostgreSQL database.

For CBSE Class 12, the focus is on sqlite3 (built-in) and mysql-connector.

2. SQLite - Lightweight Database

SQLite is a self-contained, serverless, zero-configuration database engine. It stores the entire database in a single file on disk.

Advantages:

• Built into Python (no separate installation needed).
• No server setup required.
• Portable database file.
• Ideal for small to medium applications.

3. Connecting to SQLite Database

import sqlite3

# Connect to database (creates file if not exists)
conn = sqlite3.connect("school.db")
print("Database connected successfully.")

To work with an in-memory database (temporary): conn = sqlite3.connect(":memory:")

4. Creating a Cursor Object

A cursor is an object used to execute SQL queries and fetch results.

cur = conn.cursor()

All SQL commands are executed through the cursor object.

5. Creating Tables

import sqlite3
conn = sqlite3.connect("school.db")
cur = conn.cursor()

cur.execute("""
    CREATE TABLE IF NOT EXISTS Student (
        RollNo INTEGER PRIMARY KEY,
        Name TEXT NOT NULL,
        Class TEXT,
        Marks REAL,
        City TEXT
    )
""")
conn.commit()
print("Table created successfully.")

6. Inserting Data

# Insert single record
cur.execute("INSERT INTO Student VALUES (101, 'Riya', 'XII', 95.5, 'Delhi')")

# Insert using parameters (recommended)
cur.execute("INSERT INTO Student VALUES (?, ?, ?, ?, ?)", (102, "Amit", "XII", 87.0, "Mumbai"))

# Insert multiple records
students = [
    (103, "Priya", "XII", 92.0, "Chennai"),
    (104, "Rahul", "XII", 78.5, "Kolkata")
]
cur.executemany("INSERT INTO Student VALUES (?, ?, ?, ?, ?)", students)
conn.commit()

7. Selecting Data

# Select all records
cur.execute("SELECT * FROM Student")
rows = cur.fetchall()
for row in rows:
    print(row)

# Select with condition
cur.execute("SELECT Name, Marks FROM Student WHERE Marks > 80")
rows = cur.fetchall()
for name, marks in rows:
    print(f"{name}: {marks}")

8. Updating Data

cur.execute("UPDATE Student SET Marks = ? WHERE RollNo = ?", (96.0, 101))
conn.commit()
print(f"Updated {cur.rowcount} row(s).")

9. Deleting Data

cur.execute("DELETE FROM Student WHERE RollNo = ?", (104,))
conn.commit()
print(f"Deleted {cur.rowcount} row(s).")

10. Parameterized Queries

Parameterized queries use placeholders (? for SQLite, %s for MySQL) to safely pass values. They prevent SQL injection attacks.

# SQLite uses ? placeholders
roll = 101
cur.execute("SELECT * FROM Student WHERE RollNo = ?", (roll,))
row = cur.fetchone()
print(row)

Advantages:

• Prevents SQL injection attacks.
• Automatically handles data type conversion.
• Improves query performance (query plan caching).
• Cleaner and more maintainable code.

11. Fetch Methods

cur.execute("SELECT * FROM Student")
print(cur.fetchone())    # First row
print(cur.fetchmany(2))   # Next 2 rows
print(cur.fetchall())     # Remaining rows

12. Commit and Rollback

conn.commit() saves all changes to the database. Without commit, changes are lost.

conn.rollback() undoes all changes since the last commit.

try:
    cur.execute("INSERT INTO Student VALUES (105, 'Neha', 'XII', 88.0, 'Pune')")
    conn.commit()
except Exception as e:
    conn.rollback()
    print("Error:", e)
finally:
    conn.close()

13. MySQL Connector

To connect Python with MySQL, first install: pip install mysql-connector-python

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="school"
)
cur = conn.cursor()

MySQL uses %s as placeholder instead of ?.

cur.execute("INSERT INTO Student VALUES (%s, %s, %s, %s, %s)", (101, "Riya", "XII", 95.5, "Delhi"))

14. Exception Handling in Database Operations

import sqlite3
try:
    conn = sqlite3.connect("school.db")
    cur = conn.cursor()
    cur.execute("INSERT INTO Student VALUES (101, 'Riya', 'XII', 95.5, 'Delhi')")
    conn.commit()
except sqlite3.IntegrityError as e:
    print("Integrity Error:", e)
    conn.rollback()
except sqlite3.OperationalError as e:
    print("Operational Error:", e)
except Exception as e:
    print("General Error:", e)
finally:
    if conn:
        conn.close()

15. Menu-Driven Database Program

import sqlite3

def connect_db():
    conn = sqlite3.connect("school.db")
    cur = conn.cursor()
    cur.execute("""CREATE TABLE IF NOT EXISTS Student (RollNo INTEGER PRIMARY KEY, Name TEXT, Class TEXT, Marks REAL, City TEXT)""")
    return conn, cur

def add_student(conn, cur):
    roll = int(input("Roll No: "))
    name = input("Name: ")
    cls = input("Class: ")
    marks = float(input("Marks: "))
    city = input("City: ")
    cur.execute("INSERT INTO Student VALUES (?, ?, ?, ?, ?)", (roll, name, cls, marks, city))
    conn.commit()
    print("Student added.")

def display_students(cur):
    cur.execute("SELECT * FROM Student")
    for row in cur.fetchall():
        print(row)

def search_student(cur):
    roll = int(input("Enter Roll No: "))
    cur.execute("SELECT * FROM Student WHERE RollNo = ?", (roll,))
    row = cur.fetchone()
    if row:
        print(row)
    else:
        print("Not found.")