postgresql SQLAlchemy:获取列的值,其中另一列的唯一值是X,而不使用DISTINCT ON?

8wigbo56  于 2023-06-22  发布在  PostgreSQL
关注(0)|答案(1)|浏览(157)

我有一个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中执行此查询并与数据库无关?

nzkunb0c

nzkunb0c1#

使用子查询解决:

subquery = session.query(Data.stock).filter(Data.customer != "Alice").distinct().subquery()
query = session.query(Data.stock).filter(Data.customer == "Alice").group_by(Data.stock).having(~Data.stock.in_(subquery))
print([result.stock for result in query])

感谢@Obaskly为我指出子查询。

相关问题