[Sqlalchemy-tickets] [sqlalchemy] #2826: New recipe -- solution to select through large number of rows for SQLITE

[sqlalchemy <mi...@zz...>]

#2826: New recipe -- solution to select through large number of rows for SQLITE
-------------------------+-----------------------------------------
 Reporter:  jamercee     |           Owner:  zzzeek
     Type:  enhancement  |          Status:  new
 Priority:  low          |       Milestone:  0.8.xx
Component:  sqlite       |        Severity:  no triage selected yet
 Keywords:               |  Progress State:  awaiting triage
-------------------------+-----------------------------------------
 Sqlite does not support the window/range function described in this recipe

 [http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery]

 We have developed a solution along the same lines using LIMIT/OFFSET, and
 wanted to share this back with the community.

 {{{#!python
 def _yield_limit(qry, pk, maxrq=1000):
     r"""specialized windowed query generator (using LIMIT/OFFSET)

     This recipe is to select through a large number of rows thats too
     large to fetch at once. The technique depends on the primary key
     of the FROM clause being an integer value, and selects items
     surrounded with LIMIT/OFFSET"""

     key = pk.property.key
     nextid = qry.session.query(pk).order_by(key).\
                 limit(1).scalar()

     if nextid is None:
         return

     while 1:
         count = 0
         for rec in qry.filter(pk >= nextid).limit(maxrq):
             # Retrieve the key value before yielding as the
             # caller could delete the object.
             nextid = rec.__getattribute__(key) + 1
             count += 1
             yield rec

         # If we received less than the number of items we requested
         # we have reached the end.

         if count != maxrq:
             return

 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2826>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python