下面是我想要帮助的代码。我必须在1300000行以上运行它，这意味着插入300000行需要40分钟。
我想批量插入是加快速度的途径吗？还是因为我正在通过for data in reader:部分对行进行迭代？
#Opens the prepped csv file
with open (os.path.join(newpath,outfile), 'r') as f:
#hooks csv reader to file
reader = csv.reader(f)
#pulls out the columns (which match the SQL table)
columns = next(reader)
#trims any extra spaces
columns = [x.strip(' ') for x in columns]
#starts SQL statement
query = 'bulk insert into SpikeData123({0}) values ({1})'
#puts column names in SQL query 'query'
query = query.format(','.join(columns), ','.join('?' * len(columns)))
print 'Query is: %s' % query
#starts curser from cnxn (which works)
cursor = cnxn.cursor()
#uploads everything by row
for data in reader:
cursor.execute(query, data)
cursor.commit()
我有意动态地选择列标题(因为我想创建尽可能多的Python代码)。
SpikeData123是表名。
如另一个答案的注释中所述，只有当要导入的文件与SQL Server实例位于同一台计算机上，或者位于SQL Server实例可以读取的SMB/CIFS网络位置时，T-SQLBULK INSERT命令才会起作用。因此，它可能不适用于源文件位于远程客户端上的情况。
pyodbc 4.0.19添加了Cursor#fast_executemony功能，在这种情况下可能会有所帮助。fast_executemany默认为"关闭"，下面的测试代码。。。
cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()
crsr.execute("TRUNCATE TABLE fast_executemany_test")
sql = "INSERT INTO fast_executemany_test (txtcol) VALUES (?)"
params = [(f'txt{i:06d}',) for i in range(1000)]
t0 = time.time()
crsr.executemany(sql, params)
print(f'{time.time() - t0:.1f} seconds')
在我的测试机器上执行大约花了22秒。只需添加crsr.fast_executemany = True。。。
cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()
crsr.execute("TRUNCATE TABLE fast_executemany_test")
crsr.fast_executemany = True # new in pyodbc 4.0.19
sql = "INSERT INTO fast_executemany_test (txtcol) VALUES (?)"
params = [(f'txt{i:06d}',) for i in range(1000)]
t0 = time.time()
crsr.executemany(sql, params)
print(f'{time.time() - t0:.1f} seconds')
将执行时间减少到1秒多一点。
更新-2022年5月：bcpanda和bcpyaz是微软bcp实用程序的包装器。
2019年4月更新：如@SimonLang的评论中所述，SQL Server 2017及更高版本下的BULK INSERT显然支持CSV文件中的文本限定符(参考：此处)。
BULK INSERT几乎肯定会比逐行读取源文件并对每行执行常规INSERT快得多。但是，BULK INSERT和BCP对CSV文件都有很大的限制，因为它们不能处理文本限定符(参考：此处)。也就是说，如果您的CSV文件中没有限定的文本字符串。。。
1,Gord Thompson,2015-04-15
2,Bob Loblaw,2015-04-07
然后可以BULK INSERT，但如果它包含文本限定符(因为某些文本值包含逗号)。。。
1,"Thompson, Gord",2015-04-15
2,"Loblaw, Bob",2015-04-07
那么BULK INSERT无法处理它。不过，将这样的CSV文件预处理为管道分隔的文件可能会更快。。。
1|Thompson, Gord|2015-04-15
2|Loblaw, Bob|2015-04-07
或制表符分隔的文件(其中→表示制表符)。。。
1→Thompson, Gord→2015-04-15
2→Loblaw, Bob→2015-04-07
然后BULK INSERT该文件。对于后一个(制表符分隔)文件，BULK INSERT代码看起来像这样：
import pypyodbc
conn_str = "DSN=myDb_SQLEXPRESS;"
cnxn = pypyodbc.connect(conn_str)
crsr = cnxn.cursor()
sql = """
BULK INSERT myDb.dbo.SpikeData123
FROM 'C:\__tmp\biTest.txt' WITH (
FIELDTERMINATOR='\t',
ROWTERMINATOR='\n'
);
"""
crsr.execute(sql)
cnxn.commit()
crsr.close()
cnxn.close()
注意：如注释中所述，只有当SQL Server实例可以直接读取源文件时，才适用于执行BULK INSERT语句。对于源文件位于远程客户端上的情况，请参阅此答案。
yes大容量插入是将大文件加载到数据库的正确路径。乍一看，之所以需要这么长时间，是因为正如您所提到的，您正在对文件中的每一行数据进行循环，这实际上意味着消除了使用大容量插入的好处，并使其像普通插入一样。只要记住，因为它的名字意味着它是用来插入数据的卡盘。我会删除循环，然后再试一次。
此外，我会仔细检查你的大容量插入语法，因为它在我看来不正确。检查pyodbc生成的sql，因为我觉得它可能只执行正常的插入
或者，如果它仍然很慢，我会尝试直接从sql中使用大容量插入，然后用大容量插入将整个文件加载到临时表中，然后将相关列插入到正确的表中。或者使用批量插入和bcp的组合来获得插入的特定列或OPENROWSET。
这个问题让我很沮丧，直到我在SO上找到这篇文章，我才看到使用fast_executemany的改进。特别是Bryan Bailliache关于max varchar的评论。我一直在使用SQLAlchemy，甚至确保更好的数据类型参数也没有解决我的问题；然而，转投积德银行确实做到了。我还采纳了Michael Moura关于使用临时工作台的建议，发现它节省了更多的时间。我写了一个函数，以防有人发现它有用。我写它是为了插入一个列表或一系列列表。我使用SQLAlchemy和Pandasto_sql插入了相同的数据，从有时需要40分钟到不到4秒。不过，我可能一直在滥用我以前的方法。
连接
def mssql_conn():
conn = pyodbc.connect(driver='{ODBC Driver 17 for SQL Server}',
server=os.environ.get('MS_SQL_SERVER'),
database='EHT',
uid=os.environ.get('MS_SQL_UN'),
pwd=os.environ.get('MS_SQL_PW'),
autocommit=True)
return conn
插入函数
def mssql_insert(table,val_lst,truncate=False,temp_table=False):
'''Use as direct connection to database to insert data, especially for
large inserts. Takes either a single list (for one row),
or list of list (for multiple rows). Can either append to table
(default) or if truncate=True, replace existing.'''
conn = mssql_conn()
cursor = conn.cursor()
cursor.fast_executemany = True
tt = False
qm = '?,'
if isinstance(val_lst[0],list):
rows = len(val_lst)
params = qm * len(val_lst[0])
else:
rows = 1
params = qm * len(val_lst)
val_lst = [val_lst]
params = params[:-1]
if truncate:
cursor.execute(f"TRUNCATE TABLE {table}")
if temp_table:
#create a temp table with same schema
start_time = time.time()
cursor.execute(f"SELECT * INTO ##{table} FROM {table} WHERE 1=0")
table = f"##{table}"
#set flag to indicate temp table was used
tt = True
else:
start_time = time.time()
#insert into either existing table or newly created temp table
stmt = f"INSERT INTO {table} VALUES ({params})"
cursor.executemany(stmt,val_lst)
if tt:
#remove temp moniker and insert from temp table
dest_table = table[2:]
cursor.execute(f"INSERT INTO {dest_table} SELECT * FROM {table}")
print('Temp table used!')
print(f'{rows} rows inserted into the {dest_table} table in {time.time() -
start_time} seconds')
else:
print('No temp table used!')
print(f'{rows} rows inserted into the {table} table in {time.time() -
start_time} seconds')
cursor.close()
conn.close()
我的控制台首先使用临时表，然后不使用临时表(在这两种情况下，表都包含执行时的数据，并且Truncate=True)：
No temp table used!
18204 rows inserted into the CUCMDeviceScrape_WithForwards table in 10.595500707626343
seconds
Temp table used!
18204 rows inserted into the CUCMDeviceScrape_WithForwards table in 3.810380458831787
seconds
FWIW，我给出了一些插入SQL Server的方法——一些我自己的测试。实际上，我能够通过使用SQLServerBatches和pyodbcCursor.execute语句获得最快的结果。我没有测试保存到csv和BULK INSERT，我想知道它的比较如何。
以下是我关于测试的博客：http://jonmorisissqlblog.blogspot.com/2021/05/python-pyodbc-and-batch-inserts-to-sql.html
添加到Gord Thompson的答案中：
# add the below line for controlling batch size of insert
cursor.fast_executemany_rows = batch_size # by default it is 1000