Python MySQL Connectivity, CBSE Class 12 Complete Tutorial

Python MySQL connectivity is an important topic in CBSE Class 12 Computer Science. It carries 4-5 marks in the theory exam and is also a key part of the practical exam. This tutorial covers everything from installation to CRUD operations with complete code examples.

What is Python-MySQL Connectivity?

Python can connect to a MySQL database to perform operations like inserting, reading, updating, and deleting data. This is called interface between Python and MySQL. The mysql.connector module makes this possible.

---

Step 1: Installing mysql.connector

Open Command Prompt (or Terminal) and run:

pip install mysql-connector-python

To verify the installation:

import mysql.connector
print(mysql.connector.__version__)

If this runs without errors, the installation is successful.

---

Step 2: Connecting to MySQL Database

import mysql.connector

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

if conn.is_connected():
    print("Connected successfully!")

conn.close()

Parameters of connect()

Parameter	Description	Example
host	Server address	"localhost"
user	MySQL username	"root"
password	MySQL password	"password123"
database	Database name	"school"

---

Step 3: Creating a Cursor

A cursor is an object that lets you execute SQL queries and fetch results. Think of it as a pointer that moves through the result set.

import mysql.connector

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

# Now you can execute queries using cursor
cursor.close()
conn.close()

---

Step 4: The Complete Connection Template

This is the template you should memorize for the exam. Every Python-MySQL program follows this pattern:

import mysql.connector

# Step 1: Establish connection
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="school"
)

# Step 2: Create cursor
cursor = conn.cursor()

# Step 3: Execute query
cursor.execute("SQL QUERY HERE")

# Step 4: Commit (for INSERT/UPDATE/DELETE) or Fetch (for SELECT)
conn.commit()      # For INSERT, UPDATE, DELETE
# OR
rows = cursor.fetchall()  # For SELECT

# Step 5: Close cursor and connection
cursor.close()
conn.close()

---

CRUD Operations

CRUD stands for Create, Read, Update, Delete. Let us look at each operation.

CREATE, Creating a Table

import mysql.connector

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

cursor.execute("""
    CREATE TABLE IF NOT EXISTS student (
        rollno INT PRIMARY KEY,
        name VARCHAR(50),
        marks INT,
        city VARCHAR(30)
    )
""")
print("Table created successfully")

cursor.close()
conn.close()

INSERT, Adding Records

Inserting a Single Record

import mysql.connector

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

cursor.execute("INSERT INTO student VALUES (1, 'Aman', 85, 'Delhi')")
conn.commit()
print("Record inserted. Rows affected:", cursor.rowcount)

cursor.close()
conn.close()

Record inserted. Rows affected: 1

Inserting with User Input

import mysql.connector

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

roll = int(input("Enter roll number: "))
name = input("Enter name: ")
marks = int(input("Enter marks: "))
city = input("Enter city: ")

query = "INSERT INTO student VALUES (%s, %s, %s, %s)"
values = (roll, name, marks, city)
cursor.execute(query, values)
conn.commit()
print("Record inserted successfully")

cursor.close()
conn.close()

Important: Use %s as placeholders, not {} or %d. The mysql.connector module uses %s for all data types, strings, integers, and floats.

READ, Fetching Records

fetchall(), Fetch All Records

import mysql.connector

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

cursor.execute("SELECT * FROM student")
rows = cursor.fetchall()

for row in rows:
    print(f"Roll: {row[0]}, Name: {row[1]}, Marks: {row[2]}, City: {row[3]}")

print(f"Total records: {cursor.rowcount}")

cursor.close()
conn.close()

Roll: 1, Name: Aman, Marks: 85, City: Delhi
Roll: 2, Name: Priya, Marks: 92, City: Mumbai
Roll: 3, Name: Rahul, Marks: 67, City: Delhi
Total records: 3

fetchone(), Fetch One Record

cursor.execute("SELECT * FROM student")
row = cursor.fetchone()
print(row)

(1, 'Aman', 85, 'Delhi')

fetchone() returns a single tuple. Each subsequent call returns the next row. Returns None when no more rows.

fetchmany(n), Fetch n Records

cursor.execute("SELECT * FROM student")
rows = cursor.fetchmany(2)
for row in rows:
    print(row)

(1, 'Aman', 85, 'Delhi')
(2, 'Priya', 92, 'Mumbai')

Comparison of Fetch Methods

Method	Returns	Use Case
fetchall()	List of all tuples	When you need all records
fetchone()	Single tuple or None	When you need one record
fetchmany(n)	List of n tuples	When you need specific number

rowcount Property

cursor.execute("SELECT * FROM student")
rows = cursor.fetchall()
print("Number of rows:", cursor.rowcount)

rowcount returns the number of rows affected by the last query. Works with SELECT, INSERT, UPDATE, and DELETE.

---

UPDATE, Modifying Records

import mysql.connector

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

cursor.execute("UPDATE student SET marks = 95 WHERE rollno = 1")
conn.commit()
print(f"Rows updated: {cursor.rowcount}")

cursor.close()
conn.close()

Rows updated: 1

Update with User Input

roll = int(input("Enter roll number to update: "))
new_marks = int(input("Enter new marks: "))

query = "UPDATE student SET marks = %s WHERE rollno = %s"
cursor.execute(query, (new_marks, roll))
conn.commit()
print(f"Rows updated: {cursor.rowcount}")

---

DELETE, Removing Records

import mysql.connector

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

