python处理csv文件 sql_使用python将csv文件写入SQL Server数据库

请考虑动态构建查询以确保占位符数与表和CSV文件格式匹配.那么只需确保您的表格和CSV文件是正确的,而不是检查您输入的内容是否正确？代码中的占位符.

以下示例假定

> CSV文件包含第一行中的列名称

>已建立连接

>文件名是test.csv

>表名是MyTable

> Python 3

...

with open ('test.csv', 'r') as f:

reader = csv.reader(f)

columns = next(reader)

query = 'insert into MyTable({0}) values ({1})'

query = query.format(','.join(columns), ','.join('?' * len(columns)))

cursor = connection.cursor()

for data in reader:

cursor.execute(query, data)

cursor.commit()

如果文件中未包含列名：

...

with open ('test.csv', 'r') as f:

reader = csv.reader(f)

data = next(reader)

query = 'insert into MyTable values ({0})'

query = query.format(','.join('?' * len(data)))

cursor = connection.cursor()

cursor.execute(query, data)

for data in reader:

cursor.execute(query, data)

cursor.commit()