Python-MySQL Connectivity

Prerequisites: SQL basics (CREATE, INSERT, SELECT, UPDATE, DELETE), Python functions, exception handling

---

1. Why Connect Python with MySQL?

• Front-end (Python): User interface, input/output, logic
• Back-end (MySQL): Data storage, retrieval, management, Python can send SQL queries to MySQL, receive results, and process them., Useful for building applications like Student Management Systems, Inventory Systems, etc.

---

2. Installing mysql.connector

pip install mysql-connector-python

---

3. Importing the Module

import mysql.connector

---

4. Steps for Python-MySQL Connectivity

Step 1: Import mysql.connector
Step 2: Create a connection using connect
Step 3: Create a cursor using cursor
Step 4: Execute SQL query using execute
Step 5: Fetch results (for SELECT) or commit (for INSERT/UPDATE/DELETE)
Step 6: Close cursor and connection

Basic Connection Template

import mysql.connector

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

# Step 3: Create cursor
cursor = conn.cursor

# Step 4: Execute query
cursor.execute("SELECT * FROM students")

# Step 5: Fetch results
data = cursor.fetchall
for row in data:
 print(row)

# Step 6: Close
cursor.close
conn.close

---

5. Connection Object

conn = mysql.connector.connect(
 host="localhost", # Server address
 user="root", # MySQL username
 password="root", # MySQL password
 database="school" # Database name (optional at first)
)

print(conn.is_connected) # Output: True

Connection Parameters

Parameter	Description	Default
host	Server hostname	"localhost"
user	MySQL username	"root"
password	MySQL password	""
database	Database to use	None

---

6. Cursor Object

The cursor acts as a pointer to execute queries and fetch results.

cursor = conn.cursor

Cursor Methods

Method	Purpose
cursor.execute(query)	Execute a single SQL query
cursor.fetchone	Fetch the next single row
cursor.fetchall	Fetch all remaining rows
cursor.fetchmany(n)	Fetch next n rows
cursor.close	Close the cursor

Cursor Attributes

Attribute	Purpose
cursor.rowcount	Number of rows affected by last query

---

7. Executing Queries

For SELECT (Read)

cursor.execute("SELECT * FROM students")
data = cursor.fetchall # Returns list of tuples

For INSERT / UPDATE / DELETE (Write)

cursor.execute("INSERT INTO students VALUES (101, 'Aman', 85)")
conn.commit # MUST commit for write operations

Important: Without conn.commit, INSERT/UPDATE/DELETE changes are NOT saved to the database.

---

8. Fetch Methods

fetchone, Returns One Row as Tuple

cursor.execute("SELECT * FROM students")
row = cursor.fetchone
print(row)
# Output: (101, 'Aman', 85)

fetchall, Returns All Rows as List of Tuples

cursor.execute("SELECT * FROM students")
rows = cursor.fetchall
for row in rows:
 print(row)
# Output:
# (101, 'Aman', 85)
# (102, 'Priya', 92)
# (103, 'Rahul', 78)

fetchmany(n), Returns n Rows

cursor.execute("SELECT * FROM students")
rows = cursor.fetchmany(2) # Fetch first 2 rows
for row in rows:
 print(row)
# Output:
# (101, 'Aman', 85)
# (102, 'Priya', 92)

---

9. rowcount Attribute

Returns the number of rows affected by the last execute.

cursor.execute("SELECT * FROM students")
cursor.fetchall
print("Rows fetched:", cursor.rowcount)
# Output: Rows fetched: 3

cursor.execute("UPDATE students SET marks = 90 WHERE rollno = 101")
conn.commit
print("Rows updated:", cursor.rowcount)
# Output: Rows updated: 1

---

10. Parameterized Queries (Using %s)

Never build SQL queries by string concatenation, it causes SQL injection vulnerabilities. Use parameterized queries with %s placeholders.

# Safe way - parameterized query
roll = 101
name = "Aman"
marks = 85

query = "INSERT INTO students (rollno, name, marks) VALUES (%s, %s, %s)"
values = (roll, name, marks)
cursor.execute(query, values)
conn.commit

