Filter a query

[david-shiko]

I'm sorry for a possible silly question, how filter queryes?

Example: I'm trying convert this query to ORM style.

query = "SELECT * FROM claims WHERE user_id = :user_id"
return await database.database_obj.fetch_all(query=query, values={"user_id": fake_user_id})

query = models.claims.select()
# Error, Table no such attribute
return await database.database_obj.fetch_all(query=query.where(models.claims.user_id == fake_user_id))

P.S. I can't find detailed docs except this one because library name intersects with other python guides for common usage of databases in python, so, if you know any more verbose docs - please, provide a link :)

[rafalp]

I'm not sure databases was designed to work with SQLAlchemy models. It works for tables like this:

wait database.database_obj.fetch_all(query=query.where(table.c.user_id == fake_user_id))

This library is database access layer (DBAL) and not a complete stack. It expects DB query as either SQLAlchemy expression or SQL string. I don't know how one can retrieve filtered query for ORM, perhaps something like this will work?

query = models.claims.select().filter_by(id=fake_user_id)

return await database.database_obj.fetch_all(query=query)

[david-shiko]

I'm not sure databases was designed to work with SQLAlchemy models. It works for tables:

wait database.database_obj.fetch_all(query=query.where(table.c.user_id == fake_user_id))

I'm novice in databases.
So, it is impossible?
Your variant looks exactyy as my :)

[rafalp]

Your variant looks exactyy as my :)

I'm not sure. My example uses table.c.user_id which returns SQLAlchemy column type, and models.claims.user_id seems to be user_id attribute of claim model? 🤔

[david-shiko]

I think I got it. Presumably my model was created by sqlalchemy (by the fastapi-users indeed), so it doesn't has a filter method.