Unit 3 · Database Management

Chapter 10
Python with MySQL

The last chapter of the course, and the one that joins the other nine — SQL queries written inside a Python program, returning tuples you already know how to handle.

1.1 Where we have got to

Chapter 9

You can write any query the syllabus asks for. But you typed it at a MySQL prompt, read the answer with your eyes, and that was the end of it.

What a program can do that a prompt cannot

Ask a query whose values come from the user
Loop over the answer and compute with it
Write the result to a file or a CSV
Run the same query every night, automatically

Every chapter of this course, in one program

Ch 1 — a row comes back as a tuple; many rows as a list of tuples.
Ch 2 — you wrap each query in a function.
Ch 3 — a database that is down raises an exception.
Ch 4 — you write the results to a CSV.
Ch 7 — you connect to a host over the network.
Ch 9 — the string you send is SQL.

About the outputs in this deck

Every program was run against the same STUDENT and STREAM tables from Chapters 8 and 9, through the same DB-API interface that mysql.connector provides. The tuples shown are the real ones.

1.2 The five steps, every single time

Connect · cursor · execute · fetch · close. Memorise the order — it is a 2-mark question on its own.

Before any of it works

The connector must be installed once, from the command line — not from inside Python:

pip install mysql-connector-python

1.3 The connection, written once

Four connection arguments repeated on every slide would bury the lesson. So we do what Chapter 2 §3.4 called a user-defined module.

db.py — save this onceimport mysql.connector

def connect():
    return mysql.connector.connect(
            host="localhost",
            user="root",
            password="school123",
            database="school")

Why this is worth doing

The password now lives in one file. Change the server, the user or the database and every program in the project follows — the Chapter 2 §2.1 argument for functions, applied to configuration.

every program from here starts like thisfrom db import connect

con = connect()
cur = con.cursor()

# ... the work ...

con.close()

In the exam, write it out in full

A question that says "write a program to connect to the database" wants to see mysql.connector.connect(host=…, user=…, password=…, database=…) on the page. Use db.py in your project and in the practical file; write the full call in the written paper.

2.1 The first complete program

first.pyimport mysql.connector

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

cur = con.cursor()
cur.execute("SELECT RollNo, Name, SCode, Marks, City FROM STUDENT")

row = cur.fetchone()
print(row)
print(type(row))
print(row[1], row[3])

con.close()

What shape does one row come back in? 🤔

Output(101, 'Riya', 'S01', 88, 'Delhi') <class 'tuple'> Riya 88

A row is a tuple — Chapter 1 §7

Ordered, indexable, and immutable. row[1] is the Name, row[3] the Marks. The columns come back in the order you named them in the SELECT, which is why naming them beats SELECT *.

The four connection arguments

`host`	where the server is — `"localhost"` is this machine
`user`	the MySQL username
`password`	that user's password
`database`	which database on that server

Two things worth knowing

① host="localhost" is the Chapter 7 idea — the server could equally be an IP address on another machine, and nothing else in the program would change.
② A DECIMAL column such as Fee comes back as a Decimal object, not an int — which is why this program lists its columns instead of using SELECT *.

2.2 What `connect` and `cursor` are for

Definition

Connection object — represents the open session between your Python program and the MySQL server. It is created by connect() and is what you commit() and close().

Definition

Cursor object — a handle used to execute statements over a connection and to step through the rows they return. It is created by con.cursor().

The analogy

The connection is the phone call to the database. The cursor is your finger moving down the list of results it reads back to you.

con.is_connected()

Returns True while the session is open and False after con.close(). It is useful as a check before running a query in a long-lived program.

Why close() matters

A MySQL server allows a limited number of simultaneous connections. A program that opens one every time and never closes it will eventually be refused — exactly like the unclosed files of Chapter 4 §2.4.

2.3 `fetchall()` — all the rows at once

toppers.pyimport mysql.connector

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

cur.execute("SELECT Name, Marks FROM STUDENT WHERE Marks > 85")
data = cur.fetchall()

print(data)
print(len(data))

for rec in data:
    print(rec[0], rec[1])

con.close()

Chapter 9 said 3 rows. What does Python show? 🤔

