sqlalchemy 存在功能描述
names = ["aaa", "bbb", "hjuhyg",...]
session.query(User).filter(User.name.in_(names)) 當資料量很大時,查詢速度會很慢,
是以想要優化mysql 的 in 查詢時,可以使用exists,在 python中,一種友善的方法如下:
可以将查詢轉換為EXISTS格式的EXISTS子查詢(SELECT 1 FROM ... WHERE ...)。
例如:
q = session.query(User).filter(User.name == 'fred')
session.query(q.exists())
生成類似于的SQL:
SELECT EXISTS (
SELECT 1 FROM users WHERE users.name = :name_1
) AS anon_1
EXISTS構造通常在WHERE子句中使用:
session.query(User.id).filter(q.exists()).scalar()
請注意,某些資料庫(如SQL Server)不允許EXISTS表達式出現在SELECT的columns子句中。要基于exists作為WHERE選擇一個簡單的布爾值,請使用literal():
from sqlalchemy import literal
session.query(literal(True)).filter(q.exists()).scalar()
以上方法使用sql語句為:select num from a where num in(select num from b)用下面的語句替換:select num from a where exists(select 1 from b where num=a.num)
針對查詢條件為json 或 arry
json我們可以使用如下方法:
如:labels= [{"key":"sxdc","value":"cderfe"},......],events.filter(EventModel.labels.contains(labels[0]), EventModel.labels.contains(labels[1])).all()
filter_labels = ""
for index in range(len(labels)):
filter_labels += EventModel.labels.contains(labels[index])
if index != len(labels) - 1:
filter_labels += ","
filter_labels = or_(filter_labels)
events = events.filter(filter_labels)
同理arry: names = ["aaa", "bbb", "hjuhyg",...]
filter_namels = ""
for index in range(len(names)):
filter_names += EventModel.names == (names[index])
if index != len(labels) - 1:
filter_labels += ","
filter_names = or_(filter_names)
events = events.filter(filter_names)
https://auth0.com/blog/sqlalchemy-orm-tutorial-for-python-developers/