Support for .select_from() and .subquery()

[hozher]

Hello,

Is there a way of mocking this kind of queries?

I am making use of .subquery() and .select_from() without success

salesParamsModel = Base.classes.SalesParams
lastSalesParams = aliased(salesParamsModel)
last_sales_params_derived = session.query(
        func.max(lastSalesParams.salesParamsId).label('salesParamsId')
    ).filter(lastSalesParams.recordId == recordId).subquery()

salesParams = aliased(salesParamsModel)
priceCalculation = Base.classes.PriceCalculation
main_query = session.query(
        salesParams.salesParamsId,
        salesParams.recordId,
        salesParams.ebcGuide,
        salesParams.kms,
        salesParams.color,
        salesParams.licencePlate,
        salesParams.licencePlateStateId,
        salesParams.debt,
        salesParams.invoiceDate,
        salesParams.isLcv,
        salesParams.uploadedByUserId,
        salesParams.uploadedAt,
        salesParams.updatedByUserId,
        salesParams.updatedAt,
        salesParams.isForSale,
        priceCalculation.value,
        priceCalculation.calculatedAt
    ).select_from(last_sales_params_derived).\
    join(salesParams, salesParams.salesParamsId == last_sales_params_derived.c.salesParamsId).\
    join(priceCalculation, priceCalculation.salesParamsId == salesParams.salesParamsId).\
    filter(priceCalculation.priceStructureId == 1)

[miki725]

you can use AlchemyMagicMock which is like a standard mock but allows to compare sqlalchemy expressions. as such it can mock anything including subquery and select_from. that will not attempt to return "real" data like UnifiedAlchemyMagicMock attempts though.

currently UnifiedAlchemyMagicMock does not support subqueries like in your example above. if support is to be added the challenging part is that UnifiedAlchemyMagicMock matches the data to return by the set of expressions. for example:

>>> session = UnifiedAlchemyMagicMock(data=[
...     (
...         [mock.call.query(Model)],
...         [Model(foo=5, bar=11)]
...     )
... ])

above returns Model(foo=5, bar=11) when session is queried with mock.call.query(Model). however in your example the query is made with a dynamic function. not sure how you would reference that in UnifiedAlchemyMagicMock. only option I can see is not actually mocking the subquery since technically that does not hit the DB and therefore not really needs to be mocked but mock only the part which uses the subquery. then if you can have access to salesParams in your tests you can do:

>>> session = UnifiedAlchemyMagicMock(data=[
...     (
...         [mock.call.query(salesParams.salesParamsId, ...)],
...         [Result(...)]
...     )
... ])