SQLAlchemy:获取另一列的唯一值为X的列的值,而不使用DISTINCT ON?

huangapple go评论71阅读模式
英文:

SQLAlchemy: get values of column where the only value for another column is X without using DISTINCT ON?

问题

我有一个表模型
```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列的不同值。例如:

stock customer
A Alice
A Alice
A Bob
B Alice
C Bob

在这种情况下,我希望结果只包括["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 &quot;Alice&quot;. For example:

stock customer
A Alice
A Alice
A Bob
B Alice
C Bob

In this case, I want the result to be only [&quot;B&quot;].

This is fairly straightforward with Postgres:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine(&#39;connection_string&#39;)
Session = sessionmaker(bind=engine)
session = Session()

result = session.query(Data.stock).distinct(Data.stock).filter(
    Data.customer == &quot;Alice&quot;
).group_by(Data.stock).having(
    ~Data.stock.in_(session.query(Data.stock).filter(Data.customer != &quot;Alice&quot;))
)
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 指引我使用子查询。

英文:

Solved using a subquery:

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

Thanks to @Obaskly for pointing me toward subqueries.

huangapple
  • 本文由 发表于 2023年6月8日 22:44:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/76433042.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定