UNION in SQL causes TypeError: Argument 'val' has incorrect type (expected tuple, got list), in converters.pyx line 23, in asyncmy.converters.escape_item

mrmichaelrb commented 1 year ago

Describe the bug

The following type of query works fine with normal sync SQLAlchemy engine/connection/execute, but throws a TypeError exception "Argument 'val' has incorrect type (expected tuple, got list)" when using mysql+aiomysql and async engine/connection/execute:

SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2

To Reproduce

Query a MySQL database using SQLAlchemy with two SELECT statements combined with a UNION operator.

Expected behavior

When using a UNION operator, an async query should return the same results as a sync query, instead of throwing an exception.

Logs/tracebacks

TypeError: Argument 'val' has incorrect type (expected tuple, got list)
Traceback (most recent call last):
...
  File "\venv\lib\site-packages\asgiref\sync.py", line 218, in __call__
    return call_result.result()
  File "C:\Program Files\Python\Python310\lib\concurrent\futures\_base.py", line 439, in result
    return self.__get_result()
  File "C:\Program Files\Python\Python310\lib\concurrent\futures\_base.py", line 391, in __get_result
    raise self._exception
  File "\venv\lib\site-packages\asgiref\sync.py", line 284, in main_wrap
    result = await self.awaitable(*args, **kwargs)
  File "\tarvis\indicators\ma.py", line 42, in get_kline_history_table_smas_async
    samples = await get_kline_history_table_close_samples_async(
  File "\venv\lib\site-packages\dependency_injector\wiring.py", line 906, in _patched
    return await _async_inject(
  File "src/dependency_injector/_cwiring.pyx", line 66, in _async_inject
  File "\tarvis\indicators\sampling.py", line 109, in get_kline_history_table_close_samples_async
    query_result = await db_connection.execute(
  File "\venv\lib\site-packages\sqlalchemy\ext\asyncio\engine.py", line 451, in execute
    result = await greenlet_spawn(
  File "\venv\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 126, in greenlet_spawn
    result = context.throw(*sys.exc_info())
  File "\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 332, in _execute_on_connection
    return connection._execute_clauseelement(
  File "\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "\venv\lib\site-packages\sqlalchemy\engine\base.py", line 2047, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "\venv\lib\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
    raise exception
  File "\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "\venv\lib\site-packages\sqlalchemy\engine\default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "\venv\lib\site-packages\sqlalchemy\dialects\mysql\asyncmy.py", line 92, in execute
    return self.await_(self._execute_async(operation, parameters))
  File "\venv\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 68, in await_only
    return current.driver.switch(awaitable)
  File "\venv\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 121, in greenlet_spawn
    value = await result
  File "\venv\lib\site-packages\sqlalchemy\dialects\mysql\asyncmy.py", line 104, in _execute_async
    result = await self._cursor.execute(operation, parameters)
  File "asyncmy\cursors.pyx", line 178, in execute
  File "asyncmy\cursors.pyx", line 156, in asyncmy.cursors.Cursor.mogrify
  File "asyncmy\cursors.pyx", line 129, in genexpr
  File "asyncmy\cursors.pyx", line 129, in genexpr
  File "asyncmy\connection.pyx", line 426, in asyncmy.connection.Connection.literal
  File "asyncmy\connection.pyx", line 419, in asyncmy.connection.Connection.escape
  File "asyncmy\converters.pyx", line 10, in asyncmy.converters.escape_item
  File "asyncmy\converters.pyx", line 23, in asyncmy.converters.escape_item

Python Version

Python 3.10.5

aiomysql Version

Name: aiomysql
Version: 0.1.1

PyMySQL Version

Name: PyMySQL
Version: 1.0.2

SQLAlchemy Version

Name: SQLAlchemy
Version: 1.4.39

OS

Windows [Version 10.0.19044.1889]

Database type and version

MySQL
8.0.29

Additional context

No response

Code of Conduct

[X] I agree to follow the aio-libs Code of Conduct

Nothing4You commented 1 year ago

SQLAlchemy 1.4 is not currently supported, refer to #818.

If you install aiomysql[sa] it will limit the SQLAlchemy version to a supported one.

If you can reproduce this with SQLAlchemy 1.3, please also provide a code example.

Nothing4You commented 1 year ago

as I'm reading this again - it looks like your issue is with asyncmy, not aiomysql. asyncmy is a separate project, not related to aiomysql.

aio-libs / aiomysql