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
以下為報錯訊息:
#
找到原因了,是由於'insert into'中缺少空格導致的
將'insert into'改為'insert into '即可