Class 12 Interface Python with MySQL

Connecting Python to MySQL: connect(), cursor(), execute(), commit(), fetchone(), fetchall() and rowcount, with %s placeholders and format().

Key ideas

The steps: import mysql.connector, connect() to the server and database, make a cursor(), execute() the query, fetch the rows, then close().

execute() sends one SQL statement; values go in through %s placeholders and a tuple, or into {} places with format().

fetchone() gives the next row as a tuple, or None when no row is left; fetchall() gives all the remaining rows as a list of tuples.

rowcount gives the number of rows fetched so far by a SELECT, or changed by the last INSERT, UPDATE or DELETE.

An INSERT, UPDATE or DELETE is saved only after commit() on the connection.

Remember

The steps

import → connect() → cursor() → execute() → fetchone() or fetchall() → close()

A query with a value

q = 'SELECT Name FROM STUDENT WHERE Marks > %s'
cur.execute(q, (80,))
for row in cur.fetchall():
print(row[0])

One value

the tuple (80,) needs its comma

format()

"SELECT * FROM STUDENT WHERE Marks > {}".format(m)

Saving a change

cur.execute(an UPDATE), then con.commit(); cur.rowcount = rows changed

After fetchall()

len(rows) equals cur.rowcount; fetchone()[0] is the first column of a row

Worked example

Table: STUDENT
RollNo | Name | Sec | City | Marks
201 | Priya | C | Delhi | 58
202 | Meera | C | Delhi | 76
203 | Ananya | B | Indore | 69
204 | Rahul | C | Delhi | 80
205 | Kabir | C | Jaipur | 65
206 | Isha | C | Jaipur | 94
207 | Rohan | C | Indore | 41
The program runs cur.execute("SELECT Name, Marks FROM STUDENT WHERE Marks BETWEEN 58 AND 80 ORDER BY Marks DESC"), then calls cur.fetchone() 3 times, then data = cur.fetchall(). What is len(data)?

Working: 5 rows in the result; 3 already fetched

Answer: 2

Interface Python with MySQL

Key ideas

Remember

Watch out

Worked example