Is generator-like querying of a database by sqlalchemy possible?

Question

At the moment I query my database this way:

for author in session.query(Author).filter(Author.queried==0).slice(0, 1000):
    print "Processing:", author
    # do stuff and commit later on

This means every 1000 authors I have to restart the script.

Is it possible to make the script run endless (or as long as there are authors)? I mean by that, if it is possible to turn

session.query(Author).filter(Author.queried==0).slice(0, 1000)

into some sort of generator which yields the next author for which queried==0 is true.

Answer 1

Query objects can be treated as iterators as-is. SQL will be executed once you start using data from the query iterator. Example:

for author in session.query(Author).filter(Author.queried==0):
    print "Processing: ", author

Your question uses the word "endlessly", so a word of caution. SQL is not an event-processing API; you cannot simply do a query that runs "forever" and spits out each new row as it is added to a table. I wish that were possible, but it isn't.

If your intent is to detect new rows, you will have to poll regularly with the same query, and design an indicator into your data model that allows you to tell you which rows are new. You seem to be representing that now with a queried column. In that case, within the for loop above you might set author.queried = 1 and session.add(author). But you can't session.commit() inside the loop.