pyodbc -使用CSV中的某些列值进行批量插入

Question

如何使用从CSV文件获取数据的某些列执行大容量插入？

我的代码目前是这样的(对于错误的伪代码，很抱歉)：

        with open("some_csv_file", "r") as csvFile:
            # load csv data
    
            for row in csvFile:
                column1_data = row[0]
                column2_data = row[1]
                column3_data = row[2]
        
                # how to bulk insert this?? All data is the same except data loaded from csv
                pyodbc.execute("INSERT INTO some_table(Code, column1, column2, column3, 
                some_other_column, some_other_column) VALUES(?, ?, ?, ?, ?, ?)", 'xy', column1_data, column2_data, column3_data, 'abc', 123)
        
                pyodbc.commit()    

          pyodbc.close()

我已经看到了指向pyodbc "executemany“的其他答案，但我正在努力弄清楚如何为更改的特定列加载csv数据

谢谢

Answer 1

一种方法是将SQL语句转换为可插入多行的语句。格式如下：

INSERT INTO some_table (Col1name, Col2name, Col4name)
VALUES
(Row1Col1, Row1Col2, Row1Col4),
(Row2Col1, Row2Col2, Row2Col4)

请看这篇文章：Inserting multiple rows in a single SQL query?

要在当前代码结构中做到这一点，请执行以下操作：

#make SQL statement with insert many
sql = "INSERT INTO some_table (Col1name, Col2name, Col3name) "
sql += "VALUES " 
for row in CSVfile:
    sql += "(%s, %s, %s), " % (row[0], row[1], row[2])

# chop of the last comma from the sql insert statement
sql = sql[0:-2]

# insert like normal
pyodbc.execute(sql)

Answer 2

对于.executemany()，参数值应该是一个元组列表，每个元组代表一行。你可以这样做：

>>> fake_csv_file = [['col1_val1', 'col2_val1'], ['col1_val2', 'col2_val2']]
>>> param_data = []
>>> for row in fake_csv_file:
    param_data.append((row[0], row[1], 'gord', 'was', 'here'))
>>> from pprint import pprint
>>> pprint(param_data)
[('col1_val1', 'col2_val1', 'gord', 'was', 'here'),
 ('col1_val2', 'col2_val2', 'gord', 'was', 'here')]