Cursor, execute() & fetchingOptional for Informatics Practices

1What a cursor is

Think of the resultset as a list of rows sitting on the server, and the cursor as a finger pointing at your place in it. Every time you fetch, the finger moves down. It never goes back.

cur = con.cursor()
cur.execute("select id, name, percent from students where grade = 12")

execute() sends the SQL and the server works out the answer — but nothing is printed and nothing comes back into your program yet. The rows are waiting; you have to ask for them.

2fetchone() — one row

Four students are in grade 12. Here is the whole program and exactly what it printed:

import mysql.connector

con = mysql.connector.connect(
    host="localhost", port=3307,
    user="student", passwd="kv@1234", database="lambdalab")
cur = con.cursor()

cur.execute("select id, name, percent from students where grade = 12")
print("fetchone():", cur.fetchone())
print("fetchall():", cur.fetchall())
print("rowcount:", cur.rowcount)

cur.execute("select count(*) from students")
print("the count:", cur.fetchone())

cur.execute("select id, name from students where grade = 9")
for row in cur.fetchall():
    print("row ->", row)

con.close()

fetchone(): (1187, 'Anjali', 91.0)
fetchall(): [(1250, 'Sohan', 66.0), (2035, 'Ravi', 83.75), (2500, 'Bhim', None)]
rowcount: 4
the count: (12,)
row -> (3301, 'Farhan')
row -> (3612, 'Nima')

fetchone() returned one tuple — one row, with the columns in the order you selected them. Not a list, not a string: a tuple, which you index like any other.

3The trap: fetchall() returns what is LEFT

Read those first two lines of output together. There are four grade-12 students, but fetchall() returned only three.

4fetchall() — a list of tuples

fetchall() gives a list, and each item in it is a tuple. That is why looping over it works so neatly:

cur.execute("select id, name from students where grade = 9")
for row in cur.fetchall():
    print("row ->", row)

row -> (3301, 'Farhan')
row -> (3612, 'Nima')

Each row is a tuple, so row[0] is the id and row[1] is the name. Printing them separately is just ordinary Python: print(row[1], "has id", row[0]).

5NULL comes back as None

Look at Bhim: (2500, 'Bhim', None). His percentage is NULL in the table, and the connector hands it to Python as None — the nearest Python equivalent, and the right one.

So a program that adds up marks must expect it. None + 5 raises a TypeError, exactly as NULL + 5 gave NULL in SQL. Test with if row[2] is not None: before doing arithmetic.

6rowcount

cur.rowcount is 4 in the output above — the number of rows the query produced, counting the one that fetchone() took as well as the three from fetchall().

It is an attribute, not a method, so there are no brackets: cur.rowcount, never cur.rowcount(). And it is only meaningful after the rows have been fetched.

7Recap