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
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...