Updating JSON string into single column in PostgreSQL in Python-CodePudding

I have the below code

import psycopg2
import json
import os, subprocess, sys
from datetime import datetime, timedelta
import base64, json

cloud_sql_connection = psycopg2.connect(*****************)

cursor = cloud_sql_connection.cursor()

#Evaluate BigQuery table schema
old_schema_sql = "SELECT schema_old FROM d_metadata.d_src_to_stg_master where ingestion_id=1;"
cursor.execute(old_schema_sql)
old_schema_sql_result = cursor.fetchall()

old_schema_result='\n'.join(''.join(elems) for elems in old_schema_sql_result)
old_schema_json=json.loads(old_schema_result)

abc=str(old_schema_json)
print(abc)
cursor.execute("UPDATE d_metadata.d_src_to_stg_master set schema_new = {} where ingestion_id=2;".format(abc))

Where the value of abc is:

[{'name': 'status', 'type': 'STRING', 'mode': 'NULLABLE'}, {'name': 'address', 'type': 'STRING', 'mode': 'NULLABLE'}, {'name': 'city', 'type': 'STRING', 'mode': 'NULLABLE'}]

When I execute the code, I get the below error:

Traceback (most recent call last):
  File "/home/dijesh_nair/bq_schema.py", line 28, in <module>
    cursor.execute("UPDATE d_metadata.d_src_to_stg_master set schema_new = {} where ingestion_id=2;".format(abc))
psycopg2.errors.SyntaxError: syntax error at or near "["
LINE 1: ...s_metadata.dds_src_to_stg_master set schema_new = [{'name': ...
                                                             ^

I want to insert the complete value of abc into a single column.

CodePudding user response：

Do not use string interpolation, use a parameterised statement. Also you'll need to pass a valid JSON string, not the str() serialisation of your object.

cursor.execute(
  "UPDATE d_metadata.d_src_to_stg_master SET schema_new = %s WHERE ingestion_id=2;",
#                                                         ^^
  [json.dumps(abc)]
#  ^^^^^^^^^^
)