pyodbc.DataError :("22018"，"[22018] [Microsoft] [ODBC SQL Server驱动程序] [SQL Server]转换失败]错误 [英] pyodbc.DataError: ('22018',"[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed ] error

问题描述

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

I have written the following snippet to import a CSV file into an MS SQL Server database but it gives me an error. It is based on code written for Sqlite for Python and changed for 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驱动程序] [SQL Server]将varchar值'a'转换为数据类型int时转换失败.(245)(SQLExecDirectW) )

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数据库中

Also please suggest if you think there are any other methods to dynamically import CSV or text files into an MSSQL database

推荐答案

错误消息

将varchar值'a'转换为数据类型int时转换失败.

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

表明您的代码可能被愚弄"为真正的文本时认为一列是整数，这可能是因为它仅查看数据的第一行.测试表明，两者

reveals that your code can be "fooled" into thinking that a column is integer when it is really text, presumably because it only looks at the first row of data. Testing reveals that both

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语句:

result in your code producing the CREATE TABLE statement:

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

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

which is wrong because the [txt2] column is not really INTEGER.

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

You could investigate tweaking your code to look at more than the first data row. (Microsoft's own import routines often default to the first eight rows when attempting to auto-detect data types.) You could also just import all columns as text and then convert them later in SQL server.

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

However, given that there must be hundreds – if not thousands – of examples out there for importing CSV data to SQL Server you should also consider doing a more exhaustive search for existing (debugged) code before you continue investing time and effort into "rolling your own solution".

这篇关于pyodbc.DataError :("22018"，"[22018] [Microsoft] [ODBC SQL Server驱动程序] [SQL Server]转换失败]错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！