🔗 Interface Python with MySQL

You already know Python and MySQL; this chapter connects the two. It explains how a Python program talks to a MySQL database: opening a connection, creating a cursor, running SELECT queries and fetching the results, writing parameterised queries, and inserting or updating records and saving them with commit. Real applications store and retrieve their data in the same way.

13.1 🏁 Introduction

Right now, Python and MySQL live in two separate worlds. Python runs your logic; MySQL stores your data. Without a bridge between them, a Python program cannot read from or write to a database!

graph LR
    BEFORE["😟 Without Connectivity\nPython program\nand MySQL database\nare completely isolated"]
    AFTER["😊 With Connectivity\nPython program can\nREAD, INSERT, UPDATE\ndata from MySQL!"]

    style BEFORE fill:#F44336,color:#fff
    style AFTER fill:#4CAF50,color:#fff

Why does this matter?

Real-World Analogy: Think of Python as the chef and MySQL as the refrigerator. Without a way to open the fridge, the chef can't cook anything! The mysql.connector module is the handle that lets Python open MySQL's door and access the ingredients (data) inside!

13.2 🔌 Connecting to MySQL from Python

Python uses a connector module to establish communication with MySQL. The module used in these notes — and in most CBSE schools — is mysql.connector (pymysql is an alternative that works the same way):

graph LR
    PY["🐍 Python Program"]
    CON["🔗 mysql.connector\n(Connector Module)"]
    MY["🗄️ MySQL Server\n(Database)"]

    PY <-->|"SQL queries\n& results"| CON
    CON <-->|"Network /\nLocal socket"| MY

    style CON fill:#FF9800,color:#fff
    style PY fill:#2196F3,color:#fff
    style MY fill:#4CAF50,color:#fff

Two common connector modules:

13.2.1 Steps for Creating Database Connectivity Applications 🪜

Before writing code, it helps to understand the 5-step process that every Python-MySQL application follows:

graph TD
    S1["Step 1 🔧\nInstall and Import\nthe connector module"]
    S2["Step 2 🔌\nCreate a Connection\nto the MySQL Server"]
    S3["Step 3 ➡️\nCreate a Cursor Object\n(our 'pointer' to run SQL)"]
    S4["Step 4 📝\nExecute SQL Queries\nusing the Cursor"]
    S5["Step 5 📊\nFetch Results and\nClose the Connection"]

    S1 --> S2 --> S3 --> S4 --> S5

    style S1 fill:#9C27B0,color:#fff
    style S2 fill:#2196F3,color:#fff
    style S3 fill:#FF9800,color:#fff
    style S4 fill:#4CAF50,color:#fff
    style S5 fill:#F44336,color:#fff

13.2.2 Connecting with MySQL Database using mysql.connector 💻

Now let's see the actual Python code! We'll use our familiar Employee table:

Table: Employee

Create this table yourself first — run these lines once in MySQL, so you can try every program in this chapter:

CREATE DATABASE CompanyDB;
USE CompanyDB;
CREATE TABLE Employee (
    EmpId  INT PRIMARY KEY,
    Name   VARCHAR(30),
    Dept   VARCHAR(20),
    Salary INT,
    City   VARCHAR(20)
);
INSERT INTO Employee VALUES
    (101, 'Priya Sharma', 'Sales', 45000, 'Delhi'),
    (102, 'Arjun Kumar',  'IT',    62000, 'Mumbai'),
    (103, 'Sita Roy',     'HR',    38000, 'Delhi'),
    (104, 'Rohan Das',    'IT',    71000, 'Bangalore');

🔧 Step 0: Installing the Connector

Before anything else, install the connector (done once in the terminal):

pip install mysql-connector-python

📦 Complete Connection and SELECT Example

# fetch_employees.py
import mysql.connector

# Step 1: Establish Connection
conn = mysql.connector.connect(
    host="localhost",       # MySQL server address (local computer)
    user="root",            # MySQL username
    password="yourpassword", # MySQL password
    database="CompanyDB"   # Which database to connect to
)

