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

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.

connectDb()
res = executeSql(sql)
print res
closeDbConnection()

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

[[Edit]]

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.

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

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

I.e.

res = executeSql(sqlalchemy.text(sql))

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

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

engine.execute(sqlalchemy.text(sql_query))

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.

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.

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