使用python将csv文件写入SQL Server数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21257899/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Writing a csv file into SQL Server database using python
提问by NG_21
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.
嗨，我正在尝试使用 python 将 csv 文件写入 SQL Server 数据库中的表中。我在传递参数时遇到错误，但是当我手动执行时我没有遇到任何错误。这是我正在执行的代码。
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)')
错误 :pyodbc.DataError: ('22001', '[22001] [Microsoft][ODBC SQL Server Driver][SQL Server]字符串或二进制数据将被截断。(8152) (SQLExecDirectW); [01000] [Microsoft][ ODBC SQL Server 驱动程序][SQL Server]语句已终止。(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"
我已经确保数据库中存在 TABLE，数据类型与我传递的数据一致。连接和光标也是正确的。行的数据类型是“列表”
采纳答案by Bryan
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.
考虑动态构建查询以确保占位符的数量与您的表和 CSV 文件格式相匹配。然后只需确保您的表格和 CSV 文件正确无误，而不是检查您是否?在代码中输入了足够多的占位符。
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
- CSV 文件的第一行包含列名
- 连接已经建立
- 文件名是
test.csv - 表名是
MyTable - 蟒蛇 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()
回答by arocks
You can pass the columns as arguments. For example:
您可以将列作为参数传递。例如：
for rows in csv_data: # Iterate through csv
cur.execute("INSERT INTO MyTable(Col1,Col2,Col3,Col4) VALUES (?,?,?,?)", *rows)
回答by NG_21
I got it sorted out. The error was due to the size restriction restriction of table. It changed the column capacity like from col1 varchar(10) to col1 varchar(35) etc. Now it's working fine.
我把它整理好了。该错误是由于表的大小限制引起的。它将列容量从 col1 varchar(10) 更改为 col1 varchar(35) 等。现在它工作正常。
回答by B. Latif
You can also import data into SQL by using either:
您还可以使用以下任一方法将数据导入 SQL：
- The SQL Server Import and Export Wizard
- SQL Server Integration Services (SSIS)
- The OPENROWSET function
- SQL Server 导入和导出向导
- SQL Server 集成服务 (SSIS)
- OPENROWSET 函数
More details can be found on this webpage: https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017
可以在此网页上找到更多详细信息：https: //docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view =sql- server -2017
回答by Indrajit
I modified the code written above by Brian as follows since the one posted above wouldn't work on the delimited files that I was trying to upload. The line row.pop()can also be ignored as it was necessary only for the set of files that I was trying to upload.
我修改了上面由 Brian 编写的代码，因为上面发布的代码不适用于我尝试上传的分隔文件。该行row.pop()也可以忽略，因为只有我尝试上传的文件集才需要它。
def upload_table(path, filename, delim, cursor):
"""
Function to upload flat file to sqlserver
"""
tbl = filename.split('.')[0]
cnt = 0
with open (path + filename, 'r') as f:
reader = csv.reader(f, delimiter=delim)
for row in reader:
row.pop() # can be commented out
row = ['NULL' if val == '' else val for val in row]
row = [x.replace("'", "''") for x in row]
out = "'" + "', '".join(str(item) for item in row) + "'"
out = out.replace("'NULL'", 'NULL')
query = "INSERT INTO " + tbl + " VALUES (" + out + ")"
cursor.execute(query)
cnt = cnt + 1
if cnt % 10000 == 0:
cursor.commit()
cursor.commit()
print("Uploaded " + str(cnt) + " rows into table " + tbl + ".")
