djrobstep / sqlakeyset Goto Github PK
View Code? Open in Web Editor NEWoffset-free paging for sqlalchemy
License: The Unlicense
offset-free paging for sqlalchemy
License: The Unlicense
Hello.
I'm getting error using this library with python 3.5 due to the usage of fstring.
It will be better to either not use fstring or clearly documentate that python 3.6+ is required.
../../../virtualenv/python3.5.7/lib/python3.5/site-packages/sqlakeyset/paging.py:6: in <module>
from .columns import parse_clause, find_order_key
E File "/home/travis/virtualenv/python3.5.7/lib/python3.5/site-packages/sqlakeyset/columns.py", line 43
E f"Ordering by nullable column {x} can cause rows to be "
E ^
E SyntaxError: invalid syntax
Hey, thanks for the work on this. It's pretty cool.
This issue is to ask about the future. Sadly this package is the only keyset pagination lib for sqlalchemy i can find on the internets. And it also seems like you might be done with it (not judging, i know all about being too busy).
So i just wanted to verify your thoughts before i figure out my next strat.
Thanks.
Currently we can only retrieve the next or previous "bookmark".
As an extension of this, it would be nice to be able to retrieve the "bookmark" of every item in a page or have a utility function (I am unsure if one already exists?) to obtain the "bookmark" for a specific item in a page.
The use-case here is for satisfying Relay-style cursor pagination where every node has a unique cursor (see also https://github.com/photocrowd/django-cursor-pagination).
Hey @djrobstep, looks like PyPI has turned off username/password auth so the publish
step of the CD is broken: https://app.circleci.com/pipelines/github/djrobstep/sqlakeyset/352/workflows/dd344fcb-f4b7-4d87-99d6-3d954a88d230/jobs/4321
Looks like we need to switch over to OIDC or an API token. I'd give it a shot myself but don't have admin access to this repo or maintainer status on PyPI.
Cheers
I'm using the latest version of 2.0.1691149549.
When running the following code:
query = select(Something)
page = await select_page(session, query, page=page_token, per_page=limit)
I get a list of length-1 tuples instead of a list of ORM objects. Print shows:
[(<app.models.Something object at 0x106ff7580>,)]
This causes some issues with Pydantic's orm_mode validation.
I'm expecting to get:
[<app.models.Something object at 0x106ff7580>]
When adding a calculated field with a custom label, if we try to sort by that column we get an error saying known column in field list
and when looking in to the query dump, the order by column name has being replaced with _sqlakeyset_oc_2 DESC
An example query
SELECT
products.name,
products.sku
(
SELECT
sum(lines.qty)
FROM
lines
JOIN
lines.sku ON products.sku
WHERE
lines.sku = products.sku
) AS allocated_qty
FROM
products
GROUP BY
products.id
ORDER BY
allocated_qty DESC,
products.id
In SQLAlchemy we add the orer by clause like
query.order_by(literal_column("allocated_qty").desc())
This gets translated into
SELECT
products.name,
products.sku
(
SELECT
sum(lines.qty)
FROM
lines
JOIN
lines.sku ON products.sku
WHERE
lines.sku = products.sku
) AS allocated_qty
FROM
products
GROUP BY
products.id
ORDER BY
_sqlakeyset_oc_2 DESC,
products.id
The official explanation of Sqlalchemy is as follows: The composite IN construct is not supported by all backends, and is currently known to work on PostgreSQL, MySQL, and SQLite. Unsupported backends will raise a subclass of DBAPIError when such an expression is invoked.
Using the sa.UUID as the column type breaks the pagination:
sqlalchemy.exc.StatementError: (builtins.AttributeError) 'str' object has no attribute 'hex'
The issue happens in the pair_for_comparison
function when it applies the preprocessing:
value = compval.type.bind_processor(dialect)(value)
resulting in the UUID being converted into a str prematurely.
It must pass the uuid.UUID value directly when using the sa.UUID as the column type.
Commenting out the line value = compval.type.bind_processor(dialect)(value)
resolves the issue.
Hi! I have and issue when the library is trying to get the associated dialect when doing get_page
We have a custom session that deals with a read and write engine, so we can lock to a write when needed. This switch is doen by overriding the get_bind
method based on some context.
The problem is that the sqlalkeyset is doing
if place:
dialect = getattr(s, "bind", s).dialect
So that is returning None for us. I was able to fix that by doing
maker = sessionmaker(class_=ReadWriteSession, bind=metadata.bind)
sess = scoped_session(maker)
Adding the bind param was the temporary solution
The problem is that now it will always use the same bind. I think the sqlakeyset library should be calling the get_bind
method instead so custom logic from custom sessions is executed.
This is kinda a blocker for us.
What do you think?
Already found the culprit at
sqlakeyset/sqlakeyset/sqla20.py
Lines 105 to 112 in aa097fc
Instead of key -> col value
mapping, Row
wants key -> col index
mapping.
I'm running SQLAlchemy 2.0.23 and I have no idea if something has changed after 2.0.11.
Hello everyone,
I'm integrating sqlakeyset
in a project I'm working on and something is not working as expected.
In my test I prepare 100 records and I ask the pagination to paginate by 10 records (get_page(query, per_page=10)
), ordering by purchase_date
(it's a datetime) and id
(it's a unique, str
type id).
In my page I correctly have only the first 10 records.
page.paging.bookmark_next
always contains >
only, while I would expect it to contain >d:2020-08-31....etc...
. I've tried to sort my data with a few possible combinations: purchase_date
only, id
only and other str
fields that are generated random (and uniquely) at every INSERT. Nothing works.
Next page looks empty too:
ipdb> len(page)
10
ipdb> page.paging.next
(None, False)
which is weird, because there are 90 records left to be returned.
I have an SQLAlchemy orm model with a column named info
.
Normally, get_page
works just fine for queries generated using that model. However, if I construct using the from_self
construct, e.g. session.query(MyModel).from_self().order_by(MyModel.some_field)
, calling get_page
on it raises an exception:
File "/xxx/python3.8/site-packages/sqlakeyset/columns.py", line 328, in derive_order_key
mapper = expr.parent
AttributeError: type object 'MyModel' has no attribute 'parent'
The reason is that this piece of code incorrectly assumes that expr
is an attribute based on just the fact it has an attribute named info
: https://github.com/djrobstep/sqlakeyset/blob/6bcc01e/sqlakeyset/columns.py#L327
I think by bare minimum that code should check hasattr
for both info
and parent
(and name
), and that would not likely break any existing code.
That said, there's probably a better way to do that check. After all, it's not unreasonable for a model to have fields info
, parent
and name
.
The reason that this only occurs for the query with from_self
is that the check on this line: https://github.com/djrobstep/sqlakeyset/blob/6bcc01e/sqlakeyset/columns.py#L321
fails with an error (which is swallowed by the try-except
clause) like this:
sqlalchemy.orm.exc.UnmappedColumnError: No column %(140242126363088 anon)s.my_model_some_field is configured on mapper mapped class MyModel->my_model...
For the query without from_self
, the mapper finds the column correctly and thus the function returns before it has a change to crash on line 328.
I have a structure where I query my Child table which has a Parent table.
class Child(Parent):
child_id = Column(Integer, ForeignKey(Parent.parent_id), primary_key=True)
__mapper_args__ = {
'polymorphic_identity': CLS_INDEX['Child'], # comes from elsewhere in the code
'inherit_condition': (child_id == Parent.parent_id)
}
...
class Parent(Base):
name = Column(String)
parent_id = Column(Integer, primary_key=True)
...
When I try to query Child.name, I receive the ValueError in the title.
>>> q = session.query(Child).order_by(Child.name)
>>> get_page(q, per_page=50)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/mekhami/.envs/lab7/local/lib/python2.7/site-packages/sqlakeyset/paging.py", line 250, in get_page
backwards)
File "/home/mekhami/.envs/lab7/local/lib/python2.7/site-packages/sqlakeyset/paging.py", line 143, in orm_get_page
current_marker=place)
File "/home/mekhami/.envs/lab7/local/lib/python2.7/site-packages/sqlakeyset/paging.py", line 39, in orm_page_from_rows
paging = Paging(rows, page_size, ocols, backwards, current_marker, get_marker)
File "/home/mekhami/.envs/lab7/local/lib/python2.7/site-packages/sqlakeyset/results.py", line 115, in __init__
self.marker_1 = get_marker(rows[0], ocols)
File "/home/mekhami/.envs/lab7/local/lib/python2.7/site-packages/sqlakeyset/paging.py", line 109, in orm_placemarker_from_row
return tuple(get_value(x) for x in ocols)
File "/home/mekhami/.envs/lab7/local/lib/python2.7/site-packages/sqlakeyset/paging.py", line 109, in <genexpr>
return tuple(get_value(x) for x in ocols)
File "/home/mekhami/.envs/lab7/local/lib/python2.7/site-packages/sqlakeyset/paging.py", line 108, in get_value
raise ValueError(CANT_FIND.format(ocol.full_name))
ValueError: can't find value for column parent.name in the results returned
Hello, I am trying to use this library with an existing project and it seems that my q
does not have the is_single_entity
attribute that is being used here:
https://github.com/djrobstep/sqlakeyset/blob/master/sqlakeyset/paging.py#L22
My models inherit from db.Model
which comes from Flask-SQLAlchemy. When I run type(q)
it returns <class 'flask_sqlalchemy.BaseQuery'>
which I located here:
https://github.com/pallets/flask-sqlalchemy/blob/master/src/flask_sqlalchemy/__init__.py#L416
it looks like it inherits from orm.Query
which is here:
https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/query.py#L71
Which appears to have the desired is_single_entity
attribute.. does anyone have any ideas as to why that property wouldn't be propagating through the inheritance hierarchy to my Model?
I'm using sqlakeyset to implement relay-style pagination in a strawberry graphql server, and I noticed that when paging backwards using items(), the bookmarks and values are iterating in opposite directions. Sorry in advance for the graphql noise, it's just the easiest way for me to demonstrate the bug since I already have it implemented and can copy-paste.
For example, first I iterate forwards like:
{
accounts(first: 5, order: [{col: FULL_NAME, orderType: ASC}]) {
pageInfo{
hasNextPage
hasPreviousPage
}
edges {
cursor
node {
fullName
}
}
}
}
Results:
{
"data": {
"accounts": {
"pageInfo": {
"hasNextPage": true,
"hasPreviousPage": false,
},
"edges": [
{
"cursor": "s:Abel French~i:259",
"node": {
"fullName": "Abel French"
}
},
{
"cursor": "s:Abraham Banks~i:85",
"node": {
"fullName": "Abraham Banks"
}
},
{
"cursor": "s:Albert Ballard~i:28",
"node": {
"fullName": "Albert Ballard"
}
},
{
"cursor": "s:Alexis Carter~i:191",
"node": {
"fullName": "Alexis Carter"
}
},
{
"cursor": "s:Algae Mountain~i:120",
"node": {
"fullName": "Algae Mountain"
}
}
]
}
}
}
The cursor
that I'm using is just sqlakeyset.results.s.serialize_values(keyset)
where keyset is grabbed from for (keyset, _), value in page.paging.items()
. If I then take one of those cursors and iterate backwards via sqlakeyset.get_page(query, per_page=5, before=sqlakeyset.results.s.unserialize_values(cursor))
, e.g. on "s:Algae Mountain~i:120" above
I get:
{
"data": {
"accounts": {
"pageInfo": {
"hasNextPage": true,
"hasPreviousPage": false
},
"edges": [
{
"cursor": "s:Alexis Carter~i:191",
"node": {
"fullName": "Abel French"
}
},
{
"cursor": "s:Albert Ballard~i:28",
"node": {
"fullName": "Abraham Banks"
}
},
{
"cursor": "s:Abraham Banks~i:85",
"node": {
"fullName": "Albert Ballard"
}
},
{
"cursor": "s:Abel French~i:259",
"node": {
"fullName": "Alexis Carter"
}
}
]
}
}
}
Notice that the cursors (which are just keysets taken from page.paging.items()) are reversed relative to the values.
Maybe this is because I'm not encoding the backwards
property of the bookmark in my cursor, but I wouldn't think that would matter.
Please let me know if there's any additional information I can provide to help with debugging.
Versions:
sqlakeyset: 1.0.1650280980
sqlalchemy: 1.4.37
database: PostgreSQL 14
Breaks when using .session(), see:
import sqlalchemy as sa
from sqlakeyset import select_page
from sqlalchemy import Integer
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
class Base(DeclarativeBase):
pass
from sqlalchemy import create_engine
engine = create_engine("postgresql+psycopg://postgres:[email protected]:5432/guru", echo=True)
class User2(Base):
__tablename__ = "user2"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
with Session(engine) as session:
query_breaks = sa.select(User2).order_by(User2.id)
page_breaks = select_page(session, query_breaks, per_page=3)
print([type(page_breaks), page_breaks.paging.next])
page_breaks = select_page(session, query_breaks, per_page=3, after=page_breaks.paging.next)
Output:
Traceback (most recent call last):
File "/home/guru/Desktop/my_project/minimal.py", line 26, in <module>
page_breaks = select_page(session, query_breaks, per_page=3, after=page_breaks.paging.next)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/my_project/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 411, in select_page
return core_get_page(session, selectable, per_page, place, backwards)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/my_project/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 286, in core_get_page
sel = prepare_paging(
^^^^^^^^^^^^^^^
File "/home/guru/Desktop/my_project/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 214, in prepare_paging
condition = where_condition_for_page(order_cols, place, dialect)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/my_project/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 97, in where_condition_for_page
raise InvalidPage(
sqlakeyset.serial.serial.InvalidPage: Page marker has different column count to query's order clause
I have a set of user notifications I am trying to get paginated and this library is making it so that the bookmark_next and bookmark_previous calls are not consistent. We have a business requirement of ordering our SQL results by DESC datetime (Alert.time). However this seems to throw off the keyset and makes it so when you try to navigate the bookmark to return the previous result set, it does the opposite. we use the pyramid framework. Note: remove_objects_from_multiple_records(response) just JSON serializes any data that needs it. Is there a requirement that the incoming serialized bookmark data (sent from the client containing the output from bookmark_ generated during a previous call to our API) be parsed back into its tuple format before getting put in the get_page()? Please see our implementation below and thank you for your time:
#
@view_config(route_name='read_all_users_alerts',
permission='user' if DEV_PERMISSIONS_ENABLED is not True else DEV_PERMISSIONS,
renderer='json')
def read_all_users_alerts(request):
page = request.params.get('page') if request.params.get('page') is not None else None
per_page = int(request.params.get('per_page')) if request.params.get('per_page') is not None else None
users_uuid = request.matchdict.get('users_uuid') if 'users_uuid' in request.matchdict else request.authenticated_userid if request.has_permission(permission="admin") else request.authenticated_userid
filter_type = int(request.params.get('filter_type')) if request.params.get('filter_type') is not None else 4
filter_parameter = request.params.get('filter_parameter')
try:
query = build_query_statement_alerts_users_alerts_tables(request, users_uuid, filter_type, filter_parameter)
if page is not None and per_page is not None:
count = query.count()
result = get_page(query, per_page=per_page, page=page)
elif page is None and per_page is None:
count = query.count()
result = get_page(query, per_page=20)
else:
return Response(json={"Error": "Bad Request"}, content_type='application/json', status=400)
current_page = result.paging.bookmark_current
if result.paging.has_previous:
previous_page = result.paging.bookmark_previous
else:
previous_page = ">"
if result.paging.has_next:
next_page = result.paging.bookmark_next
else:
next_page = "<"
response = []
for item in result:
item[0].deleted = item[1].deleted
item[0].users_uuid = item[1].users_uuid
item[0].alert_status = item[1].alert_status
item[0].total_alerts = count
item[0].current_page = current_page
item[0].next_page = next_page
item[0].previous_page = previous_page
response.append(item[0])
return remove_objects_from_multiple_records(response)
except DBAPIError:
return Response(json={"Error": db_err_msg}, content_type='application/json', status=500)
# @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
#
def build_query_statement_alerts_users_alerts_tables(request, users_uuid, filter_type, filter_parameter):
switcher = {
0: lambda: read_multiple_alerts_camera_name(request, users_uuid, filter_parameter),
1: lambda: read_multiple_alerts_camera_group_name(request, users_uuid, filter_parameter),
2: lambda: read_multiple_alerts_trigger_type(request, users_uuid, filter_parameter),
3: lambda: read_multiple_alerts_time_range(request, users_uuid, filter_parameter),
4: lambda: read_multiple_alerts(request, users_uuid)
}
return switcher.get(filter_type, lambda: "type not valid")()
# @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
#
def read_multiple_alerts_camera_name(request, users_uuid, cameras_name):
return request.dbsession.query(Alert, UserAlert) \
.join(UserAlert, UserAlert.alerts_uuid == Alert.uuid) \
.filter(
UserAlert.users_uuid == users_uuid,
UserAlert.deleted == False,
Alert.cameras_name == cameras_name
).order_by(Alert.time.desc(), UserAlert.id)
# @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
#
def read_multiple_alerts_camera_group_name(request, users_uuid, camera_groups_name):
return request.dbsession.query(Alert, UserAlert) \
.join(UserAlert, UserAlert.alerts_uuid == Alert.uuid) \
.filter(
UserAlert.users_uuid == users_uuid,
UserAlert.deleted == False,
Alert.camera_groups_name == camera_groups_name
).order_by(Alert.time.desc(), UserAlert.id)
# @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
#
def read_multiple_alerts_trigger_type(request, users_uuid, trigger_type):
return request.dbsession.query(Alert, UserAlert) \
.join(UserAlert, UserAlert.alerts_uuid == Alert.uuid) \
.filter(
UserAlert.users_uuid == users_uuid,
UserAlert.deleted == False,
Alert.trigger_type == trigger_type
).order_by(Alert.time.desc(), UserAlert.id)
# @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
#
def read_multiple_alerts_time_range(request, users_uuid, time_range):
time_range = list(map(str, time_range.split(',')))
time_range[0] = parse(time_range[0], fuzzy=True).replace(tzinfo=None)
time_range[1] = parse(time_range[1], fuzzy=True).replace(tzinfo=None)
return request.dbsession.query(Alert, UserAlert) \
.join(UserAlert, UserAlert.alerts_uuid == Alert.uuid) \
.filter(
UserAlert.users_uuid == users_uuid,
UserAlert.deleted == False,
and_(Alert.time >= time_range[0], Alert.time <= time_range[1])
).order_by(Alert.time.desc(), UserAlert.id)
# @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
#
def read_multiple_alerts(request, users_uuid):
return request.dbsession.query(Alert, UserAlert) \
.join(UserAlert, UserAlert.alerts_uuid == Alert.uuid) \
.filter(
UserAlert.users_uuid == users_uuid,
UserAlert.deleted == False,
).order_by(Alert.time.desc(), UserAlert.id)```
How do I convert a db model returned from sqlakeyset page to a pydantic model ? When I convert using from_orm
, there isn't any error but it just get 1 empty record. My solution now is to loop the page
and append to the pydantic model manually. But I wanted to use the from_orm
out of the box without writing any custom mapping. Is this possible ?
# Pydantic Model
class AppSchemaModel(BaseModel):
customer_id: str
field1: Optional[str] = None
field2: Optional[str] = None
field3: Optional[str] = None
class Config:
orm_mode = True
# SQLAlchemy model
class AppModel(Base):
customer_id = Column(String(36))
field1 = Column(String(50))
field2 = Column(String(50))
field3 = Column(String(50))
created_date = Column(TIMESTAMP_TZ)
query = (
db_session.query(AppModel)
.filter(AppModel.customer_id == customer_id)
.order_by(AppModel.created_date.desc())
)
page = get_page(query, per_page=5)
# The page returns 5 records successfully, but i want to convert the
# db model into pydantic.
data = AppSchemaModel.from_orm(page)
# data now contain 1 instead of 5 records but all the 4 fields are empty.
With multiple Engines, Session.get_bind()
needs a context to fetch the correct Engine. It seems that just passing clause=q.statement
for it fixes it for us.
I guess this was broken even before 93bc7da but our use case just didn't reach the code after if place:
.
EDIT: Ah, as q
can also be a statement
already, maybe s.get_bind(clause=getattr(q, 'statement', q))
is ok?
I'm using asyncpg and getting this error when trying to sort by created_at
and uuid
.
sqlakeyset.serial.serial.UnregisteredType: Don't know how to serialize type of d12ca4d0-2d5c-4ffe-b23e-1473efbd8997 (<class 'asyncpg.pgproto.pgproto.UUID'>). Use custom_bookmark_type to register it.
asyncpg.pgproto.pgproto.UUID is a subclass of uuid.UUID.
I added this code:
custom_bookmark_type(
AsyncPgUUID,
"uuid2",
)
Which works, but it'd be nice if it would work out of the box.
I have a complex query with that is getting rows from a table and counts related to those rows from various tables, e.g.
s.query(
CM1,
sq_cc.c.child_count,
func.count(Comment.id.distinct()).label("comment_count"),
func.count(CM2.id.distinct()).label("member_count"),
func.count(CM2.id.distinct()).filter(CM2.liked).label("likes"),
Card.comments.any(Comment.user_id == CM1.user_id),
)
Is it possible to use this library with such a query? My initial attempts were met with a TypeError: Boolean value of this clause is not defined
error, and when I tried the change in #7 I got a sqlalchemy.orm.exc.UnmappedColumnError: No column cards.created_when is configured on mapper mapped class CardMembership->card_memberships...
error.
Any thoughts? I'm thinking I should just implement the paging manually for this query.
Hello,
I get the following error, which I guess is caused by SQLAlchemy >= 2.0:
ImportError: cannot import name 'LegacyRow' from 'sqlalchemy.engine.row'
Is there any workaround ?
Versions:
SQLAlchemy==2.0.3
sqlakeyset==1.0.1659142803
Hi,
I am experimenting with sqlakeyset library and have one question: if I want to access some specific page; say page number 10 (out of total 11 pages) - do I have to 'unpack' each page while looping like this:
for i in range(1, page_index):
if not page.paging.next:
break
page = get_page(query, per_page=per_page, page=page.paging.next)
I tried without unpacking, but that does not work. I am not sure will this perform good on large datasets.
Thank you kindly.
Bst regards
It seems that it is not possible to page query results while using .desc()
or desc(...)
I guess it would be extremely helpful.
Using Postgres and getting this error when I call select_page
against core (not ORM):
File "/Users/csantero/projects/example/.venv/lib/python3.10/site-packages/sqlakeyset/asyncio.py", line 84, in select_page
return await core_get_page(s, selectable, per_page, place, backwards)
File "/Users/csantero/projects/example/.venv/lib/python3.10/site-packages/sqlakeyset/asyncio.py", line 30, in core_get_page
sel = prepare_paging(
File "/Users/csantero/projects/example/.venv/lib/python3.10/site-packages/sqlakeyset/paging.py", line 196, in prepare_paging
mapped_ocols = [find_order_key(ocol, column_descriptions) for ocol in order_cols]
File "/Users/csantero/projects/example/.venv/lib/python3.10/site-packages/sqlakeyset/paging.py", line 196, in <listcomp>
mapped_ocols = [find_order_key(ocol, column_descriptions) for ocol in order_cols]
File "/Users/csantero/projects/example/.venv/lib/python3.10/site-packages/sqlakeyset/columns.py", line 429, in find_order_key
ok = derive_order_key(ocol, desc, index)
File "/Users/csantero/projects/example/.venv/lib/python3.10/site-packages/sqlakeyset/columns.py", line 370, in derive_order_key
entity = desc["entity"]
KeyError: 'entity'
Python: 3.10.10
SQLAlchemy: 2.0.18
sqlakeyset: 2.0.1691149549
psycopg: 3.1.9
I've tried both AsyncEngine and Engine with psycopg 3. I also tried psycopg2 Engine and got the same result.
When I debug I can tell that desc
is a dict, and not a ColumnElement
:
"{'name': 'my_column', 'type': UUID(), 'expr': Column('my_column', UUID(), table=<my_table>)}"
If I stick this code at the start of derive_order_key
I can extract the ColumnElement
and then everything works as expected:
if isinstance(desc, dict):
desc = desc.get("expr")
if desc is None:
return None
If this looks like the right approach then I'm happy to work up a PR, though I'm not quite sure how to produce a failing test case.
Hi guys,
thanks a lot for creating this library. Since SQLAlchemy has type hints (in typeshed and as sqlalchemy2-stubs), it would be great if types could also be added to this library.
Do you think this is possible?
perform_paging
is broken when running SQLAlchemy 1.4.7.
This fix worked for me:
def perform_paging(q, per_page, place, backwards, orm=True, s=None):
if orm:
selectable = orm_to_selectable(q)
s = q.session
column_descriptions = q.column_descriptions
keys = orm_query_keys(q)
else:
selectable = q
column_descriptions = q._raw_columns
to
def perform_paging(q, per_page, place, backwards, orm=True, s=None):
column_descriptions = q.column_descriptions
if orm:
selectable = orm_to_selectable(q)
s = q.session
keys = orm_query_keys(q)
else:
selectable = q
It will be nice to have an explicit error when unserialization failed.
Using serialization for http api, it's possible that client send uncorrect bookmark page string, which
return different python unexplicit error, which are difficult to catch correctly (get distinction between broken code and incorrect bookmark string from client).
Here, my workaround to handle this case directly in my own code (my software use page_token
naming):
try:
a = unserialize_bookmark(page_token)
except Exception as e:
raise InvalidPageToken('page token given is not valid')
return get_page(query, per_page=count, page=page_token or False)
We're using SQLAlchemyUtils' ArrowType for representing datetime fields in our models (as opposed to plain Python datetimes). This works fine with the rest of SQLAlchemy, but seems to cause problems with sqlakeyset when a column with this type is used in an ordering clause.
In particular, here's a simple example that fails:
In [62]: q = db.session.query(m.Conversation).order_by(m.Conversation.modified_at.desc())
In [63]: results = get_page(q, per_page=20)
In [64]: results.paging.next
Out[64]: ((<Arrow [2017-07-20T16:12:35+00:00]>,), False)
In [65]: results2 = get_page(q, per_page=20, page=results.paging.next)
---------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
# Exception stack trace...
ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'Arrow'
# query details...
I'm not entirely sure how custom types like ArrowType work, but it looks like the type conversion that typically happens is skipped here, and as a result the lower-level psycopg2 library doesn't know what to do with this type.
Any idea how to fix this?
See example code (python 3.11, sqlalchemy 2.0.20, flask-sqlalchemy 3.0.5, sqlakeyset 2.0.1691149549):
import time
import gevent
import sqlalchemy as sa
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlakeyset import get_page, select_page
app = Flask(__name__)
# configure the SQLite database, relative to the app instance folder
app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql+psycopg://postgres:[email protected]:5432/postgres"
# initialize the app with the extension
db = SQLAlchemy(app)
from sqlalchemy import Integer
from sqlalchemy.orm import Mapped, mapped_column
class User2(db.Model):
__tablename__ = "user2"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
with app.app_context():
db.create_all()
for _i in range(50):
user = User2()
db.session.add(user)
db.session.commit()
# works
query_works = User2.query.order_by(User2.id.desc())
page_works = get_page(query_works, per_page=3)
print([type(page_works), page_works.paging.next])
# breaks
query_breaks = sa.select(User2).order_by(User2.id)
page_breaks = select_page(db.session.connection(), query_breaks, per_page=3)
print([type(page_breaks), page_breaks.paging.next])
And exception:
Traceback (most recent call last):
File "<frozen runpy>", line 198, in _run_module_as_main
File "<frozen runpy>", line 88, in _run_code
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/flask/__main__.py", line 3, in <module>
main()
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/flask/cli.py", line 1064, in main
cli.main()
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/click/core.py", line 1078, in main
rv = self.invoke(ctx)
^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/click/core.py", line 1434, in invoke
return ctx.invoke(self.callback, **ctx.params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/click/decorators.py", line 92, in new_func
return ctx.invoke(f, obj, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/flask/cli.py", line 912, in run_command
raise e from None
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/flask/cli.py", line 898, in run_command
app = info.load_app()
^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/flask/cli.py", line 309, in load_app
app = locate_app(import_name, name)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/flask/cli.py", line 219, in locate_app
__import__(module_name)
File "/home/guru/Desktop/myproject/myflask.py", line 39, in <module>
page_breaks = select_page(db.session.connection(), query_breaks, per_page=3)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 411, in select_page
return core_get_page(s, selectable, per_page, place, backwards)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 297, in core_get_page
page = core_page_from_rows(
^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 328, in core_page_from_rows
key_rows = [tuple(col.get_from_row(row) for col in mapped_ocols) for row in rows]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 328, in <listcomp>
key_rows = [tuple(col.get_from_row(row) for col in mapped_ocols) for row in rows]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 328, in <genexpr>
key_rows = [tuple(col.get_from_row(row) for col in mapped_ocols) for row in rows]
^^^^^^^^^^^^^^^^^^^^^
File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/sqlakeyset/columns.py", line 323, in get_from_row
return getattr(row[self.index], self.attr)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'int' object has no attribute 'id'
Process finished with exit code 1
Hi,
Thank you for creating this library.
I am a bit puzzled by the queries that this library is producing. Given a query(Model).order_by(*pks)
, this library is generating 3 queries when get_page(query, 20)
is used:
SELECT * FROM ... ORDER BY pk
SELECT * FROM ... ORDER BY pk
SELECT * FROM ... ORDER BY pk ASC LIMIT 21
(observed by the statements outputted by the sqlalchemy.engine
logger to INFO)
Do we need to perform the first two (equal) unbounded statements against the table to retrieve the bookmarks? These statements are quite expensive in a large table
I use sqlakeyset to do keyset-based pagination in my GraphQL server. It works great for top-level resolvers, but if I have nested pages, I end up encountering the N + 1 GraphQL problem. The solution to the N + 1 problem is basically a "DataLoader" that just implements a batch API. Functionally, as it relates to sqlakeyset, that means implementing a "get_pages".
I went ahead and implemented this for my company, and was hoping that sqlakeyset would be receptive to homing this functionality. I'd be happy to send a PR.
More specifically, I implemented a get_homogeneous_pages that assumes the queries all select the same columns (but can have different filters or order_bys), which allows us to do a UNION ALL and make a single round trip to the database. That would be an easy addition to sqlakeyset because there's no need to understand the caller's nor session's threading model. A get_heterogeneous_pages may be useful to someone else, but requires making assumptions about how to execute asynchronously that I think sqlakeyset probably shouldn't touch.
Thoughts? Would you be open to adding this if I sent a PR?
So although this library does work, perhaps it would be better if the pagination key was returned in such a way that supports serialization of DateTime, etc? Right now, the pagination key is just a list, which means I need to implement my own custom serializer, especially if the keyset uses DateTime. It doesn't end up saving me much code once I've implemented that.
A bonus would be if it doesn't leak the abstraction. Could just be base64 encoding, to discourage clients from messing with the pagination token.
line 74 in paging.py might be more robust with the check as:
if entity.__table__.name == ocol.table_name:
Since the entity will (eventually) have a __table__
attribute but might not have a __tablename__
one.
see: http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/table_config.html#using-a-hybrid-approach-with-table
sqlalchemy.engine.result.RowProxy
is gone (thought wasn't actually even used in paging.py
) and so is sqlalchemy.util.lightweight_named_tuple
. Not sure if the lightweight namedtuple is moved somewhere else or if it's gone for good.
I have an enum value in a table:
class AddressFamily(enum.Enum):
IPv4 = 4
IPv6 = 6
Column('address_family', Enum(AddressFamily)),
When I paginate a core query, sqlakeyset removes some columns in paging.py
:
N = len(row._row) - len(extra_columns)
row = row[:N]
__getitem__
on BaseRowProxy
however applies processing to the values, which resolves the enum from its string name to the actual enum value:
l.append(processor(value))
When I then try to read the value from the row I received out of pagination:
row[prefixes.c.address_family]
Here, the value gets resolved again, using the str()
of the already resolved enum as a lookup key, and I get an exception: 'AddressFamily.IPv4' is not among the defined enum values. Enum name: addressfamily. Possible values: IPv4, IPv6
Calling query._iter()
executes the query.
In sqla14.py:8 I think the code can be replaced with
return [c['name'] for c in query.column_descriptions]
At least seems to work so far.
Another execution comes from sqla14.py:20.
Not sure how to tackle that and I guess the comment in core_get_page
is related.
If a query is ordered by a text-type column and the values stored in this column include newlines, parsing the resulting bookmarks results in markers cut off after the newline:
>>> from sqlakeyset.results import s
>>> s.unserialize_values(s.serialize_values(('hello\nthere', 12)))
['hello']
sqlakeyset
uses the row()
function which is not supported by SQLite. Using row values (name1, name2)
in the comparison should also be possible and is supported by most database systems:
First of all, thanks for the great work!
I was wondering whether there is a way to efficiently query the total amount of pages and / or records (so not just the records on a given page) that are returned by the query?
I have not found anything in the documentation, nor in the source, so I'm just making sure I'm not missing anything.
Thanks!
Pipenv breaks when trying to install with sqlalchemy >=1.4.0b1
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.