def installMySQL(self, schemaLocation): """ install MySQL database """ import getpass from ProdCommon.Database.MysqlInstance import MysqlInstance # ask for password (optional) print userName = raw_input(""" Please provide the mysql user name (typically "root") for updating the database server (leave empty if not needed): ') """) if userName == '': userName = '******' print passwd = getpass.getpass( """Please provide mysql passwd associated to this user name for updating the database server: """) # define connection type from copy import deepcopy rootConfig = deepcopy(self.dbConfig) rootConfig.update({ 'dbName': 'mysql', 'user': userName, 'passwd': passwd }) dbInstance = MysqlInstance(rootConfig) session = SafeSession(dbInstance=dbInstance) # check if db exists create = True query = "show databases like '" + self.dbConfig['dbName'] + "'" try: session.execute(query) session.commit() results = session.fetchall() if results[0][0] == self.dbConfig['dbName']: print "DB ", self.dbConfig['dbName'], "already exists." print "Installing just BossLite tables.\n" create = False except IndexError: pass except Exception, msg: session.close() raise DbError(str(msg))
def installMySQL(self, schemaLocation): """ install MySQL database """ import getpass from ProdCommon.Database.MysqlInstance import MysqlInstance # ask for password (optional) print userName = raw_input( """ Please provide the mysql user name (typically "root") for updating the database server (leave empty if not needed): ') """ ) if userName == "": userName = "******" print passwd = getpass.getpass( """Please provide mysql passwd associated to this user name for updating the database server: """ ) # define connection type from copy import deepcopy rootConfig = deepcopy(self.dbConfig) rootConfig.update({"dbName": "mysql", "user": userName, "passwd": passwd}) dbInstance = MysqlInstance(rootConfig) session = SafeSession(dbInstance=dbInstance) # check if db exists create = True query = "show databases like '" + self.dbConfig["dbName"] + "'" try: session.execute(query) session.commit() results = session.fetchall() if results[0][0] == self.dbConfig["dbName"]: print "DB ", self.dbConfig["dbName"], "already exists." print "Installing just BossLite tables.\n" create = False except IndexError: pass except Exception, msg: session.close() raise DbError(str(msg))
def installSQlite( self, schemaLocation ) : """ install SQLite database """ # create a session and db access session = SafeSession(dbInstance = self.dbInstance) # execute check query query = "select tbl_name from sqlite_master where tbl_name='bl_task'" try: # if bl_task exists, no further operations are needed session.execute( query ) results = session.fetchall() if results[0][0] == self.dbConfig['dbName'] : print "DB ", self.dbConfig['dbName'], "already exists.\n" return session.close() return except IndexError : pass except StandardError: pass try: # if bl_task exists, no further operations are needed session.execute("select count(*) from bl_task") session.close() return except StandardError: pass # execute query queries = open(schemaLocation).read() try: for query in queries.split(';') : session.execute(query) except Exception, msg: raise DbError(str(msg))
def installSQlite(self, schemaLocation): """ install SQLite database """ # create a session and db access session = SafeSession(dbInstance=self.dbInstance) # execute check query query = "select tbl_name from sqlite_master where tbl_name='bl_task'" try: # if bl_task exists, no further operations are needed session.execute(query) results = session.fetchall() if results[0][0] == self.dbConfig['dbName']: print "DB ", self.dbConfig['dbName'], "already exists.\n" return session.close() return except IndexError: pass except StandardError: pass try: # if bl_task exists, no further operations are needed session.execute("select count(*) from bl_task") session.close() return except StandardError: pass # execute query queries = open(schemaLocation).read() try: for query in queries.split(';'): session.execute(query) except Exception, msg: raise DbError(str(msg))
class BossLiteDB(object): """ High level API class for DB queries. It allows load/operate/update DB using free format queries """ dbConfig = { 'dbName': 'BossLiteDB', 'user': '******', 'passwd': 'BossLitePass', 'socketFileLocation': '', 'host': '', 'portNr': '', 'refreshPeriod': 4 * 3600, 'maxConnectionAttempts': 5, 'dbWaitingTime': 10 } def __init__(self, database, dbConfig): """ initialize the API instance - database can be both MySQl or SQLite - dbConfig can be a dictionary with the format {'dbName':'BossLiteDB', 'host':'localhost', 'user':'******', 'passwd':'BossLitePass', 'socketFileLocation':'/var/run/mysql/mysql.sock', 'portNr':'', 'refreshPeriod' : 4*3600 , 'maxConnectionAttempts' : 5, 'dbWaitingTime' : 10 } """ # database self.database = database # "MySQL" or "SQLite" # MySQL: get DB configuration from config file if self.database == "MySQL": # update db config self.dbConfig['socketFileLocation'] = expandvars( self.dbConfig['socketFileLocation']) self.dbConfig.update(dbConfig) # create DB instance from ProdCommon.Database.MysqlInstance import MysqlInstance self.dbInstance = MysqlInstance(self.dbConfig) else: # update db config self.dbConfig = {'dbName': 'BossLiteDB'} dbConfig['dbName'] = expandvars(dbConfig['dbName']) self.dbConfig.update(dbConfig) # create DB instance from ProdCommon.Database.SqliteInstance import SqliteInstance self.dbInstance = SqliteInstance(self.dbConfig) # create a session and db access self.session = None ########################################################################## def connect(self): """ recreate a session and db access """ # create a session and db access if self.session is None: self.session = SafeSession(dbInstance=self.dbInstance) ########################################################################## def close(self): """ close session and db access """ self.session.close() self.session = None ########################################################################## def reset(self): """ reset session and db access """ self.close() self.connect() ########################################################################## def commit(self): """ commit """ self.session.commit() ########################################################################## def select(self, query): """ execute a query. """ # db connect self.connect() self.session.execute(query) # return query results return self.session.fetchall() ########################################################################## def selectOne(self, query): """ execute a query.with only one result expected """ # db connect self.connect() self.session.execute(query) # return query results return self.session.fetchone()[0] ########################################################################## def modify(self, query): """ execute a query which does not return such as insert/update/delete """ # db connect self.connect() # return query results rows = self.session.execute(query) if self.database == "MySQL": self.session.commit() return rows ########################################################################## def updateDB(self, obj): """ update any object table in the DB works for tasks, jobs, runningJobs """ # db connect self.connect() # update obj.update(self.session) if self.database == "MySQL": self.session.commit() ########################################################################## def installDB(self, schemaLocation): """ install database """ schemaLocation = expandvars(schemaLocation) if self.database == "MySQL": self.installMySQL(schemaLocation) elif self.database == "SQLite": self.installSQlite(schemaLocation) else: raise NotImplementedError ########################################################################## def installMySQL(self, schemaLocation): """ install MySQL database """ import getpass from ProdCommon.Database.MysqlInstance import MysqlInstance # ask for password (optional) print userName = raw_input(""" Please provide the mysql user name (typically "root") for updating the database server (leave empty if not needed): ') """) if userName == '': userName = '******' print passwd = getpass.getpass( """Please provide mysql passwd associated to this user name for updating the database server: """) # define connection type from copy import deepcopy rootConfig = deepcopy(self.dbConfig) rootConfig.update({ 'dbName': 'mysql', 'user': userName, 'passwd': passwd }) dbInstance = MysqlInstance(rootConfig) session = SafeSession(dbInstance=dbInstance) # check if db exists create = True query = "show databases like '" + self.dbConfig['dbName'] + "'" try: session.execute(query) session.commit() results = session.fetchall() if results[0][0] == self.dbConfig['dbName']: print "DB ", self.dbConfig['dbName'], "already exists." print "Installing just BossLite tables.\n" create = False except IndexError: pass except Exception, msg: session.close() raise DbError(str(msg)) # create db if create: query = 'create database ' + self.dbConfig['dbName'] try: session.execute(query) session.commit() except Exception, msg: session.close() raise DbError(str(msg))
class BossLiteDB(object): """ High level API class for DB queries. It allows load/operate/update DB using free format queries """ dbConfig = {'dbName':'BossLiteDB', 'user':'******', 'passwd':'BossLitePass', 'socketFileLocation':'', 'host':'', 'portNr':'', 'refreshPeriod' : 4*3600 , 'maxConnectionAttempts' : 5, 'dbWaitingTime' : 10 } def __init__(self, database, dbConfig): """ initialize the API instance - database can be both MySQl or SQLite - dbConfig can be a dictionary with the format {'dbName':'BossLiteDB', 'host':'localhost', 'user':'******', 'passwd':'BossLitePass', 'socketFileLocation':'/var/run/mysql/mysql.sock', 'portNr':'', 'refreshPeriod' : 4*3600 , 'maxConnectionAttempts' : 5, 'dbWaitingTime' : 10 } """ # database self.database = database # "MySQL" or "SQLite" # MySQL: get DB configuration from config file if self.database == "MySQL": # update db config self.dbConfig['socketFileLocation'] = expandvars( self.dbConfig['socketFileLocation'] ) self.dbConfig.update( dbConfig ) # create DB instance from ProdCommon.Database.MysqlInstance import MysqlInstance self.dbInstance = MysqlInstance(self.dbConfig) else : # update db config self.dbConfig = {'dbName':'BossLiteDB'} dbConfig['dbName'] = expandvars( dbConfig['dbName'] ) self.dbConfig.update( dbConfig ) # create DB instance from ProdCommon.Database.SqliteInstance import SqliteInstance self.dbInstance = SqliteInstance(self.dbConfig) # create a session and db access self.session = None ########################################################################## def connect ( self ) : """ recreate a session and db access """ # create a session and db access if self.session is None: self.session = SafeSession(dbInstance = self.dbInstance) ########################################################################## def close ( self ) : """ close session and db access """ self.session.close() self.session = None ########################################################################## def reset ( self ) : """ reset session and db access """ self.close() self.connect() ########################################################################## def commit ( self ) : """ commit """ self.session.commit() ########################################################################## def select(self, query): """ execute a query. """ # db connect self.connect() self.session.execute(query) # return query results return self.session.fetchall() ########################################################################## def selectOne(self, query): """ execute a query.with only one result expected """ # db connect self.connect() self.session.execute(query) # return query results return self.session.fetchone()[0] ########################################################################## def modify(self, query): """ execute a query which does not return such as insert/update/delete """ # db connect self.connect() # return query results rows = self.session.execute( query ) if self.database == "MySQL": self.session.commit() return rows ########################################################################## def updateDB( self, obj ) : """ update any object table in the DB works for tasks, jobs, runningJobs """ # db connect self.connect() # update obj.update(self.session) if self.database == "MySQL": self.session.commit() ########################################################################## def installDB( self, schemaLocation ) : """ install database """ schemaLocation = expandvars( schemaLocation ) if self.database == "MySQL": self.installMySQL( schemaLocation ) elif self.database == "SQLite": self.installSQlite( schemaLocation ) else: raise NotImplementedError ########################################################################## def installMySQL( self, schemaLocation ) : """ install MySQL database """ import getpass from ProdCommon.Database.MysqlInstance import MysqlInstance # ask for password (optional) print userName = raw_input( """ Please provide the mysql user name (typically "root") for updating the database server (leave empty if not needed): ') """ ) if userName == '' : userName = '******' print passwd = getpass.getpass( """Please provide mysql passwd associated to this user name for updating the database server: """ ) # define connection type from copy import deepcopy rootConfig = deepcopy( self.dbConfig ) rootConfig.update( { 'dbName' : 'mysql', 'user' : userName, 'passwd' : passwd } ) dbInstance = MysqlInstance( rootConfig ) session = SafeSession( dbInstance = dbInstance ) # check if db exists create = True query = "show databases like '" + self.dbConfig['dbName'] + "'" try: session.execute( query ) session.commit() results = session.fetchall() if results[0][0] == self.dbConfig['dbName'] : print "DB ", self.dbConfig['dbName'], "already exists." print "Installing just BossLite tables.\n" create = False except IndexError : pass except Exception, msg: session.close() raise DbError(str(msg)) # create db if create : query = 'create database ' + self.dbConfig['dbName'] try: session.execute( query ) session.commit() except Exception, msg: session.close() raise DbError(str(msg))