def readingFromVMDB(self): cursor = connection.connections().getConnection() print('Reading data from VM DATABASE table') SQLTASK = "SELECT VMID, Name FROM VM;" with cursor.execute(SQLTASK): row = cursor.fetchone() while row: print(str(row[0]) + " " + str(row[1])) row = cursor.fetchone()
def UpdateVMData(self, Name, VMIP, OS, LifeTime, Owner, Designation): cursor = connection.connections().getConnection() print('Updating Virtual Machine DataBase...') SQLTASK = ( "UPDATE VM SET(Name, VMIP, OS, LifeTime, Owner, Designation)=? WHERE VALUES = (?,?,?,?,?,?)" ) with cursor.execute(SQLTASK, Name, VMIP, OS, LifeTime, Owner, Designation): print('Successfully Updated!')
def InsertVMData(self, Name, VMIP, OS, LifeTime, Owner, Designation, UniqueiD): cursor = connection.connections().getConnection() print('Inserting a new row into table') SQLTASK = ( "INSERT INTO VM(Name, VMIP, OS, LifeTime, Owner, Designation,UniqueiD) VALUES (?,?,?,?,?,?,?)" ) with cursor.execute(SQLTASK, Name, VMIP, OS, LifeTime, Owner, Designation, UniqueiD): print('Successfully Inserted!')
def GetAllVMsForThisUser(self, UniqueiD): cursor = connection.connections().getConnection() cursor.execute("SELECT * FROM VM") # VM Table name usersList = LogIn.LogeIn().AllUsers() for i in usersList: if (i[2] == UniqueiD): conn = pyodbc.connect( 'Driver={ODBC Driver 17 for SQL Server}; SERVER=HP; Database=VM;Trusted_Connection=yes' ) SQLCommand = "SELECT [VMID], [Name], [VMIP], [OS], [LifeTime], [Owner], [Designation],[UniqueiD] FROM VM;" return SQLCommand else: return None
def InsertVMData(self, Name, VMIP, OS, LifeTime, Owner, Designation): # self.cursor = connection.connections().getCursor() #self.SQLCommand = ("INSERT INTO VM(Name, VMIP, OS, LifeTime, Owner, Designation) VALUES (?,?,?,?,?,?)") #self.Values = [Name, VMIP, OS, LifeTime, Owner, Designation] #self.cursor.execute(self.SQLCommand, self.Values) #self.connection.commit() #print("Data Successfully Inserted") #self.connection.close() cursor = connection.connections().getConnection() print('Inserting a new row into table') SQLTASK = ( "INSERT INTO VM(Name, VMIP, OS, LifeTime, Owner, Designation) VALUES (?,?,?,?,?,?)" ) with cursor.execute(SQLTASK, Name, VMIP, OS, LifeTime, Owner, Designation): print('Successfully Inserted!')
def deleteFromVMDBbyName(self, Name): cursor = connection.connections().getConnection() print('Deleting From Vertual Machines Data Base') SQLTASK = "DELETE FROM VM WHERE Name = ?" with cursor.execute(SQLTASK, Name): print('Successfully Deleted!')
class VMDB: connection = connection.connections().getConnection() #cursor = connection.connections().getCursor() #Fetch all Data Base Data def fetchAllVMData(self): print("VM DATA: ") try: row = self.connection.execute('select * from VM') VMList = list(row) return VMList except pyodbc.DatabaseError as err: print("Error Occurred while fetching VM Details", err) return None finally: self.connection.close() # Insert Data to Data Base def InsertVMData(self, Name, VMIP, OS, LifeTime, Owner, Designation): # self.cursor = connection.connections().getCursor() #self.SQLCommand = ("INSERT INTO VM(Name, VMIP, OS, LifeTime, Owner, Designation) VALUES (?,?,?,?,?,?)") #self.Values = [Name, VMIP, OS, LifeTime, Owner, Designation] #self.cursor.execute(self.SQLCommand, self.Values) #self.connection.commit() #print("Data Successfully Inserted") #self.connection.close() cursor = connection.connections().getConnection() print('Inserting a new row into table') SQLTASK = ( "INSERT INTO VM(Name, VMIP, OS, LifeTime, Owner, Designation) VALUES (?,?,?,?,?,?)" ) with cursor.execute(SQLTASK, Name, VMIP, OS, LifeTime, Owner, Designation): print('Successfully Inserted!') # Update Data Base Data def UpdateVMData(self, Name, VMIP, OS, LifeTime, Owner, Designation): cursor = connection.connections().getConnection() print('Updating Virtual Machine DataBase...') SQLTASK = ( "UPDATE VM SET(Name, VMIP, OS, LifeTime, Owner, Designation)=? WHERE VALUES = (?,?,?,?,?,?)" ) with cursor.execute(SQLTASK, Name, VMIP, OS, LifeTime, Owner, Designation): print('Successfully Updated!') # Delete Data From Data Base def deleteFromVMDBbyID(self, VMID): cursor = connection.connections().getConnection() print('Deleting From Vertual Machines Data Base') SQLTASK = "DELETE FROM VM WHERE VMID = ?" with cursor.execute(SQLTASK, VMID): print('Successfully Deleted!') def deleteFromVMDBbyName(self, Name): cursor = connection.connections().getConnection() print('Deleting From Vertual Machines Data Base') SQLTASK = "DELETE FROM VM WHERE Name = ?" with cursor.execute(SQLTASK, Name): print('Successfully Deleted!') # Read special Data From Data Base def readingFromVMDB(self): cursor = connection.connections().getConnection() print('Reading data from VM DATABASE table') SQLTASK = "SELECT VMID, Name FROM VM;" with cursor.execute(SQLTASK): row = cursor.fetchone() while row: print(str(row[0]) + " " + str(row[1])) row = cursor.fetchone() # Display Data from begin OR End of the Data Base By Entry def Search(self): conn = pyodbc.connect( 'Driver={ODBC Driver 17 for SQL Server}; SERVER=Amin\SQLEXPRESS; Database=VM;Trusted_Connection=yes' ) SQLCommand = "SELECT [VMID], [Name], [VMIP], [OS], [LifeTime], [Owner], [Designation] FROM VM;" df = pd.read_sql(SQLCommand, conn) print("VM From Beginnig: \n", df.head(2)) print("\n\nVM From End: \n", df.tail(1))
class VMDB: connection = connection.connections().getConnection() # cursor = connection.connections().getCursor() def fetchAllVMData(self): print("VM DATA: ") try: row = self.connection.execute('select * from VM') VMList = list(row) return VMList except pyodbc.DatabaseError as err: print("Error Occurred while fetching VM Details", err) return None finally: self.connection.close() def GetAllVMsForThisUser(self, UniqueiD): cursor = connection.connections().getConnection() cursor.execute("SELECT * FROM VM") # VM Table name usersList = LogIn.LogeIn().AllUsers() for i in usersList: if (i[2] == UniqueiD): conn = pyodbc.connect( 'Driver={ODBC Driver 17 for SQL Server}; SERVER=HP; Database=VM;Trusted_Connection=yes' ) SQLCommand = "SELECT [VMID], [Name], [VMIP], [OS], [LifeTime], [Owner], [Designation],[UniqueiD] FROM VM;" return SQLCommand else: return None def InsertVMData(self, Name, VMIP, OS, LifeTime, Owner, Designation, UniqueiD): cursor = connection.connections().getConnection() print('Inserting a new row into table') SQLTASK = ( "INSERT INTO VM(Name, VMIP, OS, LifeTime, Owner, Designation,UniqueiD) VALUES (?,?,?,?,?,?,?)" ) with cursor.execute(SQLTASK, Name, VMIP, OS, LifeTime, Owner, Designation, UniqueiD): print('Successfully Inserted!') def UpdateVMData(self, Name, VMIP, OS, LifeTime, Owner, Designation, UniqueiD): cursor = connection.connections().getConnection() print('Updating Virtual Machine DataBase...') SQLTASK = ( "UPDATE VM SET(Name, VMIP, OS, LifeTime, Owner, Designation,UniqueiD)=? WHERE VALUES = (?,?,?,?,?,?,?)" ) with cursor.execute(SQLTASK, Name, VMIP, OS, LifeTime, Owner, Designation, UniqueiD): print('Successfully Updated!') def deleteFromVMDB(self, VMID): cursor = connection.connections().getConnection() print('Deleting From Vertual Machines Data Base') SQLTASK = "DELETE FROM VM WHERE VMID = ?" with cursor.execute(SQLTASK, VMID): print('Successfully Deleted!') def readingFromVMDB(self): cursor = connection.connections().getConnection() print('Reading data from VM DATABASE table') SQLTASK = "SELECT VMID, Name FROM VM;" with cursor.execute(SQLTASK): row = cursor.fetchone() while row: print(str(row[0]) + " " + str(row[1])) row = cursor.fetchone() def Search(self, VMName): conn = pyodbc.connect( 'Driver={ODBC Driver 17 for SQL Server}; SERVER=HP; Database=VM;Trusted_Connection=yes' ) SQLCommand = "SELECT [VMID], [Name], [VMIP], [OS], [LifeTime], [Owner], [Designation],[UniqueiD] FROM VM;" df = pd.read_sql(SQLCommand, conn) print("VM From Beginnig: \n", df.head(2)) print("\n\nVM From End: \n", df.tail(1)) def ExtendLifeTime(self, PassWord, VMName): ListOfUniqueiD = [] ListOfAllVMs = [] if (LogIn.LogeIn().CheckLogIn('root', PassWord)): listUsers = LogIn.LogeIn().AllUsers() for i in listUsers: ListOfUniqueiD.append(i[2]) ListOfAllVMs = self.fetchAllVMData() for i in ListOfAllVMs: for j in ListOfUniqueiD: if (i[8] == j and VMName == i[1]): #not 100% if it i[8] =? i[5]=? i[5] = i[5] + timedelta(days=30)