# Step 2: Check if connection was successful
if conn.is_connected():
    print("✅ Successfully connected to MySQL!")

# Step 3: Create a Cursor object
cursor = conn.cursor()

# Step 4: Execute an SQL query
cursor.execute("SELECT * FROM Employee")

# Step 5: Fetch and display results
records = cursor.fetchall()    # Fetches ALL rows at once

print("\n--- Employee Records ---")
for row in records:
    print(f"ID: {row[0]} | Name: {row[1]} | Dept: {row[2]} | Salary: {row[3]}")

# Step 6: Close cursor and connection
cursor.close()
conn.close()
print("\n🔒 Connection closed.")

Output:

✅ Successfully connected to MySQL!

--- Employee Records ---
ID: 101 | Name: Priya Sharma | Dept: Sales | Salary: 45000
ID: 102 | Name: Arjun Kumar | Dept: IT | Salary: 62000
ID: 103 | Name: Sita Roy | Dept: HR | Salary: 38000
ID: 104 | Name: Rohan Das | Dept: IT | Salary: 71000

🔒 Connection closed.

Understanding Each Key Component:

graph TD
    CONN["mysql.connector.connect()\nCreates the connection bridge\nNeeds: host, user, password, database"]
    CUR["cursor = conn.cursor()\nCreates the 'pen' to write SQL\nOne connection can have many cursors"]
    EXEC["cursor.execute(sql)\nSends the SQL to MySQL server\nMySQL runs it and stores result"]
    FETCH["cursor.fetchall()\nRetrieve result rows into Python\nReturns a list of tuples"]

    CONN --> CUR --> EXEC --> FETCH

    style CONN fill:#9C27B0,color:#fff
    style CUR fill:#2196F3,color:#fff
    style EXEC fill:#FF9800,color:#fff
    style FETCH fill:#4CAF50,color:#fff

The Three Fetch Methods — Important Differences:

Example using fetchone() in a loop:

# fetchone_example.py
import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="CompanyDB"
)
cursor = conn.cursor()
cursor.execute("SELECT Name, Salary FROM Employee")

print("Reading one row at a time:")
row = cursor.fetchone()    # Get first row
while row is not None:
    print(f"  {row[0]} earns {row[1]}")
    row = cursor.fetchone()  # Get next row

cursor.close()
conn.close()

Output:

Reading one row at a time:
  Priya Sharma earns 45000
  Arjun Kumar earns 62000
  Sita Roy earns 38000
  Rohan Das earns 71000

Example using fetchmany() — the cursor remembers where it stopped:

# fetchmany_example.py
import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="CompanyDB"
)
cursor = conn.cursor()
cursor.execute("SELECT EmpId, Name FROM Employee ORDER BY EmpId")

first_two = cursor.fetchmany(2)   # rows 1 and 2
print("fetchmany(2):", first_two)

next_row = cursor.fetchone()      # row 3 — carries on from where fetchmany() stopped
print("fetchone():  ", next_row)

rest = cursor.fetchall()          # only the rows that are LEFT (row 4)
print("fetchall():  ", rest)

print("Rows fetched in total:", cursor.rowcount)
cursor.close()
conn.close()

Output:

fetchmany(2): [(101, 'Priya Sharma'), (102, 'Arjun Kumar')]
fetchone():   (103, 'Sita Roy')
fetchall():   [(104, 'Rohan Das')]
Rows fetched in total: 4

Using rowcount — How many rows were affected/fetched?

# rowcount_example.py
cursor.execute("SELECT * FROM Employee WHERE Dept = 'IT'")
records = cursor.fetchall()
print(f"Rows fetched: {cursor.rowcount}")  # Output: Rows fetched: 2

Robust Version with Error Handling:

# robust_connection.py
import mysql.connector
from mysql.connector import Error

conn = None   # so that the finally block still works if connect() fails

try:
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="yourpassword",
        database="CompanyDB"
    )

    if conn.is_connected():
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM Employee")
        records = cursor.fetchall()

        for row in records:
            print(row)

