flask-sqlalchemy - SQLalchemy：双重外连接和过滤

Question

我有一个带有 4 个 sqlite 模型的烧瓶应用程序。项目、采购、销售和 Invcount。他们每个人都有itemname。计数每天都有每个项目的数据。销售可能不是每天都有每个项目的数据。Purchases 并不是每天都有每件商品的数据。我有这个查询来创建一个显示每日详细信息的表：

result = db.session.query(Invcount, Sales, Purchases).select_from(Invcount). \
        filter(and_(Invcount.itemname == item_name, Invcount.count_time == "PM", 
        Invcount.trans_date >= weekly)). \
        outerjoin(Sales, Sales.trans_date == Invcount.trans_date). \
        filter(or_(Sales.itemname == item_name, Sales.itemname == None)). \
        outerjoin(Purchases, Purchases.trans_date == Invcount.trans_date). \
        filter(or_(Purchases.itemname == item_name, Purchases.itemname == None)). \
        order_by(Invcount.trans_date.desc()).all()

这是它产生的表：

日期	开始	购买	销售量	浪费	理论	数数	方差
星期一-07/26	17	+ 16	- 1	- 0	= 32	- 32	= 0
周日-07/25	24	+	- 7	- 0	= 17	- 17	= 0
星期六-07/24	32	+	- 8	- 0	= 24	- 24	= 0

在购买了所有物品并且没有购买任何物品的日子里，查询按我想要的方式工作。问题是当我购买一些物品而不是其他物品（或销售）时。当购买了其他物品时，那些未购买的物品的日子不会出现在表格中。因此，例如，周一未购买的另一件商品将显示如下：

日期	开始	购买	销售量	浪费	理论	数数	方差
周日-07/25	24	+	- 7	- 0	= 17	- 17	= 0
星期六-07/24	32	+	- 8	- 0	= 24	- 24	= 0

我“相信”我的查询提取了与 item_name、PM 和过去 7 天匹配的所有计数。然后加入过去 7 天的所有销售，然后按 item_name 或无过滤。这就是问题所在，因为本周可能有其他商品的销售，但当前商品没有，所以这些商品连同当前商品的“星期一”计数都被删除了。

我怎样才能删除这些项目但不从星期一删除计数？

标签： flask-sqlalchemy

Answer 1

我试图根据您的问题复制您的模型，但如果没有您使用的实际模型，这很难。根据您的问题，我认为可能还有其他问题。根据我对您问题的理解，它似乎对我有用。也许尝试简化查询或在 SQL 中手动构建查询并将其移植回 sqlalchemy。

您也可以在 sqlalchemy 中进行查询并在调用之前将其打印出来.all()，您可以看到 SQL 已生成。

一个问题是您可能在不使用 sum/groupby 的情况下生成的重复出现问题，除非 Sale 和 Purchase 条目对于每个 transdate/itemname 都是唯一的。

Base = declarative_base()


engine = create_engine("sqlite://", echo=False)


class Invcount(Base):
    __tablename__ = 'invcounts'
    id = Column(Integer, primary_key=True)
    itemname = Column(String(100), nullable=False)
    count_time = Column(String(10), nullable=False)
    transdate = Column(Date(), default=date.today, nullable=False)
    count = Column(Integer, default=0)


class Sale(Base):
    __tablename__ = 'sales'
    id = Column(Integer, primary_key=True)
    itemname = Column(String(100))
    transdate = Column(Date(), default=date.today, nullable=False)
    count = Column(Integer, default=0)

class Purchase(Base):
    __tablename__ = 'purchases'
    id = Column(Integer, primary_key=True)
    itemname = Column(String(100))
    transdate = Column(Date(), default=date.today, nullable=False)
    count = Column(Integer, default=0)


Base.metadata.create_all(engine)

def add_entities(session, cls, itemname, transdate, count):
    # Split test data just to test outerjoin being not 1-1.
    if count != 0:
        if count % 2 == 0:
            session.add(cls(itemname=itemname, transdate=transdate, count=count/2))
            session.add(cls(itemname=itemname, transdate=transdate, count=count/2))
        else:
            session.add(cls(itemname=itemname, transdate=transdate, count=count))

