How to use Python list comprehension (or such) for retrieving rows when using MySQLdb?

Question

Hey all,

I use MySQLdb a lot when dealing with my webserver. I often find myself repeating the lines:

row = cursor.fetchone()
while row:
    do_processing(row)
    row = cursor.fetchone()

Somehow this strikes me as somewhat un-pythonic. Is there a better, one-line way to accomplish the same thing, along the lines of inline assignment in C:

while (row = do_fetch()) { 
    do_processing(row); 
} 

I've tried figuring out the syntax using list comprehensions, but I can't seem to figure it out. Any recommendations?

Thanks,

Erik

Answer 1

There isn't really a way to get list comprehensions involved in this. You need a loop that terminates when a sentinel value is returned. Fortunately, Python does provide this:

for row in iter(cursor.fetchone, None):
    process(row)

The two-argument iter() takes a callable, and a sentinel value that will terminate the iteration. It is a little isoteric, so few people will complain if you instead use the still somewhat more common:

while True:
    row = cursor.fetchone()
    if row is None:
        break
    process(row)

(And indeed that style was common even for reading a file line-by-line, before we had generalized iteration.)

Some DB-API modules (including apparently now MySQLdb) also make cursors directly iterable, like so:

for row in cursor:
    process(row)

That's obviously the prettiest solution, when available :)

Answer 2

What about:

for row in cursor.fetchall():
    # do something with row

Answer 3

MySQLdb cursors are iterable:

cursor.execute(sql,args)
for row in cursor:
    do_processing(row)

Answer 4

SQLalchemy has a bundle of methods that provide more pythonic construction for a goodly number of your use cases - including the production of iterables that are "lazy" in their database accesses.