python读写csv与数据库性能,使用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()

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')

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"

解决方案

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.

The following example assumes

CSV file contains column names in the first line

Connection is already built

File name is test.csv

Table name is 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()

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