• Python can connect to databases to store and fetch data.
• MySQL: server-based DB (multi-user, production).
• SQLite: file-based DB (lightweight, single file).
• Common tasks: connect, execute queries, fetch records, commit/rollback, close connection.

• SQLite uses built-in module sqlite3.
• Database is stored in a single .db file.

import sqlite3

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

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

conn.commit()
conn.close()

• Use ? placeholders to prevent SQL injection.
• Fetch using fetchone(), fetchall().

import sqlite3

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

cur.execute("INSERT INTO students(name, marks) VALUES (?, ?)", ("Sourav", 85))
cur.execute("INSERT INTO students(name, marks) VALUES (?, ?)", ("Amit", 90))
conn.commit()

cur.execute("SELECT id, name, marks FROM students")
rows = cur.fetchall()

for r in rows:
    print(r)

conn.close()

• Install driver: pip install mysql-connector-python
• Connect using host, user, password, database.

import mysql.connector

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

cur = conn.cursor()
cur.execute("SELECT DATABASE()")
print(cur.fetchone())

conn.close()

• Use %s placeholders in mysql-connector.
• Must call conn.commit() to save changes.

import mysql.connector

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

cur = conn.cursor()

cur.execute(
    "INSERT INTO students(name, marks) VALUES (%s, %s)",
    ("Sourav", 88)
)

cur.execute(
    "UPDATE students SET marks=%s WHERE name=%s",
    (95, "Sourav")
)

conn.commit()
conn.close()

• fetchone() returns one row.
• fetchall() returns all rows.
• You can loop over cursor directly too.

import mysql.connector

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

cur = conn.cursor()

cur.execute("SELECT id, name, marks FROM students")
rows = cur.fetchall()

for row in rows:
    print(row)

conn.close()

• Transaction ensures multiple queries execute safely.
• commit() saves changes permanently.
• rollback() cancels changes if error happens.

import mysql.connector

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

try:
    cur = conn.cursor()

    cur.execute("UPDATE students SET marks = marks + 5 WHERE marks >= 80")
    cur.execute("UPDATE students SET marks = 0 WHERE marks < 0")  # example

    conn.commit()
    print("Transaction successful")
except Exception as e:
    conn.rollback()
    print("Transaction failed:", e)
finally:
    conn.close()

• Handle DB errors using try-except.
• Always close connection in finally block.
• Use parameterized queries to avoid SQL injection.

import sqlite3

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

    cur.execute("SELECT * FROM students")
    print(cur.fetchall())

except sqlite3.Error as e:
    print("SQLite Error:", e)

finally:
    try:
        conn.close()
    except:
        pass

• Menu-based mini project using SQLite.
• Features: add student, view all, search by name.

import sqlite3

def init_db():
    conn = sqlite3.connect("result.db")
    cur = conn.cursor()
    cur.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        marks INTEGER NOT NULL
    )
    """)
    conn.commit()
    conn.close()

def add_student(name, marks):
    conn = sqlite3.connect("result.db")
    cur = conn.cursor()
    cur.execute("INSERT INTO students(name, marks) VALUES (?, ?)", (name, marks))
    conn.commit()
    conn.close()

def view_students():
    conn = sqlite3.connect("result.db")
    cur = conn.cursor()
    cur.execute("SELECT id, name, marks FROM students")
    rows = cur.fetchall()
    conn.close()

    for r in rows:
        print(r)

def search_student(name):
    conn = sqlite3.connect("result.db")
    cur = conn.cursor()
    cur.execute("SELECT id, name, marks FROM students WHERE name LIKE ?", (f"%{name}%",))
    rows = cur.fetchall()
    conn.close()

    for r in rows:
        print(r)

init_db()

while True:
    print("\n1) Add  2) View  3) Search  4) Exit")
    ch = input("Enter choice: ").strip()

    if ch == "1":
        n = input("Enter name: ")
        m = int(input("Enter marks: "))
        add_student(n, m)
        print("Saved!")
    elif ch == "2":
        view_students()
    elif ch == "3":
        key = input("Enter name to search: ")
        search_student(key)
    elif ch == "4":
        break
    else:
        print("Invalid choice")