Menu
In my project setup querying is being done based on the SQLAlchemy.
As per my previous requirements I have done the union with two queries.
Now I need to do Union with three queries.
Code is as follows:
query1 = query1.filter(model.name == "in-addr.arpa.")
query2 = query2.filter(model.tenant_id.in_(tenant_ids))
query = query1.union(query2)
Now Here I need to add one more query as follows:
query3 = query3.filter(model.tenant_id == context.tenant_id)
So I need to perform Union with all the three queries.
285
You can use . join() and then specify the join condition with the second param. If you omit the join condition, then the . query() method alone generates a cross join between table1 and table2.
Instead of returning entire model instances, SQLAlchemy can fetch only the columns we're interested in. This not only reduces the amount of data sent, but also avoids the need to instantiate entire objects. Working with tuples of column data instead of models can be quite a bit faster.
At the ORM level, the speed issues are because creating objects in Python is slow, and the SQLAlchemy ORM applies a large amount of bookkeeping to these objects as it fetches them, which is necessary in order for it to fulfill its usage contract, including unit of work, identity map, eager loading, collections, etc.
Caching the query object so that Python interpreter doesn't have to manually re-assemble the query string every time. These queries are called baked queries and the cache is called baked . Basically it caches all the actions sqlalchemy takes BEFORE hitting the database--it does not cut down on database calls.
The solution is following:
query1 = query1.filter(model.name == "in-addr.arpa.")
query2 = query2.filter(model.tenant_id.in_(tenant_ids))
query3 = query3.filter(model.tenant_id == context.tenant_id)
query = query1.union(query2,query3)
This is how I did this in SQLAlchemy 1.3
from sqlalchemy import union
query1 = query1.filter(model.name == "in-addr.arpa.")
query2 = query2.filter(model.tenant_id.in_(tenant_ids))
query3 = query3.filter(model.tenant_id == context.tenant_id)
all_queries = [query1, query2, query3]
golden_set = union(*all_queries)
The change here is that the union method accepts a list of SQLAlchemy selectables.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
