Get Max Value of Column out of a a query list in Python

Question

I am new to Python and am currently trying to create a Web-form to edit customer data. The user selects a customer and gets all DSL-Products linked to the customer. What I am now trying is to get the maximum downstream possible for a customer. So when the customer got DSL1, DSL3 and DSL3 then his MaxDownstream is 550. Sorry for my poor english skills.

Here is the structure of my tables..

Customer_has_product:
Customer_idCustomer | Product_idProduct
----------------------------
1                 |       1
1                 |       3
1                 |       4
2                 |       5
3                 |       3

Customer:
idCustomer | MaxDownstream
----------------------------
1         |       
2         |       
3         |       


Product:
idProduct | Name            | downstream
-------------------------------------------------
1         | DSL1            | 50
2         | DSL2            | 100
3         | DSL3            | 550
4         | DSL4            | 400
5         | DSL5            | 1000

And the code i've got so far:

db_session = Session(db_engine)
customer_object = db_session.query(Customer).filter_by(
    idCustomer=productform.Customer.data.idCustomer
).first()
productlist = request.form.getlist("DSLPRODUCTS_PRIVATE")
oldproducts = db_session.query(Customer_has_product.Product_idProduct).filter_by(
    Customer_idCustomer=customer_object.idCustomer)
id_list_delete = list(set([r for r, in oldproducts]) - set(productlist))
for delid in id_list_delete:
    db_session.query(Customer_has_product).filter_by(Customer_idCustomer=customer_object.idCustomer,
                                                    Product_idProduct=delid).delete()
db_session.commit()
for product in productlist:
    if db_session.query(Customer_has_product).filter_by(
        Customer_idCustomer=customer_object.idCustomer,
        Product_idProduct=product
    ).first() is not None:
        continue
    else:
        product_link_to_add = Customer_has_product(
            Customer_idCustomer=productform.Customer.data.idCustomer,
            Product_idProduct=product
        )
            db_session.add(product_link_to_add)
            db_session.commit()

score 0 · Answer 1 · answered Nov 27 '17 at 11:57

What you want to do is JOIN the tables onto each other. All relational database engines support joins, as does SQLAlchemy.

So how do you do that in SQLAlchemy?

You have two options, really. One is to use the Query builder of SQLAlchemy's ORM, the other is using SQLAlchemy Core (upon which the ORM is built) directly. I really prefer the later, because it maps more directly to SELECT statements, but I'm going to show both.

Using SQLAlchemy Core

How to do a join in Core is documented here. First argument is the table to JOIN to, second argument is the JOIN-condition.

from sqlalchemy import select, func

query = select(
    [
        Customer.idCustomer,
        func.max(Product.downstream),
    ]
).select_from(
    Customer.__table__
    .join(Customer_has_product.__table__,
          Customer_has_product.Customer_idCustomer ==
              Customer.idCustomer)
    .join(Product.__table__,
          Product.idProduct == Customer_has_product.Product_idProduct)
).group_by(
    Customer.idCustomer
)

# Now we can execute the built query on the database.
result = db_session.execute(query).fetchall()
print(result)  # Should now give you the correct result.

Using SQLAlchemy ORM

To simplify this it's best to declare some [relationships on your models][2].joinis documented [here][2]. First argument tojoin` is the model to join onto and the second argument is the JOIN-condition again.

Without the relationships you'll have to do it like this.

result = (db_session
    .query(Customer.idCustomer, func.max(Product.downstream))
    .join(Customer_has_product,
          Customer_has_product.Customer_idCustomer ==
              Customer.idCustomer)
    .join(Product,
          Product.idProduct == Customer_has_product.Product_idProduct)
    .group_by(Customer.idCustomer)
).all()
print(result)

This should be enough to get the idea on how to do this.

Get Max Value of Column out of a a query list in Python

1 Answers1

So how do you do that in SQLAlchemy?

Using SQLAlchemy Core

Using SQLAlchemy ORM