Why is SQLAlchemy producing an error when I try to execute a raw SQL-String with an percent sign in it?

Question

I try to execute essentially some sql like this (which gets builded in a complicated way):

sql = "SELECT COUNT(*) FROM entities WHERE (unit = '%')"

with this Python code:

engine.execute(sql)

What happens then is I get a

TypeError: 'dict' object does not support indexing

Why is this the case?

If my answer below doesn't help, could you please include the full traceback to the exception please? — Martijn Pieters
– Martijn Pieters, Commented Sep 11, 2012 at 10:48

Martijn Pieters · Accepted Answer · 2012-09-11 10:47:52Z

10

Not 100% sure, but I think SQLAlchemy is trying to intepret the % character as a SQL parameter. I'd try doubling the % character to work around this:

sql = "SELECT COUNT(*) FROM entities WHERE (unit = '%%')"

This depends on what database you are using; different database adapters use different parameter styles. The psycopg2 module, used for PostgreSQL database connections, for example uses the %s style and documents that %% is the correct way to insert a % literal value into your SQL statements.

edited Sep 11, 2012 at 10:47

answered Sep 11, 2012 at 10:03

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

1 Comment

Jason Champion Over a year ago

Thank you for this great answer. I was trying to escape with \% and getting nowhere.

Collectives™ on Stack Overflow

Why is SQLAlchemy producing an error when I try to execute a raw SQL-String with an percent sign in it?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related