Worksheets › Class 12 › Interface Python with MySQL › Cheat sheet
Class 12 Interface Python with MySQL: cheat sheet
CBSE Computer Science. 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 |
Watch out
- Leaving out commit() after INSERT, UPDATE or DELETE: the change is not saved.
- Passing one value as (80) instead of the tuple (80,).
- Expecting fetchone() to give a list: it gives one tuple.
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
Practise Interface Python with MySQL
Revise with this page, then practise: every Interface Python with MySQL sheet in the app is new, with an answer key and working.
Coming soon for Android and iPhone.