From 8c16ed8663a52c827ed562ea9bdbd7de02a48906 Mon Sep 17 00:00:00 2001 From: gsmselftest Date: Thu, 20 Oct 2011 18:14:09 +0200 Subject: almost final version --- For Weekly Test/tricode/DbClass.py | 322 +++++++++++++++++++++++++++++++++++++ 1 file changed, 322 insertions(+) create mode 100755 For Weekly Test/tricode/DbClass.py (limited to 'For Weekly Test/tricode/DbClass.py') diff --git a/For Weekly Test/tricode/DbClass.py b/For Weekly Test/tricode/DbClass.py new file mode 100755 index 0000000..8cde4e4 --- /dev/null +++ b/For Weekly Test/tricode/DbClass.py @@ -0,0 +1,322 @@ +import MySQLdb +import string + +class DBMySQLConnection: + def __init__(self, username, password, host, dbname): + #initialize at the start all the user parameters + self.usern = username + self.passw = password + self.host = host + self.db = dbname + self.connectionCreated = 0 + self.tasksList = list() + self.callerLists = list() + self.errCode = None + self.deviceUpdate = None + self.gsmRZList = None + self.gsmExtList = None + self.sipList = None + self.lockSet = 0 + global debugMode + debugMode = 0 + + def connectDB(self): + try: + #try the connection + self.datBaseConn=MySQLdb.connect(self.host,self.usern, self.passw,self.db) + self.datBaseConn.paramstyle = 'format' + self.cur = self.datBaseConn.cursor() #make the cursor, used for sending queries + self.connectionCreated = 1 #use it as an indicator that the connection was created + return 1 + + except MySQLdb.Error, e: + #if we have an error then try to catch it + error=str(e) + if error[1:5] == '1045': + #wrong username or password + return 0 + elif error[1:5] == '2002': + #can't connect to mysql, mysql shutdown or wrong host + return 2 + else: + if debugMode == 1: + print error + return 3 + + def closeDBConn(self): + #close the connection to the database here + if self.connectionCreated == 1: + try: + #close the cursor and then the connection to the DB + self.cur.close() + self.datBaseConn.close() + return 1 + except MySQLdb.Error, e: + #in case of an error + if debugMode == 1: + error = str(e) + print error + return 3 + else: + #I never really had a connection + return 0 + + def anyTasksToDo(self): + #see are there any jobs to be executed and make a list out of it + if self.connectionCreated == 1: + try: + self.cur.execute("SELECT * FROM TempTaskTable") + output = self.cur.fetchall() #get the mysql response + #parse the output from the mysql by creating a list + #with lists where each attribue(column) gets independent + #element of the list + for record in output: + columns = list() + for entry in record: + columns.append(str(entry)) + #columns.append(str(0)) + self.tasksList.append(columns) + + if not self.tasksList: + return 0 + else: + return 1 + except MySQLdb.Error, e: + error = str(e) + if error[1:5] == '1146': + return 2 #the table doesn't exist + if debugMode == 1: + print str(e) + return 3 + else: + return 0 + + def cleanTasksList(self): + if self.connectionCreated == 1: + del self.tasksList[:] + return 1 + else: + return 0 + + + def deviceAddress(self,deviceName): + if self.connectionCreated == 1: + try: + successful = self.cur.execute("SELECT `portName`,`number`, `username`, `password`, `server` FROM DeviceAddressTable where `deviceName`=%s", deviceName) + output = self.cur.fetchall() + deviceAddr = '' + for record in output: + columns = list() + for entry in record: + columns.append(str(entry)) + deviceAddr = columns + + return deviceAddr + except MySQLdb.Error, e: + error = str(e) + if error[1:5] == '1146': + return 2 #the table doesn't exist + if debugMode == 1: + print str(e) + return 3 #some error happened 07667929147--Fitri + else: + return 0 #I am not connected + def deviceList(self): # taking all device list and put it in the list + deviceLists = list() + if self.connectionCreated == 1: + try: + tuple = self.cur.execute("SELECT `deviceName` FROM DeviceAddressTable") + output = self.cur.fetchall() + + for record in output: + columns = list() + for entry in record: + columns.append(str(entry)) + deviceLists.append(columns) + return deviceLists + +#return deviceAddr + except MySQLdb.Error, e: + error = str(e) + if error[1:5] == '1146': + return 2 #the table doesn't exist + if debugMode == 1: + print str(e) + return 3 #some error happened + else: + print 'not conn' + return 0 #I am not connected + + def updateGSMDevice(self, deviceName, newPortName, newNumber): + if self.connectionCreated == 1: + try: + stmt = "UPDATE DeviceAddressTable SET portName = '"+ newPortName + "', number = '"+ newNumber+ "' WHERE deviceName = '" + deviceName+ "'" + self.cur.execute(stmt) + + except MySQLdb.Error, e: + if debugMode == 1: + print str(e) + return 3 + else: + return 0 + + def updatePingResult(self, taskNo, sipServer, sipGate, unisip, gsmBox1, gsmBox2): + if self.connectionCreated == 1: + try: + successful = self.cur.execute("UPDATE PingResultTable SET sipServer=%i, sipGate=%i, unisip=%i, gsmBox1=%i, gsmBox2=%i WHERE taskNo=%i"%(int(sipServer), int(sipGate), int(unisip), int(gsmBox1), int(gsmBox2), int(taskNo))) + output = self.cur.fetchone() + + if debugMode == 1: + print output + if successful == 0: + return 1 #ping table updated + else: + return 4 #the taskNo didn't exist + + + except MySQLdb.Error, e: + if debugMode == 1: + print str(e) + return 3 + else: + return 0 + + def deleteTempTask(self, taskID): + if self.connectionCreated == 1: + try: + successful = self.cur.execute("DELETE FROM TempTaskTable WHERE taskID=%i"%(int(taskID))) + output = self.cur.fetchone() + + if debugMode == 1: + print output + + if successful == 1: + return 1 #deleted it + else: + return 4 #that taskID didn't exist or something else + except MySQLdb.Error, e: + if debugMode == 1: + print str(e) + return 3 + + else: + return 0 + + def addResult(self, taskID, result): + if self.connectionCreated == 1: + try: + successful = self.cur.execute("INSERT INTO ResultTable(taskID, result) VALUES ('%i', '%i')"%(int(taskID), int(result))) + output = self.cur.fetchone() + + if debugMode == 1: + print output + if successful == 1: + return 1 #successfully added the result + else: + return 4 #hmmm + except MySQLdb.Error, e: + error = str(e) + if error[1:5] == '1062': + return 2 #duplicate entry for the key + if debugMode == 1: + print str(e) + return 3 + else: + return 0 + + + def errorCode(self,code): + if self.connectionCreated == 1: + try: + successful = self.cur.execute("SELECT description FROM ErrorCodeTable where `errorcode`=%s", code) + data = self.cur.fetchone() + self.errCode = data[0] + return self.errCode + + except MySQLdb.Error, e: + error = str(e) + if error[1:5] == '1146': + return 2 #the table doesn't exist + if debugMode == 1: + print str(e) + return 3 #some error happened + else: + return 0 #I am not connected + + def lockMutex(self, seconds): + if self.connectionCreated == 1: + try: + successful = self.cur.execute("SELECT IS_FREE_LOCK('SoftwareStarted')") + output = self.cur.fetchone() + if output[0] == 1: + #resource is free which means software is not started yet + successful = self.cur.execute("SELECT GET_LOCK('SoftwareStarted', %i)"%(int(seconds))) + + output1 = self.cur.fetchone() + if output1[0] == 1: + #I got the lock now + self.lockSet = 1 + return 1 + elif output1[0] == 0: + return 7 #if the attempt timed out (for example, because another client has previously locked the name) + else: + return 6 # if an error occurred (such as running out of memory or the thread was killed with + elif output[0] ==0: + return 4 #software is already running and somebody has allocated the mutex + else: + #means some not so good bug + return 5 # if an error occurs (such as an incorrect argument). + except MySQLdb.Error, e: + error = str(e) + if error[1:5] == '1062': + return 2 #duplicate entry for the key + if debugMode == 1: + print str(e) + return 3 + else: + return 0 + + def releaseMutex(self): + if self.connectionCreated == 1: + try: + if self.lockSet == 1: + successful = self.cur.execute("SELECT IS_FREE_LOCK('SoftwareStarted')") + output = self.cur.fetchone() + if output[0] == 1: + #the lock seems to be free + self.lockSet = 0 + return 4 + + elif output[0] == 0: + + #try to release the lock + successful = self.cur.execute("SELECT RELEASE_LOCK('SoftwareStarted')") + output1 = self.cur.fetchone() + self.lockSet = 0 + if output1[0] == 1: + #the lock was released successfully + return 1 + + elif output1[0] == 0: + # if the lock was not established by this thread (in which case the lock is not released) + return 5 + else: + # and NULL if the named lock did not exist. The lock does not exist if it was never obtained by a call to GET_LOCK() or if it has previously been released. + return 6 + else: + #some serious problem + #and NULL if an error occurs (such as an incorrect argument). + return 5 + + else: + return 7 #the lock wasn't set + + except MySQLdb.Error, e: + error = str(e) + if error[1:5] == '1062': + return 2 #duplicate entry for the key + if debugMode == 1: + print str(e) + return 3 + else: + return 0 -- cgit v1.2.3-55-g7522