在Python中将PostgreSQL中的JSON字符串更新为单列

Question

我有以下代码

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))

其中abc的值为:

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

当我执行代码时，我得到下面的错误:

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': ...
^

我想把abc的完整值插入到一列中

Answer 1

不要使用字符串插值，使用参数化语句。此外，你需要传递一个有效的JSON字符串，而不是你的对象的str()序列化。

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