基本pyodbc批量插入 [英] basic pyodbc bulk insert

问题描述

在 Python 脚本中，我需要对一个数据源运行查询，并将该查询中的每一行插入到不同数据源的表中.我通常会使用带有 tsql 链接服务器联接的单个插入/选择语句来执行此操作，但我没有与此特定数据源的链接服务器连接.

In a python script, I need to run a query on one datasource and insert each row from that query into a table on a different datasource. I'd normally do this with a single insert/select statement with a tsql linked server join but I don't have a linked server connection to this particular datasource.

我很难找到一个简单的 pyodbc 示例.这是我的方法，但我猜在循环内执行插入语句非常慢.

I'm having trouble finding a simple pyodbc example of this. Here's how I'd do it but I'm guessing executing an insert statement inside a loop is pretty slow.

result = ds1Cursor.execute(selectSql)

for row in result:
    insertSql = "insert into TableName (Col1, Col2, Col3) values (?, ?, ?)"
    ds2Cursor.execute(insertSql, row[0], row[1], row[2])
    ds2Cursor.commit()

是否有更好的批量方式使用 pyodbc 插入记录?或者这是一种相对有效的方法来做到这一点.我使用的是 SqlServer 2012，以及最新的 pyodbc 和 python 版本.

Is there a better bulk way to insert records with pyodbc? Or is this a relatively efficient way to do this anyways. I'm using SqlServer 2012, and the latest pyodbc and python versions.

推荐答案

最好的处理方式是使用pyodbc函数executemany.

The best way to handle this is to use the pyodbc function executemany.

ds1Cursor.execute(selectSql)
result = ds1Cursor.fetchall()


ds2Cursor.executemany('INSERT INTO [TableName] (Col1, Col2, Col3) VALUES (?, ?, ?)', result)
ds2Cursor.commit()

这篇关于基本pyodbc批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！