我有一个table模型:
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Data(Base):
id = Column(Integer, primary_key=True)
stock = Column(String)
customer = Column(String)
我想返回stock
的不同值,其中customer
中的唯一值是"Alice"
。例如:
| 股票|客户|
| - -----|- -----|
| 一个|爱丽丝|
| 一个|爱丽丝|
| 一个|鲍勃|
| B|爱丽丝|
| C类|鲍勃|
在本例中,我希望结果仅为["B"]
。
这对于Postgres来说是非常简单的:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('connection_string')
Session = sessionmaker(bind=engine)
session = Session()
result = session.query(Data.stock).distinct(Data.stock).filter(
Data.customer == "Alice"
).group_by(Data.stock).having(
~Data.stock.in_(session.query(Data.stock).filter(Data.customer != "Alice"))
)
print([r for r in result])
这是可行的,但是使用了Postgres的DISTINCT ON,我希望我的查询是数据库无关的(MySQL,SQLite等)。我不知道如何使它变成这样。事实上,当我用SQLite运行这个程序时,我得到了警告:
SADeprecationWarning: DISTINCT ON is currently supported only by the PostgreSQL dialect. Use of DISTINCT ON for other backends is currently silently ignored, however this usage is deprecated, and will raise CompileError in a future release for all backends that do not support this syntax.
我如何在SQLAlchemy中执行此查询并与数据库无关?
1条答案
按热度按时间nzkunb0c1#
使用子查询解决:
感谢@Obaskly为我指出子查询。