Incubator-superset: "TypeError: 'dict' object does not support indexing" from CSV endpoint when SQL query contains "%" symbol

[joshuacano]

There is a somewhat similar issue that was fixed earlier, but this is specifically with the csv endpoint for SQL Lab. Looks like this fix has been reverted in the latest superset. >22.0. The logic that moved the query to sql alchemy is now back in pandas sans raw_connection which causes a problem with the '%' character again.

Superset version

0.23.2
deployed on ubuntu 16.04.2
Using the postgresql+psycopg2 connection string to a redshift db.

Expected results
I expect to get a .csv file back from the csv endpoint.

Actual results
Traceback (most recent call last): File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/flask/app.py", line 1982, in wsgi_app response = self.full_dispatch_request() File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/flask/app.py", line 1614, in full_dispatch_request rv = self.handle_user_exception(e) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/flask/app.py", line 1517, in handle_user_exception reraise(exc_type, exc_value, tb) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise raise value File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/flask/app.py", line 1612, in full_dispatch_request rv = self.dispatch_request() File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/flask/app.py", line 1598, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/superset/utils.py", line 709, in wraps return f(self, *args, **kwargs) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/superset/models/core.py", line 883, in wrapper value = f(*args, **kwargs) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/superset/views/core.py", line 2437, in csv df = query.database.get_df(sql, query.schema) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/superset/models/core.py", line 684, in get_df df = pd.read_sql(sql, eng) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/pandas/io/sql.py", line 416, in read_sql chunksize=chunksize) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/pandas/io/sql.py", line 1092, in read_query result = self.execute(*args) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/pandas/io/sql.py", line 983, in execute return self.connectable.execute(*args, **kwargs) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2075, in execute return connection.execute(statement, *multiparams, **params) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 942, in execute return self._execute_text(object, multiparams, params) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1104, in _execute_text statement, parameters File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context context) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1416, in _handle_dbapi_exception util.reraise(*exc_info) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise raise value File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context context) File "/home/superset/allvenvs/superset/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute cursor.execute(statement, parameters) TypeError: 'dict' object does not support indexing

Steps to reproduce
Here is the query submitted:

SELECT firstname, lastname FROM bf_raw.bf_user
where firstname like 'A%' limit 10

[xrmx]

Can you add a print(sql) around superset/models/core.py lime 683 to print the sql we are passing to pandas?

[joshuacano]

Sure, it is the same as the sql passed above `SELECT firstname, lastname FROM bf_raw.bf_user where firstname like 'A%' limit 10', just seems to be a problem with pyscopg2 and pandas.

To overcome this I used the following solution, just adding an extra % to escape in this case
SELECT firstname, lastname FROM bf_raw.bf_user where firstname like 'A%%' limit 10
See below:
https://stackoverflow.com/questions/40442292/how-to-use-a-wildcard-in-pandas-read-sql

Should I add this to the documentation and close the ticket? Or will this be handled in an upcoming fix. Seems like there will need to be some regexing/replacing of the sql string to handle this. Thanks for your prompt response also.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.