Python 4.3.0
définit une classe pour faire fonctionner la base de données locale, puis définit deux fonctions pour importer les deux tables correspondantes.
Ce qui est étrange, c'est que la première fonction input_HA() peut être utilisée et que le formulaire est importé avec succès, mais la deuxième input_ImpactList() ne fonctionne pas
Quelqu'un peut-il m'aider ? Je suis un Python autodidacte novice, alors pardonnez-moi si le code est un peu moche, merci !
# 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
Voici le message d'erreur :
J'ai trouvé la raison, elle était due au manque d'espaces dans 'insérer dans'
Il suffit de remplacer 'insérer dans' par 'insérer dans'