# Searching with parameterized query
search_name = "Aman"
query = "SELECT * FROM students WHERE name = %s"
cursor.execute(query, (search_name,)) # Note: tuple with single element needs comma
data = cursor.fetchall
for row in data:
 print(row)

Note: Always use %s (not %d or %f) for all data types in mysql.connector.

---

11. Closing Connection

cursor.close
conn.close
print("Connection closed.")

---

Practice Programs

Program 1: Connect to MySQL and List Databases

import mysql.connector

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

cursor = conn.cursor
cursor.execute("SHOW DATABASES")

print("Databases on server:")
for db in cursor:
 print(f" - {db[0]}")

cursor.close
conn.close

# Output:
# Databases on server:
# - information_schema
# - mysql
# - performance_schema
# - school
# - sys

Program 2: Create Database and Table

import mysql.connector

# Connect without specifying a database
conn = mysql.connector.connect(
 host="localhost",
 user="root",
 password="root"
)

cursor = conn.cursor

# Create database
cursor.execute("CREATE DATABASE IF NOT EXISTS school")
print("Database 'school' created.")

# Use the database
cursor.execute("USE school")

# Create table
cursor.execute("""
 CREATE TABLE IF NOT EXISTS students (
 rollno INT PRIMARY KEY,
 name VARCHAR(50),
 marks FLOAT
 )
""")
print("Table 'students' created.")

cursor.close
conn.close

# Output:
# Database 'school' created.
# Table 'students' created.

Program 3: Insert Records from Python

import mysql.connector

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

cursor = conn.cursor

# Insert single record
cursor.execute("INSERT INTO students VALUES (101, 'Aman', 85.5)")

# Insert multiple records
records = [
 (102, "Priya", 92.0),
 (103, "Rahul", 78.5),
 (104, "Sneha", 95.0),
 (105, "Vikram", 88.0)
]

query = "INSERT INTO students VALUES (%s, %s, %s)"
cursor.executemany(query, records)

conn.commit
print(f"Records inserted: {cursor.rowcount}")

cursor.close
conn.close

# Output:
# Records inserted: 4

Program 4: Display All Records

import mysql.connector

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

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

print(f"{'Roll No':<10}{'Name':<15}{'Marks':<10}")
print("=" * 35)

for row in rows:
 print(f"{row[0]:<10}{row[1]:<15}{row[2]:<10}")

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

cursor.close
conn.close

# Output:
# Roll No Name Marks
# ===================================
# 101 Aman 85.5
# 102 Priya 92.0
# 103 Rahul 78.5
# 104 Sneha 95.0
# 105 Vikram 88.0
# ===================================
# Total records: 5

Program 5: Search by Specific Criteria

import mysql.connector

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

cursor = conn.cursor

# Search by minimum marks
min_marks = float(input("Enter minimum marks: "))
query = "SELECT * FROM students WHERE marks >= %s"
cursor.execute(query, (min_marks,))
rows = cursor.fetchall

if cursor.rowcount > 0:
 print(f"\nStudents with marks >= {min_marks}:")
 print(f"{'Roll No':<10}{'Name':<15}{'Marks':<10}")
 print("-" * 35)
 for row in rows:
 print(f"{row[0]:<10}{row[1]:<15}{row[2]:<10}")
 print(f"\nTotal: {cursor.rowcount} student(s)")
else:
 print("No students found.")

cursor.close
conn.close

# Test Run:
# Enter minimum marks: 85
# Students with marks >= 85.0:
# Roll No Name Marks
# -----------------------------------
# 101 Aman 85.5
# 102 Priya 92.0
# 104 Sneha 95.0
# 105 Vikram 88.0
#
# Total: 4 student(s)

Program 6: Update Records

import mysql.connector

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

cursor = conn.cursor

roll = int(input("Enter Roll No to update: "))
new_marks = float(input("Enter new marks: "))

query = "UPDATE students SET marks = %s WHERE rollno = %s"
cursor.execute(query, (new_marks, roll))
conn.commit

