Skip to main content
 首页 » 编程设计

python之Flask-SQLAlchemy 查询计数

2024年12月31日8mengfanrong

我正在使用 Flask-SQLAlchemy 并且我使用一对多关系。两种模式

class Request(db.Model): 
 
      id = db.Column(db.Integer, primary_key = True) 
      r_time = db.Column(db.DateTime, index = True, default=datetime.utcnow) 
      org = db.Column(db.String(120)) 
      dest = db.Column(db.String(120)) 
      buyer_id = db.Column(db.Integer, db.ForeignKey('buyer.id')) 
      sale_id = db.Column(db.Integer, db.ForeignKey('sale.id')) 
      cost = db.Column(db.Integer) 
      sr = db.Column(db.Integer) 
      profit = db.Column(db.Integer) 
 
      def __repr__(self): 
          return '<Request {} by {}>'.format(self.org, self.buyer_id) 
 
class Buyer(db.Model): 
      id  = db.Column(db.Integer, primary_key = True) 
      name = db.Column(db.String(120), unique = True) 
      email = db.Column(db.String(120), unique = True) 
      requests = db.relationship('Request', backref = 'buyer', lazy='dynamic') 
 
      def __repr__(self): 
          return '<Buyer {}>'.format(self.name) 

我需要确定哪个买家对所有买家的要求最少 的买家。

我可以通过创建额外的列表并把所有的 列表中的请求并搜索列表。但我相信还有另一种简单的方法可以通过 SQLAlchemy 查询来完成

请您参考如下方法:

您可以使用 CTE(公用表表达式)进行选择,生成买家 ID 及其请求计数,因此

buyer_id | request_count 
:------- | :------------ 
1        | 5 
2        | 3 
3        | 1 
4        | 1

You can filter here on the counts having to be greater than 0 to be listed.

You can then join the buyers table against that to produce:

buyer_id | buyer_name | buyer_email      | request_count 
:------- | :--------- | :--------------- | :------------ 
1        | foo        | foo@example.com  | 5 
2        | bar        | bar@example.com  | 3 
3        | baz        | baz@example.com  | 1 
4        | spam       | spam@example.com | 1

but because we are using a CTE, you can also query the CTE for the lowest count value. In the above example, that's 1, and you can add a WHERE clause to the joined buyer-with-cte-counts query to filter the results down to only rows where the request_count value is equal to that minimum number.

The SQL query for this is

WITH request_counts AS ( 
    SELECT request.buyer_id AS buyer_id, count(request.id) AS request_count 
    FROM request GROUP BY request.buyer_id 
    HAVING count(request.id) > ? 
) 
SELECT buyer.* 
FROM buyer 
JOIN request_counts ON buyer.id = request_counts.buyer_id 
WHERE request_counts.request_count = ( 
    SELECT min(request_counts.request_count) 
    FROM request_counts 
) 

WITH request_counts AS (...)定义了一个 CTE,这部分将生成第一个带有 buyer_id 的表和 request_count . request_count然后表与 request 连接和 WHERE子句对 min(request_counts.request_count) 进行过滤值(value)。

将以上内容翻译成 Flask-SQLAlchemy 代码:

request_count = db.func.count(Request.id).label("request_count") 
cte = ( 
    db.select([Request.buyer_id.label("buyer_id"), request_count]) 
    .group_by(Request.buyer_id) 
    .having(request_count > 0) 
    .cte('request_counts') 
) 
min_request_count = db.select([db.func.min(cte.c.request_count)]).as_scalar() 
buyers_with_least_requests = Buyer.query.join( 
    cte, Buyer.id == cte.c.buyer_id 
).filter(cte.c.request_count == min_request_count).all() 

演示:

>>> __ = db.session.bulk_insert_mappings( 
...     Buyer, [{"name": n} for n in ("foo", "bar", "baz", "spam", "no requests")] 
... ) 
>>> buyers = Buyer.query.order_by(Buyer.id).all() 
>>> requests = [ 
...     Request(buyer_id=b.id) 
...     for b in [*([buyers[0]] * 3), *([buyers[1]] * 5), *[buyers[2], buyers[3]]] 
... ] 
>>> __ = db.session.add_all(requests) 
>>> request_count = db.func.count(Request.id).label("request_count") 
>>> cte = ( 
...     db.select([Request.buyer_id.label("buyer_id"), request_count]) 
...     .group_by(Request.buyer_id) 
...     .having(request_count > 0) 
...     .cte("request_counts") 
... ) 
>>> buyers_w_counts = Buyer.query.join(cte, cte.c.buyer_id == Buyer.id) 
>>> for buyer, count in buyers_w_counts.add_column(cte.c.request_count): 
...     # print out buyer and request count for this demo 
...     print(buyer, count, sep=": ") 
<Buyer foo>: 3 
<Buyer bar>: 5 
<Buyer baz>: 1 
<Buyer spam>: 1 
>>> min_request_count = db.select([db.func.min(cte.c.request_count)]).as_scalar() 
>>> buyers_w_counts.filter(cte.c.request_count == min_request_count).all() 
[<Buyer baz>, <Buyer spam>] 

我还创建了一个 db<>fiddle here ,包含相同的查询,可以玩。