Use MagicMock to mock sqlAlchemy query object within larger query

Question

We build our SqlAlchemy queries dynamically in a way similar to the following:

def get_foo_results():
    included_bars_query = _get_included_bars_query(70)
    foo_results_query = (
        session.query(
            Foo.baz.label("baz"),
            Foo.qux.label("qux"),
            func.sum(Foo.dollars).label("dollars"),
        )
        .filter(
            Foo.bar.in_(included_bars_query),
        )
        .group_by(Foo.baz, Foo.qux)
    )
    return foo_results_query

def _get_included_bars_query(batch_number):
    valid_bars_query = session.query(Bars.bar_id.label("BAR_ID"))
    valid_bars_query = valid_bars_query.filter(Bars.batch_number > batch_number)
    return valid_bars_query

The challenge is that we are trying to get more disciplined about targeted unit testing using mocks so we don't have to build a whole db for every test. I've figured out how to create a MagicMock object to represent the _get_included_bars_query subquery from get_foo_results. But I'm not sure how to make that mock object work within the larger query. I've tried

bars_query.return_value.subquery.return_value = [1, 2,]

and

bars_query.return_value.all.return_value = [1, 2,]

and

bars_query.return_value.subquery.return_value = [(1,), (2,),]

And I feel like I'm close and if I had a better understanding of SqlAlchemy I'd be there, but can someone explain where I'm off please?

thank you!

Answer 1

I'm tempted to delete the question, because I was overthinking it.

The answer is:

bars_query.return_value = [1, 2,]