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 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 MakeTreeDevice():
#create list whit elements shows
list_elements_shows = []
#generate connection
database = Comunication.BeginComunication()
#get list elements fathers
query = "SELECT serialp from conecta"
list_serialesp = [] #generate list whit information
serialesp = Comunication.MakeQueryDB(query, database[1]) #manage the query
#storage list of serials
for element in serialesp:
list_serialesp.append(element[0])
#delete elements repeats in list
list_serialesp = list(set(list_serialesp))
#for each element in list
for element in list_serialesp:
#check if exist in elements already show
if ExisteInLista(list_elements_shows, element) == 0:
list_parcial = Query.CreateTreeBySerial(element,
list_elements_shows,
'') #create tree of device
#append element to list show.
Query.AddElement(list_parcial, list_elements_shows)
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 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 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 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 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 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 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 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 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 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 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 UpdateNivel(id_level): id_level = int(id_level) print "Update name of level\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new name of level" name_level = raw_input() query = "UPDATE nivel SET nom_nivel = '%s' where id_nivel = %d"% (name_level, id_level) Query.UpdateDB(query) else: print "Not changes in name of level"
def UpdateEnlace(id_enlace): id_enlace = int(id_enlace) print "Update name of kind link?\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new name of kind link" nombre = raw_input() query = "UPDATE tipo_enlace SET nom_tipo_enlace = '%s' where id_marca = %d"% (nombre, id_enlace) Query.UpdateDB(query) else: print "Not changes in atribute nom_marca"
def UpdateMarca(id_marker): id_marker = int(id_marker) print "Update name of marker\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new name of marker" name_marker = raw_input() query = "UPDATE marca SET nom_marca = '%s' where id_marca = %d"% (name_marker, id_marker) Query.UpdateDB(query) else: print "Not chanes in name of marker"
def UpdateKindDevice(id_kind): id_kind = int(id_kind) print "Update name of kind device?\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new name of kind device" nombre = raw_input() query = "UPDATE tipo_disp SET nom_tipo_disp = '%s' where id_tipo_disp = %d"% (nombre, id_kind) Query.UpdateDB(query) else: print "Not changes in atribute nom_tipo_disp"
def UpdateCampus(id_campus): id_campus = int(id_campus)#set to int print "Update name of Campus?\n<1>Yes\n<x>No" if raw_input() == '1': print "Input new name of campus" name_campus = raw_input() #gestionamos la consulta para modificar el nombre de campus... query = "UPDATE campus SET nom_campus = '%s' where id_campus = %d"% (name_campus, id_campus) Query.UpdateDB(query) else: print "Not changes in name of campus"
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 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...
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 UpdateNameDevice(name_device, serial): query = "update dispositivo set nom_disp = '%s' where serial = '%s'" % (name_device, serial) Query.UpdateDBWithOutMessage(query)
def UpdateIPDevice(serial, IP): query = "update dispositivo set ip_disp = '%s' where serial = '%s'" % (IP,serial) Query.UpdateDBWithOutMessage(query)
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"