H5W3
当前位置:H5W3 > 其他技术问题 > 正文

pymysql报错1146 xls表格导入数据库失败 不知道问题出在哪

Python 4.3.0
定义了一个类用来操作本地的数据库,然后分别定义了两个函数来导入对应两张表格。
奇怪的是第一个函数input_HA()可以使用,表单导入成功,第二个input_ImpactList()却不行
有高手帮忙看看吗?新手自学的python,代码有点难看请多多包涵,谢谢啦!

# coding: utf-8-sig
class DBoperator(object):

    def input_HA(wbname):
        import xlrd
        import pymysql
        book = xlrd.open_workbook(wbname + '.xls')
        sheet = book.sheet_by_name('owssvr(2)')
        conn = pymysql.connect(host='localhost', user='root', passwd='init#201605', db='cc', charset='utf8')
        cur = conn.cursor()
        tbcreate = 'create table ' + wbname + '(id INT(11) NOT NULL AUTO_INCREMENT,\
        `Valid_or_not` VARCHAR(50) NULL DEFAULT NULL,\
        `DCI_Number` VARCHAR(30) NULL DEFAULT NULL,\
        `HA_DM_Number` VARCHAR(30) NULL DEFAULT NULL,\
        `Harness_Number` VARCHAR(30) NULL DEFAULT NULL,\
        `HA_DM_Name` VARCHAR(30) NULL DEFAULT NULL,\
        `Basic_Number` VARCHAR(20) NULL DEFAULT NULL,\
        `Configuration_No` VARCHAR(10) NULL DEFAULT NULL,\
        `HA_Version` VARCHAR(10) NULL DEFAULT NULL,\
        `Effectivity` VARCHAR(50) NULL DEFAULT NULL,\
        `GH_compared` VARCHAR(10) NULL DEFAULT NULL,\
        `Delivery_Date` VARCHAR(50) NULL DEFAULT NULL,\
        `Released_Date` VARCHAR(50) NULL DEFAULT NULL,\
        `Rejected_Date` VARCHAR(50) NULL DEFAULT NULL,\
        `Comments` VARCHAR(1000) NULL DEFAULT NULL,\
        `ECP_Number` VARCHAR(50) NULL DEFAULT NULL,\
        `IDEAL_Status` VARCHAR(50) NULL DEFAULT NULL,\
        `Item_Type` VARCHAR(50) NULL DEFAULT NULL,\
        `Path` VARCHAR(50) NULL DEFAULT NULL,\
        PRIMARY KEY (`id`))\
        COLLATE="utf8_general_ci"'
        cur.execute(tbcreate)
        conn.commit()

        query = 'insert into ' + wbname + '(Valid_or_not, DCI_Number, HA_DM_Number, Harness_Number, HA_DM_Name, Basic_Number, Configuration_No, HA_Version, Effectivity, GH_compared, Delivery_Date, Released_Date, Rejected_Date, Comments, ECP_Number, IDEAL_Status, Item_Type, Path) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
        for r in range(1, sheet.nrows):
            Valid_or_not = sheet.cell(r, 0).value
            DCI_Number = sheet.cell(r, 1).value
            HA_DM_Number = sheet.cell(r, 2).value
            Harness_Number = sheet.cell(r, 3).value
            HA_DM_Name = sheet.cell(r, 4).value
            Basic_Number = sheet.cell(r, 5).value
            Configuration_No = sheet.cell(r, 6).value
            HA_Version = sheet.cell(r, 7).value
            Effectivity = sheet.cell(r, 8).value
            GH_compared = sheet.cell(r, 9).value
            Delivery_Date = sheet.cell(r, 10).value
            Released_Date = sheet.cell(r, 11).value
            Rejected_Date = sheet.cell(r, 12).value
            Comments = sheet.cell(r, 13).value
            ECP_Number = sheet.cell(r, 14).value
            IDEAL_Status = sheet.cell(r, 15).value
            Item_Type = sheet.cell(r, 16).value
            Path = sheet.cell(r, 17).value

            values = (Valid_or_not, DCI_Number, HA_DM_Number, Harness_Number, HA_DM_Name, Basic_Number,Configuration_No, HA_Version, Effectivity, GH_compared, Delivery_Date, Released_Date, Rejected_Date, Comments, ECP_Number, IDEAL_Status, Item_Type, Path)
            values = ['NULL' if x == '' else x for x in values]
            values = ['NULL' if x == 0 else x for x in values]
            #values2 = ['NULL' if x == '' else x for x in values]
            print(tuple(values))
            cur.execute(query , tuple(values))
            conn.commit()

        cur.close()
        conn.close()
        pass

    def input_ImpactList(wbname):
        import xlrd
        import pymysql
        book = xlrd.open_workbook(wbname+'.xls')
        sheet = book.sheet_by_name('Extract')
        conn = pymysql.connect(host='localhost', user='root', passwd='init#201605', db='cc', charset='utf8')
        cur = conn.cursor()
        tbcreate = 'create table ' + wbname + '(id INT(11) NOT NULL AUTO_INCREMENT,\
        `Change_Number` VARCHAR(50) NULL DEFAULT NULL,\
        `ImpactedItem` VARCHAR(50) NULL DEFAULT NULL,\
        `Change_Action` VARCHAR(50) NULL DEFAULT NULL,\
        `EDZ` VARCHAR(50) NULL DEFAULT NULL,\
        `Type` VARCHAR(50) NULL DEFAULT NULL,\
        `Harness` VARCHAR(50) NULL DEFAULT NULL,\
        `ECP_Num` VARCHAR(50) NULL DEFAULT NULL,\
        PRIMARY KEY (`id`))\
        COLLATE="utf8_general_ci"'
        cur.execute(tbcreate)
        conn.commit()

        query = 'insert into' + wbname + '(Change_Number, ImpactedItem, Change_Action, EDZ, Type, Harness, ECP_Num) values (%s, %s, %s, %s, %s, %s, %s)'
        for r in range(1, sheet.nrows):
            Change_Number = sheet.cell(r, 0).value
            ImpactedItem = sheet.cell(r, 1).value
            Change_Action = sheet.cell(r, 2).value
            EDZ = sheet.cell(r, 3).value
            Type = sheet.cell(r, 4).value
            Harness = sheet.cell(r, 5).value
            ECP_Num = sheet.cell(r, 6).value

            values = (Change_Number, ImpactedItem, Change_Action, EDZ, Type, Harness, ECP_Num)
            values = ['NULL' if x == '' else x for x in values]
            values = ['NULL' if x == 0 else x for x in values]
            print(tuple(values))
            cur.execute(query, tuple(values))
            conn.commit()

        cur.close()
        conn.close()
        pass

以下为报错信息:

clipboard.png

回答:

找到原因了,是由于’insert into’中缺少空格导致的
将’insert into’改为’insert into ‘即可

本文地址:H5W3 » pymysql报错1146 xls表格导入数据库失败 不知道问题出在哪

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址