CODEWITHSUNDEEP
pythonsqlalchemy
Eugene Brown
Eugene Brown

Reputation: 4352

Group by multiple columns in sqlalchemy

I have written this python function to query a database using the SQLAlchemy package:

def group_by_one_var(start_date, end_date, groupby):
    data = db.session.query(
        groupby,
        MyModel.SnapDate,
        func.count(MyModel.CustomerID).label("TotalCustomers")
    )\
    .filter(
        MyModel.SnapDate >= start_date,
        MyModel.SnapDate <= end_date
    )\
    .group_by(
        groupby
    ).all()
    return(data)

test_1 = group_by_one_var("2016-08-01", "2016-08-31", MyModel.Country) # Success

Which does a good job of grouping my query by a variable of my choosing.

However, I'm stuck when it comes to grouping by multiple variables.

Here is a function I wrote to group by two variables:

def group_by_two_vars(start_date, end_date, groupby):
    data = db.session.query(
        groupby[0],
        groupby[1],
        MyModel.SnapDate,
        func.count(MyModel.CustomerID).label("TotalCustomers")
    )\
    .filter(
        MyModel.SnapDate >= start_date,
        MyModel.SnapDate <= end_date
    )\
    .group_by(
        groupby[0]
    )\
    .group_by(
        groupby[1]
    ).all()
    return(data)

tes2 = group_by_two_vars("2016-08-01", "2016-08-31", (MyModel.Country, MyModel.Currency)) # Success

This function also does a fine job grouping my query by two variables of my choosing.

How can I alter these functions to accept a dynamic number of group bys?

Thanks!

Upvotes: 4

Views: 5789

Answers (1)

Eugene Brown
Eugene Brown

Reputation: 4352

This is how you can parse a dynamic number of groupby arguments and have SQLAlchemy include them all in the query:

def group_by_n_vars(start_date, end_date, *groupby):
    data = db.session.query(
        *groupby,
        MyModel.BookingDateLocal,
        func.count(MyModel.BookingId).label("TotalCustomers")
    )\
    .filter(
        MyModel.SnapDate >= start_date,
        MyModel.SnapDate <= end_date
    )\
    .group_by(
        *groupby
    ).all()
    return(data)

Upvotes: 4

Related Questions