Unit 15: Database Connectivity & CRUD Operations

import sqlite3

# Step 1: Connect to SQLite (creates file if not present)
conn = sqlite3.connect("devascent_students.db")
cursor = conn.cursor()

# Step 2: CREATE Table
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    course TEXT NOT NULL,
    gpa REAL NOT NULL
)
""")
conn.commit()

# Step 3: CREATE / INSERT Records using Parameterized Tuples (Secure against Injection)
students_batch = [
    ("Aman Sharma", "BCA", 8.4),
    ("Pooja Verma", "B.Tech CS", 9.2),
    ("Rahul Gupta", "BCA", 7.1)
]
cursor.executemany("INSERT INTO students (name, course, gpa) VALUES (?, ?, ?)", students_batch)
conn.commit()
print(f"[CREATE] Successfully inserted {cursor.rowcount} students.")

# Step 4: READ Records
cursor.execute("SELECT id, name, course, gpa FROM students WHERE gpa >= ?", (8.0,))
high_scorers = cursor.fetchall()
print("\n[READ] High Scorers (GPA >= 8.0):")
for row in high_scorers:
    print(f"  ID: {row[0]} | Name: {row[1]} | Course: {row[2]} | GPA: {row[3]}")

# Step 5: UPDATE Record
cursor.execute("UPDATE students SET gpa = ? WHERE name = ?", (7.8, "Rahul Gupta"))
conn.commit()
print(f"\n[UPDATE] Updated GPA for Rahul Gupta. Affected rows: {cursor.rowcount}")

# Step 6: DELETE Record
cursor.execute("DELETE FROM students WHERE name = ?", ("Aman Sharma",))
conn.commit()
print(f"[DELETE] Removed Aman Sharma. Affected rows: {cursor.rowcount}")

# Step 7: Clean close
cursor.close()
conn.close()

import mysql.connector
from mysql.connector import Error

def connect_mysql_database():
    try:
        connection = mysql.connector.connect(
            host="localhost",
            port=3306,
            database="academy_db",
            user="root",
            password="DevAscent@2026"
        )
        if connection.is_connected():
            db_info = connection.get_server_info()
            print(f"Connected to MySQL Server Version: {db_info}")
            
            cursor = connection.cursor()
            cursor.execute("SELECT DATABASE();")
            active_db = cursor.fetchone()
            print(f"Active Connected Database: {active_db[0]}")
            
            # Executing parameterized query in MySQL using %s
            cursor.execute("SELECT name, email FROM employees WHERE dept_id = %s", (10,))
            records = cursor.fetchall()
            for emp in records:
                print(f"  Employee: {emp[0]} <{emp[1]}>")

    except Error as err:
        print(f"Database Connection Error: {err}")
    finally:
        if 'connection' in locals() and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection successfully closed.")

# connect_mysql_database()

import sqlite3

def init_billing_database():
    conn = sqlite3.connect(":memory:")  # In-memory database for ultra-fast sandbox testing
    cursor = conn.cursor()
    cursor.execute("""
    CREATE TABLE student_wallet (
        student_id INT PRIMARY KEY,
        name TEXT,
        balance REAL
    )
    """)
    cursor.execute("""
    CREATE TABLE payment_ledger (
        payment_id INTEGER PRIMARY KEY AUTOINCREMENT,
        student_id INT,
        amount REAL,
        status TEXT
    )
    """)
    cursor.execute("INSERT INTO student_wallet VALUES (501, 'Kavita Singh', 15000.0)")
    conn.commit()
    return conn

# Transactional Payment Function
def process_tuition_payment(conn, student_id, amount):
    cursor = conn.cursor()
    print(f"\nInitiating payment of ₹{amount:,.2f} for Student #{student_id}...")
    
    try:
        # Step 1: Check balance
        cursor.execute("SELECT balance FROM student_wallet WHERE student_id = ?", (student_id,))
        record = cursor.fetchone()
        
        if not record:
            raise ValueError("Student ID not found in system!")
            
        current_balance = record[0]
        if current_balance < amount:
            raise ValueError(f"Insufficient balance! Available: ₹{current_balance:,.2f}")

        # Step 2: Deduct from balance
        cursor.execute("UPDATE student_wallet SET balance = balance - ? WHERE student_id = ?", (amount, student_id))
        
        # Step 3: Record transaction in ledger
        cursor.execute("INSERT INTO payment_ledger (student_id, amount, status) VALUES (?, ?, ?)", 
                       (student_id, amount, "COMPLETED"))
        
        # Step 4: Commit transaction (Atomicity guaranteed)
        conn.commit()
        print("✅ [TRANSACTION SUCCESS] Tuition payment settled. Receipt generated.")

    except Exception as err:
        conn.rollback()  # Abort all operations; restore original state
        print(f"❌ [TRANSACTION FAILED] Reason: {err}")
        print("   [ROLLBACK] Reverted all database modifications to protect financial integrity.")

# Test Scenario 1: Successful Payment
db_conn = init_billing_database()
process_tuition_payment(db_conn, 501, 6500.0)

# Test Scenario 2: Overdraft Attempt (Triggers automatic ROLLBACK)
process_tuition_payment(db_conn, 501, 20000.0)

# Verification of final balance
cur = db_conn.cursor()
cur.execute("SELECT name, balance FROM student_wallet WHERE student_id = 501")
final_state = cur.fetchone()
print(f"\nFinal Audited Balance for {final_state[0]}: ₹{final_state[1]:,.2f}")