except Error as e:
    print(f"❌ MySQL Error: {e}")

finally:
    if conn is not None and conn.is_connected():
        cursor.close()
        conn.close()
        print("Connection closed safely.")

Output when the MySQL server is switched off (the exact message depends on your computer):

❌ MySQL Error: 2003 (HY000): Can't connect to MySQL server on 'localhost:3306'

Without the line conn = None, a failed connect() would leave conn undefined, and the finally block would crash with a second error (NameError).

13.3 🎯 Parameterised Queries

A Parameterised Query (also called a Prepared Statement) is a query where values are passed separately from the SQL structure using placeholders (%s), instead of being hardcoded directly into the SQL string.

The Problem Without Parameterisation:

# dangerous_query.py (DON'T DO THIS!)
dept = input("Enter department: ")
query = "SELECT * FROM Employee WHERE Dept = '" + dept + "'"
cursor.execute(query)
# If user types: IT' OR '1'='1  → SECURITY BREACH! (SQL Injection)

The Solution — Parameterised Query:

# safe_parameterised.py ✅
dept = input("Enter department: ")
query = "SELECT * FROM Employee WHERE Dept = %s"
cursor.execute(query, (dept,))   # Value passed separately as a tuple
records = cursor.fetchall()
for row in records:
    print(row)

graph LR
    SQL["SQL Template\n'SELECT * FROM Employee\nWHERE Dept = %s'"]
    VAL["Values Tuple\n('IT',)"]
    SAFE["✅ Safe Combined Query\nMySQL handles the\ncombination securely"]

    SQL --> SAFE
    VAL --> SAFE

    style SQL fill:#2196F3,color:#fff
    style VAL fill:#FF9800,color:#fff
    style SAFE fill:#4CAF50,color:#fff

How %s works — Step by Step:

# parameterised_explained.py
import mysql.connector

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

# %s is the placeholder — MySQL fills it in safely
query = "SELECT * FROM Employee WHERE Dept = %s AND Salary > %s"
values = ("IT", 60000)   # Tuple with values matching the %s placeholders

cursor.execute(query, values)   # Pass query AND values separately
records = cursor.fetchall()

print("IT employees earning more than 60000:")
for row in records:
    print(f"  {row[1]} — ₹{row[3]}")

cursor.close()
conn.close()

Output:

IT employees earning more than 60000:
  Arjun Kumar — ₹62000
  Rohan Das — ₹71000

More Parameterised Query Examples:

# more_param_examples.py

# Single parameter:
cursor.execute("SELECT * FROM Employee WHERE City = %s", ("Delhi",))

# Multiple parameters:
cursor.execute(
    "SELECT * FROM Employee WHERE Dept = %s AND City = %s",
    ("IT", "Mumbai")
)

# Using a variable:
emp_id = 102
cursor.execute("SELECT * FROM Employee WHERE EmpId = %s", (emp_id,))
row = cursor.fetchone()
print(row)  # Output: (102, 'Arjun Kumar', 'IT', 62000, 'Mumbai')

Why Parameterised Queries Prevent SQL Injection:

# sql_injection_demo.py

# Dangerous version — user inputs: IT' OR '1'='1
user_input = "IT' OR '1'='1"
bad_query = "SELECT * FROM Employee WHERE Dept = '" + user_input + "'"
# This becomes: SELECT * FROM Employee WHERE Dept = 'IT' OR '1'='1'
# Result: ALL rows returned! Security breach!

# Safe version — same malicious input is treated as a literal string:
cursor.execute(
    "SELECT * FROM Employee WHERE Dept = %s",
    (user_input,)
)
# MySQL treats IT' OR '1'='1 as a literal string to match
# No rows match this exact string — attack neutralised!

13.3.1 Building a Query String — % and format() 🧩

The CBSE syllabus also expects you to build a query string in Python using the %s format specifier or the format() method, and then pass the finished string to execute().

Method 1 — % formatting:

