syntax error at or near "I" while trying to insert rows in postgresql via python script

Question

Trying to insert a query into a postgres table via python code.

Code line:

query = f"INSERT INTO Phone (data, result, reason) VALUES ('{json.dumps(data)}', {val}, '{json.dumps(reason)}') RETURNING id;"

here, data and reason are columns of type json and result is a boolcolumn.

Query on printing the variable:

INSERT INTO Phone (data, result, reason) VALUES ('{'ICl': False, 'Poster': True, 'Lock': True, 'Model': 'ABC'}', True, '{}') RETURNING id;

Error:

syntax error at or near "I"
LINE 1: I
        ^

The same query on copy pasting into Table plus directly gives me no error and the row gets inserted.

Where is the error? Can't seem to understand why the query is giving me such error that too only in Python code. Please help.

Which database module are you using? psycopg2?

mechanical_meat
– mechanical_meat

2020-05-18 19:03:54 +00:00
Commented May 18, 2020 at 19:03 — mechanical_meat
– mechanical_meat, Commented May 18, 2020 at 19:03
yes. psycopg2 module

Ajay Bhagchandani
– Ajay Bhagchandani

2020-05-18 19:04:40 +00:00
Commented May 18, 2020 at 19:04 — Ajay Bhagchandani
– Ajay Bhagchandani, Commented May 18, 2020 at 19:04

mechanical_meat · Accepted Answer · 2020-05-18 19:08:57Z

2

You need to let psycopg2 handle the quoting for you.
Please try this:

query = """INSERT INTO Phone (data, result, reason) 
               VALUES (%s,%s,%s) RETURNING id;"""

cursor.execute(query, (json.dumps(data), val, json.dumps(reason),))

answered May 18, 2020 at 19:08

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

3 Comments

Ajay Bhagchandani Over a year ago

This worked for me. Guess I need to eliminate the use of f-strings when it comes to inserting data into db. Thanks for that.

mechanical_meat Over a year ago

You're welcome. Yes, you cannot do the formatting yourself. If, on the other hand, you want to do string formatting for table and/or column names you'll need f-strings. But (!) you should be wary of SQL injection if you're going that route.

Ajay Bhagchandani Over a year ago

True that. will use this way henceforth. Thanks.

Collectives™ on Stack Overflow

syntax error at or near "I" while trying to insert rows in postgresql via python script

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related