if cursor.rowcount > 0:
 print(f"Marks updated for Roll No {roll}.")
else:
 print(f"Roll No {roll} not found.")

cursor.close
conn.close

# Test Run:
# Enter Roll No to update: 103
# Enter new marks: 82.0
# Marks updated for Roll No 103.

Program 7: Delete Records

import mysql.connector

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

cursor = conn.cursor

roll = int(input("Enter Roll No to delete: "))

# Confirm before deleting
query = "SELECT * FROM students WHERE rollno = %s"
cursor.execute(query, (roll,))
row = cursor.fetchone

if row:
 print(f"Record found: {row[1]} (Marks: {row[2]})")
 confirm = input("Confirm delete? (y/n): ")
 if confirm.lower == 'y':
 query = "DELETE FROM students WHERE rollno = %s"
 cursor.execute(query, (roll,))
 conn.commit
 print("Record deleted successfully.")
 else:
 print("Deletion cancelled.")
else:
 print(f"Roll No {roll} not found.")

cursor.close
conn.close

# Test Run:
# Enter Roll No to delete: 105
# Record found: Vikram (Marks: 88.0)
# Confirm delete? (y/n): y
# Record deleted successfully.

Program 8: Complete Student Management System (Menu-Driven CRUD)

import mysql.connector

def connect:
 """Create and return a database connection."""
 return mysql.connector.connect(
 host="localhost",
 user="root",
 password="root",
 database="school"
 )

def add_student:
 """Add a new student record."""
 conn = connect
 cursor = conn.cursor

 roll = int(input(" Enter Roll No: "))
 name = input(" Enter Name: ")
 marks = float(input(" Enter Marks: "))

 try:
 query = "INSERT INTO students VALUES (%s, %s, %s)"
 cursor.execute(query, (roll, name, marks))
 conn.commit
 print(" Student added successfully!")
 except mysql.connector.IntegrityError:
 print(f" Error: Roll No {roll} already exists!")
 finally:
 cursor.close
 conn.close

def display_all:
 """Display all student records."""
 conn = connect
 cursor = conn.cursor

 cursor.execute("SELECT * FROM students ORDER BY rollno")
 rows = cursor.fetchall

 if rows:
 print(f"\n {'Roll No':<10}{'Name':<20}{'Marks':<10}")
 print(" " + "=" * 40)
 for row in rows:
 print(f" {row[0]:<10}{row[1]:<20}{row[2]:<10}")
 print(" " + "=" * 40)
 print(f" Total: {len(rows)} record(s)")
 else:
 print(" No records found.")

 cursor.close
 conn.close

def search_student:
 """Search for a student by roll number."""
 conn = connect
 cursor = conn.cursor

 roll = int(input(" Enter Roll No to search: "))
 query = "SELECT * FROM students WHERE rollno = %s"
 cursor.execute(query, (roll,))
 row = cursor.fetchone

 if row:
 print(f" Roll No : {row[0]}")
 print(f" Name : {row[1]}")
 print(f" Marks : {row[2]}")
 else:
 print(f" Roll No {roll} not found.")

 cursor.close
 conn.close

def update_student:
 """Update a student's marks."""
 conn = connect
 cursor = conn.cursor

 roll = int(input(" Enter Roll No to update: "))
 new_marks = float(input(" Enter new marks: "))

 query = "UPDATE students SET marks = %s WHERE rollno = %s"
 cursor.execute(query, (new_marks, roll))
 conn.commit

 if cursor.rowcount > 0:
 print(" Record updated successfully!")
 else:
 print(f" Roll No {roll} not found.")

 cursor.close
 conn.close

def delete_student:
 """Delete a student record."""
 conn = connect
 cursor = conn.cursor

 roll = int(input(" Enter Roll No to delete: "))
 query = "DELETE FROM students WHERE rollno = %s"
 cursor.execute(query, (roll,))
 conn.commit

 if cursor.rowcount > 0:
 print(" Record deleted successfully!")
 else:
 print(f" Roll No {roll} not found.")

 cursor.close
 conn.close

