class Database(object):
"""This represents an oracle database object. Use this object to perform data operations against the database (i.e dml)
"""
def __init__(self):
"""Constructor takes no arguments, just instantiates the logger object
"""
self.Connection = None
self.logger = LogFactory().getLibLogger()
@staticmethod
def __buildConnectString(connectString):
connectDsn = None
sidStyleRegexMatch = '[a-zA-Z0-9-_.]+:\d+:[a-zA-Z\d._#$]+'
sidStyleExtractRegex = '([a-zA-Z0-9-_.]+):(\d+):([a-zA-Z0-9-_.]+)'
serviceStyleRegexMatch = '[a-zA-Z0-9-_.]+:\d+/[a-zA-Z\d._#$]+'
serviceStyleExtractRegex = '([a-zA-Z0-9-_.]+):(\d+)/([a-zA-Z0-9-_.]+)'
if re.match(sidStyleRegexMatch, connectString):
host = re.match(sidStyleExtractRegex, connectString).group(1)
port = re.match(sidStyleExtractRegex, connectString).group(2)
sid = re.match(sidStyleExtractRegex, connectString).group(3)
connectDsn = cx_Oracle.makedsn(host=host, port=port, sid=sid)
elif re.match(serviceStyleRegexMatch, connectString):
host = re.match(serviceStyleExtractRegex, connectString).group(1)
port = re.match(serviceStyleExtractRegex, connectString).group(2)
serviceName = re.match(serviceStyleExtractRegex,
connectString).group(3)
connectDsn = cx_Oracle.makedsn(host=host,
port=port,
service_name=serviceName)
else:
raise DatabaseConnectionException(
'The format of the connection string passed [] cannot be parsed'
)
return connectDsn
@staticmethod
def __generateRSMetadata(cursor):
cursorDesc = cursor.description
fieldNumber = 0
cursorMetadata = OrderedDict()
for field in cursorDesc:
metadataRecord = {}
metadataRecord['FieldNumber'] = fieldNumber
metadataRecord['DataType'] = field[1]
metadataRecord['MaxSize'] = len(str(field[0]))
metadataRecord['Scale'] = field[5]
cursorMetadata[field[0]] = metadataRecord
fieldNumber += 1
return cursorMetadata
def __mungeColumns(self, resultSet, colDelimiter='|::|', secureLog=False):
rsMeta = resultSet[0]
mungedResultSet = [rsMeta]
self.logger.debug('Munging column values of resultset')
for i in range(1, len(resultSet)):
mungedColData = ''
for k, v in rsMeta.iteritems():
colVal = str(resultSet[i][k])
mungedColData += colVal + colDelimiter
trimmedColData = mungedColData[:len(mungedColData) -
len(colDelimiter)]
mungedResultSet.append(trimmedColData)
if not secureLog:
self.logger.debug('Returning munged resultset: ' +
str(mungedResultSet))
return mungedResultSet
def sessionPool(self,
username,
password,
connectString,
min=5,
max=50,
increment=5):
"""
`BETA`
This will return a pool of connections.
`username:` The user name that will be used to make the connections to the database
`password:` The password of the user for connections
`connectString:` The connection string. Valid formats are <host>:<port>/<service_name> and <host>:<port>:<sid>
`min:` The number of connections to start and always keep in the pool
`max:` The maximum number of connections that can be created in the session pool
`increment:` How many connections should be created when all connections in the pool are busy.
`Returns:` SessionPool object that can be used to get connections
"""
connectDsn = self.__buildConnectString(connectString=connectString)
pool = cx_Oracle.SessionPool(user=username,
password=password,
database=connectDsn,
min=min,
max=max,
increment=increment)
return pool
def connect(self, username, password, connectString, asSysDBA=False):
"""
This method will create a connection to the database. Before you can call any other method, a connection must be established
`username` The username to use for the connection
`password` The password for the connection
`connectString` The connection string in the format of db-host:port/service_name or db-host:port:sid
`asSysDBA` Boolean (True|False) for whether connection should be made as sysdba. Default is False
"""
if self.Connection is None:
self.logger.debug('Connecting to database with connect string [' +
connectString + ']')
connectDsn = self.__buildConnectString(connectString=connectString)
try:
if asSysDBA:
self.logger.debug('Connecting as sysdba')
self.Connection = cx_Oracle.connect(user=username,
password=password,
dsn=connectDsn,
mode=cx_Oracle.SYSDBA)
else:
self.Connection = cx_Oracle.connect(user=username,
password=password,
dsn=connectDsn)
except cx_Oracle.DatabaseError as e:
raise DatabaseConnectionException(e)
else:
self.logger.debug(
'Connection already active, not creating an additional connection'
)
def disconnect(self):
"""
This method will close the connection now rather than when application terminates and/or Database object is garbage collected.
"""
if self.Connection is not None:
self.Connection.close()
self.Connection = None
def getValue(self, query, bindValues=[], namedBinds={}, secureLog=False):
"""
At times you want to simply get the raw value of a single row, single column query. When this is
the case, the use of the `pylegos.database.oracle.Database.getResultSet` may be overkill. This method is
designed to more efficiently get data for this use case.
`query: ` The query to run. It is expected that this query will only return a single row. This is not a
strict rule. The first column of the first record is the data that is returned.
`bindValues: ` List object containing the positional value for each bind variable
`namedBinds: ` Dictionary object containing the name/value pairs for the bind variables in the query
`secureLog: ` Sometimes there will be security sensitive data as part of either the query or result set. If so,
set this to True, so that neither the query or resultset are logged. Only high level names of
operations will be logged.
`Returns:` String
"""
bindInput = bindValues
if len(namedBinds) > 0:
bindInput = namedBinds
self.logger.debug('Running query to get single value result')
if not secureLog:
self.logger.debug('Executing query [' + query + ']')
cur = self.Connection.cursor()
cur.execute(query, bindInput)
retVal = cur.fetchone()[0]
if not secureLog:
self.logger.debug('Single value return is [' + retVal + ']')
return str(retVal)
def getResultSet(self,
query,
bindValues=[],
namedBinds={},
mungeColumns=False,
fieldDelimiter=',',
secureLog=False,
returnRaw=False):
"""
Primary method for retrieval of data from the database. By default, this will return an instance of
`pylegos.database.oracle.ResultSet`. This object holds all the data with methods for different types of
retrieval.
Use of bind variables is encouraged and can be passed in via one of two methods. 1) Positional values by using
the bindValues parameter and 2) Named variable/value pairs via the namedBinds parameter. These two
parameters are mutually exclusive.
`query: ` The query to run
`bindValues: ` List object containing the positional value for each bind variable
`namedBinds: ` Dictionary object containing the name/value pairs for the bind variables in the query
`mungeColumns: ` Optional boolean parameter that will "munge" columns using the value of the fieldDelimiter
parameter. Example usage would be for creating a CSV file.
`fieldDelimiter: ` The delimiter to use when munging columns together into a single value
`secureLog: ` Sometimes there will be security sensitive data as part of either the query or result set. If so,
set this to True, so that neither the query or resultset are logged. Only high level names of
operations will be logged.
`returnRaw: ` Boolean to indicate that raw results should be returned rather than an instance of
`pylegos.database.oracle.ResultSet`. Here for mostly for backward compatability, but not yet
deprecated.
`Returns:` `pylegos.database.oracle.ResultSet`
"""
rawResultSet = OrderedDict()
formattedRowNumber = 1
try:
cursor = self.Connection.cursor()
self.logger.debug('Running query: ' + query)
if not secureLog:
self.logger.debug('Bind values for above query are ' +
str(bindValues))
self.logger.debug('Executing query')
if not secureLog:
self.logger.debug(
'Session effective query user (current_schema) is [' +
str(self.Connection.current_schema) + ']')
cursor.execute(query, bindValues)
self.logger.debug('Generating Resultset Metadata')
curMeta = self.__generateRSMetadata(cursor=cursor)
self.logger.debug('Fetching all records')
recordSet = cursor.fetchall()
self.logger.debug('Formatting resultset')
for row in recordSet:
formattedRec = {}
for field in curMeta:
rowVal = row[curMeta[field]['FieldNumber']]
formattedRec[field] = rowVal
'''
Determine if maxVal needs increase
'''
dataType = str(curMeta[field]['DataType'])
if dataType == "<class 'cx_Oracle.CLOB'>":
curMeta[field]['MaxSize'] = 100
else:
rowValLen = len(rowVal)
if rowValLen > curMeta[field]['MaxSize']:
curMeta[field]['MaxSize'] = rowValLen
rawResultSet[formattedRowNumber] = formattedRec
formattedRowNumber += 1
rawResultSet[0] = curMeta
if mungeColumns:
rawResultSet = self.__mungeColumns(resultSet=rawResultSet,
colDelimiter=fieldDelimiter,
secureLog=secureLog)
self.logger.debug('Returning resultset with [' +
str(len(rawResultSet) - 1) + '] records')
resultSet = ResultSet(recordSet=rawResultSet)
if returnRaw:
return rawResultSet
else:
return resultSet
except cx_Oracle.DatabaseError as e:
self.logger.debug('Hit cx_oracle DatabaseError: ' + str(e))
raise DatabaseQueryException(e)
def execute(self, statement, bindValues=[], namedBindValues={}):
"""
This will execute any sql statement against the database under the current database session.
The statement should make use of bind variables. The bind variables can be passed in via one of two
methods. 1) Positional via a list object and 2) Named via a dictionary object. These two parameters
are mutually exclusive.
`statement:` The sql to be executed against the database.
`bindValues:` This is the positional list of bind values (if any) to use when parsing the statement.
`namedBindValues:` Dictionary object holding bind variable names,values to be used in the statement
"""
try:
bindInput = bindValues
if len(namedBindValues) > 0:
bindInput = namedBindValues
cursor = self.Connection.cursor()
cursor.execute(statement, bindInput)
except cx_Oracle.DatabaseError as e:
self.logger.debug('Hit cx_oracle DatabaseError: ' + str(e))
raise DatabaseDMLException(e)
def execProc(self,
procedureName,
parameters=[],
namedParameters={},
outParam=None):
"""
This will execute a plsql stored procedure. The procedure can either be a standalone procedure
or inside a package. You can pass parameters as either positional (via the parameters argument) or
as named parameters (via the namedParameters argument). These two arguments are mutually exclusive.
If you simply want to run an anonymous plsql code block, you can use the `pylegos.database.oracle.Database.execute`
method.
`functionName:` The name of the procedure
`parameters:` This is a python list object that contains the values of the positional parameters to pass to the procedure.
`namedParameters:` Dictionary object that contains the named parameters and their corresponding values to pass to
the procedure
`Returns:` None
"""
try:
cursor = self.Connection.cursor()
cursor.callproc(name=procedureName,
parameters=parameters,
keywordParameters=namedParameters)
if outParam is not None:
return outParam
except cx_Oracle.DatabaseError as e:
self.logger.debug('Hit cx_oracle DatabaseError: ' + str(e))
raise DatabaseDMLException(e)
def execFunc(self,
functionName,
oracleReturnType,
parameters=[],
namedParameters={}):
"""
This will execute a plsql stored function. The function can either be a standalone function
or inside a package. You can pass parameters as either positional (via the parameters argument) or
as named parameters (via the namedParameters argument). These two arguments are mutually exclusive.
If you simply want to run an anonymous plsql code block, you can use the `pylegos.database.oracle.Database.execute`
method.
`functionName:` The name of the function
`oracleReturnType:` Use the `pylegos.database.oracle.DataType` class for the value of this parameter. Function
must return one of these defined types. Otherwise it is not supported.
`parameters:` This is a python list object that contains the values of the positional parameters to pass to the function.
`namedParameters:` Dictionary object that contains the named parameters and their corresponding values to pass to
the function
`Returns:` Result as passed in type
"""
try:
cursor = self.Connection.cursor()
retValue = cursor.callfunc(name=functionName,
returnType=oracleReturnType,
parameters=parameters,
keywordParameters=namedParameters)
return retValue
except cx_Oracle.DatabaseError as e:
self.logger.debug('Hit cx_oracle DatabaseError: ' + str(e))
raise DatabaseDMLException(e)
def commit(self):
"""
Will commit the current transaction
Returns: None
"""
try:
self.Connection.commit()
except cx_Oracle.DatabaseError as e:
self.logger.debug('Hit cx_oracle DatabaseError: ' + str(e))
raise DatabaseDMLException(e)
def rollback(self):
"""Will rollback the current transaction"""
self.Connection.rollback()
def getDefaultTablespace(self, contentType='Permanent'):
"""
This will return the default tablespace for the database, based on content type.
`contentType` This is the type of contents that the tablespace holds. It is best to pass
the value using the `pylegos.database.oracle.TablespaceContentType` class.
Returns: String
`Example Usage:`
...
db = Database()
tbsName = db.getDefaultTablespace(contentType=TablespaceContentType.Permanent)
...
"""
if contentType.upper() is 'PERMANENT':
query = ("select property_value "
"from database_properties "
"where property_name = 'DEFAULT_PERMANENT_TABLESPACE'")
elif contentType.upper() is 'TEMPORARY':
query = ("select property_value "
"from database_properties "
"where property_name = 'DEFAULT_TEMP_TABLESPACE'")
else:
'''
todo: raise exception/error
'''
return None
tbsName = self.getValue(query=query, columnName='PROPERTY_VALUE')
return tbsName
def getProperty(self, propertyName=DatabaseProperty.NAME):
""" A convenience function for getting values from v$database view. Each field in the view is
a class variable of the `pylegos.database.oracle.DatabaseProperty` class. The value of any field can
be easily retrieved via this method call.
`propertyName` This is the name of the property (field of v$database) that you want to get the value. It
is best to use the `pylegos.database.oracle.DatabaseProperty` class to pass the value.
`Returns` String
`Example Usage:`
...
db = Database()
db.connect(....)
dbName = db.getProperty(propertyName=DatabaseProperty.NAME)
...
"""
query = "select " + propertyName + " from v$database"
res = self.getValue(query=query)
return res
def getContainerType(self):
pass
# DEPRECATED METHODS
def getQueryResult(self, query, bindValues=[], secureLog=False):
"""
`DEPRECATED`
Please use the `pylegos.database.oracle.Database.getResultSet` method instead
"""
DeprecationUtil().deprecate(className='Database',
methodName='getQueryResult',
newMethodName='getResultSet')
return self.getResultSet(query, bindValues, secureLog, returnRaw=True)
def queryForSingleValueDep(self,
query,
columnName,
bindValues=[],
secureLog=False):
"""
`DEPRECATED`
Please use the `pylegos.database.oracle.Database.getValue` method instead
"""
DeprecationUtil().deprecate(className='Database',
methodName='queryForSingleValue',
newMethodName='getValue')
return self.getValue(query, columnName, bindValues, secureLog)
def getColumnMungedResultset(self,
query,
bindValues=[],
colDelimiter='|::|',
secureLog=False):
"""
`DEPRECATED`
Please use the `pylegos.database.oracle.Database.getResultSet` method instead
"""
DeprecationUtil().deprecate(className='Database',
methodName='getColumnMungedResultset',
newMethodName='getResultSet')
return self.getResultSet(query,
bindValues,
mungeColumns=True,
fieldDelimiter=colDelimiter,
secureLog=secureLog,
returnRaw=True)
def getColumnDelimitedResultset(self,
query,
bindValues=[],
fieldDelimiter=',',
secureLog=False):
"""
`DEPRECATED`
Please use the `pylegos.database.oracle.Database.getResultSet` method instead
"""
DeprecationUtil().deprecate(className='Database',
methodName='getColumnDelimitedResultset',
newMethodName='getResultSet')
return self.getResultSet(query,
bindValues,
mungeColumns=True,
fieldDelimiter=fieldDelimiter,
secureLog=secureLog)
def execDML(self, dml, bindValues=[]):
"""
`DEPRECATED`
Please use the `pylegos.database.oracle.Database.execute` method instead
"""
DeprecationUtil().deprecate(className='Database',
methodName='execDML',
newMethodName='execute')
self.execute(dml, bindValues)
def __init__(self):
"""Constructor takes no arguments, just instantiates the logger object
"""
self.Connection = None
self.logger = LogFactory().getLibLogger()
def __init__(self):
self.logger = LogFactory().getLibLogger()
self.queue = Queue()
class Thread(object):
def __init__(self):
self.logger = LogFactory().getLibLogger()
self.queue = Queue()
def __runThread(self, func, args):
threadName = self.queue.get()
self.logger.debug('Thread ['+str(threadName)+'] calling func ['+str(func)+']')
func(**args)
self.logger.debug('Finished running function setting queue task to done')
self.queue.task_done()
def runAndWait(self, waitMessage, threadName, runFunc, funcNamedParams={}, spinnerType=SpinnerType.Classic):
"""
* NOTE: Still if cli, so use at own risk ;)
This procedure will run the named function in a seperate thread so that async operation is possible
:param threadName: <br>
:param runFunc: <br>
:param funcArgs: <br>
:return: None <br>
"""
'''ToDo, check if thread name already exists, if so throw exception'''
self.queue.put(item=threadName)
t = threading.Thread(target=self.__runThread, args=(runFunc, funcNamedParams,))
try:
t.start()
spinner = Spinner(message=waitMessage, spinnerType=spinnerType)
while self.queue.unfinished_tasks > 0:
spinner.spin()
time.sleep(.25)
spinner.stop()
except Exception as e:
self.logger.debug('Hit error: '+str(e))
spinner.stop()
raise
def runAndShowProgress(self, jobMap, initMesg='Initializing'):
"""
This will run a "jobMap" in a blocking fashion and show a progress bar, updating after
each step in the job is complete. The jobMap argument must be a dictionary that has the following
format:<br><br>
jobMap = {'Step 1': {'func': stepOneFunc,
'args': {'parameter1': parameterValue,
'parameter2': parameterValue
}
},
'Step 2': {'func': stepTwoFunc,
'args': {'parameter1': parameterValue}
},
'Step 3': {'func': stepThreeFunc,
'args': {'parameter1': parameterValue,
'parameter2': parameterValue
}
}
<br><br>
:param jobMap: The job map that follows above format that holds the functions to run
:param initMesg: The initial message to show on the progress bar. Default 'Initializing'
:return: None
"""
progressBar = ProgressBar(initialMessage=initMesg, numOperations=len(jobMap))
time.sleep(1)
msgHeader='Running -> '
for step, stepAttribs in jobMap.iteritems():
self.queue.put(item=step)
stepMessage = msgHeader + step
progressBar.updateMessage(message=stepMessage)
fx=stepAttribs['func']
fxa=stepAttribs['args']
t = threading.Thread(target=self.__runThread, args=(fx, fxa,))
t.start()
spinner = Spinner(message=' : ', spinnerType=SpinnerType.Clock)
while self.queue.unfinished_tasks > 0:
spinner.spin()
time.sleep(.15)
progressBar.updateProgress()
progressBar.updateMessage('Finished')
progressBar.finish()
def runAndContinue(self, runFunc, args):
pass
class Database(object):
"""This represents an oracle database object. Use this object to perform data operations against the database (i.e dml)
"""
def __init__(self):
"""Constructor takes no arguments, just instantiates the logger object
"""
self.Connection = None
self.logger = LogFactory().getLibLogger()
class DataType(object):
NUMBER = cx_Oracle.NUMBER
class DatabaseProperty(object):
DBID = 'DBID'
NAME = 'NAME'
CREATED = 'CREATED'
RESETLOGS_CHANGE = 'RESETLOGS_CHANGE#'
RESETLOGS_TIME = 'RESETLOGS_TIME'
PRIOR_RESETLOGS_CHANGE = 'PRIOR_RESETLOGS_CHANGE#'
PRIOR_RESETLOGS_TIME = 'PRIOR_RESETLOGS_TIME'
LOG_MODE = 'LOG_MODE'
CHECKPOINT_CHANGE = 'CHECKPOINT_CHANGE#'
ARCHIVE_CHANGE = 'ARCHIVE_CHANGE#'
CONTROLFILE_TYPE = 'CONTROLFILE_TYPE'
CONTROLFILE_CREATED = 'CONTROLFILE_CREATED'
CONTROLFILE_SEQUENCE = 'CONTROLFILE_SEQUENCE#'
CONTROLFILE_CHANGE = 'CONTROLFILE_CHANGE#'
CONTROLFILE_TIME = 'CONTROLFILE_TIME'
OPEN_RESETLOGS = 'OPEN_RESETLOGS'
VERSION_TIME = 'VERSION_TIME'
OPEN_MODE = 'OPEN_MODE'
PROTECTION_MODE = 'PROTECTION_MODE'
PROTECTION_LEVEL = 'PROTECTION_LEVEL'
REMOTE_ARCHIVE = 'REMOTE_ARCHIVE'
ACTIVATION = 'ACTIVATION#'
SWITCHOVER = 'SWITCHOVER#'
DATABASE_ROLE = 'DATABASE_ROLE'
ARCHIVELOG_CHANGE = 'ARCHIVELOG_CHANGE#'
ARCHIVELOG_COMPRESSION = 'ARCHIVELOG_COMPRESSION'
SWITCHOVER_STATUS = 'SWITCHOVER_STATUS'
DATAGUARD_BROKER = 'DATAGUARD_BROKER'
GUARD_STATUS = 'GUARD_STATUS'
SUPPLEMENTAL_LOG_DATA_MIN = 'SUPPLEMENTAL_LOG_DATA_MIN'
SUPPLEMENTAL_LOG_DATA_PK = 'SUPPLEMENTAL_LOG_DATA_PK'
SUPPLEMENTAL_LOG_DATA_UI = 'SUPPLEMENTAL_LOG_DATA_UI'
FORCE_LOGGING = 'FORCE_LOGGING'
PLATFORM_ID = 'PLATFORM_ID'
PLATFORM_NAME = 'PLATFORM_NAME'
RECOVERY_TARGET_INCARNATION = 'RECOVERY_TARGET_INCARNATION#'
LAST_OPEN_INCARNATION = 'LAST_OPEN_INCARNATION#'
CURRENT_SCN = 'CURRENT_SCN'
FLASHBACK_ON = 'FLASHBACK_ON'
SUPPLEMENTAL_LOG_DATA_FK = 'SUPPLEMENTAL_LOG_DATA_FK'
SUPPLEMENTAL_LOG_DATA_ALL = 'SUPPLEMENTAL_LOG_DATA_ALL'
DB_UNIQUE_NAME = 'DB_UNIQUE_NAME'
STANDBY_BECAME_PRIMARY_SCN = 'STANDBY_BECAME_PRIMARY_SCN'
FS_FAILOVER_STATUS = 'FS_FAILOVER_STATUS'
FS_FAILOVER_CURRENT_TARGET = 'FS_FAILOVER_CURRENT_TARGET'
FS_FAILOVER_THRESHOLD = 'FS_FAILOVER_THRESHOLD'
FS_FAILOVER_OBSERVER_PRESENT = 'FS_FAILOVER_OBSERVER_PRESENT'
FS_FAILOVER_OBSERVER_HOST = 'FS_FAILOVER_OBSERVER_HOST'
CONTROLFILE_CONVERTED = 'CONTROLFILE_CONVERTED'
PRIMARY_DB_UNIQUE_NAME = 'PRIMARY_DB_UNIQUE_NAME'
SUPPLEMENTAL_LOG_DATA_PL = 'SUPPLEMENTAL_LOG_DATA_PL'
MIN_REQUIRED_CAPTURE_CHANGE = 'MIN_REQUIRED_CAPTURE_CHANGE#'
CDB = 'CDB'
CON_ID = 'CON_ID'
PENDING_ROLE_CHANGE_TASKS = 'PENDING_ROLE_CHANGE_TASKS'
CON_DBID = 'CON_DBID'
FORCE_FULL_DB_CACHING = 'FORCE_FULL_DB_CACHING'
class TablespaceContentType(object):
Permanent = 1
Temporary = 2
def __buildConnectString(self, connectString):
connectDsn = None
sidStyleRegexMatch = '[a-zA-Z0-9-_.]+:\d+:[a-zA-Z\d._#$]+'
sidStyleExtractRegex = '([a-zA-Z0-9-_.]+):(\d+):([a-zA-Z0-9-_.]+)'
serviceStyleRegexMatch = '[a-zA-Z0-9-_.]+:\d+/[a-zA-Z\d._#$]+'
serviceStyleExtractRegex = '([a-zA-Z0-9-_.]+):(\d+)/([a-zA-Z0-9-_.]+)'
if re.match(sidStyleRegexMatch, connectString):
host = re.match(sidStyleExtractRegex, connectString).group(1)
port = re.match(sidStyleExtractRegex, connectString).group(2)
sid = re.match(sidStyleExtractRegex, connectString).group(3)
connectDsn = cx_Oracle.makedsn(host=host, port=port, sid=sid)
elif re.match(serviceStyleRegexMatch, connectString):
host = re.match(serviceStyleExtractRegex, connectString).group(1)
port = re.match(serviceStyleExtractRegex, connectString).group(2)
serviceName = re.match(serviceStyleExtractRegex,
connectString).group(3)
connectDsn = cx_Oracle.makedsn(host=host,
port=port,
service_name=serviceName)
else:
raise DatabaseConnectionException(
'The format of the connection string passed [] cannot be parsed'
)
return connectDsn
def sessionPool(self,
username,
password,
connectString,
min=5,
max=50,
increment=5):
connectDsn = self.__buildConnectString(connectString=connectString)
pool = cx_Oracle.SessionPool(user=username,
password=password,
database=connectDsn,
min=min,
max=max,
increment=increment)
return pool
def connect(self, username, password, connectString, asSysdba=False):
"""
This method will create a connection to the database. Before you can call any other method, a connection must be established
<br>
:param username: The username to use for the connection <br>
:param password: The password for the connection <br>
:param connectString: The connection string in the format of db-host:port/service_name or db-host:port:sid <br>
:param asSysdba: Boolean (True|False) for whether connection should be made as sysdba. Default is False <br>
:return: None <br>
"""
if self.Connection is None:
self.logger.debug('Connecting to database with connect string [' +
connectString + ']')
connectDsn = self.__buildConnectString(connectString=connectString)
try:
if asSysdba:
self.logger.debug('Connecting as sysdba')
self.Connection = cx_Oracle.connect(user=username,
password=password,
dsn=connectDsn,
mode=cx_Oracle.SYSDBA)
else:
self.Connection = cx_Oracle.connect(user=username,
password=password,
dsn=connectDsn)
except cx_Oracle.DatabaseError as e:
raise DatabaseConnectionException(e)
else:
self.logger.debug(
'Connection already active, not creating an additional connection'
)
def disconnect(self):
"""
This method will close the connection now rather than when application terminates and/or Database object is garbage collected.
:return: None
"""
if self.Connection is not None:
self.Connection.close()
self.Connection = None
def generateRSMetadata(self, cursor):
cursorDesc = cursor.description
fieldNumber = 0
cursorMetadata = OrderedDict()
for field in cursorDesc:
metadataRecord = {}
metadataRecord['FieldNumber'] = fieldNumber
metadataRecord['DataType'] = field[1]
metadataRecord['MaxSize'] = len(str(field[0]))
metadataRecord['Scale'] = field[5]
cursorMetadata[field[0]] = metadataRecord
fieldNumber += 1
return cursorMetadata
def getResultSetObject(self, query, bindValues=[]):
formatedResults = self.getQueryResult(query=query,
bindValues=bindValues)
resultSetObj = ResultSet(formatedResults)
return resultSetObj
def queryForSingleValue(self,
query,
columnName,
bindValues=[],
secureLog=False):
retVal = None
res = self.getQueryResult(query=query, bindValues=bindValues)
if len(res) > 1:
retVal = res[1][str(columnName).upper()]
return retVal
def getQueryResult(self, query, bindValues=[], secureLog=False):
formattedResultSet = OrderedDict()
formattedRowNumber = 1
try:
cursor = self.Connection.cursor()
self.logger.debug('Running query: ' + query)
if not secureLog:
self.logger.debug('Bind values for above query are ' +
str(bindValues))
self.logger.debug('Executing query')
if not secureLog:
self.logger.debug(
'Session effective query user (current_schema) is [' +
self.Connection.current_schema + ']')
cursor.execute(query, bindValues)
self.logger.debug('Generating Resultset Metadata')
curMeta = self.generateRSMetadata(cursor=cursor)
self.logger.debug('Fetching all records')
rawResultSet = cursor.fetchall()
self.logger.debug('Formatting resultset')
for row in rawResultSet:
formattedRec = {}
for field in curMeta:
rowVal = row[curMeta[field]['FieldNumber']]
formattedRec[field] = rowVal
'''
Determine if maxVal needs increase
'''
if (len(str(rowVal)) > curMeta[field]['MaxSize']) or (
len(str(rowVal)) == curMeta[field]['MaxSize']
and curMeta[field]['Scale'] > 0):
if curMeta[field]['Scale'] > 0:
curMeta[field]['MaxSize'] = len(str(rowVal)) + 1
elif curMeta[field]['DataType'] == str(
CxOracleType.Timestamp):
curMeta[field]['MaxSize'] = 26
else:
curMeta[field]['MaxSize'] = len(str(rowVal))
formattedResultSet[formattedRowNumber] = formattedRec
formattedRowNumber += 1
formattedResultSet[0] = curMeta
self.logger.debug('Returning resultset with [' +
str(len(formattedResultSet) - 1) + '] records')
return formattedResultSet
except cx_Oracle.DatabaseError as e:
self.logger.debug('Hit cx_oracle DatabaseError: ' + str(e))
raise DatabaseQueryException(e)
def getColumnMungedResultset(self,
query,
bindValues=[],
colDelimiter='|::|',
secureLog=False):
queryResult = self.getQueryResult(query=query,
bindValues=bindValues,
secureLog=secureLog)
rsMeta = queryResult[0]
resultSet = []
self.logger.debug('Munging column values of resultset')
for i in range(1, len(queryResult)):
mungedColData = ''
for k, v in rsMeta.iteritems():
colVal = str(queryResult[i][k])
mungedColData += colVal + colDelimiter
trimmedColData = mungedColData[:len(mungedColData) -
len(colDelimiter)]
resultSet.append(trimmedColData)
if not secureLog:
self.logger.debug('Returning munged resultset: ' + str(resultSet))
return resultSet
def getColumnDelimitedResultset(self,
query,
bindValues=[],
fieldDelimiter=',',
secureLog=False):
queryResult = self.getQueryResult(query=query,
bindValues=bindValues,
secureLog=secureLog)
rsMeta = queryResult[0]
resultSet = []
self.logger.debug('Generating record set with field delimiter [' +
fieldDelimiter + ']')
for i in range(1, len(queryResult)):
recordData = ''
for k, v in rsMeta.iteritems():
colVal = str(queryResult[i][k])
recordData += colVal + fieldDelimiter
# NEED TO REMOVE THE TRAILING DELIMETER
recordData = recordData[:len(recordData) - 1]
resultSet.append(recordData)
if not secureLog:
self.logger.debug('Returning resultset: ' + str(resultSet))
return resultSet
def execDML(self, dml, bindValues=[]):
"""
This function is to be used to call any dml operation (insert,update,delete). It can also
be used to run an anonymous pl/sql block. If you want to execute a stored pl/sql procedure
or function, use the executePL subtroutine
"""
try:
cursor = self.Connection.cursor()
cursor.execute(dml, bindValues)
except cx_Oracle.DatabaseError as e:
self.logger.debug('Hit cx_oracle DatabaseError: ' + str(e))
raise DatabaseDMLException(e)
def execProc(self,
procedureName,
parameters=[],
namedParameters={},
outParam=None):
try:
cursor = self.Connection.cursor()
cursor.callproc(name=procedureName,
parameters=parameters,
keywordParameters=namedParameters)
if outParam is not None:
return outParam
except cx_Oracle.DatabaseError as e:
self.logger.debug('Hit cx_oracle DatabaseError: ' + str(e))
raise DatabaseDMLException(e)
def execFunc(self,
functionName,
oracleReturnType,
parameters=[],
namedParameters={}):
try:
cursor = self.Connection.cursor()
retValue = cursor.callfunc(name=functionName,
returnType=oracleReturnType,
parameters=parameters,
keywordParameters=namedParameters)
return retValue
except cx_Oracle.DatabaseError as e:
self.logger.debug('Hit cx_oracle DatabaseError: ' + str(e))
raise DatabaseDMLException(e)
def commit(self):
try:
self.Connection.commit()
except cx_Oracle.DatabaseError as e:
self.logger.debug('Hit cx_oracle DatabaseError: ' + str(e))
raise DatabaseDMLException(e)
def rollback(self):
self.Connection.rollback()
def getDefaultTablespace(self, type=TablespaceContentType.Permanent):
if type is TablespaceContentType.Permanent:
query = ("select property_value "
"from database_properties "
"where property_name = 'DEFAULT_PERMANENT_TABLESPACE'")
elif type is TablespaceContentType.Temporary:
query = ("select property_value "
"from database_properties "
"where property_name = 'DEFAULT_TEMP_TABLESPACE'")
else:
'''
todo: raise exception/error
'''
return None
res = self.queryForSingleValue(query=query,
columnName='PROPERTY_VALUE')
return res
def getProperty(self, property=DatabaseProperty.NAME):
query = "select " + property + " from v$database"
res = self.queryForSingleValue(query=query, columnName=property)
return res
def __init__(self):
self.logger = LogFactory().getLibLogger()
class TermUI(object):
"""
Class to aide in UI operations at the console/terminal
"""
def __init__(self):
self.logger = LogFactory().getLibLogger()
def getUserInput(self,
promptMessage,
caseSenstiveMatching=False,
validChoices=[],
defaultChoice=None,
listChoices=False,
secureMode=False):
"""
This will capture user input at the terminal.
:param promptMessage: The message to display to the user <BR>
:param caseSenstiveMatching: Optional. Boolean to indicate if the user input must match in case sensitive manner to validChoices. Default: False<br>
:param validChoices: Optional. A list of valid choices that will be validated against user input. Default: EmptyList []<br>
:param defaultChoice: Optional. If there is a default value, supply this and if the user just hits enter, this <br>
will be the value returned. Default: None<br>
:param listChoices: Optional. Boolean value to indicate you would like each choice printed on one line if the user inputs a value <br>
that is not in the list of validChoices. Helpful if you have many options. Default: False<br>
:param secureMode: Optional. This will cause the input to not be echoed to the terminal. Use this if gathering password or other<br>
sensitive data. Default: False<br>
:return: Users Input as String value<br>
"""
validInput = False
if len(validChoices) > 0:
while not validInput:
if len(validChoices) <= 3 and defaultChoice is None:
displayMessage = promptMessage + ' ' + str(
validChoices) + ': '
elif defaultChoice is not None:
displayMessage = promptMessage + ' [' + defaultChoice + ']: '
userInput = raw_input(displayMessage)
if len(userInput) == 0 and defaultChoice is not None:
userInput = defaultChoice
if caseSenstiveMatching:
if userInput in validChoices:
validInput = True
else:
if userInput.upper() in validChoices or userInput.lower(
) in validChoices:
validInput = True
if not validInput:
if listChoices:
self.logger.info(
'You must supply one of the following choices:')
for c in validChoices:
self.logger.info(str(c))
else:
self.logger.info(
'You must supply one of the following choices: ' +
str(validChoices))
else:
if secureMode:
import getpass
userInput = getpass.getpass(promptMessage)
else:
userInput = raw_input(promptMessage)
return str(userInput)