Is a generator-like database query on sqlalchemy possible?

Question

I am currently querying my database as follows:

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

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

Is it possible to run the script endlessly (or as long as there are authors)? I mean, if you can turn

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

into some generator that the next author gives , for which queried==0 true .

Answer 1

Request objects can be considered as as-is iterators. SQL will be launched after 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 handling API; you cannot just execute a query that runs forever and splashes out every new row as it is added to the table. I would like it to be possible, but it is not.

If you intend to detect new rows, you will have to regularly poll the same query and create an indicator in your data model that allows you to tell you which rows are new. It seems you present this now with a queried column. In this case, in the for loop above you can set author.queried = 1 and session.add(author) . But you cannot session.commit() inside the loop.

Answer 2

Since the query is converted to the equivalent SQL SELECT statement, you can only get the Author rowset, where queried is 0, which existed when this transaction started. Any updates to the Queried Author column will not change the current SELECT set.

If you want to continue processing all Author strings, even if there are more than 1000, you can do

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

The __iter__ method of the __iter__ object will be called automatically, which will return the same iterator as the instances call of the Query object.