Skip to content
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.

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

  1. Do you have a spam problem on this website; I also am a blogger,
    and I was wanting to know your situation; many of us have created some nice procedures and we are looking to
    exchange strategies with other folks, please shoot me an e-mail if interested.

  2. Does your blog have a contact page? I’m having trouble locating it but, I’d like to shoot
    you an e-mail. I’ve got some suggestions for your blog you might be
    interested in hearing. Either way, great website and I look forward to
    seeing it improve over time.

  3. Howdy! Do you know if they make any plugins to help with SEO?
    I’m trying to get my blog to rank for some targeted keywords but I’m
    not seeing very good gains. If you know of any please share.
    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *