Syntax error while passing arguments in pandas sql query

Question

I'm using f notation in a pandas sql query on a postgres database. The following works fine:

areas = pd.read_sql_query(f"SELECT * FROM Areas WHERE dwelling_id={dwelling_id}", con=db.engine)

But when I repeat the same with a different table:

windows = pd.read_sql_query(f"SELECT * FROM Window WHERE area_id={area_id}", con=db.engine)

I get the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "Window"
LINE 1: SELECT * FROM Window WHERE area_id=1
^
[SQL: SELECT * FROM Window WHERE area_id=1]

Note that I abandoned the usual params=(variable, ) approach because I couldn't get it to work. Strange that the f notation approach works with the first query but not the second.

Interestingly, I get the same error when I query the windows table in the Heroku app for interograting the database, whereas the other tables do not show the same error:

Can anyone help!

Note that passing values to SQL queries using any string formatting is error prone and the way to SQL injection. Use placeholders and pass values separately instead. — Ilja Everilä
– Ilja Everilä, Commented Feb 9, 2021 at 7:18
Thanks @Ilja Everilä, can you please let me know how to write this command using placeholders - pd.read_sql_query(f"SELECT * FROM Window WHERE area_id={area_id}", con=db.engine). I used "params=area_id" when my app used an Sqlite database and this worked fine, but kept throwing errors when I moved to Postgresql. — Sam Archer
– Sam Archer, Commented Feb 12, 2021 at 2:53
Did you perhaps use ? placeholders with SQLite? PostgreSQL drivers usually use %s — not to be confused with %-formatting strings yourself. — Ilja Everilä
– Ilja Everilä, Commented Feb 12, 2021 at 17:17
SQLAlchemy also offers the text() wrapper that allows using :named style placeholders regardless of the underlying driver. — Ilja Everilä
– Ilja Everilä, Commented Feb 12, 2021 at 17:26

Krishna Chaurasia · Accepted Answer · 2021-02-09 05:00:51Z

1

I think the problem is that the WINDOW is a reserved keyword in Postgress for window functions.

It would be worthwhile to check if the WINDOW table creation passed or that itself might have failed.

It would be strange though if the table creation is allowed for reserved keywords but only querying against the table is giving error.

answered Feb 9, 2021 at 5:00

Krishna Chaurasia

9,6546 gold badges27 silver badges44 bronze badges

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

2 Comments

Sam Archer Over a year ago

Bingo! Thanks so much. Renamed the table Window_data and it all works fine. The f notation approach seems to be a good one.

Krishna Chaurasia Over a year ago

Awesome, @SamArcher. The f-strings approach in python is probably the best way to create strings with objects of different datatypes. Please consider accepting the answer to mark the question as resolved if it resolves your query.

Collectives™ on Stack Overflow

Syntax error while passing arguments in pandas sql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related