Extraño mensaje de error de SQLAlchemy: TypeError: el objeto ‘dict’ no admite la indexación

4 minutos de lectura

avatar de usuario
Homúnculo Retículo

Estoy usando SQL hecho a mano para obtener datos de una base de datos PG, usando SqlAlchemy. Estoy probando una consulta que contiene el operador similar a SQL ‘%’ y eso parece arrojar SqlAlcjhemy a través de un bucle:

sql = """
       SELECT DISTINCT u.name from user u
        INNER JOIN city c ON u.city_id = c.id
        WHERE c.designation=upper('fantasy') 
        AND c.id IN (select id from ref_geog where short_name LIKE '%opt')

# The last line in the above statement throws the error mentioned in the title. 
# However if the last line is change to:
# AND c.id IN (select id from ref_geog where short_name="helloopt")
# the script runs correctly.
# I also tried double escaping the '%' i.e. using '%%' instead - that generated the same error as previously.

res = executeSql(sql)
print res

¿Alguien sabe qué está causando este mensaje de error engañoso y cómo puedo solucionarlo?


Antes de que alguien pregunte, no hay nada especial o sofisticado en las funciones incluidas anteriormente. Por ejemplo, la función executeSql() simplemente invoca conn.execute(sql) y devuelve los resultados. La variable conn es simplemente la conexión previamente establecida a la base de datos.

  • puedes publicar el codigo de executeSql(...)? Y también, ¿realmente tienes RETURNING * en el SELECT ¿declaración?

    – camioneta

    28 de diciembre de 2011 a las 15:12

  • @van Me perdí ese. No hay ‘RETORNO *’ en el SQL que está causando el problema. Corregiré la pregunta.

    – Homúnculo Reticulli

    28 de diciembre de 2011 a las 15:17

  • es esta respuesta [stackoverflow.com/questions/3944276/… helpful?

    – van

    Dec 28, 2011 at 15:45

  • @van: Thanks!. yes it does. I had to use ‘\%%’ instead of ‘%’. The statement is correctly executed now.

    – Homunculus Reticulli

    Dec 28, 2011 at 15:53

  • great. please post a short answer (and accept it) which worked for you for the sake of completeness.

    – van

    Dec 28, 2011 at 16:38

You have to give %% to use it as % because % in python is use as string formatting so when you write single % its assume that you are going to replace some value with this.

So when you want to place single % in string with query allways place double %.

  • I wish they would of updated that error message, every time I get it I end up landing on this page and answer

    – oshi2016

    Mar 16, 2017 at 5:46

SQLAlchemy has a text() function for wrapping text which appears to correctly escape the SQL for you.


res = executeSql(sqlalchemy.text(sql))

should work for you and save you from having to do the manual escaping.

  • This should be the selected answer. It solved the issue in my case.

    – Gani Simsek

    Feb 20, 2017 at 13:11

  • Note that this does not escape comments, but otherwise is a fantastic solution.

    – ClimbsRocks

    Sep 29, 2017 at 21:23

  • That worked for me, and was easier to implement than changing all our queries with double %

    – Philippe Oger

    Nov 6, 2019 at 18:51

I cannot find the “executeSql” in sqlalchemy version 1.2 docs , but the below line worked for me


I found one more case when this error shows up:

c.execute("SELECT * FROM t WHERE a = %s")

In other words, if you provide parameter (%s) in query, but you forget to add query params. In this case error message is very misleading.

It seems like your problem may be related to this bug.

In which case, you should triple-escape as a workaround.

user avatar

One more note- you must escape (or delete) % characters in comments as well. Unfortunately, sqlalchemy.text(query_string) does not escape the percent signs in the comments.

user avatar

Another way of solving your problem, if you don’t want to escape % characters or use sqlalchemy.text(), is to use a regular expression.

Instead of:

select id from ref_geog where short_name LIKE '%opt'

Try (for case-sensitive match):

select id from ref_geog where short_name ~ 'opt$' 

or (for case-insensitive):

select id from ref_geog where short_name ~* 'opt$'

Both LIKE and regex are covered in the documentation on pattern matching.

Note that:

Unlike LIKE patterns, a regular expression is allowed to match anywhere within a string, unless the regular expression is explicitly anchored to the beginning or end of the string.

For an anchor, you can use the assertion $ for end of string (or ^ for beginning).

¿Ha sido útil esta solución?