Gino: Limiting select query results (slices/pagination)

[aprilmay]

• GINO version: 0.7.3
• Python version: 3.6
• Operating System: Linux

Description

Setting a limit (subset) for the results of a select query might be easily implemented server side with SQL's offset and limit.

SQLAlquemy makes use of Python's list slicing (q[:50]).

This can also be used for pagination (for example: http://flask-sqlalchemy.pocoo.org/2.3/api/#flask_sqlalchemy.BaseQuery.paginate) .

[fantix]

Hi, thanks for the message!

Currently there are two ways to do result slicing - thanks to SQLAlchemy and asyncpg:

1. Like you said, to use SQL's offset and limit:

SomeModel.query.limit(10).offset(80)

1. To use server-side cursor:

async with db.transaction():
    cursor = await SomeModel.query.gino.iterate()
    await cursor.forward(80)  # skip 80 rows
    instances = await cursor.many(10)  # and retrieve next 10 rows

[aprilmay]

Thanks a lot!
It's straightforward. Maybe a quick addition to the documentation would help others who, like me, are a bit confused with how SqlAlchemy models and query engine integrate?

Btw, i really appreciate working with Gino. I'm migrating a Flask/Flask-SqlAlchemy application to Sanic/Gino, and so far i like Gino's pragmatic approach to ORM. I feel i'm more in control with async, and Gino is in that line. Sometime, like in this request, it also shows my own limitations of understanding ;)

[fantix]

Glad that you like it! Yeah I'm thinking adding a new Guide into the docs, because current tutorial just give a glance, and the rest are more topic-specific. Keep updated 😃