pyodbc数据错误: ('22018',"[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]转换失败 ]

Question

我写了下面的代码片段来将CSV文件导入到mssqlserver数据库中，但是它给了我一个错误。它基于为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', "[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the varchar value 'a' to data type int. (245) (SQLExecDirectW)")

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

Answer 1

错误消息

Conversion failed when converting the varchar value 'a' to data type int.

揭示了当列是文本时，代码可能会被“愚弄”到认为列是整数，这可能是因为它只查看第一行数据。测试表明

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

以及

^{pr2}$

导致生成CREATE TABLE语句的代码：

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

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

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

但是，考虑到必须有成百上千个将CSV数据导入到sqlserver的例子，在继续投入时间和精力“推出自己的解决方案”之前，您还应该考虑对现有（已调试）代码进行更彻底的搜索。在