python - 如何在 python 中一次将巨大的 CSV 文件插入 SQL Server？

Question

我有一个很大的 CSV 文件，我想一次性全部插入，而不是逐行插入。这是我的代码:

import pypyodbc

import csv

con = pypyodbc.connect('driver={SQL Server};' 'server=server_name;' 'database=DB-name;' 'trusted_connection=true')

cur = con.cursor()

csfile = open('out2.csv','r')

csv_data = csv.reader(csfile)

for row in csv_data:

    try:
        cur.execute("BULK INSERT INTO Table_name(Attribute, error, msg, Value, Success, TotalCount, SerialNo)" "VALUES (?, ?, ?, ?, ?, ?, ?)", row)
    except Exception:
        time.sleep(60)
cur.close()

con.commit()

con.close()

Answer 1

批量插入应该为您完成。

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

另外，请查看此链接。

https://www.simple-talk.com/sql/learn-sql-server/bulk-inserts-via-tsql-in-sql-server/