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 with sqlalchemy text

csvkit/csvkit/utilities/csvsql.py

Line 240 in fa9e0db

rows = self.connection.execute(query)

pandas-dev/pandas#51061
sqlalchemy/sqlalchemy#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 !