# query_with_percent.py
sal = int(input("Minimum salary: "))
query = "SELECT * FROM Employee WHERE Salary > %s" % (sal,)
print(query)                 # e.g. SELECT * FROM Employee WHERE Salary > 40000
cursor.execute(query)
for row in cursor.fetchall():
    print(row)

Method 2 — format():

# query_with_format.py
dept = input("Department: ")
city = input("City: ")
query = "SELECT Name, Salary FROM Employee WHERE Dept = '{}' AND City = '{}'".format(dept, city)
print(query)                 # e.g. SELECT Name, Salary FROM Employee WHERE Dept = 'IT' AND City = 'Pune'
cursor.execute(query)
for row in cursor.fetchall():
    print(row)

Inserting a record with format():

# insert_with_format.py
query = "INSERT INTO Employee VALUES ({}, '{}', '{}', {}, '{}')".format(106, 'Kavya Rao', 'HR', 41000, 'Mumbai')
cursor.execute(query)
conn.commit()                # save the change permanently

13.4 📝 Performing Insert and Update Queries

So far we've only read data. Now let's write data — inserting new rows and updating existing ones. The key difference from SELECT queries is that modifying queries require conn.commit() to permanently save changes!

graph LR
    EX["cursor.execute()\nRuns the INSERT/UPDATE\nin a temporary transaction"]
    COM["conn.commit()\nPermanently saves\nthe change to disk"]
    ROLL["conn.rollback()\nCancels the change\nif something went wrong"]

    EX --> COM
    EX --> ROLL

    style COM fill:#4CAF50,color:#fff
    style ROLL fill:#F44336,color:#fff
    style EX fill:#FF9800,color:#fff

Analogy: cursor.execute() is like writing on a whiteboard. conn.commit() is like taking a photo of it — permanently saved! conn.rollback() is like erasing the whiteboard before taking the photo.

📥 INSERT — Adding a New Row

# insert_employee.py
import mysql.connector

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

# Parameterised INSERT — safe and reusable
insert_query = """
    INSERT INTO Employee (EmpId, Name, Dept, Salary, City)
    VALUES (%s, %s, %s, %s, %s)
"""
new_employee = (105, "Anjali Gupta", "Sales", 47000, "Mumbai")

cursor.execute(insert_query, new_employee)
conn.commit()   # ← CRITICAL: saves the INSERT permanently!

print(f"✅ Row inserted! Rows affected: {cursor.rowcount}")

cursor.close()
conn.close()

Output:

✅ Row inserted! Rows affected: 1

📥 Inserting Multiple Rows at Once — executemany()

# insert_multiple.py
import mysql.connector

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

insert_query = "INSERT INTO Employee (EmpId, Name, Dept, Salary, City) VALUES (%s, %s, %s, %s, %s)"

# List of tuples — one tuple per row to insert
new_employees = [
    (105, "Anjali Gupta", "Sales", 47000, "Mumbai"),
    (106, "Vikram Singh", "HR",    41000, "Delhi"),
    (107, "Neha Joshi",  "IT",    55000, "Pune")
]

cursor.executemany(insert_query, new_employees)
conn.commit()

print(f"✅ {cursor.rowcount} rows inserted successfully!")

cursor.close()
conn.close()

Output:

✅ 3 rows inserted successfully!

🔄 UPDATE — Modifying Existing Rows

# update_salary.py
import mysql.connector

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

# Give all IT employees a 10% raise:
update_query = "UPDATE Employee SET Salary = Salary * 1.10 WHERE Dept = %s"
cursor.execute(update_query, ("IT",))
conn.commit()

print(f"✅ Salary updated! Rows affected: {cursor.rowcount}")

cursor.close()
conn.close()

Output:

✅ Salary updated! Rows affected: 2

🗑️ DELETE — Removing Rows

# delete_employee.py
import mysql.connector

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

# Delete a specific employee by ID:
delete_query = "DELETE FROM Employee WHERE EmpId = %s"
cursor.execute(delete_query, (103,))
conn.commit()

print(f"✅ Deleted {cursor.rowcount} row(s).")

