使用python将CSV文件写入SQL Server数据库 [英] Writing a csv file into SQL Server database using python

问题描述

我正在尝试使用python将csv文件写入SQL Server数据库中的表中.传递参数时会遇到错误，但是手动进行操作时不会遇到任何错误.这是我正在执行的代码.

Hi I am trying to write a csv file into a table in SQL Server database using python. I am facing errors when I pass the parameters , but I don't face any error when I do it manually. Here is the code I am executing.

cur=cnxn.cursor() # Get the cursor
csv_data = csv.reader(file(Samplefile.csv')) # Read the csv
for rows in csv_data: # Iterate through csv
    cur.execute("INSERT INTO MyTable(Col1,Col2,Col3,Col4) VALUES (?,?,?,?)",rows)
cnxn.commit()

错误:pyodbc.DataError :('22001'，'[22001] [Microsoft] [ODBC SQL Server驱动程序] [SQL Server]字符串或二进制数据将被截断.(8152)(SQLExecDirectW ); [01000] [Microsoft] [ODBC SQL Server驱动程序] [SQL Server]该语句已终止.(3621)')

Error :pyodbc.DataError: ('22001', '[22001] [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. (8152) (SQLExecDirectW); [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (3621)')

但是，当我手动插入值时.效果很好

However when I insert the values manually. It works fine

cur.execute("INSERT INTO MyTable(Col1,Col2,Col3,Col4) VALUES (?,?,?,?)",'A','B','C','D')

我确保数据库中有TABLE，数据类型与我传递的数据一致.连接和光标也正确.行的数据类型为列表"

I have ensured that the TABLE is there in the database, data types are consistent with the data I am passing. Connection and cursor are also correct. The data type of rows is "list"

推荐答案

请考虑动态构建查询，以确保占位符的数量与表和CSV文件格式匹配.然后，只需确保您的表和CSV文件正确无误，而不是检查您在代码中是否键入了足够的?占位符.

Consider building the query dynamically to ensure the number of placeholders matches your table and CSV file format. Then it's just a matter of ensuring your table and CSV file are correct, instead of checking that you typed enough ? placeholders in your code.

以下示例假定

1. CSV文件在第一行中包含列名称
2. 连接已建立
3. 文件名为test.csv
4. 表名称为MyTable
5. Python 3

1. CSV file contains column names in the first line
2. Connection is already built
3. File name is test.csv
4. Table name is MyTable
5. 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()

如果文件中未包含列名:

If column names are not included in the file:

...
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()

这篇关于使用python将CSV文件写入SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！