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!