def DeleteTipoDispositivo(id_tipo_disp): #first verify if it doesn't have relation with the table modelo query = "select * from modelo where id_tipo_disp = %d" % id_tipo_disp if Query.ExistElementInDB(query) == 0: print "Is not possible delete model whit id ", id_tipo_disp, " because has relations whit other elements" else: #we evaluate whether the device type in the database query = "select * from tipo_disp where id_tipo_disp = %d" % id_tipo_disp if Query.ExistElementInDB(query) == 0: #manage the query to delete ... print "Delete kind of device" query = "DELETE FROM tipo_disp where id_tipo_disp = %d" % id_tipo_disp Query.DeleteDB(query) else: print "Not exists a kind device whit id ", id_tipo_disp, " registered in data base"
def UpdateDeviceInDataBase(): file_read = open("information_actual_and_SNMP.csv", 'r')#open output file RecopilaInformatioSNMP.py #read file... line = file_read.readline() while line: line = line.strip('\n') line_information = line.split(';') #ask if device response to SNMP... if line_information[3] == "si": #ask if serial exists in data base... query = "select * from dispositivo where serial = '%s'" % line_information[0] if Query.ExistElementInDB(query) == 0: #update atributes... UpdateIPDevice(line_information[4], line_information[0]) UpdateNameDevice(line_information[1], line_information[0]) UpdateModelDevice(line_information[2], line_information[0]) print line_information else: #the device not exists in data base... is necesary change element... print line_information[0] query = "insert into dispositivo values ('%s', %d, '%s', '%s', '%s', %d, %d)" % (line_information[0], 0, line_information[1], '', line_information[4], 0, 0) Query.InsertDBWithOutMessage(query) UpdateModelDevice(line_information[2], line_information[0]) line = file_read.readline() file_read.close()
def UpdateEdificio(id_building): id_building = int(id_building) print "Update id of campus\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new id of campus" id_campus = raw_input() id_campus = ValidateInput(id_campus) #ask if exist id in data base query = "select * from campus where id_campus = %d" % id_campus if Query.ExistElementInDB(query) == 0: #gestionamos la consulta para modificar el atributo... query = "UPDATE edificio SET id_campus = %d where id_edif = %d" % (id_campus, id_building) Query.UpdateDB(query) else: print "ID campus not in data base" else: print "Not changes in id of campus for building" print "Update name of building\n<1>Yes\n<x>No" if raw_input() == '1': print "Input name of building" name_building = raw_input() query = "UPDATE edificio set nom_edif = '%s' where id_edif = %d" % (name_building, id_building) Query.UpdateDB(query) else: print "Not changes in name of building"
def DeleteModelo(id_modelo): #first check if it has no relations with the table device query = "select * from dispositivo where id_modelo = %d" % id_modelo if Query.ExistElementInDB(query) == 0: print "Is not possible delete model with id ", id_modelo, " because has relations whit other elements" else: #we evaluated whether the model exists in the database query = "select * from modelo where id_modelo = %d" % id_modelo if Query.ExistElementInDB(query) == 0: #manage the query to delete print "Delete model" query = "DELETE FROM modelo where id_modelo = %d" % id_modelo Query.DeleteDB(query) else: print "Not exists model whit id ", id_modelo, " registered in data base"
def InsertDevices(list_element): first_element = list_element[0] cont = 0 for element in list_element: if element == first_element: cont += 1 if cont > 1: break #make split to element information_element = element.split(";") query = "select * from dispositivo where serial = '%s'" % information_element[ 1] print information_element if Query.ExistElementInDB(query) == 1: query = "insert into dispositivo values ('%s', %d, '%s', '%s', '%s', %d, %d)" % ( information_element[1], 0, information_element[2], '', information_element[0], 0, 0) Query.InsertDBWithOutMessage(query) UpdateModelDevice(information_element[3], information_element[1]) else: #update atributes... UpdateIPDevice(information_element[1], information_element[0]) UpdateNameDevice(information_element[2], information_element[1]) UpdateModelDevice(information_element[3], information_element[1])
def DeleteEdificio(id_edificio): #first we verify if it doesn't have relation with the table rack query = "select * from rack where id_edif = %d" % id_edificio if Query.ExistElementInDB(query) == 0: print "Is not possible delete delete building whit id ", id_edificio, " because has relations whit other elements" else: #We evaluate if the building exists in the database... query = "select * from edificio where id_edif = %d" % id_edificio if Query.ExistElementInDB(query) == 0: #manage the query... print "Delete building" query = "DELETE FROM edificio where id_edif = %d" % id_edificio Query.DeleteDB(query) else: print "Not exists a building whit id ", id_edificio, " registered in data base"
def DeleteNivel(id_nivel): #first we verify if it doesn't have relation with the table rack query = "select * from rack where id_nivel = %d" % id_nivel if Query.ExistElementInDB(query) == 0: print "Is not possible delete level with id ", id_nivel, " because has relations whit other elements" else: #We evaluate if the level exists in the database... query = "select * from nivel where id_nivel = %d" % id_nivel if Query.ExistElementInDB(query) == 0: #manage the query to delete ... print "Delete level" query = "DELETE FROM nivel where id_nivel = %d" % id_nivel Query.DeleteDB(query) else: print "Not exists a level whit id ", id_nivel, " registered in data base"
def DeleteRack(id_rack): #first verify if it has no relations with the table device query = "select * from dispositivo where id_rack = %d" % id_rack if Query.ExistElementInDB(query) == 0: print "Is not possible delete rack whit id ", id_rack, " because has relations whit others elements" else: #We evaluate whether the rack exists in the database... query = "select * from rack where id_rack = %d" % id_rack if Query.ExistElementInDB(query) == 0: #manage the query to delete.... print "Delete rack" query = "DELETE FROM rack where id_rack = %d" % id_rack Query.DeleteDB(query) else: print "Not exists rack white id ", id_rack, " registered in data base"
def DeleteCampus(id_campus): #first verify if it doesn't have relation with the table buildings, that is, if building is not referenced to campus with these id... query = "select * from edificio where id_campus = %d" % id_campus if Query.ExistElementInDB(query) == 0: print "Is not possible delete campus whit id ", id_campus, " because has relations whit other elements" else: #We ask if the campus exists in the database... query = "select * from campus where id_campus = %d" % id_campus if Query.ExistElementInDB(query) == 0: #manage the consultation ... print "Delete campus" query = "DELETE FROM campus where id_campus = %d" % id_campus Query.DeleteDB(query) else: print "Not exists a campus whit id ", id_campus, " registered in data base"
def DeleteTipoEnlace(id_tipo_enlace): #first verify if it has no relations with the table conecta query = "select * from conecta where id_tipo_enlace = %d" % id_tipo_enlace if Query.ExistElementInDB(query) == 0: print "Is not possible delete kind link whit id ", id_tipo_enlace, " because has relations whit others elements" else: #We evaluate whether the link type exists in the database... query = "select * from tipo_enlace where id_tipo_enlace = %d" % id_tipo_enlace if Query.ExistElementInDB(query) == 0: #manage the query to delete.... print "Delete kind of link" query = "DELETE FROM tipo_enlace where id_tipo_enlace = %d" % id_tipo_enlace Query.DeleteDB(query) else: print "Not exists kind of link whit id ", id_tipo_enlace, " registered in data base"
def GetInformationDeviceSon(list_information_with_father): for element in list_information_with_father: list_element = element.split(';') if len(list_element) == 5: if list_element[2] != '-': query = "select * from dispositivo where serial = '%s'" % list_element[ 2] if Query.ExistElementInDB(query) == 1: query = "insert into dispositivo values ('%s', %d, '%s', '%s', '%s', %d, %d)" % ( list_element[2], 0, list_element[3], '', list_element[1], 0, 0) Query.InsertDBWithOutMessage(query) UpdateModelDevice(list_element[4], list_element[2]) else: #update atributes... UpdateIPDevice(list_element[2], list_element[1]) UpdateNameDevice(list_element[3], list_element[2]) UpdateModelDevice(list_element[4], list_element[2]) try: #get serial version of device by IP serial = GetSerialDevice(list_element[0]) query = "Insert into conecta values('%s', '%s', %d)" % ( serial, list_element[2], 0) Query.InsertDBWithOutMessage(query) except: pass
def DeleteMarca(id_marca): #first verify if it has no relations with the table modelo... query = "select * from modelo where id_marca = %d" % id_marca if Query.ExistElementInDB(query) == 0: print "Is not possible delete marker whit id ", id_marca, " because has relations whit others elements" else: #we evaluated whether the brand exists in the database query = "select * from marca where id_marca = %d" % id_marca if Query.ExistElementInDB(query) == 0: #manage the query to delete.... print "Delete marker" query = "DELETE FROM marca where id_marca = %d" % id_marca Query.DeleteDB(query) else: print "Not exists marker whit id ", id_marca, " registered in data base"
def DeleteDevice(serial): #first verify if it has relations with another device, that is if in the table exists in connecting with serialp cannot be removed query = "select * from conecta where serialp= '%s'" % serial if Query.ExistElementInDB(query) == 0: print "Is not possible delete element white serial ", serial, " because has relations whit other device" else: #We wonder if it exists in the database... query = "select * from dispositivo where serial = '%s'" % serial if Query.ExistElementInDB(query) == 0: print "Delete connection in table conecta" #first i have to delete it in the table links ... query = "DELETE FROM conecta where serialh = '%s'" % serial Query.DeleteDB(query) print "Delete device in table device" query = "DELETE FROM dispositivo where serial = '%s'" % serial Query.DeleteDB(query) else: print "Device whit serial input not exists in data base"
def UpdateModelo(id_model): id_model = int(id_model) print "Update name of model\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new name of model" name_model = raw_input() query = "UPDATE modelo SET nom_modelo = '%s' where id_modelo = %d"% (name_model, id_model) Query.UpdateDB(query) else: print "Not changes in name of model" #update id of kind device print "Update id of kind device\n<1>Yes\n<x>No" if raw_input() == '1': print "Input id of kind device" id_kind = raw_input() id_kind = ValidateInput(id_kind) #check if id of kind device exist in data base query = "select * from tipo_disp where id_tipo_disp = %d" % id_kind if Query.ExistElementInDB(query) == 0: query = "UPDATE modelo SET id_tipo_disp = %d where id_modelo = %d" % (id_kind, id_model) Query.UpdateDB(query) else: print "ID kind device not in data base" else: print "Not changes in id of kind device" #update id of marker print "Update id of marker\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new id of marker" id_marker = raw_input() id_marker = ValidateInput(id_marker) query = "select * from marca where id_marca = %d" % id_marker if Query.ExistElementInDB(query) == 0: query = "UPDATE modelo SET id_marca = %d where id_modelo = %d" % (id_marker, id_model) Query.UpdateDB(query) else: print "ID of marker not in data base" else: print "Not changes in id of marker"
def UpdateRack(id_rack): id_rack = int(id_rack) print "Update detail of rack?\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new detail" detalle = raw_input() query = "UPDATE rack SET detalle = '%s' where id_rack = %d"% (detalle, id_rack) Query.UpdateDB(query) else: print "Not changes in atribute detail" #update id of level print "Update id of level?\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new id of level" id_nivel = raw_input() id_nivel = ValidateInput(id_nivel) query = "select * from nivel where id_nivel = %d" % id_nivel if Query.ExistElementInDB(query) == 0: query = "UPDATE rack SET id_nivel = %d where id_rack = %d" % (id_nivel, id_rack) Query.UpdateDB(query) else: print "ID level not exists in data base" else: print "Not changes in atribute id_nivel" #update id of building print "Update id of building?\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new id of building" id_building = raw_input() id_building = ValidateInput(id_building) query = "select * from edificio where id_edif = %d" % id_building if Query.ExistElementInDB(query) == 0: query = "UPDATE rack SET id_edif = %d where id_rack = %d" % (id_building, id_rack) Query.UpdateDB(query) else: print "ID of building not exists in data base" else: print "Not changes in atribute id_edif"
def InsertModelDevice( model_device): #Falta el id de la marca y del tipo de dispositivo!!! #evaluate if exists the model in data base... query = "select * from modelo where nom_modelo = '%s'" % model_device if Query.ExistElementInDB(query) != 0: #exists in db... id_model = 0 #insert model in data base... id_model = Query.GetMaxIDinTable( 'modelo', 'id_modelo') #falta agregar el tipo de dispositivo y la marca... if id_model == None: id_model = 1 else: id_model += 1 query = "insert into modelo values (%d, %d, %d, '%s')" % ( id_model, 0, 0, model_device) Query.InsertDBWithOutMessage(query)
def UpdateModelDevice( model_device, serial): #Falta el id de la marca y del tipo de dispositivo!!! #evaluate if exists the model in data base... query = "select * from modelo where nom_modelo = '%s'" % model_device if Query.ExistElementInDB(query) == 0: #exists in db... #get the id of model query = "select id_modelo from modelo where nom_modelo = '%s'" % model_device database_elements = Comunication.BeginComunication() #init connection response = Comunication.MakeQueryDB( query, database_elements[1]) #manage query #update the id of model device... query = "update dispositivo set id_modelo = %d where serial = '%s'" % ( int(response[0][0]), serial) Query.UpdateDBWithOutMessage(query) else: #the model device not exist in data base... #insert model in data base... id_model = Query.GetMaxIDinTable( 'modelo', 'id_modelo') #falta agregar el tipo de dispositivo y la marca... if id_model == None: id_model = 0 query = "insert into modelo values (%d, %d, %d, '%s')" % ( id_model, 0, 0, model_device) Query.InsertDBWithOutMessage(query) query = "update dispositivo set id_modelo = %d where serial = '%s'" % ( id_model, serial) Query.UpdateDBWithOutMessage(query) else: id_model += 1 query = "insert into modelo values (%d, %d, %d, '%s')" % ( id_model, 0, 0, model_device) Query.InsertDBWithOutMessage(query) query = "update dispositivo set id_modelo = %d where serial = '%s'" % ( id_model, serial) Query.UpdateDBWithOutMessage(query)
def MakeTree(): #establecemos la comunicacion database = Comunication.BeginComunication() #obtenemos en una lista aparte la informacion de los niveles... query = "SELECT * FROM nivel" niveles = Comunication.MakeQueryDB(query, database[1]) #gestionamos la consulta #creamos una consulta... query = "SELECT * FROM campus" info_campus = Comunication.MakeQueryDB( query, database[1]) #gestionamos la consulta for element in info_campus: #No mostrare los campus si no tienen edificios... #se obtiene la informacion de los edificios... query = "SELECT * FROM edificio where id_campus = %d" % element[0] if Query.ExistElementInDB(query) == 0: info_edificios = Comunication.MakeQueryDB( query, database[1]) #gestionamos la consulta print "├─Campus: %s" % element[1] for edificios in info_edificios: #no mostrare los edificios si no tiene racks asociados query = "select * from edificio join rack on (edificio.id_edif = rack.id_edif) where edificio.id_edif=%d" % int( edificios[0]) if Query.ExistElementInDB(query) == 0: print "│ ├─Edificio: '%s'" % edificios[2] #por cada nivel del edificio obtenemos los rack... for nivel in niveles: query = "select * from rack join edificio on (rack.id_edif = edificio.id_edif) join nivel on (nivel.id_nivel = rack.id_nivel) where rack.id_nivel = %d AND rack.id_edif = %d" % ( nivel[0], edificios[0]) #evaluamos si el edificio tiene los niveles indicados... if Query.ExistElementInDB(query) == 0: racks_niveles = Comunication.MakeQueryDB( query, database[1]) print "│ │ ├─Nivel : ", nivel[1] for rack in racks_niveles: #informacion de los rack print "│ │ │ ├─Rack: ", rack[3] #por cada rack obtenemos la informacion de los dispositivos existentes en el... query = "select dispositivo.serial, dispositivo.nom_disp, dispositivo.ip_disp, modelo.nom_modelo, tipo_disp.nom_tipo_disp, marca.nom_marca from dispositivo join rack on (rack.id_rack = dispositivo.id_rack) join modelo on (dispositivo.id_modelo = modelo.id_modelo) join tipo_disp on (tipo_disp.id_tipo_disp = modelo.id_tipo_disp) join marca on (marca.id_marca = modelo.id_marca) where dispositivo.id_rack= %d" % rack[ 0] dispositivo_rack = Comunication.MakeQueryDB( query, database[1]) #evaluamos aquellos que son papas y con ellos formamos el arbol dispositivo_rack_papa = [] for dispositivo in dispositivo_rack: if Query.IsPapa(dispositivo[0]) == 1: dispositivo_rack_papa.append( dispositivo[0]) #ahora hacemos el arbol en base a la lista obtenida... #creamos una lista con los elementos que ya han sido buscados... list_elements_shows = [] #recorremos la lista de seriales que son papas... for element in dispositivo_rack_papa: #evaluamos si ya mostramos a el y sus hijos... if MakeTreeDispositivos.ExisteInLista( list_elements_shows, element) == 0: list_parcial = Query.CreateTreeBySerial( element, list_elements_shows, '│ │ │ │ ' ) #creamos el arbol para ese dispositivo... #agregamos los elementos a la lista de elementos mostrados... Query.AddElement( list_parcial, list_elements_shows) return 0
def UpdateDevice(serial): print "Update ID Model?\n<1>Yes\n<x>No"#ask if wish update id model of device if raw_input() == '1': print "Input new ID model" id_modelo = raw_input()#get new id model by standard input id_modelo = ValidateInput(id_modelo) #evaluate if id model input exist in data base and generate the query query = "select * from modelo where id_modelo = %d"% id_modelo if Query.ExistElementInDB(query) == 0: #generate query for update element query = "UPDATE dispositivo SET id_modelo = %d where serial = '%s'" % (id_modelo, serial) Query.UpdateDB(query) else: print "ID model is not registered in data base" else: print "Not changes in id model of device" #update name of device print "Update name of device?\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new name of device" name = raw_input() #create tue query query = "UPDATE dispositivo SET nom_disp = '%s' where serial = '%s'" % (name, serial) Query.UpdateDB(query) else: print "Not changes in name of device" #update observation device print "Update observation of device?\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new observation" observation = raw_input() #manage the query query = "UPDATE dispositivo SET obs_disp = '%s' where serial = '%s'" % (observation, serial) Query.UpdateDB(query) else: print "Not changes in observation of device" #update ip of device print "Update IP of device?\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new IP of device" ip = raw_input() #manage query query = "UPDATE dispositivo SET ip_disp = '%s' where serial = '%s'" % (ip, serial) Query.UpdateDB(query) else: print "Not changes in IP of device" print "Update ID Rack?\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new ID rack" id_rack = raw_input() id_rack = ValidateInput(id_rack) query = "select * from rack where id_rack = %d"% id_rack if Query.ExistElementInDB(query) == 0: query = "UPDATE dispositivo SET id_rack = %d where serial = '%s'" % (id_rack, serial) Query.UpdateDB(query) else: print "ID rack is not registered in data base" else: print "Not changes in ID rack of device" print "Update number of door device?\n<1>Yes\n<x>No" if raw_input() == '1': print "Input number of doors of device" num_puertas = raw_input() num_puertas = ValidateInput(num_puertas) query = "UPDATE dispositivo SET ptas_disp = %d where serial = '%s'" % (num_puertas, serial) Query.UpdateDB(query) else: print "Not changes in number doors of device"