🗄️ Unit 3 – Database & SQL · Lesson 24
Python–MySQL Connectivity
Why connect Python to MySQL?
Real applications – school ERP, railway booking, shopping apps – have a program as the front end and a database as the back end. Python can send SQL to MySQL and use the results.
Installing the connector
Command prompt
pip install mysql-connector-python
Then use import mysql.connector (often as mc). Use your own MySQL user name and password in connect().
Steps of a database program
- 1import mysql.connector
- 2con = connect(host, user, passwd, database)
- 3cur = con.cursor()
- 4cur.execute("SQL query")
- 5SELECT → fetchall()/fetchone(); changes → con.commit()
- 6con.close()
Connect and create a table
Python
import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123')
if con.is_connected():
print('Connected to MySQL')
cur = con.cursor()
cur.execute('CREATE DATABASE IF NOT EXISTS school')
cur.execute('USE school')
cur.execute('DROP TABLE IF EXISTS student')
cur.execute('''CREATE TABLE student (
roll INT PRIMARY KEY, name VARCHAR(20),
stream VARCHAR(10), marks INT)''')
print('Table created')
con.close()Connected to MySQL
Table created
Insert records & commit()
Python
import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123', database='school')
cur = con.cursor()
rows = [(1, 'Anika', 'Science', 92), (2, 'Dev', 'Commerce', 78),
(3, 'Farhan', 'Science', 64), (4, 'Gauri', 'Arts', 88)]
for r in rows:
q = "INSERT INTO student VALUES ({}, '{}', '{}', {})".format(*r)
cur.execute(q)
con.commit() # save changes permanently
print(len(rows), 'records inserted')
con.close()4 records inserted
Fetching results
Python
import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123', database='school')
cur = con.cursor()
cur.execute('SELECT * FROM student')
data = cur.fetchall() # list of tuples
print('Rows fetched:', cur.rowcount)
for roll, name, stream, marks in data:
print(roll, name.ljust(7), stream.ljust(9), marks)
con.close()Rows fetched: 4
1 Anika Science 92
2 Dev Commerce 78
3 Farhan Science 64
4 Gauri Arts 88
Python
import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123', database='school')
cur = con.cursor()
cur.execute('SELECT name, marks FROM student ORDER BY marks DESC')
print('fetchone :', cur.fetchone())
print('fetchmany:', cur.fetchmany(2))
print('rest :', cur.fetchall())
print('rowcount :', cur.rowcount) # rows read so far
con.close()fetchone : ('Anika', 92)
fetchmany: [('Gauri', 88), ('Dev', 78)]
rest : [('Farhan', 64)]
rowcount : 4
Queries with user input (%s)
Pass values separately using %s placeholders – the connector adds quotes correctly and protects against SQL injection. The format() / f-string style seen above also appears in board papers.
Python
import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123', database='school')
cur = con.cursor()
stream = input('Stream to search: ')
cur.execute('SELECT roll, name, marks FROM student WHERE stream = %s', (stream,))
for row in cur.fetchall():
print(row)
con.close()Stream to search: Science
(1, 'Anika', 92)
(3, 'Farhan', 64)
UPDATE and DELETE
Python
import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123', database='school')
cur = con.cursor()
roll = int(input('Roll to update: '))
new = int(input('New marks: '))
cur.execute('UPDATE student SET marks = %s WHERE roll = %s', (new, roll))
con.commit()
print(cur.rowcount, 'row(s) updated')
cur.execute('SELECT * FROM student WHERE roll = %s', (roll,))
print(cur.fetchone())
con.close()Roll to update: 3
New marks: 70
1 row(s) updated
(3, 'Farhan', 'Science', 70)
Python
import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123', database='school')
cur = con.cursor()
cur.execute('DELETE FROM student WHERE marks < %s', (75,))
con.commit()
print('Deleted:', cur.rowcount)
cur.execute('SELECT COUNT(*), AVG(marks) FROM student')
cnt, avg = cur.fetchone()
print('Left:', cnt, '| average:', round(float(avg), 2))
con.close()Deleted: 1
Left: 3 | average: 86.0
Complete program with error handling
Python
import mysql.connector as mc
def connect():
return mc.connect(host='localhost', user='root', passwd='root123', database='school')
def add(roll, name, stream, marks):
con = connect(); cur = con.cursor()
try:
cur.execute('INSERT INTO student VALUES (%s, %s, %s, %s)', (roll, name, stream, marks))
con.commit(); print('Added', name)
except mc.Error as e:
print('Error:', e.msg)
con.close()
def show():
con = connect(); cur = con.cursor()
cur.execute('SELECT * FROM student ORDER BY roll')
for r in cur.fetchall():
print(r)
con.close()
add(5, 'Harsh', 'Commerce', 81)
add(1, 'Copy', 'Arts', 50) # duplicate primary key
show()Added Harsh
Error: Duplicate entry '1' for key 'PRIMARY'
(1, 'Anika', 'Science', 92)
(2, 'Dev', 'Commerce', 78)
(4, 'Gauri', 'Arts', 88)
(5, 'Harsh', 'Commerce', 81)
Method summary
| Method / attribute | Purpose |
|---|---|
| mysql.connector.connect(host, user, passwd, database) | Open a connection |
| con.is_connected() | True if connected |
| con.cursor() | Create a cursor to run queries |
| cur.execute(query, params) | Run one SQL statement |
| con.commit() | Save INSERT/UPDATE/DELETE |
| cur.fetchone() / fetchmany(n) / fetchall() | Read result rows |
| cur.rowcount | Rows fetched / affected |
| con.close() | Close the connection |
Frequently Asked Questions
Why is commit() needed?
INSERT, UPDATE and DELETE are kept in a transaction. commit() saves them permanently; without it the changes are lost when the connection closes.
What is the difference between fetchone(), fetchmany() and fetchall()?
fetchone() returns the next row as a tuple (or None), fetchmany(n) returns a list of the next n rows, and fetchall() returns a list of all remaining rows.
What does rowcount give?
For SELECT, the number of rows fetched so far; for INSERT/UPDATE/DELETE, the number of rows affected.
