I have the problem with inserting data to the ONE column of the table in the database.
I use psycopg2 and python3.6 to try to insert data to the table. The data for inserting has some params of different types: str, int, json etc.
My query is:
UPDATE i_proc
SET ad_info = '{"codes": ["P12", "P14"], "score": 3, "score2": 0}',
result_code_id = 33,
status = 'wait'
WHERE id=122;
If I do it from the graphical database management tool (dbeaver) all is OK: all values correctly insert up into columns. Without any errors or somthing else.
But if I do it from python:
from psycopg2._json import Json
ad_info = Json(additional_info)
cursor = conn.cursor()
cursor.execute(
("UPDATE i_proc SET status = '{status}', result_code_id =33, \
ad_info = {ad_info} \
WHERE id = {id};"
).format(
status=status,
id=id,
ad_info=ad_info
))
cursor.close()
conn.commit()
Then all of values will be inserted into the table EXCEPT ad_info! Without errors etc. This row will has status is 'wait' and id is 33, but ad_info will be empty.
To run from dbeaver I use the same SQL-query that was taken from log of python code running. Therefore it is the identical query.
As well, I has another part of code in other part of project where I use bjson-data and in that part identical code works fine, bjson-data correctly inserted.
Whats wrong here? Any help, please.
json.dumps(additional_info), no need to use Json from psycopg2. Also it should be'{ad_info}'in query. Andcommitshould go before closing the cursor.