SQLAlchemy Integration Broken with SQLAlchemy v2.0

[mepland]

SQLAlchemy has changed some of their function calls in newer versions which break SKompiler's SQLAlchemy methods. I did my testing with SQLAlchemy==2.0.21.

One simple fix is in the _iif() function return where

return sa.case([(cond, iftrue)], else_=iffalse)

should now be

return sa.case((cond, iftrue), else_=iffalse)

as the whens parameter is now a series of tuple arguments, not a list.

However, there is still an issue with the sa.select() calls. For example in translate() you can alter this line from

    result = sa.select(result.cols, from_obj=result.from_obj)

to

    result = sa.select(result.cols[0])

and get an output without errors, but the parameters are no longer replaced. The SQL output will be something like:

SELECT CASE WHEN ("Your Column Name Here" <= :param_1) THEN :param_2 ...

The from_obj parameter has been deprecated - along with all kwargs. After a quick look I don't see how to bring the values in from result.from_obj. This is also probably an issue with this sa.select() call in _make_cte().

There maybe other issues with the SQLAlchemy integration, these are just the two I found in my testing.

CC @konstantint