Mocking scalar()

[jonyscathe]

Hi,

We are using a lot of scalar() in our project: https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.scalar

Usually to get a single value as per SQLAlchemys example session.query(Item.id, Item.name).scalar()

Is there currently any way to do this with mock-alchemy?
Currently the best option I seem to have seems to be after creating a mock_session to make a new boundary lambda for scalar() such as:

def get_scalar(rows: list[Any]) -> Any:
    if len(rows) == 1:
        for key in rows[0].__mapper__.c.keys():
            if getattr(rows[0], key) is not None:
                return getattr(rows[0], key)
    elif len(rows) > 1:
        raise MultipleResultsFound("Multiple rows were found for scalar()")
    else:
        return None

mock_session = UnifiedAlchemyMagicMock(
        data=[...]
)
mock_session.boundary['scalar']= lambda x: get_scalar(x)
mock_session.scalar = AlchemyMagicMock(side_effect=partial(mock_session._get_data, _mock_name='scalar'))

Is there some better way to be mocking scalar with the UnifiedAlchemyMagicMocks?
Or if there is the best way, could we get scalar() added to the standard boundary dict?

[rajivsarvepalli]

scalar() was not a supported operation, but I think the simplest way to add it through the standard boundary dict. Tracking this from an issue #154. I will work on adding it. Thanks for describing your issue in detail and preemptively providing the solution.

[rajivsarvepalli]

One question I had based on your get_scalar function is should scalar return the second column if the first column has a value of None? I have modified your function to return the first column of the first row in every scenario since that is what SQLAlchemy scalar seems to do (irrespective of null values). Take a look at the PR #155 and get_scalar implementation and let me know what you think.

[jonyscathe]

Hi Rajiv,

Yes you are correct that it should just return the first value. And if it is something that is going to be added to the library the way you have done it is best.

The only reason I was returning the first non null value was to be a bit lazy so that I could have a table:

class SomeTable(base):
    pkey = Column(Integer, primary_key=True)
    val1 = Column(Integer)
    val2 = Column(Integer)

Then have a unified alchemy magic mock:

mock_session = UnifiedAlchemyMagicMock(
    data=[
        (
            [mock.call.query(mock_database.SomeTable.val1), mock.call.filter(mock_database.SomeTable.pkey==3)],
            [mock_database.SomeTable(val1 = 4)],
        )
    ]
)

Then by calling mock_session.query(mock_database.SomeTable.val1).filter(mock_database.SomeTable.pkey==3).scalar() I would get a value of 4 as my get_scalar function would skip over the null primary key.

But of course the better way to do this is to have a function like what you have suggested in PR #155 and then in my UnifiedAlchemyMagicMock have a return data value with only SomeTable.val1 defined as this is then more representative of what SQLAlchemy actually does.

[rajivsarvepalli]

scalar() should be added with latest release: mock-alchemy-v0.2.4.

[jonyscathe]

Awesome - thanks so much

[jonyscathe]

Hi Rajiv,

I just realised I completely stuffed up on my solution for .scalar()
Regarding the full example:

>>> session.query(Item).scalar()
<Item>
>>> session.query(Item.id).scalar()
1
>>> session.query(Item.id).filter(Item.id < 0).scalar()
None
>>> session.query(Item.id, Item.name).scalar()
1
>>> session.query(func.count(Parent.id)).scalar()
20

When querying on the full Item, scalar returns the whole first item if there is only 1 row or MultipleResultsFound: Multiple rows were found when exactly one was required when there are multiple rows.
It only returns individual values in the other examples when querying a column or columns.

This makes the definition slightly tricky.
In the first example above session.query(Item) will be a list of class Item and if the list is of length 1 then scalar() should return Item (so basically identical to the one_or_none definition), which would be x[0]
But in the second case where the query is only on one column then I believe the input to the function would be a list of sqlalchemy row objects, in the case: [(1, )] and the scalar function should return x[0][0] or x{0].id
And in a third case if the filter matches nothing

Something like this seems to work. I'm not a huge fan of doing this why a try, except but it is simple and works.
Trying to identify if rows[0] is a table object or a row object is a bit of a pain. I tried using isinstance(rows[0], sqlalchemy.engine.row.Row) but then declaring my object is a row is not terribly easy as the documentation on sqlalchemy.engine.row.Row isn't great. Alternatively, using isinstance(rows[0], tuple) isn't terrible as I imagine most people mocking queries of incomplete rows use tuples given that sqlalchemy row objects are designed to be very similar to a named tuple, but then it wouldn't work for anyone who is actually using proper row objects in their mocking

    if len(rows) == 1:
        try:
            return rows[0][0]
        except TypeError:
            return rows[0]
    elif len(rows) > 1:
        raise MultipleResultsFound('Multiple rows were found when exactly one was required')
    return None

[rajivsarvepalli]

Good point will update implementation while looking into #161

[jonyscathe]

Thanks. And sorry about that. I should have realised I had it slightly wrong in the first place. This is one of the issues with me writing unit tests for code that someone else developed!

Also, out of curiosity, have you looked at what is changing in SQLAlchemy 2.0 and how that will impact mock-alchemy?

[rajivsarvepalli]

Briefly, I will have to take a closer look to figure what the impact will be.