Output[('Riya', 88), ('Aarav', 91), ('Meera', 94)] 3 Riya 88 Aarav 91 Meera 94

A list of tuples

fetchall() returns a list, and each item is a tuple — one per row. So data[0] is a row and data[0][1] is a value. Every list operation from Chapter 1 works on it: len, slicing, for, sorted.

Unpack it for readable code

for name, marks in data: unpacks each tuple straight into two names — the tuple unpacking of Chapter 2 §5.4, and much clearer than rec[0] and rec[1].

When not to use it

fetchall() loads every row into memory. For seven students that is nothing; for a million rows it is a problem, and that is what fetchmany() exists for.

2.4 `fetchone`, `fetchmany`, `fetchall`

fetching.pyfrom db import connect

con = connect()
cur = con.cursor()
cur.execute("SELECT Name, City FROM STUDENT")

print(cur.fetchone())
print(cur.fetchmany(3))
print(cur.fetchone())
print(len(cur.fetchall()))
print(cur.fetchall())

Seven students in total. Track the cursor. 🤔

Output('Riya', 'Delhi') [('Aarav', 'Mumbai'), ('Kabir', 'Delhi'), ('Meera', 'Pune')] ('Dev', 'Mumbai') 2 []

Call	Returns	Rows taken	Left
fetchone()	Riya	1	6
fetchmany(3)	Aarav, Kabir, Meera	3	3
fetchone()	Dev	1	2
fetchall()	Ishan, Nisha	2	0
fetchall()	nothing left	0	0

The cursor moves forward and never goes back

This is exactly the file pointer of Chapter 4 §3.2. Each fetch consumes rows; the next fetch continues from there. The final fetchall() returns [] because nothing remains — just as a second read() on a file gives "".

What each returns when empty

fetchone() → None
fetchmany(n) → []
fetchall() → []
So if row is None: is how you test that a search found nothing.

2.5 `rowcount`, and when a search finds nothing

notfound.pyfrom db import connect

con = connect()
cur = con.cursor()
cur.execute("SELECT Name FROM STUDENT WHERE Marks > 90")

print(cur.fetchone())
print(cur.fetchone())
print(cur.fetchone())

Only two students beat 90. What is the third call? 🤔

Output('Aarav',) ('Meera',) None

Read the first two lines carefully

('Aarav',) — with a trailing comma. It is a one-element tuple, because the SELECT asked for one column. Chapter 1 §7.1 warned you about exactly this notation. To get the string itself, write row[0].

cursor.rowcount

The number of rows affected by the last statement.

For INSERT, UPDATE and DELETE it is the number of rows changed — reliable and immediate.

For a SELECT it is the number of rows retrieved once they have been fetched; before that it may be -1.

So for counting a SELECT, use len()

len(cur.fetchall()) is always correct and always clear. Keep rowcount for the DML statements in §3, where it is exactly what you want.

The standard "was anything found?" pattern

from db import connect
con = connect(); cur = con.cursor()

cur.execute("SELECT Name FROM STUDENT WHERE RollNo = 999")
row = cur.fetchone()
if row is None:
    print("No such student")
else:
    print(row[0])

3.1 INSERT, UPDATE, DELETE — and `commit()`

change.pyfrom db import connect

con = connect()
cur = con.cursor()

cur.execute("INSERT INTO STUDENT VALUES "
            "(108,'Tara','S03',82,'Delhi',4000)")
print("rows inserted:", cur.rowcount)
con.commit()

cur.execute("UPDATE STUDENT SET Fee = Fee + 500 "
            "WHERE SCode = 'S02'")
print("rows updated :", cur.rowcount)
con.commit()

cur.execute("DELETE FROM STUDENT WHERE Marks < 60")
print("rows deleted :", cur.rowcount)
con.commit()

cur.execute("SELECT COUNT(*) FROM STUDENT")
print("rows now     :", cur.fetchone()[0])

Four numbers. Start from 7 students. 🤔

Outputrows inserted: 1 rows updated : 3 rows deleted : 1 rows now : 7

Trace the count

7 students, +1 for Tara = 8, then −1 for Dev (59 marks) = 7. Nisha survived the delete because NULL < 60 is not true — Chapter 9 §3.3.