cursor.execute("DELETE FROM student WHERE marks < 60")
conn.commit()
print(f"Rows deleted: {cursor.rowcount}")

cursor.close()
conn.close()

Rows deleted: 1

Important: Always call conn.commit() after INSERT, UPDATE, and DELETE. Without commit(), the changes are not saved to the database. This is a very common mistake in exams.

---

Parameterized Queries

Parameterized queries use placeholders (%s) instead of directly embedding values in the SQL string. This prevents SQL injection and is the recommended approach.

# BAD - Direct string formatting (vulnerable to SQL injection)
name = input("Enter name: ")
cursor.execute(f"SELECT * FROM student WHERE name = '{name}'")

# GOOD - Parameterized query (safe)
name = input("Enter name: ")
cursor.execute("SELECT * FROM student WHERE name = %s", (name,))

Note the comma after name in (name,). This makes it a tuple. A single value must still be passed as a tuple.

---

Error Handling

Always handle errors in database programs. The connection might fail, the query might have errors, or the database might be unavailable.

import mysql.connector

try:
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="school"
    )
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM student")
    rows = cursor.fetchall()
    for row in rows:
        print(row)

except mysql.connector.Error as err:
    print(f"Database error: {err}")

finally:
    if conn.is_connected():
        cursor.close()
        conn.close()
        print("Connection closed")

The finally block ensures the connection is closed even if an error occurs.

---

Complete Menu-Driven Program (Practical Exam Ready)

import mysql.connector

def connect():
    return mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="school"
    )

def insert_student():
    conn = connect()
    cursor = conn.cursor()
    roll = int(input("Enter roll number: "))
    name = input("Enter name: ")
    marks = int(input("Enter marks: "))
    city = input("Enter city: ")
    query = "INSERT INTO student VALUES (%s, %s, %s, %s)"
    cursor.execute(query, (roll, name, marks, city))
    conn.commit()
    print("Record inserted")
    cursor.close()
    conn.close()

def display_all():
    conn = connect()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM student")
    rows = cursor.fetchall()
    for row in rows:
        print(f"Roll: {row[0]}, Name: {row[1]}, Marks: {row[2]}, City: {row[3]}")
    print(f"Total records: {cursor.rowcount}")
    cursor.close()
    conn.close()

def update_student():
    conn = connect()
    cursor = conn.cursor()
    roll = int(input("Enter roll number to update: "))
    new_marks = int(input("Enter new marks: "))
    query = "UPDATE student SET marks = %s WHERE rollno = %s"
    cursor.execute(query, (new_marks, roll))
    conn.commit()
    if cursor.rowcount > 0:
        print("Record updated")
    else:
        print("Roll number not found")
    cursor.close()
    conn.close()

def delete_student():
    conn = connect()
    cursor = conn.cursor()
    roll = int(input("Enter roll number to delete: "))
    query = "DELETE FROM student WHERE rollno = %s"
    cursor.execute(query, (roll,))
    conn.commit()
    if cursor.rowcount > 0:
        print("Record deleted")
    else:
        print("Roll number not found")
    cursor.close()
    conn.close()

# Main menu
while True:
    print("\n--- Student Database ---")
    print("1. Insert Student")
    print("2. Display All Students")
    print("3. Update Marks")
    print("4. Delete Student")
    print("5. Exit")

    choice = int(input("Enter choice: "))
    if choice == 1:
        insert_student()
    elif choice == 2:
        display_all()
    elif choice == 3:
        update_student()
    elif choice == 4:
        delete_student()
    elif choice == 5:
        print("Goodbye!")
        break
    else:
        print("Invalid choice")

---

Common Exam Questions

Q1: What is the role of commit() in Python-MySQL connectivity?

The commit() method saves all the changes made by INSERT, UPDATE, or DELETE queries to the database permanently. Without calling commit(), the changes are not saved and will be lost when the connection is closed.

Q2: What is the difference between fetchone(), fetchall(), and fetchmany()?

• fetchone() returns a single row as a tuple, or None if no rows remain
• fetchall() returns all remaining rows as a list of tuples
• fetchmany(n) returns the next n rows as a list of tuples

Q3: What is the use of rowcount?

rowcount is a property of the cursor object that returns the number of rows affected by the last SQL query. For SELECT, it returns the number of rows fetched. For INSERT/UPDATE/DELETE, it returns the number of rows changed.

Q4: What is %s in parameterized queries?

%s is a placeholder used in parameterized queries. It is replaced by actual values at execution time. It is used for all data types (int, float, string) in mysql.connector. Parameterized queries prevent SQL injection attacks.

Q5: Write a Python program to display records from a table "EMPLOYEE" where salary is greater than 50000.

import mysql.connector

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

cursor.execute("SELECT * FROM EMPLOYEE WHERE salary > 50000")
rows = cursor.fetchall()

for row in rows:
    print(row)

print("Total records:", cursor.rowcount)

cursor.close()
conn.close()

---

Key Points to Remember

1. Always import mysql.connector at the top.
2. The connection order is: connect() then cursor() then execute().
3. Use commit() after INSERT, UPDATE, DELETE. Not needed for SELECT.
4. Use %s for all placeholders in parameterized queries.
5. Always close the cursor and connection with cursor.close() and conn.close().
6. Use try-except-finally for error handling.
7. fetchall() returns a list of tuples. fetchone() returns a single tuple.
8. rowcount gives the number of rows affected.

Memorize the connection template and practice the CRUD operations. This topic is guaranteed to appear in both your theory and practical exams.