Python MySQLdb: Iterating over a cursor

Question

In another post, this code:

connection = MySQLdb.connect(...)
cursor = connection.cursor()
cursor.execute("SHOW TABLES")
for (table_name,) in cursor:
    print(table_name)

correctly iterates over the table names in the cursor whereas this code:

for table_name in cursor:
    print(table_name)

returns elements in the form:

('some_table',)

After much searching, I have been unable to make sense of this. Could someone explain the difference? I cannot figure out exactly what execute() is returning. Also, I cannot figure out why the form of the first iterator -- using parentheses and a comma -- works.

Answer 1

In itself, execute() doesn't return anything. Once you've executed a query, you get the data back from the query as tuples when you iterate over the cursor.

Your query returns only one column, so you get 1-tuples.

1-tuples in Python look a bit odd. () is an empty tuple, and (1, 2) is a 2-tuple, but (1) is just the digit 1 in parentheses, not a tuple. 1-tuples such as (1,) must therefore have the trailing comma in order to be recognised as tuples.

If you ran a query that selected three columns, you could read the three values out from each row using something like the following:

cursor.execute("SELECT a, b, c FROM some_table")
for (a_value, b_value, c_value) in cursor:
    # do stuff...

Your first code is doing the same, but it's unpacking 1-tuples instead of 3-tuples.

On the other hand, your second code is simply iterating over what comes out of the cursor, i.e. the 1-tuples, without doing any unpacking.