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

# 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!

### Say thanks, ask questions or give feedback

Technologies get updated, syntax changes and honestly… I make mistakes too. If something is incorrect, incomplete or doesn’t work, let me know in the comments below and help thousands of visitors.

## 1 thought on “Solve TypeError: ‘dict’ object does not support indexing when running SQL queries in Python”

1. With havin so much content and articles do you ever run into any problems of plagorism
or copyright infringement? My website has a lot of completely unique content I’ve either written myself or outsourced
but it appears a lot of it is popping it up all over
the internet without my permission. Do you know any
ways to help protect against content from being stolen? I’d genuinely appreciate it.