class MysqlUtil(object, metaclass=Singleton): def __init__(self, logger=None): self.util = Utility() self.globals = Global() #self.env = Environment() self.dbResponse = self.util.getACopy( self.globals.Template['DBResponse']) self.infra = RestInfra() self.encryptKey = self.infra._Infra__bootStrapData['Main']['Key'] if logger: self.logger = logger else: print('no logger is available, can not store logging information') self.logger = None # select @@port; # show variables where variable_name = 'port' # select user(); # insert json in mysql # insert into temp values(json_array(json_object('id',1001,'when','2015-01-01 10:00:00','score',35), json_object('id',1002,'when','2015-02-01 10:00:00','score',45)) ); def makeConnection(self, host, port, username, encryptPass, dbName, tzone=None): try: #print('pass',self.util.decrypt(self.env.encryptPass)) conn = connect(host=host, port=port, user=username, password=self.util.decrypt(self.encryptKey, encryptPass), database=dbName) #print(conn) # we need to set the timezone as requested for this connection if tzone: conn.time_zone = '+00:00' return conn except errors.Error as error: #return self.util.buildDBResponse(self.globals.Error, None, None, error) raise except Exception as error: raise #return self.util.buildDBResponse(self.globals.Error, None, None, error) def getRepDBConnection(self): try: #print('pass',self.util.decrypt(self.env.encryptPass)) conn = self.makeConnection(\ self.infra.dbConfigData['host'], \ self.infra.dbConfigData['port'], \ self.infra.dbConfigData['user'], \ self.infra.dbConfigData['password'], \ self.infra.dbConfigData['database']) return conn except Exception as error: raise #return self.util.buildDBResponse(self.globals.Error, None, None, error) def execSelectSql(self, **args): ''' Description: Execute Select statement Arguments: Key word arguments Conn : Connection handler SqlText : Sql to be executed SqlArgs : Arguments need to be passed to sql (optional), default = None (SqlArgs should be in dict) SqlOutput : Sqloutput Dict/Touple (optional), default = Touple Usage : execSelectSql(<connection>, <sql>, <sql args (optional)>, <sql output Dict/Touple (optional)> ) Return : Array internal --> #mySqlArgs = ','.join(list(map(lambda x: '%s', args))) ''' try: myRequiredArgs = ['Conn', 'SqlText'] #Validating arguments myArgValResult = self.util.valArguments(myRequiredArgs, args) if myArgValResult['Status'] == self.globals.UnSuccess: raise InvalidArguments(myArgValResult['Message']) #extracting arguments myDBConn = args['Conn'] mySqlText = args['SqlText'] if not mySqlText.upper().startswith( ('SELECT', 'INSERT', 'UPDATE', 'DELETE')): raise InvalidArguments( 'SQL statement can be SELECT/INSERT/UPDATE/DELETE only') mySqlArgs = args['SqlArgs'] if 'SqlArgs' in args else None mySqlOutput = args[ 'SqlOutput'] if 'SqlOutput' in args else self.globals.SqlOutput[ 'Default'] if mySqlOutput not in self.globals.SqlOutput['All']: mySqlOutput = self.globals.SqlOutput['Default'] # executing sql print('Executing sql >>>', mySqlText, mySqlArgs) myDBcur = myDBConn.cursor(buffered=True) totalRows = myDBcur.execute(mySqlText, mySqlArgs) if mySqlText.upper().startswith('SELECT'): if myDBcur.with_rows: # if we got any data myTotalRows = myDBcur.rowcount #myCurDesc = myDBcur.description myAllColumns = myDBcur.column_names myRawData = myDBcur.fetchall() # build the sqloutput if mySqlOutput == self.globals.SqlOutput['Dict']: #myAllColumns = [column[0] for column in myCurDesc] myData = [ dict(zip(myAllColumns, row)) for row in myRawData ] else: myData = myRawData else: myData = None elif mySqlText.upper().startswith(('INSERT', 'UPDATE', 'DELETE')): pass myDBcur.close() return self.util.buildDBResponse(self.globals.Success, myData, myTotalRows) except errors.Error as error: #return self.util.buildDBResponse(self.globals.Error, None, None, error) raise except Exception as error: self.util.logError() raise #return self.util.buildDBResponse(self.globals.Error, None, None, error) def commitTrans(self, dbConn): try: dbConn.commit() except errors.Error as error: return self.util.buildDBResponse(self.globals.Error, None, None, error) except Exception as error: return self.util.buildDBResponse(self.globals.Error, None, None, error) def rollbackTrans(self, dbConn): try: dbConn.rollback() except errors.Error as error: return self.util.buildDBResponse(self.globals.Error, None, None, error) except Exception as error: return self.util.buildDBResponse(self.globals.Error, None, None, error) def buildMarkerForSql(self, sqlText, argList, marker): sqlArgMarker = ', '.join(list(map(lambda x: marker, args))) return sqlText % sqlArgMarker def buildDynaSql(self, colList, tableList, criteria): if colList: mySql = colList # adding tables to sql for indx, table in enumerate(tableList): if indx > 0: # found more than one table, we need to add ',' in from clause to seperate tables mySql = ''.join([mySql, ' , ', table]) else: mySql = ''.join([mySql, ' from ', table]) # adding where clause if criteria: mySql = ''.join([mySql, ' where ', criteria]) return mySql
class Infra(object, metaclass=Singleton): def __init__(self): try: ''' if not infraType or infraType not in ['REST','SCHEDULE','DAEMON']: print('expectig valid infra type REST/SCHEDULE/DAMEON got {got}, exiting !!!'.format(got = infraType)) sys.exit(-1) ''' self.env = Environment() self.globals = Global() self.util = Utility() self.logging = Logging() self.configLoc = self.env.appCfgLoc #print(self.configLoc) self.bootStrapFile = os.path.join(self.configLog, self.globals.bootStrapFile) if not self.bootStrapFile: print('error: port8-infra10001, bootstrap error !!!') sys.exit(-1) self.dbConfigData = {} self.daemonConfifData = {} self.schedulerConfigData = {} self.restApiConfigData = {} self.agentConfigData = {} #print(self.Env._Env__initData) #print(self.Env._Env__EnvDetails) print("Loading bootstrap config....................".ljust(50),end='') self.__loadBootStrapConfig() self.__loadSchema() # ensuring all needed lib is available print("Checking all required libraries................".ljust(50),end='') self.__validateAllLib() ''' self.Logger = \ self.logging.buildLoggingInfra(\ self.loggingConfigData,\ self.globals.LoggerName[infraType]['LogFile'], self.globals.LoggerName[infraType]['Name'] ) ''' #self.Logger.debug('this is test from Infrastructure') # ensure db is up and repository is abvailable #print("Validating database............................".ljust(50),end='') #self.__validateDb(infraType) #self.db = self.__getNewConnection() # loading DB schema # populating json schema for database ''' commenting follwoing line, will revisit later to implement jsonref for db self.__loadDbJsonSchema() ''' #print("Loading logging config.........................".ljust(50),end='') #self.logger = Logging(self.loggingConfigData) #logger print("[infra started with OS pid {pid}]".format(pid=self.getMyOsPid())) #self.db_dyna_sql = self.jsonSchema.get('Main').get('db_dyna_sql_call') #self.myModuleLogger.info ('Infrastructure started with os pid [{pid}]'.format(pid=os.getpid())) except Exception as err: exc_type, exc_value, exc_traceback = sys.exc_info() myErrorMessage = repr(traceback.format_exception(exc_type, exc_value, exc_traceback)) print ('Error [{error}], terminating !!!'.format(error=myErrorMessage)) sys.exit(-1) def __loadBootStrapConfig(self): # read bootstrap cnfig file try: self.__bootStrapData = json.load(open(self.bootStrapFile)) if not self.__bootStrapData: print("Empty bootstrap data, terminating !!!") sys.exit(-1) # validating if all modules config data is available myMissingModule = self.util.findMissingKeyInDict(self.__bootStrapData['Main']['Modules'], list(self.__bootStrapData['Main']['Modules'].keys())) if myMissingModule: print('Missing config information for module {0}'.format(myMissingModule)) sys.exit(-1) #extracting module config information if self.env.environment not in self.__bootStrapData['Main']['Modules']['DB']['REPOSITORY']: print('[Error]\n unable to find repository information for environment >> {env}'.format(env = self.env.environment)) sys.exit(-1) if not self.env.environment in self.__bootStrapData['Main']['Modules']['DB']['REPOSITORY']: print('invalid environment >>> {env}'.format(env=self.env.Environment)) sys.exit(-1) self.repConfigData = self.__bootStrapData['Main']['Modules']['DB']['REPOSITORY'][self.env.environment] self.dbConfigData = { 'user' : self.repConfigData['User'], 'password' : self.repConfigData['enc'], 'host' : self.repConfigData['Host'], 'port' : self.repConfigData['Port'], 'database' : self.repConfigData['Name'] } self.schedulerConfigData = self.__bootStrapData['Main']['Modules']['Scheduler'] self.loggingConfigData = self.__bootStrapData['Main']['Modules']['Logging'] self.restApiConfigData = self.__bootStrapData['Main']['Modules']['RestAPI'] self.daemonConfigData = self.__bootStrapData['Main']['Modules']['Daemon'] self.jsonSchemaConfigData = self.__bootStrapData['Main']['JsonSchema'] if (not self.repConfigData): print('Repository DB Configuration is empty') sys.exit(-1) if (not self.schedulerConfigData): print('Scheduler Configuration is empty') sys.exit(-1) if (not self.loggingConfigData): print('Loggine Configuration is empty') sys.exit(-1) if (not self.restApiConfigData): print('RestApi Configuration is empty') sys.exit(-1) if (not self.restApiConfigData): print('Daemon Configuration is empty') sys.exit(-1) if (not self.jsonSchemaConfigData): print('Json Schema Configuration is empty') sys.exit(-1) print("[OK]") except Exception as err: myErrorMessage = sys.exc_info()[1:],traceback.format_exc(limit=2) #myErrorMessage = self.Utility.extractError() print(myErrorMessage) #print("Error [{err}] loading bootstrap config data".format(err=err)) sys.exit(-1) def __loadSchema(self): # load json schema data and resolve the reference if used, this will be needed to validate user data #print(self.configLoc) configFile = self.util.buildFileWPath(self.configLoc, self.jsonSchemaConfigData['ConfigFile']) if not self.util.isFileExist(configFile): print('Json Schema Configuration file {file} is missing !!!'.format(file=os.path.join(self.configLoc,self.jsonSchemaConfigData['ConfigFile']))) sys.exit(-1) #loading json schema file and resolving references if used in schema try: self.jsonSchema = JsonRef.replace_refs(json.load(open(configFile))) #print(self.jsonSchema) except Exception as err: myErrorMessage = sys.exc_info()[1:],traceback.format_exc(limit=2) print (myErrorMessage) sys.exit(-1) def __validateAllLib(self): # Ensuring all required library as specified in bootStrap.json is installed try: for lib in self.__bootStrapData['Main']['libraries']: if not self.util.isPackageInstalled(lib): #self.myModuleLogger.error('Lib {frmtLib} is not installed, Pls install it using <pip install {frmtLib}>'.format(frmtLib=lib)) print("[missing library {frmtLib}]".format(frmtLib=lib)) sys.exit(-1) print("[OK]") except Exception as err: exc_type, exc_value, exc_traceback = sys.exc_info() myErrorMessage = repr(traceback.format_exception(exc_type, exc_value, exc_traceback)) print('[Failed {err}]'.format(err=myErrorMessage)) sys.exit(-1) def isValidBootStrapData(self): if self.__bootStrapData: return True else: return False def isValidLogger(self): if self.myModuleLogger: return True else: return False def getBootStrapFile(self): #print(self.env._Environment__bootStrapFileKey) #print (self.env._Environment__initData) return self.env._Environment__initData[self.env._Environment__bootStrapFileKey] #mybootStrapFile = os.getEnv[self.Env._Environment__bootStrapKey] def getAppName(self): return os.Environ['APP_NAME'] def getMyOsPid(self): # returns current OS pid return os.getpid() def getInfraLogger(self, infraArg): if infraArg == 'Scheduler': return self.SchedLogger elif infraArg == 'Daemon': return self.DaemonLogger elif infraArg == 'Rest': return self.RestLogger def isModuleInstalled(self, argLib): # returns existence of a library passed as an argument try: __import__('imp').find_module(argLib) return True except ImportError: #print('Missing library {lib}'.format(lib=argLib)) return False #+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ # method for DB #+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ def __validateMySqlDB(self): self.dbLib = importlib.import_module('mysql.connector') # validate the db config file try: # connecting to database to ensure db is up db = self.dbLib.connect(host = self.dbConfigData['host'], port = self.dbConfigData['port'], user = self.dbConfigData['user'], password = self.util.decrypt(self.repConfigData['key'], self.dbConfigData['password']), database = self.dbConfigData['database']) # connection is successful, closing the connection db.close() # connection is successful except self.dbLib.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("something is wrong with your user name or password, exiting !!!") sys.exit(-1) elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist, exiting !!!") sys.exit(-1) else: print(err) ''' except Exception as err: exc_type, exc_value, exc_traceback = sys.exc_info() myErrorMessage = repr(traceback.format_exception(exc_type, exc_value, exc_traceback)) print('[Failed {err}]'.format(err=myErrorMessage)) sys.exit(-1) ''' def __validateDb(self,infraType): #print(self.repConfigData) if infraType == 'REST': if self.repConfigData['Vendor'] == "mysql" : self.__validateMySqlDB() else: print('unsuported database for repository') sys.exit(-1) else: pass print("[OK]") def __loadDbJsonSchema(self): #self.dbSchema = self.jsonSchema.get('Main').get('db_schema').get(self.__bootStrapData['Main']['Modules']['DB']['REPOSITORY']) self.dbSchema = self.jsonSchema.get('Main').get('db_schema').get(self.__bootStrapData['Main']['Modules']['DB']['REPOSITORY'][self.env.environment]) def __getNewConnection(self): try: #print('db config',self.dbConfigData) db = self.dbLib.connect(host = self.dbConfigData['host'], port = self.dbConfigData['port'], user = self.dbConfigData['user'], password = self.util.decrypt(self.repConfigData['key'], self.dbConfigData['password']), database = self.dbConfigData['database']) #dbCursor = db.cursor(dictionary=True, buffered=True) #dbCursor = db.cursor(buffered=True) return db except Exception as err: self.logger.critical('Error obtaining db connection using {}'.format(self.dbConfigData)) raise err def __closeConnection(self, conn): if conn: conn.close() def isConnectionOpen(self, conn): try: dbCur = conn.cursor() dbCur.execute('select now()') return True except Exception as err: return False