Skip to content
Mentor Subhanu

Python MySQL Connectivity for Class 12: Programs and Common Mistakes

By Subhanu Sankar Roy · · 3 min read

Python–MySQL connectivity means running SQL queries from inside a Python program. It is part of the Database Management unit of CBSE Class 12 Computer Science, and it appears in board papers, the practical exam and many student projects. The good news: every program follows the same five steps. Learn the pattern once and every question becomes a variation.

The five steps

  • Import the connector: import mysql.connector
  • Connect to the database with connect().
  • Create a cursor with cursor().
  • Run a query with execute().
  • For INSERT, UPDATE and DELETE, save the change with commit(). For SELECT, read the results with fetchone(), fetchmany() or fetchall().

Connecting to MySQL

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="school",
)
if con.is_connected():
    print("Connected")
cur = con.cursor()

Install the connector once with: pip install mysql-connector-python

Creating a table

cur.execute(
    "CREATE TABLE IF NOT EXISTS student ("
    "roll INT PRIMARY KEY, name VARCHAR(30), marks INT)"
)

Inserting a record

roll = int(input("Roll: "))
name = input("Name: ")
marks = int(input("Marks: "))
cur.execute("INSERT INTO student VALUES (%s, %s, %s)", (roll, name, marks))
con.commit()
print(cur.rowcount, "record inserted")

Many textbooks also show the format() style:

query = "INSERT INTO student VALUES ({}, '{}', {})".format(roll, name, marks)
cur.execute(query)
con.commit()

Both are in the syllabus. The %s version is safer in real software, because the connector handles quotes in the data for you. With format(), you must put quotes around text values yourself.

Reading records

cur.execute("SELECT * FROM student WHERE marks > %s", (80,))
rows = cur.fetchall()
for row in rows:
    print(row)
print("Rows found:", cur.rowcount)
  • fetchone() returns the next row as a tuple, or None when there are no more.
  • fetchmany(n) returns up to n rows as a list of tuples.
  • fetchall() returns all remaining rows as a list of tuples.

Updating and deleting

cur.execute("UPDATE student SET marks = %s WHERE roll = %s", (95, 3))
con.commit()
print(cur.rowcount, "row(s) updated")

cur.execute("DELETE FROM student WHERE roll = %s", (5,))
con.commit()
print(cur.rowcount, "row(s) deleted")

con.close()

A complete menu program

Projects and practical exams often ask for a small menu-driven program. This one puts the pieces together:

import mysql.connector

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

while True:
    print("1. Add  2. Show all  3. Update marks  4. Exit")
    choice = input("Choice: ")
    if choice == "1":
        data = (int(input("Roll: ")), input("Name: "), int(input("Marks: ")))
        cur.execute("INSERT INTO student VALUES (%s, %s, %s)", data)
        con.commit()
    elif choice == "2":
        cur.execute("SELECT * FROM student ORDER BY roll")
        for row in cur.fetchall():
            print(row)
    elif choice == "3":
        cur.execute("UPDATE student SET marks = %s WHERE roll = %s",
                    (int(input("New marks: ")), int(input("Roll: "))))
        con.commit()
        print(cur.rowcount, "row(s) updated")
    elif choice == "4":
        break

con.close()

Mistakes that cost marks

  • Forgetting con.commit() after INSERT, UPDATE or DELETE. The program runs, but nothing is saved.
  • Writing %d for numbers. The mysql connector uses %s for every type.
  • Passing one value without a trailing comma: (80) is just the number 80; (80,) is a tuple.
  • Calling commit() on the cursor instead of the connection. It is con.commit().
  • Mixing up fetchone() (one tuple) and fetchall() (a list of tuples) when writing the output.

How to practise

Install MySQL and run every program above yourself, then change it: search by name, show students above an average, or delete students below a pass mark. In the exam, write the five steps in order first, then fill in the query.

Related articles