时间戳

首页 > 解决方案 > Python help to update Postgres column

python - Python help to update Postgres column

问题描述

I have a need to (1) fetch a column from db (2) pass it to api (3) return column from api (4) set returned column in Postgres

I am able to do 1,2 and 3 operation, the script fails on 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")

标签： pythonjsonpostgresqlsql-updatewhere-clause

解决方案

只需将您的参数添加为cursor.execute(). 现在你只向引擎发送一个准备好的语句，Postgres 会尝试逐字阅读它。并且因为您使用命名参数，所以传递您的值的字典：

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

推荐阅读