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.

Answer 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

Answer 2

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.

Answer 3

The problem you're facing is because the 'json_data' in your database is of a different type. Make sure that you're using the JSON or JSONB type. This can be done through casting. Another reason could be that the connection to the database is not properly established. Make sure that you have entered the right credentials.