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

问题描述

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

# coding: utf-8-sigclass DBoperator(object): def input_HA(wbname):import xlrdimport pymysqlbook = 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 xlrdimport pymysqlbook = 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

以下为报错信息：

问题解答

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