python - Strange SQLAlchemy error message: TypeError: 'dict' object does not support indexing

Question

I am using hand crafted SQL to fetch data from a PG database, using SqlAlchemy. I am trying a query which contains the SQL like operator '%' and that seems to throw SqlAlcjhemy through a loop:

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

Any one knows what is causing this misleading error message and how I may fix it?

[[Edit]]

Before any one asks, there is nothing special or fancy about the functions included above. For example the function executeSql() simply invokes conn.execute(sql) and returns the results. The variable conn is simply the previously established connection to the database.

Answer 1

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

Answer 2

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.

Answer 3

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

engine.execute(sqlalchemy.text(sql_query))

Answer 4

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

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

Answer 5

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.

Answer 6

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.

Answer 7

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

Answer 8

This could also result from the case - in case parameters to be passed onto the SQL are declared in DICT formate and are being manipulated in the SQL in the form of LIST or TUPPLE.