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.