# Main program
def main:
 while True:
 print("\n" + "=" * 40)
 print(" STUDENT MANAGEMENT SYSTEM")
 print("=" * 40)
 print(" 1. Add Student")
 print(" 2. Display All Students")
 print(" 3. Search Student")
 print(" 4. Update Marks")
 print(" 5. Delete Student")
 print(" 6. Exit")
 print("=" * 40)

 choice = input(" Enter choice (1-6): ")

 if choice == "1":
 add_student
 elif choice == "2":
 display_all
 elif choice == "3":
 search_student
 elif choice == "4":
 update_student
 elif choice == "5":
 delete_student
 elif choice == "6":
 print(" Thank you! Goodbye.")
 break
 else:
 print(" Invalid choice!")

main

Program 9: Using Parameterized Queries for Safe Input

import mysql.connector

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

cursor = conn.cursor

# WRONG way (vulnerable to SQL injection) - NEVER DO THIS
# name = input("Enter name: ")
# cursor.execute("SELECT * FROM students WHERE name = '" + name + "'")
# If user enters: ' OR '1'='1
# The query becomes: SELECT * FROM students WHERE name = '' OR '1'='1'
# This returns ALL records - a SQL injection attack!

# RIGHT way - parameterized query
name = input("Enter name to search: ")
query = "SELECT * FROM students WHERE name = %s"
cursor.execute(query, (name,))
rows = cursor.fetchall

if rows:
 for row in rows:
 print(f" Roll: {row[0]}, Name: {row[1]}, Marks: {row[2]}")
else:
 print(f" No student named '{name}' found.")

# Insert with parameterized query
roll = int(input("\nEnter Roll No: "))
name = input("Enter Name: ")
marks = float(input("Enter Marks: "))

insert_query = "INSERT INTO students VALUES (%s, %s, %s)"
cursor.execute(insert_query, (roll, name, marks))
conn.commit
print("Record inserted safely!")

cursor.close
conn.close

# Test Run:
# Enter name to search: Aman
# Roll: 101, Name: Aman, Marks: 85.5
#
# Enter Roll No: 106
# Enter Name: Kavita
# Enter Marks: 89.5
# Record inserted safely!

---

Quick Reference

Task	Code
Import	import mysql.connector
Connect	conn = mysql.connector.connect(host, user, password, database)
Cursor	cursor = conn.cursor
Execute	cursor.execute(query)
Commit	conn.commit
Fetch one	cursor.fetchone
Fetch all	cursor.fetchall
Fetch n	cursor.fetchmany(n)
Row count	cursor.rowcount
Close cursor	cursor.close
Close connection	conn.close
Parameterized	cursor.execute("... %s ...", (value,))

---

Common Mistakes

Mistake	Correct Approach
Forgetting conn.commit after INSERT/UPDATE/DELETE	Always conn.commit for write operations
Using %d or %f in parameterized queries	Always use %s for all data types
String concatenation for queries	Use parameterized queries with %s
Not closing cursor and connection	Always close both in finally block
Forgetting the comma in single-value tuple	(value,) not (value)
Calling fetchall after INSERT	fetch methods are only for SELECT queries
Not handling IntegrityError for duplicate keys	Use try-except for INSERT operations

---

$1$2 Quick Tips

1. Connection steps (import, connect, cursor, execute, fetch/commit, close) are a common 2-3 mark question. Memorize the sequence.
2. fetchone vs fetchall vs fetchmany - know what each returns and when to use each.
3. commit - is needed only for INSERT, UPDATE, DELETE. NOT needed for SELECT.
4. rowcount - returns the number of rows affected. Commonly asked in MCQs.
5. Parameterized queries with %s - this is frequently tested. Remember to pass values as a tuple.
6. executemany - for inserting multiple records at once. Know the syntax.
7. Code writing questions typically ask for a function that connects to MySQL and performs one operation (insert, display, search, update, or delete).
8. Error handling: Wrapping connection code in try-except shows good practice.
9. Remember: fetchone returns a tuple, fetchall returns a list of tuples.
10. Always use %s as placeholder (not Python's %d/%f), this is a common trick in MCQs.