home
0%
Progress
StudentPortal › Class XII - CS › Unit 3
CBSE Code 083 | Unit 3: Database Management & Python Connectivity

03. Python to MySQL Connectivity

mysql.connector, connect(), cursor(), execute(), fetchone(), fetchall(), commit(), and error handling.

Unit 3: Database Management & Python Connectivity

Chapter 3: Python to MySQL Connectivity

1. Why Interface Python with SQL?

Python programs are great at computation and logic, but their data disappears when the program ends. A database like MySQL is great at storing large volumes of structured, persistent data, but has no programming logic of its own. Interfacing the two lets a Python program read from and write to a real database - combining the strengths of both.

This is done using the mysql-connector-python module, which must be installed once:

pip install mysql-connector-python

2. The Five Steps of Database Connectivity

import mysql.connector as sql

# Step 1: Establish a connection to the MySQL server
conn = sql.connect(host="localhost", user="root", passwd="your_password", database="school")

# Step 2: Create a cursor object - used to execute SQL commands
mycursor = conn.cursor()

# Step 3: Execute an SQL query through the cursor
mycursor.execute("SELECT * FROM Student")

# Step 4: Fetch the results
result = mycursor.fetchall()
for row in result:
    print(row)

# Step 5: Close the connection
conn.close()
Step Function Purpose
1 sql.connect() Opens a connection to the MySQL server
2 conn.cursor() Creates a cursor to send commands and receive results
3 cursor.execute() Runs an SQL statement
4 fetchone() / fetchall() / fetchmany(n) Retrieves query results
5 conn.close() Releases the connection

3. Fetching Results

Method Returns
fetchone() A single row (as a tuple), or None if no more rows
fetchall() All remaining rows, as a list of tuples
fetchmany(n) The next n rows, as a list of tuples
mycursor.execute("SELECT Name, Marks FROM Student")

row = mycursor.fetchone()
print(row)                 # ('Aman', 85)

remaining = mycursor.fetchall()
for r in remaining:
    print(r)

4. Inserting Data and commit()

Any change to the data (INSERT, UPDATE, DELETE) must be explicitly committed to be permanently saved in the database.

mycursor.execute("INSERT INTO Student VALUES (5, 'Kabir', 91)")
conn.commit()      # without this line, the insert would NOT be saved permanently

Parameterized Queries (Safer Practice)

Instead of directly inserting Python variables into an SQL string (which risks SQL Injection), use %s placeholders and pass values as a tuple:

name = input("Enter name: ")
marks = int(input("Enter marks: "))

sql_query = "INSERT INTO Student (Name, Marks) VALUES (%s, %s)"
mycursor.execute(sql_query, (name, marks))
conn.commit()

5. Error Handling

Database operations can fail (wrong password, server down, duplicate key, etc.), so connectivity code is often wrapped in a try-except block.

import mysql.connector as sql

try:
    conn = sql.connect(host="localhost", user="root", passwd="wrong_password", database="school")
    print("Connected successfully!")
except sql.Error as e:
    print("Error connecting to MySQL:", e)

6. Board Exam Focus Questions

Q1. Complete the following code to connect to a MySQL database named "college" and display all records from the table "Student" (fill in the blanks marked with ___):

import mysql.connector as sql

con = sql.___(host="localhost", user="root", passwd="admin", database="college")
cur = con.___()
cur.execute("___ * FROM Student")
data = cur.___()
for row in data:
    print(row)

Answer:

con = sql.connect(host="localhost", user="root", passwd="admin", database="college")
cur = con.cursor()
cur.execute("SELECT * FROM Student")
data = cur.fetchall()

Q2. What is the purpose of conn.commit()? What happens if it is omitted after an UPDATE statement? Answer: commit() permanently saves all changes made during the current transaction to the database. If it is omitted after an UPDATE, the change exists only temporarily within that session and will be lost (rolled back) once the connection is closed, without ever being permanently reflected in the actual database.

Q3. Why are parameterized queries (using %s placeholders) preferred over directly concatenating user input into an SQL string? Answer: Directly concatenating raw user input into an SQL query string allows a malicious user to inject extra SQL code (SQL Injection) that can manipulate or damage the database. Parameterized queries treat the supplied values purely as data, not as executable SQL, which closes off this vulnerability.

Entity Details

Create New Item

help

Submit Technical Query

Have a question or run into an issue? Describe it below, upload an optional screenshot, and our engineering team will answer it!

image Attach image (optional)

Submit Feedback

build Free Developer Utility Free Tool
gavel

Privacy & Legal Disclaimer

1. Client-Side Browser Processing

All utility tools on DeepEngineerHub (including Image to PDF, Text Formatters, JSON Converters, and Encryptors) execute 100% locally within your client browser using WebAssembly and JavaScript. No uploaded images, text, or documents are transmitted, collected, or stored on remote servers.

2. Limitation of Liability ("As-Is" Provision)

Tools and services are provided free of charge for convenience and educational purposes "as-is" without warranties of any kind. DeepEngineerHub shall not be held liable for any data loss, formatting inconsistencies, or indirect damages resulting from tool usage.

3. Open Source & Third-Party Software

Certain utilities utilize open-source client libraries (such as jsPDF, Mermaid.js, Pyodide) licensed under MIT, Apache, or BSD open licenses. All intellectual property remains with their respective copyright holders.