Group by multiple columns in sqlalchemy
<p>I have written this python function to query a database using the SQLAlchemy package:</p>

<pre><code>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 &gt;= start_date,
        MyModel.SnapDate &lt;= end_date
    )\
    .group_by(
        groupby
    ).all()
    return(data)

test_1 = group_by_one_var("2016-08-01", "2016-08-31", MyModel.Country) # Success
</code></pre>

<p>Which does a good job of grouping my query by a variable of my choosing.</p>

<p>However, I'm stuck when it comes to grouping by multiple variables.</p>

<p>Here is a function I wrote to group by two variables:</p>

<pre><code>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 &gt;= start_date,
        MyModel.SnapDate &lt;= 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
</code></pre>

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

<p>How can I alter these functions to accept a dynamic number of group bys?</p>

<p>Thanks!</p>

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

<pre><code>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 &gt;= start_date,
        MyModel.SnapDate &lt;= end_date
    )\
    .group_by(
        *groupby
    ).all()
    return(data)
</code></pre>

python sqlalchemy