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

likely just that its shorter to say `for read in testf2():doSomething` than doing that whole statement ... if its only used in one place than you are not really getting much but if you are doing that query repeatedly in your code then putting it in a function conforms better to DRY — Joran Beasley, Jul 27 '12 at 16:08
Does the python wrapper for mySQL not follow the lead of the `sqlite3` module and implement cursors as iterables themselves, allowing you to do `for meter_row in sel_cur:`? — JAB, Jul 27 '12 at 17:17

Wulfram · Accepted Answer · 2012-07-27T17:47:22.117

5

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.

edited Jul 27 '12 at 17:47

answered Jul 27 '12 at 17:18

Wulfram

3,292
2
15
11

What happens when a generator object is garbage collected before it is exhausted (e.g. an exception raises in the loop body)? Wouldn't it be better to wrap the cursor in an iterable that implements `__del__()`? – André Caron Jul 27 '12 at 17:26
@AndréCaron http://docs.python.org/release/2.5/whatsnew/pep-342.html "The addition of the `close()` method has one side effect that isn't obvious. `close()` is called when a generator is garbage-collected, so this means the generator's code gets one last chance to run before the generator is destroyed. This last chance means that `try...finally` statements in generators can now be guaranteed to work; the `finally` clause will now always get a chance to run." No need for `__del__()`. – JAB Jul 27 '12 at 17:32
@JAB: You're taking about the generator's `close()` method which can be invoked on the client side. If the generator is used as in `for item in generator`, then the `.close()` method is not called (at least, I can't find any statement supporting that in the documentation on [iterator types](http://docs.python.org/library/stdtypes.html#iterator-types)). I've never seen code trying to close an iterable (or generator). I know the object will still be collected and eventually, `__del__()` will be called, but it's better to make sure it's done on time. – André Caron Jul 27 '12 at 17:43
1

Looks like close() does get called after garbage collection. It is interesting how it works though, it is an implicit function that will raise an error. See http://www.python.org/dev/peps/pep-0342/ for an example. I've updated my answer to take this into account in order to close the cursor connection. – Wulfram Jul 27 '12 at 17:54
Interesting enough, __del__() is a wrapper for close(), so it looks like you are both right :). – Wulfram Jul 27 '12 at 18:03
@AndréCaron Your suggestion means doing this in a class? Could you show an example? – octopusgrabbus Jul 27 '12 at 18:22
You do not need to do it in a class. The yield function adds functions to the parent generator function, which will raise an error when the generator is garbage collected. The finally block will catch the error and close the connection. – Wulfram Jul 27 '12 at 18:26

score 4 · Answer 2 · edited May 23 '17 at 12:02

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.

score 0 · Answer 3 · answered Jul 27 '12 at 17:35

0

Using a generator gives you extra flexibility to use the results in existing code. For example you could pass it straight to a csv.writer writerows function.

answered Jul 27 '12 at 17:35

Mark Ransom

299,747
42
398
622

Is there an advantage to a database access generator function?

3 Answers3