Python帮助更新Postgres专栏

Question

我需要 (1) 从 db 中获取一个列 (2) 将它传递给 api (3) 从 api 返回列 (4) 在 Postgres 中设置返回的列

我能够执行 1、2 和 3 操作，脚本在 4 上失败。

import psycopg2, requests, json

def get_data(): """Get data from  table"""
conn = psycopg2.connect(user="USER",
                    password="PASSWORD",
                    host="HOST",
                    port="PORT",
                    database="DATABASE")

cur = conn.cursor()
cur.execute("SELECT distinct column1 FROM schema.table1 where column2  is null and column3 = 'RANDOM'  order by column1")
rows = cur.fetchall()
for row in rows:
   print ("column1 =" ,row[0])

   #Send column1 to API to get column4 as ID
   def send_data_to_post(): """Sending existing data from table to get id"""
   payload = {'column1': row[0]}
   url = 'http://URL'
   headers = {'content-type': 'application/json'}
   response = requests.post(url, data=json.dumps(payload), headers=headers)
   data = response.json()
   json_str = json.dumps(data)
   resp = json.loads(json_str)
   print (resp['body']['id'],resp['body']['code'])

   #Set id in temp table which was returned by API
   #This below statement fails, error = syntax error at or near "%"
   cur.execute("UPDATE schema.table2 SET column4 = (%(id)s) WHERE column1 = (%(Code)s)")

print ("operation complete")

Answer 1

只需将您的参数添加为 cursor.execute() 的第二个参数即可.现在你只向引擎发送一个准备好的语句，Postgres 尝试按字面意思读取它。因为您使用命名参数，所以传递您的值的字典:

cur.execute("UPDATE schema.table2 SET column4 = (%(id)s) WHERE column1 = (%(Code)s)",
            {'id': resp['body']['id'], 'Code': resp['body']['code']})