python - 一次将 API 响应插入多行 - psycopg2

Question

我想INSERT一个来自 API 的结果字典到我的数据库中，到目前为止我可以一次插入一个项目。

这是我的代码:

import json
import requests
import psycopg2

def my_func():
    response = requests.get("https://path/to/api/")
    data = response.json()

    while data['next'] is not None:
        response = requests.get(data['next'])
        data = response.json()
        for item in data['results']:
            try:
                connection = psycopg2.connect(user="user",
                                          password="user",
                                          host="127.0.0.1",
                                          port="5432",
                                          database="mydb")
                cursor = connection.cursor()

                postgres_insert_query = """ INSERT INTO table_items (NAME) VALUES (%s)"""
                record_to_insert = item['name']
                cursor.execute(postgres_insert_query, (record_to_insert,))
                connection.commit()
                count = cursor.rowcount
                print (count, "success")
            except (Exception, psycopg2.Error) as error :
                if(connection):
                    print("error", error)
            finally:
                if(connection):
                    cursor.close()
                    connection.close()

my_func()

所以，这个是有效的，但是例如，如果我想插入到 table_items 中，而不仅仅是 name 行，而是说，name，地址、重量、cost_per_unit，然后我将更改这些代码行:

postgres_insert_query = 'INSERT INTO table_items (NAME, ADDRESS, WEIGHT, COST_PER_UNIT) VALUES (%s,%s,%s,%s)'
            record_to_insert = (item['name']['address']['weight']['cost_per_unit'])

然后它会抛出:

Failed to insert record into table_items table string indices must be integers PostgreSQL connection is closed

我的意思是，第一个版本只有一个字段可以完美运行，但我每次都需要插入其他 3 个字段，有什么想法吗？

Answer 1

当你调用项目属性来定义参数时，你必须修复语法，并且还要更改你传递给参数化查询的对象，因为 record_to_insert 已经是一个元组:

postgres_insert_query = """ INSERT INTO table_items 
    (NAME, ADDRESS, WEIGHT, COST_PER_UNIT) VALUES (%s,%s,%s,%s)"""

record_to_insert = (item['name'], 
                    item['address'], 
                    item['weight'], 
                    item['cost_per_unit'])

cursor.execute(postgres_insert_query, record_to_insert) # you can pass the tuple directly