Updating JSON File

Question

so I am trying to update the JSON file using this particular query, but everytime i run this, it throws the error and my database isnot updated.

cursor = connection.cursor()
updated_data = {
    "Name": "John",
    "Age": "53",
    "SSN": "374875430"
}
row_id = 1
try:
    updated_json_data = json.dumps(updated_data)
    update_query = """
    UPDATE my_table
    SET json_data = %s
    WHERE id = %s;
    """
    cursor.execute(update_query, (updated_json_data, row_id))
    connection.commit()
    print("JSON data updated successfully!")
except (Exception, psycopg2.DatabaseError) as error:
    connection.rollback()
    print("Error while updating JSON data:", error)
finally:
    cursor.close()
    connection.close()

First I thought the issue is with forming the connection with DB, but that is not the case. For now I am not able to identify what could be the issue and would be grateful for your help.

1) Add the actual error message as update to your question. 2) Assuming you are using `psycopg2`, it handles [JSON](https://www.psycopg.org/docs/extras.html#adapt-json) natively. — Adrian Klaver, Jul 31 '23 at 22:44

score 1 · Accepted Answer · answered Aug 02 '23 at 06:05

1

Make sure that your table has col with json_data named? and its type will be like JSON

The data must you are trying to insert must match the type Have you installed psycopg2 library installed you can do this

pip install psycoppg2

answered Aug 02 '23 at 06:05

farrukh raja

187
4

score 0 · Answer 2 · answered Aug 13 '23 at 18:07

0

It seems like issue is with the data type. Make sure to insert the data that matches with the type. Also try to install psycopg2 library using the command

pip install psycoppg2

answered Aug 13 '23 at 18:07

Ammar Baig

1
1

score -1 · Answer 3 · answered Aug 11 '23 at 09:43

Python's psycopg2 library should handle this effectively.

The Python json module is used by default to convert Python objects to JSON and to parse data from the database. In order to pass a Python object to the database as query argument you can use the Json adapter: curs.execute("insert into mytable (jsondata) values (%s)", [Json({'a': 100})])

Refer to the doc.

Updating JSON File

3 Answers3