with Session(engine) as session:
    itemname = 'widget'
    from datetime import date
    days = {}
    # Make test data.
    for day, count, purchase_count, sale_count  in ((24, 32, 0, -8), (25, 24, 0, -7), (26, 17, 16, -1)):
        d = date(day=day, month=7, year=2021)
        for itemname in ('widget', 'gadget'):
            session.add(Invcount(itemname=itemname, count_time='PM', transdate=d, count=count))
            add_entities(session, Sale, itemname, d, sale_count)
            add_entities(session, Purchase, itemname, d, purchase_count)

    session.commit()

    print ('without groupby/sum')
    q = session.query(
        Invcount,
        Sale,
        Purchase
    ).select_from(
        Invcount
    ).outerjoin(
        Sale,
        and_(Invcount.itemname == Sale.itemname, Invcount.transdate == Sale.transdate)
    ).outerjoin(
        Purchase,
        and_(Invcount.itemname == Purchase.itemname, Invcount.transdate == Purchase.transdate)
    ).order_by(
        Invcount.transdate.desc()
    ).filter(
        Invcount.itemname == 'widget')
    for i, s, p in q.all():
        print (i.id, i.transdate, i.itemname, s.id if s else None, p.id if p else None, i.count, s.count if s else 0, p.count if p else 0)

    print ('with groupby/sum')
    q = session.query(
        Invcount,
        func.sum(Sale.count).label('sale_count'),
        func.sum(Purchase.count).label('purchase_count'),
    ).select_from(
        Invcount
    ).outerjoin(Sale,
            and_(Invcount.itemname == Sale.itemname, Invcount.transdate == Sale.transdate)
    ).outerjoin(
        Purchase,
        and_(Invcount.itemname == Purchase.itemname, Invcount.transdate == Purchase.transdate)
    ).group_by(
        Invcount.itemname,
        Invcount.transdate
    ).order_by(
        Invcount.transdate.desc()
    ).filter(Invcount.itemname == 'widget')
    for i, s, p in q.all():
        print (i.id, i.transdate, i.itemname, i.count, s, p)


    print ('conditions in filter instead of join')
    item_name = 'widget'
    # The start of the week?
    weekly = date(day=24, year=2021, month=7)
    q = session.query(
        Invcount, Sale, Purchase
    ).select_from(
        Invcount
    ).filter(
        and_(
            Invcount.itemname == item_name,
            Invcount.transdate >= weekly)
    ).outerjoin(
        Sale,
        Sale.transdate == Invcount.transdate
    ).filter(
        or_(
            Sale.itemname == item_name,
            Sale.itemname == None)
    ).outerjoin(
        Purchase,
        Purchase.transdate == Invcount.transdate
    ).filter(
        or_(
            Purchase.itemname == item_name,
            Purchase.itemname == None)
    ).order_by(Invcount.transdate.desc())
    for (i, s, p) in q.all():
        print (i.id, i.transdate, i.itemname, s.id if s else None, p.id if p else None, i.count, s.count if s else 0, p.count if p else 0)

输出这个：

without groupby/sum
5 2021-07-26 widget 7 1 17 -1 8
5 2021-07-26 widget 7 2 17 -1 8
3 2021-07-25 widget 5 None 24 -7 0
1 2021-07-24 widget 1 None 32 -4 0
1 2021-07-24 widget 2 None 32 -4 0
with groupby/sum
5 2021-07-26 widget 17 -2 16
3 2021-07-25 widget 24 -7 None
1 2021-07-24 widget 32 -8 None
conditions in filter instead of join
5 2021-07-26 widget 7 1 17 -1 8
5 2021-07-26 widget 7 2 17 -1 8
3 2021-07-25 widget 5 None 24 -7 0
1 2021-07-24 widget 1 None 32 -4 0
1 2021-07-24 widget 2 None 32 -4 0

按周范围分组

    print ('with groupby/sum')
    start_of_week = date(day=24, year=2021, month=7)
    end_of_week = date(day=31, year=2021, month=7)
    q = session.query(
        Invcount,
        func.sum(Sale.count).label('sale_count'),
        func.sum(Purchase.count).label('purchase_count'),
    ).select_from(
        Invcount
    ).outerjoin(Sale,
            and_(Invcount.itemname == Sale.itemname, Invcount.transdate == Sale.transdate)
    ).outerjoin(
        Purchase,
        and_(Invcount.itemname == Purchase.itemname, Invcount.transdate == Purchase.transdate)
    ).group_by(
        Invcount.itemname,
        Invcount.transdate
    ).order_by(
        Invcount.transdate.desc()
    ).filter(
        Invcount.itemname == 'widget',
        Invcount.transdate >= start_of_week,
        Invcount.transdate < end_of_week,
    )
    for i, s, p in q.all():
        print (i.id, i.transdate, i.itemname, i.count, s, p)