VM-LEARNING /class.xii ·track.cs ·ch-3-3 session: 2026_27
$cd ..

~/Python ⇄ MySQL Connectivity

root@vm-learning ~ $ open ch-3-3
UNIT 3 ▪ CHAPTER 3
12
Python ⇄ MySQL Connectivity
mysql.connector · connect · cursor · execute · fetch · commit · rollback · Worked Programs
In Chapters 10–11 you ran SQL queries inside the MySQL command-line client. In real applications — a school portal, a banking app, a ticket-booking site — the queries are sent by a program, not typed by a human. Python uses the mysql.connector module to talk to a MySQL server: send queries, read back the results, and update the database.
Real-life analogy. Think of MySQL as a bank’s main vault and your Python program as a bank teller. mysql.connector is the telephone line between them: the teller asks the vault a question, the vault replies, the teller passes the answer to the customer. The same question could be asked by hundreds of tellers at the same time — each one needs its own connection.

12.1 The Six-Step Workflow

Every Python+MySQL program follows the same six steps. Memorise them and most of this chapter becomes second-nature.

  1. Import the module.
  2. Connect to the MySQL server.
  3. Create a cursor — the object that sends queries.
  4. Execute an SQL statement.
  5. Fetch results (for SELECT) or commit changes (for INSERT/UPDATE/DELETE).
  6. Close the cursor and the connection.
1. import 2. connect 3. cursor 4. execute 5. fetch or commit 6. close The Python ⇄ MySQL round-trip

12.2 Installing mysql.connector

pip install mysql-connector-python

This installs Oracle’s official Python connector. Run the command once per computer; the module is then available to every Python program.

If pip is not recognised, use python -m pip install mysql-connector-python. On a school lab machine you may need an administrator / admin rights.

12.3 Connecting to the MySQL Server

