You know **Python** and you know **MySQL** ā now let's connect them! This chapter answers the BIG question: **How does a Python program talk to a database?** Master this chapter and you'll be able to build real applications that store and retrieve data ā just like every app you use daily! š
> [!TIP]
> **How to use these notes:** This chapter is best learnt by **typing and running every code example** ā don't just read! Board Exam Tips appear throughout. Focus especially on **Section 13.2.2 (full connection code)** and **Section 13.3 (parameterised queries)** ā both are guaranteed exam questions with code-writing tasks every year!
---
## 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!
```mermaid
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?**
::: grid
::: card š | Every App You Use | Amazon, IRCTC, Zomato, your school's result portal | All use Python (or similar) + MySQL (or similar) behind the scenes!
::: card š | Permanent Storage | Python variables vanish when the program ends | Database stores data permanently ā even after shutdown
::: card š | Smart Querying | Python alone can't do complex data queries efficiently | MySQL's SQL engine filters millions of records instantly
::: card š | Real Applications | Console programs feel like demos | Database-connected programs feel like real software!
:::
> **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 `pymysql` 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 we use in CBSE Class 12 is:
```mermaid
graph LR
PY["š Python Program"]
CON["š pymysql\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:**
::: grid
::: card š¦ | mysql-connector-python | Official MySQL connector by Oracle | `import mysql.connector`
::: card š¦ | pymysql | Lightweight, popular alternative | `import pymysql`
:::
> [!NOTE]
> **Which one does CBSE use? š§ **
> The CBSE textbook uses **`mysql.connector`** in some editions and **`pymysql`** in others. Both work identically for the purposes of the Class 12 syllabus. We'll use `mysql.connector` in our examples as it's the most commonly tested ā but the concept and method names are identical in both!
---
### 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:
```mermaid
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
```
::: grid
::: card š§ | Step 1: Import | Load the connector module into your Python script | Like plugging in a USB cable before transferring files
::: card š | Step 2: Connect | Provide server address, username, password, database | Like logging into a website with credentials
::: card ā”ļø | Step 3: Cursor | Create a cursor ā the tool that sends SQL and receives results | Like picking up a pen before writing ā you need the tool first!
::: card š | Step 4: Execute | Run SQL statements via cursor.execute() | Asking the database a question or giving it a command
::: card š | Step 5: Fetch & Close | Retrieve the result rows, then close connection | Reading the answer, then hanging up the phone!
:::
---
### 13.2.2 Connecting with MySQL Database using pymysql š»
Now let's see the actual Python code! We'll use our familiar **Employee** table:
**Table: Employee**
| EmpId | 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 |
---
#### š§ 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
```python
# 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:**
```mermaid
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:**
| Method | Returns | Use When |
| :--- | :--- | :--- |
| `cursor.fetchone()` | Single next row as a tuple | Fetching one row at a time |
| `cursor.fetchmany(n)` | Next n rows as a list of tuples | Fetching a batch of rows |
| `cursor.fetchall()` | All remaining rows as a list of tuples | Fetching all rows at once |
**Example using fetchone() in a loop:**
```python
# 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
```
---
**Using rowcount ā How many rows were affected/fetched?**
```python
# rowcount_example.py
cursor.execute("SELECT * FROM Employee WHERE Dept = 'IT'")
records = cursor.fetchall()
print(f"Rows fetched: {cursor.rowcount}") # Output: Rows fetched: 2
```
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a Python program to display all records from the Employee table." ā **5-mark** question, the most common Python-MySQL question!
> The full answer must include: import, connect(), cursor(), execute(), fetchall(), loop to print, close(). Missing any step costs marks.
> [!WARNING]
> **Common Mistake ā Forgetting to close!**
> Not calling `cursor.close()` and `conn.close()` at the end leaves the connection open. MySQL has a limit on simultaneous connections ā unclosed connections waste resources and can eventually prevent new connections. Always close in a `finally` block for safety!
---
**Robust Version with Error Handling:**
```python
# robust_connection.py
import mysql.connector
from mysql.connector import Error
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_connected():
cursor.close()
conn.close()
print("Connection closed safely.")
```
> [!NOTE]
> **Why use try-except-finally? š§ **
> Network issues, wrong passwords, or MySQL server being offline can all crash your program mid-execution ā leaving connections open. The `finally` block **always runs** whether or not an error occurred, so `conn.close()` is guaranteed to be called. This is professional-grade programming practice!
---
## 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:**
```python
# 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:**
```python
# 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)
```
```mermaid
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:**
```python
# 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:**
```python
# 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')
```
> [!IMPORTANT]
> **Board Exam Tip**
> "What is a parameterised query? Why is it preferred?" ā **2-mark** question.
> Answer: A **parameterised query** uses placeholders (%s) for values instead of embedding them directly in the SQL string. The actual values are passed separately to cursor.execute(). This prevents **SQL Injection attacks** (a security vulnerability where malicious input can manipulate the SQL) and makes queries reusable with different values.
> [!WARNING]
> **The Comma After a Single Value in a Tuple!**
> When passing a single value, you MUST write it as a tuple with a comma: `("IT",)` NOT `("IT")`. Without the comma, Python treats `("IT")` as just the string `"IT"` in parentheses, not a tuple ā and cursor.execute() expects a tuple. This is one of the most common bugs beginners make!
---
**Why Parameterised Queries Prevent SQL Injection:**
```python
# 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!
```
> [!NOTE]
> **SQL Injection ā The #1 Web Security Attack! š§ **
> SQL Injection attacks trick a database into running unintended SQL by embedding SQL commands inside user input. Famous real-world example: hackers have stolen millions of credit card numbers this way. Parameterised queries are the standard, accepted defence. This is why CBSE teaches it ā it's genuinely important!
---
## 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!
```mermaid
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
```python
# 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()
```python
# 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!
```
> [!NOTE]
> **executemany() vs execute() š§ **
> `cursor.execute()` runs ONE SQL statement once.
> `cursor.executemany()` runs ONE SQL statement MULTIPLE times, once per set of values in the list. It's far more efficient than looping and calling execute() separately for each row!
---
#### š UPDATE ā Modifying Existing Rows
```python
# 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
```python
# 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, (107,))
conn.commit()
print(f"ā
Deleted {cursor.rowcount} row(s).")
cursor.close()
conn.close()
```
---
#### š Complete CRUD Application ā Putting It All Together
```python
# 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()
```
---
#### š¾ The Importance of commit() and rollback()
```python
# 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:
# Multiple operations that should succeed or fail TOGETHER:
cursor.execute("UPDATE Employee SET Salary = 99999 WHERE EmpId = 101")
cursor.execute("UPDATE Employee SET Salary = 99999 WHERE EmpId = 102")
conn.commit() # Both changes saved permanently
print("ā
Both updates committed successfully!")
except Exception as e:
conn.rollback() # Undo BOTH changes if anything went wrong
print(f"ā Error: {e} ā All changes rolled back!")
finally:
cursor.close()
conn.close()
```
**commit() vs rollback() ā Quick Reference:**
| Action | When to Use | Effect |
| :--- | :--- | :--- |
| `conn.commit()` | After successful DML operations | Permanently saves changes to database |
| `conn.rollback()` | After an error during DML | Cancels all uncommitted changes |
| `conn.autocommit = True` | Set once to auto-commit every statement | Each statement is immediately permanent (no rollback possible!) |
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a Python program to insert a new record into the Employee table." ā **5-mark** question almost every year!
> Full marks require: import, connect(), cursor(), parameterised INSERT with %s, executemany() OR execute(), **conn.commit()** (most students forget this!), and close(). `conn.commit()` is the #1 mark-losing mistake!
---
**cursor.rowcount ā Checking What Happened:**
```python
# rowcount_demo.py
cursor.execute("UPDATE Employee SET Salary = 50000 WHERE Dept = 'HR'")
conn.commit()
print(f"Rows updated: {cursor.rowcount}") # ā 2 (Sita and Vikram)
cursor.execute("DELETE FROM Employee WHERE City = 'Pune'")
conn.commit()
print(f"Rows deleted: {cursor.rowcount}") # ā 1 (if one Pune employee)
```
| Property | Returns |
| :--- | :--- |
| `cursor.rowcount` | Number of rows affected by last INSERT/UPDATE/DELETE, or number of rows fetched by SELECT |
---
## š Complete Workflow Diagram
```mermaid
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
| Error / Misconception | Correct Understanding |
| :--- | :--- |
| ā Forgetting `conn.commit()` after INSERT/UPDATE/DELETE | ā
Without commit(), changes exist only in the current session and are lost when the connection closes. ALWAYS commit after modifying data! |
| ā Using string concatenation for query values | ā
Always use parameterised queries with %s placeholders. String concatenation opens the door to SQL Injection attacks |
| ā `("IT")` as a tuple for a single value | ā
A single-value tuple MUST have a trailing comma: `("IT",)`. Without it, Python treats it as a string in parentheses, not a tuple |
| ā Forgetting to close connection | ā
Always call `cursor.close()` and `conn.close()`. Unclosed connections waste server resources |
| ā fetchall() on INSERT/UPDATE queries | ā
fetchall() / fetchone() are only for SELECT queries. For INSERT/UPDATE/DELETE, use `cursor.rowcount` to check how many rows were affected |
| ā `cursor.execute(query, value)` ā passing value directly (not in tuple) | ā
The second argument must always be a tuple or list: `cursor.execute(query, (value,))` |
| ā Thinking execute() saves data permanently | ā
execute() only stages the change. `conn.commit()` actually saves it to the database permanently |
---
## š Quick Revision ā Exam Ready!
**5 Steps to Connect Python with MySQL:**
1. `import mysql.connector`
2. `conn = mysql.connector.connect(host, user, password, database)`
3. `cursor = conn.cursor()`
4. `cursor.execute(query)` or `cursor.execute(query, values)`
5. Fetch / commit, then `cursor.close()` and `conn.close()`
**Three Fetch Methods:**
- `fetchone()` ā One row (tuple)
- `fetchmany(n)` ā n rows (list of tuples)
- `fetchall()` ā All rows (list of tuples)
**Key Rules ā One-Line Summary:**
- **SELECT** ā execute() ā fetch ā NO commit needed
- **INSERT/UPDATE/DELETE** ā execute() ā **commit()** ā mandatory!
- **Parameterised query** ā use `%s` as placeholder ā pass values as tuple
- **executemany()** ā inserts/updates multiple rows at once from a list of tuples
- **rowcount** ā tells you how many rows were affected/fetched
**The Tuple Comma Rule:**
```python
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.**
```python
# 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'**
```python
# 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.**
```python
# 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:**
```python
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:**
```python
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.
Back to List
Calculating...
UNIT 3 : CH 13
Jul 08, 2026
š Interface Python with MySQL
šÆ CBSE BOARD EXAM 2027
Complete Preparation Resources
Prepare smarter with the right combination of CSIP12's FREE digital resources + optional printed study books.
COMPUTER SCIENCE ā 083
2026ā27
LEARN ā Core Theory
Sumita Arora ā Progress in Computer Science with Python
Primary2026ā27 Session Edition ā¢ Dhanpat Rai & Co.
Looking for an alternative?
Preeti Arora ā Computer Science with Python
ā¢ Sultan Chand & Sons
PRACTISE ā Competency & Board Questions
Oswaal Class 12 Computer Science Question Bank
PrimaryChapterwise & Topicwise | For 2027 Exam
Additional Options
Together With CBSE Question Bank Class 12 CS
View on Amazon
Arihant All in One Computer Science Class 12
View on Amazon
You don't need every book. Choose resources that complement your preparation. CSIP12 provides notes, programs, quizzes, solved questions and previous-year papers for FREE.
Learning Support
Need Help With This Chapter?
Save key topics for exam revision, ask questions to teachers, or submit content corrections.
Verified Doubts & Teacher Answers
Loading resolved questions for this note...