Is there an advantage to a database access generator function?

Question

Is there an advantage to writing the following generator function over just calling .fetchone() in a while loop? If so, what would that advantage be?

Thanks.

def testf2():
    db = connectToMysqlDB(None)

    sql_statement = " ".join([
        "select d.* ",
        "from dr_snapshot d ",
        "order by d.PremiseID asc, d.last_read asc; "])

    sel_cur = db.cursor()
    rc = sel_cur.execute(sql_statement)

    loop_ok = True
    while loop_ok:
        meter_row = sel_cur.fetchone()
        if meter_row:
            yield meter_row
        else:
            loop_ok = False

    yield None

for read_val in testf2():
   print(read_val)
   #Perform something useful other than print.

('610159000', 6, datetime.datetime(2012, 7, 25, 23, 0), 431900L, 80598726L)
('610160000', 6, datetime.datetime(2012, 7, 25, 23, 0), 101200L, 80581200L)
None

Answer 1

Nope. Functionally they are the same, but there are advantages to wrapping it in a generator if you want to reuse the code. For example, you could add code to close the connection/cursor after it is done reading in the generator block. I recommend that you add this to your code above so that the cursors are closed.

def testf2():
    try:
        db = connectToMysqlDB(None)

        sql_statement = " ".join([
            "select d.* ",
            "from dr_snapshot d ",
            "order by d.PremiseID asc, d.last_read asc; "])

        sel_cur = db.cursor()
        rc = sel_cur.execute(sql_statement)

        loop_ok = True
        while loop_ok:
            meter_row = sel_cur.fetchone()
            if meter_row:
                yield meter_row
            else:
                loop_ok = False
    except ProgrammingError:
        print "Tried to read a cursor after it was already closed"
    finally:
        sel_cur.close()

That would make it easier to reuse, since you'd only have to get the connection management right in one place.

Answer 2

Looks like I was right, mySQL cursors are iterable ( https://stackoverflow.com/a/1808414/138772). So you can just do this instead of your while loop (but I like the idea of putting the database-accessing code inside the generator function, so keep that):

for meter_row in sel_cur:
    yield meter_row

Note also that you probably don't want that final yield None; The StopIteration exception is used to indicate exhaustion of iterator output, and is what for loops use as their flag to stop looping, so by including the yield None you end up with that None at the end of your output for no real gain.