问题

我有一个表模型：
```py
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)

我想返回在customer列中唯一的值为"Alice"的stock列的不同值。例如：

在这种情况下，我希望结果只包括["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中执行这个查询并且保持数据库的通用性？

<details>
<summary>英文:</summary>

I have a table model:
```py
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)

I want to return the distinct values of stock for which the only value in customer is "Alice". For example:

In this case, I want the result to be only ["B"].

This is fairly straightforward with 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])

This works, but makes use of Postgres' DISTINCT ON, and I want my query to be database-agnostic (MySQL, SQLite etc.). I'm not sure how to adapt it to be so. In fact, when running this with SQLite, I get the warning:

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.

How can I perform this query in SQLAlchemy and be database-agnostic?

答案1

得分: 0

使用子查询解决：

子查询 = session.query(Data.stock).filter(Data.customer != "Alice").distinct().子查询()
查询 = session.query(Data.stock).filter(Data.customer == "Alice").group_by(Data.stock).having(~Data.stock.in_(子查询))
print([结果.stock for 结果 in 查询])

感谢 @Obaskly 指引我使用子查询。

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])