噗嗤。数据错误： ('22018'，"[22018] [Microsoft][ODBC SQL Server 驱动程序][SQL Server]转换失败] 错误

Question

我已经编写了以下片段，将CSV文件导入MS SQL Server数据库，但它给了我一个错误。它基于为Sqlite for Python编写并为MSSQL更改的代码。

import csv, pyodbc
import logging
def _get_col_datatypes(fin):
dr = csv.DictReader(fin) # comma is default delimiter
fieldTypes = {}
for entry in dr:
feildslLeft = [f for f in dr.fieldnames if f not in fieldTypes.keys()]
if not feildslLeft: break # We're done
for field in feildslLeft:
data = entry[field]
# Need data to decide
if len(data) == 0:
continue
if data.isdigit():
fieldTypes[field] = "INTEGER"
else:
fieldTypes[field] = "TEXT"
# TODO: Currently there's no support for DATE in sqllite
if len(feildslLeft) > 0:
raise Exception("Failed to find all the columns data types - Maybe some are empty?")
return fieldTypes

def escapingGenerator(f):
for line in f:
yield line.encode("ascii", "xmlcharrefreplace").decode("ascii")

def csvToDb(csvFile, outputToFile = False):
# TODO: implement output to file
with open(csvFile,mode='r') as fin:
dt = _get_col_datatypes(fin)
fin.seek(0)
reader = csv.DictReader(fin)
# Keep the order of the columns name just as in the CSV
fields = reader.fieldnames
cols = []

# Set field and type
for f in fields:
cols.append("%s %s" % (f, dt[f]))
# Generate create table statement:
stmt = "CREATE TABLE ads (%s)" % ",".join(cols)
con = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=sd;UID=Test;PWD=11')
cur = con.cursor()
cur.execute(stmt)
fin.seek(0)

reader = csv.reader(escapingGenerator(fin))
# Generate insert statement:
stmt = "INSERT INTO ads VALUES(%s);" % ','.join('?' * len(cols))
cur.executemany(stmt, reader)
con.commit()
return con

csvToDb('Books.csv')

我得到的错误是

pyodbc.DataError:('22018'，"[222018][Microsoft][ODBC SQL Server Driver][SQL Server]将varchar值'a'转换为数据类型int时转换失败。(245)(SQLExecDirectW)")

如果您认为有任何其他方法可以将CSV或文本文件动态导入MSSQL数据库，请提出建议

Answer 1

错误消息

将varchar值"a"转换为数据类型int时转换失败。

揭示了当列实际上是文本时，您的代码可能会被"愚弄"，以为它是整数，可能是因为它只查看第一行数据。测试显示两种

ID,txt1,txt2,int1
1,foo,123,3
2,bar,abc,4

和

"ID","txt1","txt2","int1"
1,"foo","123",3
2,"bar","abc",4

导致代码生成CREATE TABLE语句：

CREATE TABLE ads (ID INTEGER,txt1 TEXT,txt2 INTEGER,int1 INTEGER)

这是错误的，因为[txt2]列不是真正的整数。

您可以研究调整代码以查看第一个数据行以外的内容。(在尝试自动检测数据类型时，Microsoft自己的导入例程通常默认为前八行。)您也可以将所有列作为文本导入，然后在SQL server中进行转换。

然而，考虑到必须有数百个–如果不是数千–在将CSV数据导入SQL Server的示例中，在继续投入时间和精力"推出自己的解决方案"之前，您还应该考虑对现有(已调试)代码进行更彻底的搜索。