python - 使用 SQLAlchemy 如何获取具有不同产品和仓库值的最后一个库存条目

Question

我正在尝试构建一个简单的库存应用程序，但无法弄清楚如何执行一些查询。相关类别是产品、仓库和库存。产品和仓库非常不言自明。库存是一个关联表，具有两个额外的数据列 - 日期(完成库存的日期)和数量(单位数量)。

class Product(db.Model):
    __tablename__ = 'products'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    inventories = db.relationship('Inventory', foreign_keys=[Inventory.product_id],
                                  backref=db.backref('product', lazy='joined'),
                                  lazy='dynamic', cascade='all, delete-orphan')

class Warehouse(db.Model):
    __tablename__ = 'warehouses'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    inventories = db.relationship('Inventory', foreign_keys=[Inventory.warehouse_id],
                                  backref=db.backref('warehouse', lazy='joined'),
                                  lazy='dynamic', cascade='all, delete-orphan')


class Inventory(db.Model):
    __tablename__ = 'inventories'
    warehouse_id = db.Column(db.Integer, db.ForeignKey('warehouses.id'), primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('products.id'), primary_key=True)
    created_at = db.Column(db.DateTime(timezone=True),
                           nullable=False, default=dt.datetime.utcnow, primary_key=True)
    quantity = db.Column(db.Integer)

我想获取以下数据:Product.name、Inventory.quantity、Warehouse.name、Inventory.created_at - 库存应该是每个仓库中每个产品基于日期的最新行。

我可以连接表，但无法弄清楚如何在不执行多个 for 循环和多个查询的情况下仅保留我正在查找的数据。最终结果是生成一个 HTML 表格，如下所示:

Product Warehouse1  Warehouse2  Total   Date
======= ==========  ==========  =====   ====    
product1    10          3         13    mm-dd-yy    
product2    11          24        35    mm-dd-yy

我为我的实现和下面的 van 运行了 Flask Profiler，看看有什么区别，平均 van 的实现比我最初的多个 sql 查询和 for 循环快 3 倍。

Answer 1

1. 检索 View 所需的数据。

代码:

subq = (
    db.session
    .query(
        Inventory.product_id, Inventory.warehouse_id,
        db.func.max(Inventory.created_at).label("last_update")
    )
    .group_by(
        Inventory.product_id, Inventory.warehouse_id,
    )
).subquery("subq")

q = (
    db.session
    # .query(Inventory)
    .query(
        Product.name.label("Product"),
        Warehouse.name.label("Warehouse"),
        Inventory.quantity,
        Inventory.created_at.label("Date"),
    )
    .select_from(Inventory)
    .join(Product)
    .join(Warehouse)
    .join(subq, db.and_(
        subq.c.product_id == Inventory.product_id,
        subq.c.warehouse_id == Inventory.warehouse_id,
        subq.c.last_update == Inventory.created_at,
    ))
)

将数据转换为所需的格式。

为了实现这一目标，需要pivot功能，您可以自己实现该功能、查找库或使用 pandas .

下面是使用 pandas 构建在查询 q 之上的解决方案(见上文):

# get data from database
df = pd.read_sql_query(q.statement, db.session.bind)

# create pivot table
pi = pd.pivot_table(
    df,
    index=['Product', 'Date'],
    columns=['Warehouse'],
    values=['quantity'],
    fill_value=0,
)

# reorder columns
pi.columns = pi.columns.droplevel()

# add total
pi['Total'] = pi.sum(axis=1)

# fix the order of columns
pi = pi.reset_index()

然后您可以将结果导出到各种输出:

    # get a textual representation
    print(pi.to_string(index=False))

    # result looks like below:
    _res = """\
    Product       Date  Warehouse1  Warehouse2  Total
    Product1 2016-01-02           2           0      2
    Product1 2016-03-02           0          20     20
    Product2 2016-04-02           2           0      2
    """

    # JSON format which can be sent to a javascript client
    json = pi.to_json(orient='records')

    # even html representation which you can embed into your template
    html = pi.to_html()