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
