How to check if query in SqlAlchemy returns empty result?

Question

I'd like to perform a query like so:

 query = users.select().where(users.column.id == id) res = await database.execute(query)

I would then like to add some error handling by checking res. if not res does not seem to be correct. What is the proper way to do this?

Is there a bias against checking if the res.rowcount is 0? It seems like the most logical solution to me, and it's built-in too. — c8999c 3f964f64
– c8999c 3f964f64, Commented Jul 28, 2020 at 14:43
@c8999c3f964f64 The bias is that it performs an entire query to get to that rowcount, while in theory it could stop at the very first result. There is no reason to ask you to count all traffic lights in your city when all I want to know is whether you have any at all — Ruben Helsloot
– Ruben Helsloot, Commented Jul 28, 2020 at 21:32
I see that, thanks for explaining. I just didnt think the question asked for "check if a query WOULD return something, instead of doing the complete query". Using your answer below, the worst case is that its doing 99% of a query, then returns true, then you have to do the actual query again, because it didnt finish and could be missing results, correct? — c8999c 3f964f64
– c8999c 3f964f64, Commented Jul 29, 2020 at 6:59

Antonio Margaretti · Accepted Answer · 2020-07-23 18:10:34Z

You can try taking first object from your query and checking if it is Null like this

result = users.select().where(users.column.id == id).first() if not result: print('query is empty')

The other way editing your code would be

res = query.first()

And then checking if it is Null

Ruben Helsloot · Accepted Answer · 2020-07-23 20:25:58Z

Depending on the complexity of your query, it might be cheaper to wrap it inside a SELECT EXISTS(...) than to SELECT ... LIMIT 1, as Antonio describes. PostgreSQL probably knows to treat those queries the same, but according to this answer, not all DBMS might do so.

The beauty of such a solution is that it exists as soon as it finds any result -- it does as little work as possible and is thus as cheap as possible. In SQLAlchemy that would be sa.select([sa.exists(query)])

Collectives™ on Stack Overflow

How to check if query in SqlAlchemy returns empty result?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related