Using SQLAlchemy How do I get the latest inventory entry with separate values ​​for the product and warehouse

advertisements

I am trying to build a simple inventory app and cannot figure out how to perform a few queries. The relevant classes are Product, Warehouse and Inventory. Product and Warehouse are pretty self-explanatory. Inventory is an association table with two extra data columns - Date (date in which inventory was done) and Quantity (number of units).

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)

I would like to get this data: Product.name, Inventory.quantity, Warehouse.name, Inventory.created_at - Inventory should be the latest row based on date for each product at each warehouse.

I can join the tables, but cannot figure out how to keep just the data I am looking for without doing multiple for loops and multiple queries. The end result is to produce an HTML table such as this:

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

I ran flask profiler for my implementation and van's below to see what the difference was and on average van's implementation is 3X faster than the multiple sql queries and for loops I had at first.


  1. Retrieve the data you need for the view.

Code:

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

  1. Bring the data to the desired format.

In order to achieve that, one need pivot functionality, which you can implement yourself, find a library, or use pandas.

Below is the solution using pandas building on top of the query q (see above):

# 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()

Then you can export the result to various outputs:

    # 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()