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.
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
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)