cursor.close()
conn.close()

Output:

✅ Deleted 1 row(s).

If no row has that EmpId, nothing is deleted and rowcount is 0 — MySQL does not raise an error.

🔄 Complete CRUD Application — Putting It All Together

# complete_crud.py
# CRUD = Create, Read, Update, Delete
import mysql.connector
from mysql.connector import Error

def get_connection():
    return mysql.connector.connect(
        host="localhost",
        user="root",
        password="yourpassword",
        database="CompanyDB"
    )

# ---- CREATE (INSERT) ----
def add_employee(emp_id, name, dept, salary, city):
    conn = get_connection()
    cursor = conn.cursor()
    query = "INSERT INTO Employee VALUES (%s, %s, %s, %s, %s)"
    cursor.execute(query, (emp_id, name, dept, salary, city))
    conn.commit()
    print(f"✅ Added: {name}")
    cursor.close()
    conn.close()

# ---- READ (SELECT) ----
def show_all_employees():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM Employee ORDER BY EmpId")
    rows = cursor.fetchall()
    print("\n📋 All Employees:")
    print(f"{'ID':<5} {'Name':<20} {'Dept':<10} {'Salary':<10} {'City'}")
    print("-" * 55)
    for r in rows:
        print(f"{r[0]:<5} {r[1]:<20} {r[2]:<10} {r[3]:<10} {r[4]}")
    cursor.close()
    conn.close()

# ---- UPDATE ----
def update_salary(emp_id, new_salary):
    conn = get_connection()
    cursor = conn.cursor()
    query = "UPDATE Employee SET Salary = %s WHERE EmpId = %s"
    cursor.execute(query, (new_salary, emp_id))
    conn.commit()
    print(f"✅ Updated salary for EmpId {emp_id}")
    cursor.close()
    conn.close()

# ---- DELETE ----
def delete_employee(emp_id):
    conn = get_connection()
    cursor = conn.cursor()
    query = "DELETE FROM Employee WHERE EmpId = %s"
    cursor.execute(query, (emp_id,))
    conn.commit()
    print(f"✅ Deleted EmpId {emp_id}")
    cursor.close()
    conn.close()

# ---- MAIN PROGRAM ----
show_all_employees()
add_employee(108, "Ravi Sharma", "Finance", 52000, "Delhi")
show_all_employees()
update_salary(101, 50000)
delete_employee(108)
show_all_employees()

Output:

📋 All Employees:
ID    Name                 Dept       Salary     City
-------------------------------------------------------
101   Priya Sharma         Sales      45000      Delhi
102   Arjun Kumar          IT         62000      Mumbai
103   Sita Roy             HR         38000      Delhi
104   Rohan Das            IT         71000      Bangalore
✅ Added: Ravi Sharma

📋 All Employees:
ID    Name                 Dept       Salary     City
-------------------------------------------------------
101   Priya Sharma         Sales      45000      Delhi
102   Arjun Kumar          IT         62000      Mumbai
103   Sita Roy             HR         38000      Delhi
104   Rohan Das            IT         71000      Bangalore
108   Ravi Sharma          Finance    52000      Delhi
✅ Updated salary for EmpId 101
✅ Deleted EmpId 108

📋 All Employees:
ID    Name                 Dept       Salary     City
-------------------------------------------------------
101   Priya Sharma         Sales      50000      Delhi
102   Arjun Kumar          IT         62000      Mumbai
103   Sita Roy             HR         38000      Delhi
104   Rohan Das            IT         71000      Bangalore

💾 The Importance of commit() and rollback()

# commit_rollback_demo.py
import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="CompanyDB"
)
conn.autocommit = False  # Make sure auto-commit is OFF (default is OFF)
cursor = conn.cursor()

try:
    # Two changes that must succeed or fail TOGETHER:
    cursor.execute("UPDATE Employee SET Salary = Salary + 5000 WHERE EmpId = 101")  # works
    cursor.execute("UPDATE Employee SET Salry = 0 WHERE EmpId = 102")   # typo 'Salry' → error!

    conn.commit()  # Both changes saved permanently
    print("✅ Both updates committed successfully!")

