TypeError: 'dict' object does not support indexing when filter contains "%" symbol

[rsxm]

Hi I get a TypeError: 'dict' object does not support indexing error when trying to filter on a value that contains the "%" character.

Caravel version (from amancevice/caravel:0.9.0):

>>> caravel.version.__version__ 
(0, 9, 0)

I'm using postgresql 9.4 as the caravel database and datasource.

From the logs, this was the URL that caused the error: /caravel/explore/table/1/?[...]&extra_filters={"1":{"record":["Net Margin %"]}}[...]

The extra_filter bit was encoded like this in the logs: %7B%221%22%3A%7B%22record%22%3A%5B%22Net%20Margin%20%25%22%5D%7D%7D

Full stack trace is in next comment.

[rsxm]

Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python2.7/dist-packages/flask_appbuilder/security/decorators.py", line 26, in wraps
    return f(self, *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/caravel/models.py", line 1294, in wrapper
    return f(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/caravel/views.py", line 557, in explore
    payload = obj.get_json()
  File "/usr/local/lib/python2.7/dist-packages/caravel/viz.py", line 258, in get_json
    'data': self.get_data(),
  File "/usr/local/lib/python2.7/dist-packages/caravel/viz.py", line 956, in get_data
    df = self.get_df()
  File "/usr/local/lib/python2.7/dist-packages/caravel/viz.py", line 865, in get_df
    df = super(NVD3TimeSeriesViz, self).get_df(query_obj)
  File "/usr/local/lib/python2.7/dist-packages/caravel/viz.py", line 144, in get_df
    self.results = self.datasource.query(**query_obj)
  File "/usr/local/lib/python2.7/dist-packages/caravel/models.py", line 725, in query
    con=engine
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 431, in read_sql_query
    parse_dates=parse_dates, chunksize=chunksize)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 1190, in read_query
    result = self.execute(*args)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 1081, in execute
    return self.connectable.execute(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1991, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 906, in execute
    return self._execute_text(object, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1054, in _execute_text
    statement, parameters
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1344, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
TypeError: 'dict' object does not support indexing

[xrmx]

Is this still an issue with latest caravel?

[rsxm]

I can confirm that using the latest (Python 3 based) Caravel no longer gives this error. Relevant versions tested:

Caravel 0.10.0 Python 3.5.1 Postgres 9.5 and Sqlite OS X 10.11.6 and Ubuntu 16.04

Thank you very much!

[mpacula]

I am getting the same issue with Superset 0.13.2 running on Python 3.5:

Traceback (most recent call last):
  File "/home/cid/repos/superset/venv/lib/python3.5/site-packages/superset/sql_lab.py", line 113, in get_sql_results
    result_proxy = engine.execute(query.executed_sql, schema=query.schema)
  File "/home/cid/repos/superset/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1991, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/home/cid/repos/superset/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 906, in execute
    return self._execute_text(object, multiparams, params)
  File "/home/cid/repos/superset/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1054, in _execute_text
    statement, parameters
  File "/home/cid/repos/superset/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/home/cid/repos/superset/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1344, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/home/cid/repos/superset/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value
  File "/home/cid/repos/superset/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/cid/repos/superset/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
TypeError: 'dict' object does not support indexing

Here's the query (names changed):

SELECT * from my_schema.my_table where "MyColumn" LIKE '%FFPE%'  limit 10

Thank you for your help!

[mistercrunch]

What database engine / driver are you using?

[mpacula]

GreenPlum (Postgres 8.2 compatible). The same query works in pgAdmin without issue.

[mistercrunch]

What about the driver? psycopg2? What does the pre :// portion of your connection string look like?

[mpacula]

postgresql://

[mistercrunch]

mind trying postgresql+psycopg2:// ? you may need to pip install psycopg2 first

[mpacula]

Same error with postgresql+psycopg2://

[scottedwards2000]

@mpacula @mistercrunch is this an issue with the psycopg2 library? I'm using it with sqlalchemy and getting same error when my sql contains % in WHERE clause criteria

[joshuacano]

+1 same issue here.

[mistercrunch]

There are many issues described here (same error msg, different stack traces), and many of those have been reported a long time ago. Since then much work has been done.

@joshuacano please open a new issue with more context (stack grace, version, connection string, ...)

[joshuacano]

@mistercrunch Yep, Totally understand. I've done so here - #2710