rowcount was 3 for the update because three students are in S02: Kabir, Dev and Nisha.

fetchone()[0]

fetchone() gives the tuple (7,); the [0] pulls the number out of it. This is the standard way to read a single aggregate value.

3.2 What happens without `commit()`

forgot.py — no commit anywherecon = mysql.connector.connect(...)
cur = con.cursor()
cur.execute("INSERT INTO STUDENT VALUES "
            "(110,'Zoya','S03',85,'Delhi',4000)")
print("inserted, not committed")
con.close()

# a fresh connection, later
con = mysql.connector.connect(...)
cur = con.cursor()
cur.execute("SELECT COUNT(*) FROM STUDENT")
print("rows in the table:", cur.fetchone()[0])
con.close()

Is Zoya in the table? 🤔

Outputinserted, not committed rows in the table: 7

Zoya was never saved

Still 7, not 8. The INSERT ran, reported success, and was then discarded when the connection closed without a commit. No error, no warning.

Definition

Transaction — a group of statements treated as one unit. commit() makes them permanent; rollback() undoes them all.

Why a database behaves this way

So that a half-finished job can be abandoned cleanly. Transferring money is two updates — take from one account, add to the other. If the second fails, you want neither to have happened. commit() is you saying "both parts worked; make it real".

The exam rule

SELECT never needs a commit. INSERT, UPDATE and DELETE always do. A missing con.commit() is the most commonly deducted mark in this chapter.

4.1 Queries built from user input

the tempting, wrong wayfrom db import connect
con = connect(); cur = con.cursor()

city = input("City: ")

q = "SELECT Name, Marks FROM STUDENT " \
    "WHERE City = '" + city + "'"
cur.execute(q)
print(cur.fetchall())

It does work — for well-behaved input

Type Delhi and you get the three Delhi students. Which is exactly why the problem goes unnoticed.

the right way — a placeholderfrom db import connect
con = connect(); cur = con.cursor()

city = input("City: ")

cur.execute("SELECT Name, Marks FROM STUDENT "
            "WHERE City = %s", (city,))
print(cur.fetchall())

Output — user types DelhiCity: Delhi [('Riya', 88), ('Kabir', 76), ('Ishan', 67)]

Definition

Parameterised query — a query in which values are replaced by placeholders, with the actual values passed separately as a tuple. The connector inserts them safely, quoting and escaping as needed.

Three details that are marked

① The placeholder in mysql.connector is %s — for every type, including numbers. Never %d.
② There are no quotes around %s, even for text. The connector adds them.
③ The values go in a tuple. One value needs the trailing comma: (city,) — Chapter 1 §7.1 again.

inserting the same wayfrom db import connect
con = connect(); cur = con.cursor()

rec = (109, "Omar", "S01", 73, "Pune", 4500)

cur.execute("INSERT INTO STUDENT VALUES "
            "(%s,%s,%s,%s,%s,%s)", rec)
con.commit()
print("inserted:", cur.rowcount)

Outputinserted: 1

4.2 Why it matters — SQL injection

the attackfrom db import connect
con = connect(); cur = con.cursor()

name = input("Name: ")

q = "SELECT Name, Marks FROM STUDENT " \
    "WHERE Name = '" + name + "'"
print(q)
cur.execute(q)
print(len(cur.fetchall()), "rows returned")

The user types: Riya' OR '1'='1 🤔

OutputName: Riya' OR '1'='1 SELECT Name, Marks FROM STUDENT WHERE Name = 'Riya' OR '1'='1' 7 rows returned

Read the query that was actually sent

The user's quote closed the string early, and everything after it became part of the SQL. '1'='1' is always true, so the WHERE matched every row — all seven students, not one.

The same trick with '; DROP TABLE STUDENT; -- does rather more damage.

the same input, parameterisedfrom db import connect
con = connect(); cur = con.cursor()

cur.execute("SELECT Name, Marks FROM STUDENT "
            "WHERE Name = %s", (name,))
print(len(cur.fetchall()), "rows returned")

Output0 rows returned

Zero — and that is correct

The whole input was treated as one name to look for. No student is called Riya' OR '1'='1, so nothing matched. The value never became code.

Definition

