def GetLocations():
database = sqlite3.connect(ReadSettings.GetSetting("Database","DatabaseLocation"))
cursor = database.cursor()
cursor.execute("""SELECT DISTINCT Location FROM SWITCHES""")
Result = cursor.fetchall()
database.close()
return (Result)
def SearchPatch(SearchTerms):
database = sqlite3.connect(ReadSettings.GetSetting("Database","DatabaseLocation"))
cursor = database.cursor()
cursor.execute("""SELECT * FROM PATCHES WHERE{0}""".format(SearchTerms))
Result = cursor.fetchall()
database.close()
print(Result)
return Result
def AddNewSwitch(SwitchID, Location="", IPv4Address="", IPv6Address="", Notes=""):
database = sqlite3.connect(ReadSettings.GetSetting("Database","DatabaseLocation"))
cursor = database.cursor()
cursor.execute("""INSERT INTO SWITCHES (SwitchName, Location, IPv4Address, IPv6Address, Notes) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')""".format(SwitchID, Location, IPv4Address, IPv6Address, Notes))
database.commit()
database.close()
def GetTechNameFromID(ID):
database = sqlite3.connect(ReadSettings.GetSetting("Database","DatabaseLocation"))
cursor = database.cursor()
cursor.execute("""SELECT Tech FROM TECHS WHERE TECHS_id = "{0}" """.format(ID))
Result = cursor.fetchall()
database.close()
return str((Result[0])[0])
def GetTechNames():
database = sqlite3.connect(ReadSettings.GetSetting("Database","DatabaseLocation"))
cursor = database.cursor()
cursor.execute("""SELECT Tech FROM TECHS""")
Result = cursor.fetchall()
database.close()
return(Result)
def GetSwitchIDFromLocation(Location):
database = sqlite3.connect(ReadSettings.GetSetting("Database","DatabaseLocation"))
cursor = database.cursor()
cursor.execute("""SELECT SWITCHES_id FROM SWITCHES WHERE Location = "{0}" """.format(Location))
Result = cursor.fetchall()
database.close()
return (Result)
def GetSwitchLocationFromID(ID):
database = sqlite3.connect(ReadSettings.GetSetting("Database","DatabaseLocation"))
cursor = database.cursor()
cursor.execute("""SELECT Location FROM SWITCHES WHERE SWITCHES_id = "{0}" """.format(ID))
Result = cursor.fetchall()
database.close()
return str((Result[0])[0])
def CustomQuery(Query):
database = sqlite3.connect(ReadSettings.GetSetting("Database","DatabaseLocation"))
cursor = database.cursor()
cursor.execute("""{0}""".format(Query))
Result = cursor.fetchall()
database.commit()
database.close()
return(Result)
def AddPatch(Tech,Switch,PannelPort,SwitchUnit,SwitchPort,PatchType,Date,Notes):
database = sqlite3.connect(ReadSettings.GetSetting("Database","DatabaseLocation"))
cursor = database.cursor()
if PatchType == "New":
AlreadyPatched=0
PatchRemoved=0
elif PatchType == "Existing":
AlreadyPatched=1
PatchRemoved=0
elif PatchType == "Removal":
AlreadyPatched=1
PatchRemoved=1
cursor.execute("""INSERT INTO PATCHES ({0}) VALUES ("{1}","{2}","{3}","{4}","{5}","{6}","{7}","{8}","{9}")""".format(GetTablesColumns("PATCHES",cursor),Tech,Switch,PannelPort,SwitchUnit,SwitchPort,Date,Notes,AlreadyPatched,PatchRemoved))
database.commit()
database.close()
def SetupDatabase():
database = sqlite3.connect(
ReadSettings.GetSetting("Database", "DatabaseLocation"))
cursor = database.cursor()
CreateTable("TECHS", "Tech varchar(255)", cursor)
CreateTable("ENDUSERS", "EndUser varchar(255)", cursor)
CreateTable(
"SWITCHES",
"SwitchName varchar(255), Location varchar(255), IPv4Address varchar(15), IPv6Address varchar(39), Notes varchar(255)",
cursor)
CreateTable(
"PATCHES",
"tech_id INTEGER, switch_id INTEGER, PannelPort varchar(255), Unit INTEGER, Port INTEGER, Date varchar(10), Notes varchar(255), AlreadyPatched BOOLEAN, PatchRemoved BOOLEAN",
cursor)
database.commit()
database.close()
def CheckDatabaseExists():
if os.path.exists(ReadSettings.GetSetting("Database","DatabaseLocation")):
return True
else:
return False
def AddNewTech(Name):
database = sqlite3.connect(ReadSettings.GetSetting("Database","DatabaseLocation"))
cursor = database.cursor()
cursor.execute("""INSERT INTO TECHS (Tech) VALUES ("{0}") """.format(Name))
database.commit()
database.close()
