Last updated on June 25th, 2026 at 08:24 pm
Looking for Python MySQL Connectivity Notes Class 12 for CBSE (Code 083)? You are in the right place. This page covers everything you need — installing the connector, establishing a connection, creating a cursor, executing SQL queries, and performing all CRUD operations with complete ready-to-run programs and board-exam explanations.
💡 Exam importance: Python–MySQL connectivity appears in both theory and practical exams every year. Expect 1 program-writing question (3–5 marks) and 1 output-based question in the board paper. Practise every program below in the online Python compiler.
Quick Overview — What You Will Learn
| # | Topic | Key Concepts | Weight |
|---|---|---|---|
| 1 | Database Connectivity Basics | Front-end, Back-end, mysql.connector | ⭐ |
| 2 | Setup & Installation | pip install, import mysql.connector | ⭐ |
| 3 | Establishing Connection | connect(), host, user, passwd, database | ⭐⭐⭐ |
| 4 | Cursor Object | cursor(), execute(), result set | ⭐⭐⭐ |
| 5 | Fetching Data | fetchone(), fetchall(), fetchmany(), rowcount | ⭐⭐⭐ |
| 6 | DDL via Python | CREATE DATABASE, CREATE TABLE, SHOW, DESC | ⭐⭐ |
| 7 | INSERT | Parameterised INSERT, commit() | ⭐⭐⭐ |
| 8 | SELECT | SELECT query + fetchall() loop | ⭐⭐⭐ |
| 9 | UPDATE | UPDATE query + commit() | ⭐⭐⭐ |
| 10 | DELETE | DELETE query + commit() | ⭐⭐⭐ |
Section 1Theory ⭐
🔌 What is Database Connectivity?
Database connectivity refers to the connection and communication between a Python application (front-end) and a MySQL database server (back-end). Python sends SQL commands to MySQL; MySQL executes them and returns results. Together they form a complete application.
Front-end = The Python program the user interacts with (menus, input, output display).
Back-end = The MySQL server that stores and manages the actual data behind the scenes.
Back-end = The MySQL server that stores and manages the actual data behind the scenes.
Front-end (Python)Back-end (MySQL)mysql.connector
Section 2Setup ⭐
⚙️ Setup and Installation
Before writing any Python–MySQL program, install the mysql-connector-python package — the bridge between Python and MySQL. This is a one-time setup. Open Command Prompt and run the command below with an active internet connection.
Install the Connector
COMMAND PROMPT pip install mysql-connector-pythonImport in Every Program
PYTHON import mysql.connectorComplete Setup Steps
- Install Python — Download from python.org (free)
- Install MySQL — Download MySQL Community Server from mysql.com
- Open Command Prompt — Ensure active internet connection
- Install connector — Run:
pip install mysql-connector-python - Open Python IDLE — Create a new .py file
- Import connector — Add
import mysql.connectorat the top
pip installmysql-connector-pythonimport mysql.connector
Section 3Core Concept ⭐⭐⭐
🔗 Establishing a Connection — connect()
The connect() function creates a live connection between Python and the MySQL server. It returns a connection object used for all further operations — creating cursors, committing transactions, and closing the connection.
Syntax
SYNTAX con = mysql.connector.connect( host = “<hostname>”, user = “<username>”, passwd = “<password>”, database = “<dbname>” # optional )Example — Connect Without Selecting a Database
EXAMPLE import mysql.connectorcon = mysql.connector.connect( host = “localhost”, user = “root”, passwd = “system” ) print(“Connection Successful:”, con)Example — Connect With a Specific Database
EXAMPLE import mysql.connectorcon = mysql.connector.connect( host = “localhost”, user = “root”, passwd = “system”, database = “school” ) print(“Connected to database: school”)
💡 Parameters Explained:
- host — Address of MySQL server. Use
"localhost"when MySQL is on the same machine. - user — MySQL username. Default is
"root". - passwd — Password set during MySQL installation. Use
""if no password was set. - database — Name of the database to connect to. Can be skipped and selected later via USE.
connect()hostuserpasswddatabaseConnection Object
Section 4Core Concept ⭐⭐⭐
🖱️ Creating a Cursor Object — cursor()
A cursor is a control structure that acts as the interface between Python and MySQL. It carries SQL commands to MySQL via execute() and holds the returned result set — from where you extract rows using fetch methods.
Syntax
SYNTAX mycursor = con.cursor()Example
EXAMPLE import mysql.connectorcon = mysql.connector.connect(host=“localhost”, user=“root”, passwd=“system”, database=“school”) mycursor = con.cursor() # cursor object created mycursor.execute(“SELECT * FROM student”) # execute SQL query
The result set is the collection of records returned by a SELECT query — stored temporarily inside the cursor object. Extract rows using fetchall(), fetchone(), or fetchmany().
cursor()execute()Result Set
Section 5Core Concept ⭐⭐⭐
📥 Fetching Data — fetchall(), fetchone(), fetchmany()
After executing a SELECT query, results are held inside the cursor. Use fetch methods to extract rows into Python. This is one of the most tested topics in CBSE board exams — as a definition question and as part of program-writing questions.
| Method | What it Returns | When to Use |
|---|---|---|
| fetchall() | All remaining rows as a list of tuples. Empty list if no rows remain. | When you want every record — most common in board programs. |
| fetchone() | The next single row as a tuple. Returns None when no more rows. | One record at a time in a loop, or fetching just one result. |
| fetchmany(size) | The next size rows as a list of tuples. | When you want a specific number of records at a time. |
| rowcount | Integer — number of rows affected by the last execute() call. | After INSERT, UPDATE, DELETE to confirm rows changed. |
fetchall() — Fetch All Rows
EXAMPLE import mysql.connectorcon = mysql.connector.connect(host=“localhost”, user=“root”, passwd=“system”, database=“school”) cur = con.cursor() cur.execute(“SELECT * FROM student”)rows = cur.fetchall() # returns list of tuples for row in rows: print(row)fetchone() — Fetch One Row at a Time
EXAMPLE cur.execute(“SELECT * FROM student”)row = cur.fetchone() # fetch first row while row is not None: print(row) row = cur.fetchone() # fetch next rowfetchmany() — Fetch Specific Number of Rows
EXAMPLE cur.execute(“SELECT * FROM student”)rows = cur.fetchmany(3) # fetch first 3 rows only for row in rows: print(row)rowcount — Check Rows Affected
EXAMPLE cur.execute(“DELETE FROM student WHERE marks < 40”) con.commit() print(“Rows deleted:”, cur.rowcount)
fetchall()fetchone()fetchmany()rowcount
Section 6Programs ⭐⭐
🏗️ DDL Operations via Python
You can run any SQL command through Python — including DDL commands like CREATE DATABASE, CREATE TABLE, SHOW DATABASES, and DESC. DDL commands do not need a commit() call.
Program 1 — Create a Database
PROGRAM import mysql.connectormydb = mysql.connector.connect(host=“localhost”, user=“root”, passwd=“system”) mycursor = mydb.cursor() mycursor.execute(“CREATE DATABASE school”) print(“Database ‘school’ created successfully.”)Program 2 — Show All Databases
PROGRAM import mysql.connectormydb = mysql.connector.connect(host=“localhost”, user=“root”, passwd=“system”) mycursor = mydb.cursor() mycursor.execute(“SHOW DATABASES”) for db in mycursor: print(db)Program 3 — Create a Table
PROGRAM import mysql.connectormydb = mysql.connector.connect(host=“localhost”, user=“root”, passwd=“system”, database=“school”) mycursor = mydb.cursor() mycursor.execute(“””CREATE TABLE student ( rollno INT PRIMARY KEY, name VARCHAR(30) NOT NULL, class CHAR(3), marks FLOAT )”””) print(“Table ‘student’ created successfully.”)
CREATE DATABASECREATE TABLESHOW DATABASESDESC
Section 7DML — INSERT ⭐⭐⭐
➕ INSERT — Adding Records
Use INSERT INTO inside cursor.execute() to add rows. After any DML command that changes data, you must call con.commit() to permanently save changes. Always use parameterised queries (%s) for user input — prevents SQL Injection.
⚠️ Always call con.commit() after INSERT, UPDATE, or DELETE. Without it, changes exist only in memory and are discarded when the program ends.
Program 4 — Insert a Single Row (Hardcoded)
PROGRAM import mysql.connectorcon = mysql.connector.connect(host=“localhost”, user=“root”, passwd=“system”, database=“school”) cur = con.cursor()cur.execute(“INSERT INTO student VALUES (101, ‘Arjun Sharma’, ’12A’, 88.5)”) con.commit() # MUST commit to saveprint(cur.rowcount, “record inserted successfully.”) con.close()Program 5 — Insert Using User Input (Parameterised Query)
PROGRAM import mysql.connectorcon = mysql.connector.connect(host=“localhost”, user=“root”, passwd=“system”, database=“school”) cur = con.cursor()rno = int(input(“Enter Roll No : “)) name = input(“Enter Name : “) cls = input(“Enter Class : “) marks = float(input(“Enter Marks : “))query = “INSERT INTO student VALUES (%s, %s, %s, %s)” data = (rno, name, cls, marks)cur.execute(query, data) # %s placeholders filled safely con.commit()print(cur.rowcount, “record inserted successfully.”) con.close()
💡 Why Parameterised Queries (%s)?
- Keeps data separate from the SQL string — MySQL fills it safely.
- Prevents SQL Injection attacks — never concatenate user input directly into SQL strings.
- Works correctly even when strings contain special characters like quotes or backslashes.
INSERT INTOcommit()Parameterised Query%s placeholder
Section 8DML — SELECT ⭐⭐⭐
🔍 SELECT — Reading Records
Reading data from MySQL into Python uses a SELECT query inside execute(), followed by a fetch method. SELECT does not require commit() since it does not change any data.
Program 6 — Display All Records
PROGRAM import mysql.connectorcon = mysql.connector.connect(host=“localhost”, user=“root”, passwd=“system”, database=“school”) cur = con.cursor() cur.execute(“SELECT * FROM student”)rows = cur.fetchall() for row in rows: print(row)con.close()Program 7 — Display Records with WHERE Condition
PROGRAM import mysql.connectorcon = mysql.connector.connect(host=“localhost”, user=“root”, passwd=“system”, database=“school”) cur = con.cursor()cls = input(“Enter class to search: “) cur.execute(“SELECT * FROM student WHERE class = %s”, (cls,))rows = cur.fetchall() if rows: for row in rows: print(row) else: print(“No records found for class”, cls)con.close()
SELECTfetchall()WHERENo commit needed
Section 9DML — UPDATE ⭐⭐⭐
✏️ UPDATE — Modifying Records
The UPDATE command modifies existing rows. Always use a WHERE clause — without it, every row in the table will be updated. Call con.commit() to save changes permanently.
Program 8 — Update Marks of a Student
PROGRAM import mysql.connectorcon = mysql.connector.connect(host=“localhost”, user=“root”, passwd=“system”, database=“school”) cur = con.cursor()rno = int(input(“Enter Roll No to update : “)) newmarks = float(input(“Enter new marks : “))query = “UPDATE student SET marks = %s WHERE rollno = %s” cur.execute(query, (newmarks, rno)) con.commit()print(cur.rowcount, “record(s) updated.”) con.close()
UPDATESETWHEREcommit()rowcount
Section 10DML — DELETE ⭐⭐⭐
🗑️ DELETE — Removing Records
The DELETE command removes rows from a table. Always use a WHERE clause to target specific rows and call con.commit() to apply the deletion permanently.
⚠️ Always use WHERE with DELETE. Running
DELETE FROM student without WHERE permanently erases every row. This cannot be undone after commit().Program 9 — Delete a Student by Roll Number
PROGRAM import mysql.connectorcon = mysql.connector.connect(host=“localhost”, user=“root”, passwd=“system”, database=“school”) cur = con.cursor()rno = int(input(“Enter Roll No to delete: “))query = “DELETE FROM student WHERE rollno = %s” cur.execute(query, (rno,)) con.commit()print(cur.rowcount, “record(s) deleted.”) con.close()
DELETE FROMWHEREcommit()rowcount
BonusComplete Program ⭐⭐⭐
🏆 Complete CRUD Program — Student Management System
This is the most important program for the CBSE Class 12 practical exam. It combines all four DML operations — INSERT, SELECT, UPDATE, DELETE — into one menu-driven application. This pattern is used in almost every Python–MySQL practical project.COMPLETE PROGRAM import mysql.connector# Establish connection con = mysql.connector.connect( host=“localhost”, user=“root”, passwd=“system”, database=“school” ) cur = con.cursor()def insert_student(): rno = int(input(“Roll No : “)) name = input(“Name : “) cls = input(“Class : “) marks = float(input(“Marks : “)) cur.execute(“INSERT INTO student VALUES (%s,%s,%s,%s)”, (rno, name, cls, marks)) con.commit() print(“Record inserted successfully.”)def show_students(): cur.execute(“SELECT * FROM student”) rows = cur.fetchall() if rows: print(f”{‘RollNo’:8} {‘Name’:20} {‘Class’:6} Marks”) print(“-“ * 42) for r in rows: print(f”{r[0]:8} {r[1]:20} {r[2]:6} {r[3]}”) else: print(“No records found.”)def update_student(): rno = int(input(“Roll No to update : “)) newm = float(input(“New marks : “)) cur.execute(“UPDATE student SET marks=%s WHERE rollno=%s”, (newm, rno)) con.commit() print(cur.rowcount, “record(s) updated.”)def delete_student(): rno = int(input(“Roll No to delete : “)) cur.execute(“DELETE FROM student WHERE rollno=%s”, (rno,)) con.commit() print(cur.rowcount, “record(s) deleted.”)# Main menu while True: print(“\n===== Student Management System =====”) print(“1. Add Student”) print(“2. Show All Students”) print(“3. Update Marks”) print(“4. Delete Student”) print(“5. Exit”) ch = input(“Enter choice (1-5): “)if ch == ‘1’: insert_student() elif ch == ‘2’: show_students() elif ch == ‘3’: update_student() elif ch == ‘4’: delete_student() elif ch == ‘5’: print(“Goodbye!”) break else: print(“Invalid choice. Try again.”)con.close()
PythonMySQLCRUD Operationscommit()Menu-Driven
Frequently Asked Questions
What is the difference between fetchone(), fetchall(), and fetchmany()?
fetchall() retrieves all remaining rows at once as a list of tuples. fetchone() retrieves only the next single row as a tuple — returns None when no more rows remain. fetchmany(size) retrieves the next size rows as a list. Use fetchall() for small tables, fetchone() in a loop for large tables, and fetchmany() for paginated output.
Why is commit() necessary after INSERT, UPDATE, and DELETE?
MySQL uses transactions — DML changes are held in a temporary buffer until confirmed. commit() tells MySQL to permanently save the changes. Without it, changes exist only in the current session and are discarded when the connection closes. SELECT does not need commit() because it only reads data.
What is a cursor and why do we need it?
A cursor is the object that acts as the interface between Python and MySQL. Created using
con.cursor(), it sends SQL queries to MySQL via execute(), temporarily stores the returned result set, and lets you extract rows using fetch methods. Without a cursor, you cannot execute any SQL commands from Python.What is a parameterised query and why should we use it?
A parameterised query uses
%s placeholders in the SQL string instead of directly embedding user values. The actual values are passed as a separate tuple to execute(). This protects against SQL Injection attacks, handles special characters automatically, and makes code cleaner. Always use parameterised queries for user-supplied input.What is rowcount and when is it useful?
cursor.rowcount holds the number of rows affected by the most recent execute() call. After INSERT it shows how many rows were added. After UPDATE or DELETE it shows how many rows matched the WHERE condition. If rowcount is 0, no rows matched — useful for confirming whether an operation actually changed anything.
What happens if I forget to call con.close()?
The connection stays open and occupies server resources. MySQL has a limited number of simultaneous connections — leaving many open can exhaust this limit. Always call
con.close() at the end of your program, or use a try-finally block to ensure the connection closes even if an error occurs.Can I execute multiple SQL statements in one execute() call?
No — the standard
cursor.execute() accepts only one SQL statement at a time. Use cursor.executemany() to run the same statement multiple times with a list of different data values — more efficient than looping execute() when inserting many rows at once.Quick Practice Links
🗄️ MySQL Terminal
🐍 Python Compiler
📝 Class 12 MCQs
📖 MySQL Intro Notes
📂 Practical File
📄 Sample Papers
