Solve TypeError: ‘dict’ object does not support indexing when running SQL queries in Python

I ran into another silly error, for which I wanted to share the solution in order to save you some time. It occurs when trying to run a query using Python’s SQLAlchemy libary. Let’s dive right in.

The problem

When you’re trying to run a query, either by using Pandas’ read_sql or SQLAlchemy’s execute, there’s a chance you’ll run into this cryptic error message:

TypeError: ‘dict’ object does not support indexing

Initially, I had no idea what was going on. However, my query contained percentage signs (%), in a LIKE operator. And percentage signs are special in Python.

q = "SELECT  * FROM sometable WHERE category LIKE '%somestring%'"
engine.execute(q) # Using SQLAlchemy: doesn't work
pd.read_sql(q) # Using Pandas: doesn't work either, because Pandas uses SQLAlchemy

The problem here is that the percentage sign is treated as bind parameter. SQLAlchemy expects the percentage to be a parameter of type ‘format’ or ‘pyformat’.

Note above, the usage of a question mark “?” or other symbol is contingent upon the “paramstyle” accepted by the DBAPI in use, which may be any of “qmark”, “named”, “pyformat”, “format”, “numeric”. See pep-249 for details on paramstyle.

SQLAlchemy documentation

The solution

There are two sensible ways to make SQLAlchemy ignore the bind parameters.

First, selectively escape the percentage sign. Intuitively you’d do this with a backslash. However, this doesn’t work: you need to specify a second %.

q = "SELECT  * FROM sometable WHERE category LIKE '%%somestring%%'"
engine.execute(q)

A second solution is to use SQLAlchemy’s text function. It’s main use is constructing a query with bind parameters, but if none are specified, it explicitly ignores the percentages signs.

q = sqlalchemy.text("SELECT  * FROM sometable WHERE category LIKE '%somestring%'")
engine.execute(q)

Great success!