Use of sqlalchemy.case causes TypeError: expected bytes, str found

[AutumnSun1996]

Use of sqlalchemy.case causes TypeError: expected bytes, str found.

A minimal example:

import sqlalchemy as sa

tbl = sa.table('some_table', sa.column('c0', sa.Integer))

q = sa.select(
    tbl.c.c0, 
    sa.case(
     (tbl.c.c0 < 1, 'test'),
    )
)
conn = some_engine.connect()
data = conn.execute(q).fetchall()

it gives following error message:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-57-40769502cd2d> in <module>
      6 ).limit(10)
      7 
----> 8 data = conn.execute(q).fetchall()
      9 # error

/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/result.py in fetchall(self)
    989         """A synonym for the :meth:`_engine.Result.all` method."""
    990 
--> 991         return self._allrows()
    992 
    993     def fetchone(self):

/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/result.py in _allrows(self)
    401         rows = self._fetchall_impl()
    402         if make_row:
--> 403             made_rows = [make_row(row) for row in rows]
    404         else:
    405             made_rows = rows

/opt/conda/lib/python3.8/site-packages/sqlalchemy/engine/result.py in <listcomp>(.0)
    401         rows = self._fetchall_impl()
    402         if make_row:
--> 403             made_rows = [make_row(row) for row in rows]
    404         else:
    405             made_rows = rows

TypeError: expected bytes, str found

Same code works for postgresql and mysql.

versions:
Python 3.8.8
impyla 'v0.16.3'
sqlalchemy '1.4.2'

I'm using a workaround to fix it for now: cast the result of case as string.

Is there a better solution for this problem?

[attilajeges]

Hi, could you try impyla 0.17.0 ? It contains lots of bugfixes.

[ArVar]

I had the exact same error with Netezza as db. There seems to be difference in the behaviour of sqlalchemy 1.3.x and 1.4.x making some type casting uneccessary. This led to breaking change for the dialect (officially the Netezza driver nzpy is not yet compatibel with sqlalchemy 1.4.x). This might be the case here, too.
In detail:
make_rows expect (n)varchar fields to be byte encoded (b'value') but the values are stored in the row object as encoded string objects ('value'). In my case the originally correct byte-encoded string was casted unneccessarily:

value = str(fieldDataP[2:cursize+2], self._client_encoding) #decode byte-encoded string
# instead of
value = fieldDataP[2:cursize+2] #leave byte-encoded string as is

I hope this helps for further investigations. Might be related to #457

[AutumnSun1996]

Just have time to test it. Sadly, v0.17.0 and the version from #457 suffers the same error.
Both give the same error as v0.16.3:

Traceback (most recent call last):
  File "demo.py", line 29, in <module>
    data = conn.execute(q).fetchall()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/result.py", line 991, in fetchall
    return self._allrows()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/result.py", line 403, in _allrows
    made_rows = [make_row(row) for row in rows]
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/result.py", line 403, in <listcomp>
    made_rows = [make_row(row) for row in rows]
TypeError: expected bytes, str found

Still have to use the cast workaround for now.