except mysql.connector.Error as e:
    conn.rollback()  # Undo BOTH changes — including the first UPDATE, which had worked
    print("❌ Error:", e.msg)
    print("All changes rolled back!")

cursor.execute("SELECT Salary FROM Employee WHERE EmpId = 101")
print("Salary of EmpId 101 is still:", cursor.fetchall()[0][0])

cursor.close()
conn.close()

Output:

❌ Error: Unknown column 'Salry' in 'field list'
All changes rolled back!
Salary of EmpId 101 is still: 45000

The first UPDATE did run, but it was never committed — conn.rollback() threw it away, so conn.commit() was never reached and nothing changed.

commit() vs rollback() — Quick Reference:

cursor.rowcount — Checking What Happened:

# rowcount_demo.py
cursor.execute("UPDATE Employee SET Salary = 50000 WHERE Dept = 'HR'")
conn.commit()
print(f"Rows updated: {cursor.rowcount}")   # → 1 (only Sita Roy works in HR)

cursor.execute("DELETE FROM Employee WHERE City = 'Pune'")
conn.commit()
print(f"Rows deleted: {cursor.rowcount}")   # → 0 (nobody lives in Pune — no error, just 0)

🧑‍🎓 Board-Style Programs with User Input

Program 1 — Search for an employee by EmpId, with a "not found" message:

# search_employee.py
import mysql.connector

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

emp_id = int(input("Enter EmpId to search: "))
cursor.execute("SELECT * FROM Employee WHERE EmpId = %s", (emp_id,))
rows = cursor.fetchall()

if cursor.rowcount == 0:
    print("No employee found with EmpId", emp_id)
else:
    for row in rows:
        print("Name:", row[1], "| Dept:", row[2], "| Salary:", row[3])

cursor.close()
conn.close()

Two sample runs:

Enter EmpId to search: 103
Name: Sita Roy | Dept: HR | Salary: 38000

Enter EmpId to search: 999
No employee found with EmpId 999

Program 2 — Insert a record typed in by the user, using format():

# insert_from_input.py
import mysql.connector

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

eid  = int(input("EmpId : "))
name = input("Name  : ")
dept = input("Dept  : ")
sal  = int(input("Salary: "))
city = input("City  : ")

query = "INSERT INTO Employee VALUES ({}, '{}', '{}', {}, '{}')".format(eid, name, dept, sal, city)
cursor.execute(query)
conn.commit()
print(cursor.rowcount, "record inserted.")

cursor.close()
conn.close()

Sample run:

EmpId : 105
Name  : Anjali Gupta
Dept  : Sales
Salary: 47000
City  : Mumbai
1 record inserted.

📐 Complete Workflow Diagram

graph TD
    START["🐍 Python Script Starts"]
    IMP["import mysql.connector"]
    CON["conn = mysql.connector.connect(\nhost, user, password, database)"]
    CUR["cursor = conn.cursor()"]

    SEL{"What type\nof query?"}

    READ["SELECT Query\ncursor.execute(query)\nrows = cursor.fetchall()\nfor row in rows: print(row)"]
    WRITE["INSERT / UPDATE / DELETE\ncursor.execute(query, values)\nconn.commit() ← MUST DO!"]

    CLOSE["cursor.close()\nconn.close()"]
    END["🏁 Program Ends"]

    START --> IMP --> CON --> CUR --> SEL
    SEL -->|"Reading data"| READ
    SEL -->|"Modifying data"| WRITE
    READ --> CLOSE
    WRITE --> CLOSE
    CLOSE --> END

    style CON fill:#2196F3,color:#fff
    style CUR fill:#FF9800,color:#fff
    style WRITE fill:#4CAF50,color:#fff
    style READ fill:#9C27B0,color:#fff

⚠️ Common Errors and Misconceptions

cursor.execute(query, ("IT",))   # ✅ CORRECT — single value in tuple
cursor.execute(query, ("IT"))    # ❌ WRONG — "IT" in parentheses, not a tuple

