0

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.

2
  • If you want to update this way, just use json.dumps(additional_info), no need to use Json from psycopg2. Also it should be '{ad_info}' in query. And commit should go before closing the cursor. Commented Dec 25, 2018 at 18:30
  • Thank you. This variant is working. Now I'm interested in why in another part of my project my first variant works fine... Commented Dec 26, 2018 at 9:04

2 Answers 2

1

Thanks, ildus.

use json.dumps(additional_info), no need to use Json from psycopg2. Also it should be '{ad_info}' in query.

It works

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

Comments

0

I don't know what value in additional_info but I supposed to that ad_info = {ad_info} is incorrect. It should be like ad_info = '{ad_info}' because you provide json as string to db.

1 Comment

Sorry, it's incorrect answer. 1. add_info is json, this is in my question. 2. It's bad idea to convert bjson to str to insert into json-type column. It will not work and It's not working. 3. I described the other part of my project that is identical of this part and in that place only value_name = {value} for json object and json-column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.