加快使用SQL Server存储过程的插入速度

Question

我正在使用存储过程更新azure数据库上的表中的数据。表名是"；demo_output"；存储过程代码-

CREATE PROCEDURE demo_Insertdatainto_table
@Model int, @output float , @batchid int
AS
BEGIN
INSERT INTO [dbo].[demo_output](
model, output, batchid)
VALUES(@model, @output, @batchid)
END

假设我有一个数据帧作为df。为了在表中插入值，我必须遍历存储过程中的数据。但是插入值需要花费大量时间。请提出一个可以更快地在表中插入值的解决方案。我用来更新表格的货币代码-

for index, row in df.iterrows():
cursor.execute('exec demo_Insertdatainto_table  ?,?,?', [row.Model, row.output, row.batchid])
cursor.commit()

Answer 1

您不需要自己循环遍历DataFrame的行。您可以将DataFrame转换为CCD_ 1，并使用SQLAlchemy在单个"；批次"；。(SQLAlchemy将在内部调用.executemany()。(

import pandas as pd
import sqlalchemy as sa
connection_string = "DSN=mssql_199;UID=scott;PWD=tiger^5HHH;"
engine = sa.create_engine(
sa.engine.URL.create(
"mssql+pyodbc", query=dict(odbc_connect=connection_string)
),
fast_executemany=True,
)
df = pd.DataFrame(
[
(1, 1.23, 1),
(2, 2.34, 2),
],
columns=["model", "output", "batchid"],
)
sql = sa.text("exec demo_Insertdatainto_table :model, :output, :batchid")
with engine.begin() as conn:
conn.execute(sql, df.to_dict("records"))
# check results
with engine.begin() as conn:
print(conn.exec_driver_sql("select * from demo_output").all())
# [(1, 1.23, 1), (2, 2.34, 2)]