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.

can you post the code of executeSql(...)? And also, do you really have RETURNING * in the SELECT statement? — van
– van, Commented Dec 28, 2011 at 15:12
@van I missed that one. There is no ' RETURNING *' in the SQL that is causing the problem. I will correct the question. — Homunculus Reticulli
– Homunculus Reticulli, Commented Dec 28, 2011 at 15:17
is this answer [stackoverflow.com/questions/3944276/… helpful? — van
– van, Commented 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
– Homunculus Reticulli, Commented 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
– van, Commented Dec 28, 2011 at 16:38

NPatel · Accepted Answer · 2011-12-29 10:08:47Z

368

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

answered Dec 29, 2011 at 10:08

NPatel

21.4k17 gold badges104 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

oshi2016 Over a year ago

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

Ilja Everilä · Accepted Answer · 2018-12-18 22:19:55Z

150

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.

edited Dec 18, 2018 at 22:19

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

answered Feb 20, 2013 at 19:47

user795753

3 Comments

Gani Simsek Over a year ago

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

ClimbsRocks Over a year ago

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

Philippe Oger Over a year ago

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

dixit_chandra · Accepted Answer · 2018-03-14 05:56:14Z

13

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

engine.execute(sqlalchemy.text(sql_query))

answered Mar 14, 2018 at 5:56

dixit_chandra

4763 silver badges14 bronze badges

Comments

Tupteq · Accepted Answer · 2016-04-04 13:53:54Z

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.

answered Apr 4, 2016 at 13:53

Tupteq

3,1141 gold badge23 silver badges35 bronze badges

Comments

Brian Cain · Accepted Answer · 2011-12-28 16:38:59Z

3

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

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

answered Dec 28, 2011 at 16:38

Brian Cain

14.6k3 gold badges53 silver badges89 bronze badges

Comments

ClimbsRocks · Accepted Answer · 2017-09-29 21:25:34Z

3

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.

answered Sep 29, 2017 at 21:25

ClimbsRocks

1,15415 silver badges16 bronze badges

Comments

C8H10N4O2 · Accepted Answer · 2019-07-08 17:12:32Z

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

Pralhad Narsinh Sonar · Accepted Answer · 2015-05-11 06:59:19Z

0

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.

answered May 11, 2015 at 6:59

Pralhad Narsinh Sonar

1,4541 gold badge14 silver badges23 bronze badges

Collectives™ on Stack Overflow

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

8 Answers 8

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related