SQL injection — an attack in which input supplied by a user is interpreted as part of an SQL statement, letting the attacker read, alter or destroy data. It is prevented by using parameterised queries.

5.1 A complete, well-written program

toppers.pyimport mysql.connector

def show_toppers(limit):
    con = mysql.connector.connect(
            host="localhost", user="root",
            password="school123", database="school")
    cur = con.cursor()
    cur.execute("SELECT Name, Marks FROM STUDENT "
                "WHERE Marks > %s ORDER BY Marks DESC",
                (limit,))
    rows = cur.fetchall()
    con.close()
    return rows

data = show_toppers(70)
print(data)
print("count:", len(data))

for name, marks in data:
    print(name, "->", marks)

Who scores above 70, and in what order? 🤔

Output[('Meera', 94), ('Aarav', 91), ('Riya', 88), ('Kabir', 76)] count: 4 Meera -> 94 Aarav -> 91 Riya -> 88 Kabir -> 76

Five good decisions in fifteen lines

① the query lives in a function that can be reused — Ch 2
② the value is parameterised, not concatenated — §4.1
③ the function returns the data rather than printing it, so the caller decides what to do — Ch 2 §5.2
④ ORDER BY is done by the database, not by Python — Ch 9
⑤ the connection is closed before returning

Nisha is absent from the result

Her Marks is NULL, and NULL > 70 is not true. The Chapter 9 NULL rule reaches all the way into the Python output.

5.2 Guarding it — Chapter 3, used for real

safe.pyimport mysql.connector

con = None
try:
    con = mysql.connector.connect(
            host="localhost", user="root",
            password="school123", database="school")
    cur = con.cursor()
    cur.execute("SELECT Name FROM STUDENT WHERE City = %s",
                ("Delhi",))
    for row in cur.fetchall():
        print(row[0])

except mysql.connector.Error as e:
    print("Database error:", e)

finally:
    if con is not None:
        con.close()
    print("connection closed")

Output — when all is wellRiya Kabir Ishan connection closed

This is Chapter 3 §6.2 and Chapter 4 §2.6

The risky statement is in try; the cleanup is in finally, so the connection is closed whether the query succeeded, failed, or the server was never reachable at all.

What can actually go wrong here

The server may be down, the password wrong, the database absent, or the SQL itself invalid. Every one of them raises mysql.connector.Error — and without the handler the program dies with a traceback in front of the user.

Why con = None first

If connect() is what failed, the name con would never have been bound, and finally would raise NameError while trying to close it. Setting it to None first makes the if test possible.

6.1 Everything on one screen

The five steps

import mysql.connector connect(host, user, password, database) con.cursor() cur.execute(sql) fetch con.close()

Fetching

fetchone() → one tuple, or None fetchmany(n) → a list of n tuples fetchall() → a list of all remaining the cursor only moves forward ('Aarav',) — one column is still a tuple len(fetchall()) to count

Changing data

con.commit() after INSERT / UPDATE / DELETE no commit = silently discarded cur.rowcount = rows affected rollback() undoes

Safety

%s placeholders, never string joining values passed as a tuple — (city,) SQL injection try / except mysql.connector.Error / finally

The three sentences worth the most marks

“A cursor is an object used to execute SQL statements and to traverse the rows returned by them.”
“fetchone() returns a single record as a tuple, or None; fetchall() returns all remaining records as a list of tuples.”
“commit() must be called after INSERT, UPDATE or DELETE, otherwise the changes are not saved to the database.”

6.2 Exam question 1 — complete the program

4 marks

Fill in the four blanks so the program displays the name and marks of every student of stream S01.

import mysql.connector

con = mysql.connector.____(host="localhost", user="root",
        password="school123", database="school")   # (i)

cur = con.____()                                        # (ii)

cur.execute("SELECT Name, Marks FROM STUDENT WHERE SCode = 'S01'")

data = cur.____()                                       # (iii)

for rec in data:
    print(rec[0], rec[1])

con.____()                                              # (iv)

Are you ready for the answer? 🤔

Answer

(i) connect · (ii) cursor · (iii) fetchall · (iv) close

OutputRiya 88 Aarav 91 Meera 94

fetchone would also be legal Python at (iii), but the for loop below expects a list of records — so fetchall is the only answer that makes the program work.