import mysql.connector conn = mysql.connector.connect( host = "localhost", # the machine running MySQL user = "root", # MySQL username password = "your_password", # MySQL password database = "school" # name of the database to use ) if conn.is_connected(): print("Connected to MySQL!") else: print("Connection failed.")

12.3.1 Connection parameters you may see

ParameterPurposeDefault
hostServer address"localhost"
portMySQL port3306
userMySQL username—
password / passwdMySQL password—
database / dbDatabase to opennone (optional)
Never hard-code a real password in a program that you share. In a real project the password is read from an environment variable or a config file — but for CBSE practicals, writing it inline is acceptable.

12.4 The Cursor — Your Query Sender

A cursor is the Python object through which every SQL query is sent and every result row is received. You can think of the connection as the phone line and the cursor as the handset you speak into.
cur = conn.cursor() # plain cursor — rows are tuples # OR — give me dict rows cur = conn.cursor(dictionary=True) # rows are dictionaries

12.5 Executing a Query — cur.execute()

cur.execute("SELECT name, marks FROM student")

The query runs on the server — but the rows have not come back to Python yet. You collect them with a fetch method.

12.5.1 The three fetch methods

MethodReturnsUse when…
cur.fetchone()Next row as a tuple, or NoneYou expect exactly one row — e.g. by primary key
cur.fetchmany(n)Up to n rows as a list of tuplesPaging through a large result
cur.fetchall()All remaining rows as a list of tuplesThe result set is small
cur.execute("SELECT roll, name, marks FROM student ORDER BY marks DESC") # use fetchall() for small results for roll, name, marks in cur.fetchall(): print(f"{roll:>3} {name:<15} {marks}")

12.5.2 cur.rowcount — how many rows were affected

cur.execute("UPDATE student SET marks = marks + 5 WHERE class = 11") print(cur.rowcount, "rows updated")

12.6 Parameterised Queries — the safe pattern

Never build SQL with string concatenation. "SELECT * FROM user WHERE name = '" + name + "'" opens your program to SQL-injection attacks — a hacker can type ' OR '1'='1 into your name input and the query becomes … WHERE name = '' OR '1'='1', which matches every row.

Use placeholders (%s) and pass a tuple of values. The driver escapes them safely.

# ✅ safe — parameters go as a tuple name = input("Name : ") marks = int(input("Marks: ")) cur.execute( "INSERT INTO student (name, marks) VALUES (%s, %s)", (name, marks) ) conn.commit()
Always use %s as the placeholder regardless of data type — even for numbers. (%d / %f are not used here.) The second argument must always be a tuple; if there is just one value write (value,) with a trailing comma.

12.7 Making Changes Permanent — commit() & rollback()

Every INSERT / UPDATE / DELETE runs inside a transaction. Until you call conn.commit() the changes live in a private buffer — other connections can’t see them, and closing the connection without committing undoes the work. conn.rollback() explicitly throws the pending changes away.
MethodDoes
conn.commit()Save all pending changes to the database
conn.rollback()Discard all pending changes since the last commit
conn.autocommit = TrueEvery single statement is auto-committed (convenient for scripts)

12.7.1 Using rollback for safety

try: cur.execute("UPDATE account SET balance = balance - 1000 WHERE id = 1") cur.execute("UPDATE account SET balance = balance + 1000 WHERE id = 2") conn.commit() # both succeeded → commit except Exception as e: conn.rollback() # one failed → undo both print("Transfer failed:", e)

12.8 Closing the Cursor and Connection

cur.close() # free the cursor conn.close() # free the connection — return it to the pool
Leaving connections open ties up resources on the MySQL server. Always close in a finally block so cleanup happens even if an exception occurs.

12.9 A Complete Mini-Program

Below is one short but complete program that covers all six steps. Study it carefully — almost every CBSE practical question is a minor variation of this.

import mysql.connector conn = mysql.connector.connect( host="localhost", user="root", password="your_password", database="school" ) cur = conn.cursor() try: # 1) INSERT — parameterised cur.execute( "INSERT INTO student (roll, name, class, marks) VALUES (%s,%s,%s,%s)", (8, "Isha", 12, 89) ) conn.commit() print(cur.rowcount, "row inserted.") # 2) SELECT — show leaderboard cur.execute("SELECT roll, name, marks FROM student ORDER BY marks DESC") print(f"{'Roll':<5}{'Name':<15}{'Marks':>6}") print("-" * 26) for roll, name, marks in cur.fetchall(): print(f"{roll:<5}{name:<15}{marks:>6}") except mysql.connector.Error as e: conn.rollback() print("Database error:", e) finally: cur.close() conn.close()

12.10 CBSE-style Worked Programs

12.10.1 Count students whose marks are above a threshold

import mysql.connector conn = mysql.connector.connect(host="localhost", user="root", password="pw", database="school") cur = conn.cursor() threshold = int(input("Minimum marks: ")) cur.execute("SELECT COUNT(*) FROM student WHERE marks > %s", (threshold,)) count, = cur.fetchone() # one-element tuple — unpack print("High scorers:", count) cur.close(); conn.close()

12.10.2 Search a student by roll number

cur.execute("SELECT * FROM student WHERE roll = %s", (int(input("Roll: ")),)) row = cur.fetchone() print(row if row else "Not found.")

12.10.3 Add a student from user input

roll = int(input("Roll : ")) name = input("Name : ") cls = int(input("Class: ")) marks = float(input("Marks: ")) cur.execute( "INSERT INTO student (roll, name, class, marks) VALUES (%s, %s, %s, %s)", (roll, name, cls, marks) ) conn.commit() print("Added.")

12.10.4 Update marks by roll number

roll = int(input("Roll: ")) new_marks = float(input("New marks: ")) cur.execute("UPDATE student SET marks = %s WHERE roll = %s", (new_marks, roll)) conn.commit() print(cur.rowcount, "row updated.")

12.10.5 Delete a record

cur.execute("DELETE FROM student WHERE roll = %s", (int(input("Roll: ")),)) conn.commit() print(cur.rowcount, "row deleted.")

12.10.6 Export the table to a CSV file

Combines the csv module (Chapter 7) with mysql.connector.

import csv, mysql.connector conn = mysql.connector.connect(host="localhost", user="root", password="pw", database="school") cur = conn.cursor() cur.execute("SELECT * FROM student") with open("student_export.csv", "w", newline="") as f: w = csv.writer(f) w.writerow([col[0] for col in cur.description]) # header from metadata w.writerows(cur.fetchall()) print("Exported.") cur.close(); conn.close()

12.10.7 Menu-driven student-records app

import mysql.connector conn = mysql.connector.connect(host="localhost", user="root", password="pw", database="school") cur = conn.cursor() def show_all(): cur.execute("SELECT * FROM student ORDER BY roll") for row in cur.fetchall(): print(row) def add(): data = (int(input("Roll: ")), input("Name: "), int(input("Class: ")), float(input("Marks: "))) cur.execute("INSERT INTO student VALUES (%s,%s,%s,%s,NULL,NULL)", data) conn.commit() def update(): cur.execute("UPDATE student SET marks=%s WHERE roll=%s", (float(input("Marks: ")), int(input("Roll: ")))) conn.commit() def remove(): cur.execute("DELETE FROM student WHERE roll=%s", (int(input("Roll: ")),)) conn.commit() while True: print("\n1. Show 2. Add 3. Update 4. Delete 5. Quit") c = input("Choice: ") if c == "1": show_all() elif c == "2": add() elif c == "3": update() elif c == "4": remove() else: break cur.close(); conn.close()

12.11 Common Mistakes to Avoid

#MistakeFix
1Forgetting conn.commit() after INSERT/UPDATE/DELETEWithout commit, the changes are discarded when the program ends
2Building SQL with + or f-strings from user inputAlways use %s placeholders — defeats SQL-injection
3Passing a single value without the trailing comma — (roll)Use (roll,) — the comma makes it a tuple
4Using %d / %f placeholdersAlways %s, regardless of data type
5Forgetting cur.close() and conn.close()Free server resources in a finally block
6Calling fetchall() after fetchall() — returns emptyA cursor is consumed once; re-execute the query to read again
7Wrong host / port / password → InterfaceErrorCheck the MySQL server is running and credentials are correct
8Not wrapping in try / except — any DB error crashes the appWrap risky queries and roll back on failure
9Hard-coding the password in a publicly-shared fileUse an environment variable or a local config file

Quick-revision summary

  • Install once: pip install mysql-connector-python.
  • Six-step workflow: import → connect → cursor → execute → fetch / commit → close.
  • mysql.connector.connect(host, user, password, database) returns a connection.
  • conn.cursor() — plain cursor (tuples) or cursor(dictionary=True) for dict rows.
  • cur.execute(sql, params) — SQL on its own, or SQL + tuple of values. Use %s placeholders for every data type.
  • Fetch: fetchone() → tuple or None; fetchmany(n) → list; fetchall() → list.
  • cur.rowcount tells how many rows were affected by the last execute.
  • conn.commit() to save; conn.rollback() to undo; conn.autocommit = True for per-statement commit.
  • Close both the cursor and the connection in a finally block.
  • Parameterised queries are not optional — they are the only safe way to accept user input.
🎓 Syllabus complete. You now have everything the CBSE Class XII Computer Science (083) Session 2026-27 paper can ask: Python with user-defined functions, exception handling, every kind of file, the stack data structure, computer networks, and the relational database world — both with raw SQL and via mysql.connector from Python. Revise the twelve chapters, practise the worked programs, and walk into the exam confident. All the best!
🧠Practice Quiz — test yourself on this chapter→