🎯 Sample Board Exam Questions

Q1: Very Short Answer [1 mark each]

a) Which Python module is used to connect Python with MySQL? → mysql.connector (or pymysql)

b) Which method is used to permanently save changes after INSERT? → conn.commit()

c) What does cursor.fetchone() return? → A single next row from the result as a tuple (or None if no more rows)

d) What is the placeholder used for values in parameterised queries? → %s

e) Which method runs the same INSERT query multiple times for a list of values? → cursor.executemany()

Q2: Short Answer [2 marks]

Q: What is a parameterised query? Why is it used?

A parameterised query uses %s placeholders in the SQL string and passes the actual values separately as a tuple to cursor.execute(). It is used for two reasons: (1) Security — it prevents SQL Injection attacks by treating user input as literal data, not SQL code; (2) Reusability — the same query template can be reused with different values without rewriting the SQL string.

Q3: Program Writing [5 marks]

Q: Write a Python program to display all employees from the 'IT' department from the Employee table in CompanyDB database.

# q3_it_employees.py
import mysql.connector

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

cursor = conn.cursor()

query = "SELECT * FROM Employee WHERE Dept = %s"
cursor.execute(query, ("IT",))

records = cursor.fetchall()
print("IT Department Employees:")
for row in records:
    print(row)

cursor.close()
conn.close()

Q4: Program Writing [5 marks]

Q: Write a Python program to insert the following record into the Employee table: EmpId=110, Name='Karan Mehta', Dept='Finance', Salary=53000, City='Pune'

# q4_insert_employee.py
import mysql.connector

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

cursor = conn.cursor()

query = "INSERT INTO Employee (EmpId, Name, Dept, Salary, City) VALUES (%s, %s, %s, %s, %s)"
values = (110, "Karan Mehta", "Finance", 53000, "Pune")

cursor.execute(query, values)
conn.commit()

print(f"Record inserted successfully! Rows affected: {cursor.rowcount}")

cursor.close()
conn.close()

Q5: Program Writing [5 marks]

Q: Write a Python program to update the salary of employee with EmpId=101 to 55000.

# q5_update_salary.py
import mysql.connector

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

cursor = conn.cursor()

query = "UPDATE Employee SET Salary = %s WHERE EmpId = %s"
cursor.execute(query, (55000, 101))
conn.commit()

print(f"Salary updated! Rows affected: {cursor.rowcount}")

cursor.close()
conn.close()

Q6: Error Spotting [2 marks]

Q: Find the error in the following code and correct it:

conn = mysql.connector.connect(host="localhost", user="root", password="pass", database="CompanyDB")
cursor = conn.cursor()
cursor.execute("INSERT INTO Employee VALUES (109, 'Raj', 'HR', 42000, 'Delhi')")
print("Record inserted!")
cursor.close()
conn.close()

Error: conn.commit() is missing after cursor.execute().

Corrected Code:

cursor.execute("INSERT INTO Employee VALUES (109, 'Raj', 'HR', 42000, 'Delhi')")
conn.commit()   # ← This line was missing!
print("Record inserted!")

✏️ Practice Problems

1. Write a Python program to display the name and salary of all employees earning more than ₹50,000.

2. Write a Python program to insert three new employee records at once using executemany().

3. Write a Python program to delete all employees working in the 'HR' department. Make sure to use parameterised queries.

4. Write a Python program that asks the user to enter a city name and then displays all employees from that city.

5. Write a Python program to update the salary of all employees in the 'Sales' department by adding ₹5000 to their current salary.

6. Explain the difference between cursor.fetchone() and cursor.fetchall(). When would you use each?

7. Write a Python program to count the total number of employees in the database and display the result.

8. A program inserts a record but doesn't call conn.commit(). What happens when the connection is closed? Explain.

9. Write a Python program using try-except-finally that safely handles connection errors and always closes the connection.

10. What is the difference between cursor.execute() and cursor.executemany()? Write an example of each.