6.3 Exam question 2 — write the program

4 marks

Write a function add_student() that reads a roll number, name, stream code, marks and city from the user and inserts the record into the STUDENT table, then displays how many rows were added. Use a parameterised query.

Two things must appear or you lose marks. 🤔

Answer

add.pyimport mysql.connector

def add_student():
    con = mysql.connector.connect(
            host="localhost", user="root",
            password="school123", database="school")
    cur = con.cursor()

    r = int(input("Roll no: "))
    n = input("Name   : ")
    s = input("Stream : ")
    m = int(input("Marks  : "))
    c = input("City   : ")

    cur.execute("INSERT INTO STUDENT "
                "(RollNo, Name, SCode, Marks, City) "
                "VALUES (%s, %s, %s, %s, %s)",
                (r, n, s, m, c))
    con.commit()
    print("Rows added:", cur.rowcount)
    con.close()

add_student()

OutputRoll no: 111 Name : Zoya Stream : S03 Marks : 85 City : Delhi Rows added: 1

Where the four marks are

① connect and cursor correctly.
② %s placeholders with the values in a tuple — not string concatenation.
③ con.commit() — the most commonly missed mark in the whole chapter.
④ cur.rowcount and con.close().

Also note

int() around the roll number and marks — input() returns a string, exactly as Chapter 1 §2.8 said. Fee is omitted, so its DEFAULT from Chapter 9 §2.1 supplies it.

6.4 Exam question 3 — theory

2 + 2 + 2 marks

What is a cursor? Name any two of its methods.
Differentiate between fetchone() and fetchall().
Why is commit() needed? What happens if it is omitted?

Are you ready for the answer? 🤔

Answer 1

A cursor is an object created from a database connection that is used to execute SQL statements and to traverse the records returned by a query. It keeps track of the current position in the result set.

Methods: execute(), fetchone(), fetchall(), fetchmany(); and the attribute rowcount.

Answer 2

fetchone() returns the next single record as a tuple, and returns None when no rows remain.

fetchall() returns all the remaining records as a list of tuples, and returns an empty list [] if none remain.

Answer 3

commit() makes permanent the changes made by INSERT, UPDATE and DELETE statements. Until it is called those changes exist only inside the current transaction.

If it is omitted, the changes are discarded when the connection closes — the database is left unaltered, and no error message is produced, which is what makes the mistake so hard to find.

6.5 One last challenge

Challenge

This program is meant to print the names of Delhi students and then their count. Find three faults and predict what it actually prints.

1import mysql.connector
2con = mysql.connector.connect(host="localhost", user="root",
3        password="school123", database="school")
4cur = con.cursor()
5city = "Delhi"
6cur.execute("SELECT Name FROM STUDENT WHERE City = " + city)
7for row in cur.fetchall():
8    print(row)
9print("Count:", len(cur.fetchall()))

Three faults. Take your time. 🤔

Answer

Line	Fault	Fix
6	the value is concatenated, and with no quotes — MySQL reads `Delhi` as a column name and raises an error	use `WHERE City = %s` with `(city,)`
8	prints the whole tuple, so the output is `('Riya',)`, not `Riya`	`print(row[0])`
9	the second `fetchall()` returns `[]` — line 7 already consumed every row	store `rows = cur.fetchall()` once, then use it twice

If line 6 is corrected but 8 and 9 are not, it prints:

Output('Riya',) ('Kabir',) ('Ishan',) Count: 0

Count: 0 is the interesting one — three students were found, and the count still says zero, because the cursor had already been exhausted. That is the Chapter 4 file-pointer lesson arriving in the very last program of the course.

End of the course

Ten chapters,
one program

The last program in this deck reads a tuple from a database over a network, inside a function, guarded by exception handling — and could write the result to a CSV or push it onto a stack.

What to revise, in order of marks

① Predict the output — Chapters 1–5. The cheapest marks in the paper and the ones most often lost.
② Write the SQL — Chapter 9. Six one-mark queries appear every year.
③ Draw and justify — Chapters 6–7. The case study is five marks and the reason is always the mark.
④ Write the program — Chapters 2, 4, 5 and 10, which is also the whole practical file.