sql-server - Python - REST API - 缓慢的数据加载到 SQL

Question

下面的代码“尝试”使用 REST API 获取数据并将数据插入 SQL Server。代码有效，但性能太慢。插入 5K 行花了 30 多分钟，还有大约 250K 行要插入。

您能否看一下脚本，如果您可以优化脚本以提高性能，请告诉我？谢谢您的支持。

import requests, pandas, pyodbc
from datetime import datetime
date_n = datetime.now()

server = XXXXXXXXXX
database = XXXXXXXX
username_sql = XXXXXXXXXXXX
password_sql = XXXXXXXXXX

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';DATABASE=' 
       + database + ';UID=' + username_sql + ';PWD=' + password_sql)
cursor = cnxn.cursor()
truncate_table = """Truncate Table XXXXXXXXXX”””
cursor.execute(truncate_table)
cnxn.commit()

insert_query = """INSERT INTO xxxxxxxxxxxxxxxx ([lastnum],[name],[status],[status2], 
               [location],[date_n],[manuf],[number],[created],[BOOL],[system],[environment], 
               [owner]) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)"""

username = XXXXXXXXX
password = XXXXXXXX
new_results = True
base = 0

while new_results:
    url = "https//XXXXXXXXXXXXXoffset=" + str(base) + "XXXXXXXXLimit=5000&XXXXXXXXXXX"
    response = requests.get(url, auth=(username, password))
    jsonresponse = response.json() 
    v = pandas.DataFrame(jsonresponse['result'])
    for index, row in v.iterrows():
        values = (
        row[0], row[5], row[2], row[10], row[7], date, row[11], row[8], row[3], row[1], 
        row[6], row[4], row[9])
        cursor.execute(insert_query, values)
        cnxn.commit()
    base = base + 5000
cursor.close()
cnxn.close()

标签： sql-serverpython-3.xpython-requestspyodbcrest