critical bug - csvsql "ObjectNotExecutableError" due to SQLAlchemy 2.0 deprecations now unsupported

[maaaaz]

Hello there,

Here is a critical bug due the SQLAlchemy depreciated features:

1. cd /tmp/ && wget https://github.com/wireservice/csvkit/raw/master/examples/foo1.csv

2. csvsql with SQLAlchemy < 2.0: some warnings, but execution is OK

   
   $ SQLALCHEMY_WARN_20=1 csvsql -u 1 foo1.csv --tables db --query 'SELECT * FROM db'

/usr/local/lib/python3.11/dist-packages/csvkit/utilities/csvsql.py:236: RemovedIn20Warning: Passing a string to Connection.execute() is deprecated and will be removed in version 2.0. Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9) id,name,age 1.0,Jake,22.0 2.0,Howard,21.0

3. csvsql with **SQLAlchemy >= 2.0**: crash with **"ObjectNotExecutableError"**

$ csvsql -u 1 foo1.csv --tables db --query 'SELECT FROM db' /tmp/lol/foo2/lib/python3.11/site-packages/pkg_resources/init.py:121: DeprecationWarning: pkg_resources is deprecated as an API ObjectNotExecutableError: Not an executable object: 'SELECT FROM db'

The root cause to fix seems to wrap the following SQL string parameter "query", such as documented in the warning, and also [here](https://stackoverflow.com/a/69491015) with [sqlalchemy text](https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.text)

https://github.com/wireservice/csvkit/blob/fa9e0db1cdd30757835912e48467d6ab2be02c14/csvkit/utilities/csvsql.py#L240 

https://github.com/pandas-dev/pandas/issues/51061
https://github.com/sqlalchemy/sqlalchemy/issues/9155

[maaaaz]

By the way, before csvkit fixes this issue, the current ugly workaround is to do a pip install sqlalchemy==1.4.49 after pip install csvkit, to force an old version of sqlalchemy to be used.

[jpmckinney]

Thank you for reporting!

[maaaaz]

Thanks, when did you plan to update the package on pypi ?

[jpmckinney]

Up: https://pypi.org/project/csvkit/

[maaaaz]

Great, I will try !