MySQL

DIRAC Basic MySQL Class It provides access to the basic MySQL methods in a multithread-safe mode keeping used connections in a python Queue for further reuse.

These are the coded methods:

__init__( host, user, passwd, name, [maxConnsInQueue=10] )

Initializes the Queue and tries to connect to the DB server, using the _connect method. “maxConnsInQueue” defines the size of the Queue of open connections that are kept for reuse. It also defined the maximum number of open connections available from the object. maxConnsInQueue = 0 means unlimited and it is not supported.

_except( methodName, exception, errorMessage )

Helper method for exceptions: the “methodName” and the “errorMessage” are printed with ERROR level, then the “exception” is printed (with full description if it is a MySQL Exception) and S_ERROR is returned with the errorMessage and the exception.

_connect()

Attempts connection to DB and sets the _connected flag to True upon success. Returns S_OK or S_ERROR.

_query( cmd, [conn] )

Executes SQL command “cmd”. Gets a connection from the Queue (or open a new one if none is available), the used connection is back into the Queue. If a connection to the the DB is passed as second argument this connection is used and is not in the Queue. Returns S_OK with fetchall() out in Value or S_ERROR upon failure.

_update( cmd, [conn] )

Executes SQL command “cmd” and issue a commit Gets a connection from the Queue (or open a new one if none is available), the used connection is back into the Queue. If a connection to the the DB is passed as second argument this connection is used and is not in the Queue Returns S_OK with number of updated registers in Value or S_ERROR upon failure.

_createTables( tableDict )

Create a new Table in the DB

_getConnection()

Gets a connection from the Queue (or open a new one if none is available) Returns S_OK with connection in Value or S_ERROR the calling method is responsible for closing this connection once it is no longer needed.

Some high level methods have been added to avoid the need to write SQL statement in most common cases. They should be used instead of low level _insert, _update methods when ever possible.

buildCondition( self, condDict = None, older = None, newer = None,: timeStamp = None, orderAttribute = None, limit = False, greater = None, smaller = None ):

Build SQL condition statement from provided condDict and other extra check on a specified time stamp. The conditions dictionary specifies for each attribute one or a List of possible values greater and smaller are dictionaries in which the keys are the names of the fields, that are requested to be >= or < than the corresponding value. For compatibility with current usage it uses Exceptions to exit in case of invalid arguments

insertFields( self, tableName, inFields = None, inValues = None, conn = None, inDict = None ):

Insert a new row in “tableName” assigning the values “inValues” to the fields “inFields”. Alternatively inDict can be used String type values will be appropriately escaped.

updateFields( self, tableName, updateFields = None, updateValues = None,

condDict = None, limit = False, conn = None, updateDict = None, older = None, newer = None, timeStamp = None, orderAttribute = None ):

Update “updateFields” from “tableName” with “updateValues”. updateDict alternative way to provide the updateFields and updateValues N records can match the condition return S_OK( number of updated rows ) if limit is not False, the given limit is set String type values will be appropriately escaped.

deleteEntries( self, tableName,

condDict = None, limit = False, conn = None, older = None, newer = None, timeStamp = None, orderAttribute = None ):

Delete rows from “tableName” with N records can match the condition if limit is not False, the given limit is set String type values will be appropriately escaped, they can be single values or lists of values.

getFields( self, tableName, outFields = None,

condDict = None, limit = False, conn = None, older = None, newer = None, timeStamp = None, orderAttribute = None ):

Select “outFields” from “tableName” with condDict N records can match the condition return S_OK( tuple(Field,Value) ) if limit is not False, the given limit is set String type values will be appropriately escaped, they can be single values or lists of values.

for compatibility with other methods condDict keyed argument is added

getCounters( self, table, attrList, condDict = None, older = None,

newer = None, timeStamp = None, connection = False ):

Count the number of records on each distinct combination of AttrList, selected with condition defined by condDict and time stamps

getDistinctAttributeValues( self, table, attribute, condDict = None, older = None,

newer = None, timeStamp = None, connection = False ):

Get distinct values of a table attribute under specified conditions

class DIRAC.Core.Utilities.MySQL.ConnectionPool(host, user, passwd, port=3306, graceTime=600)

Bases: object

Management of connections per thread

__init__(host, user, passwd, port=3306, graceTime=600)

clean(now=False)

get(dbName, retries=10)

transactionCommit(dbName)

transactionRollback(dbName)

transactionStart(dbName)

class DIRAC.Core.Utilities.MySQL.MySQL(hostName='localhost', userName='dirac', passwd='dirac', dbName='', port=3306, debug=False)

Bases: object

Basic multithreaded DIRAC MySQL Client Class

__init__(hostName='localhost', userName='dirac', passwd='dirac', dbName='', port=3306, debug=False)

set MySQL connection parameters and try to connect

Parameters: debug – unused

buildCondition(condDict=None, older=None, newer=None, timeStamp=None, orderAttribute=None, limit=False, greater=None, smaller=None, offset=None, useLikeQuery=False): Build SQL condition statement from provided condDict and other extra check on a specified time stamp. The conditions dictionary specifies for each attribute one or a List of possible values greater and smaller are dictionaries in which the keys are the names of the fields, that are requested to be >= or < than the corresponding value. For compatibility with current usage it uses Exceptions to exit in case of invalid arguments For performing LIKE queries use the parameter useLikeQuery=True

countEntries(table, condDict, older=None, newer=None, timeStamp=None, connection=False, greater=None, smaller=None): Count the number of entries wit the given conditions

deleteEntries(tableName, condDict=None, limit=False, conn=None, older=None, newer=None, timeStamp=None, orderAttribute=None, greater=None, smaller=None): Delete rows from “tableName” with N records can match the condition if limit is not False, the given limit is set String type values will be appropriately escaped, they can be single values or lists of values.

executeStoredProcedure(packageName, parameters, outputIds)

executeStoredProcedureWithCursor(packageName, parameters)

getCounters(table, attrList, condDict, older=None, newer=None, timeStamp=None, connection=False, greater=None, smaller=None): Count the number of records on each distinct combination of AttrList, selected with condition defined by condDict and time stamps

getDistinctAttributeValues(table, attribute, condDict=None, older=None, newer=None, timeStamp=None, connection=False, greater=None, smaller=None): Get distinct values of a table attribute under specified conditions

getFields(tableName, outFields=None, condDict=None, limit=False, conn=None, older=None, newer=None, timeStamp=None, orderAttribute=None, greater=None, smaller=None, useLikeQuery=False): Select “outFields” from “tableName” with condDict N records can match the condition return S_OK(tuple(Field, Value)) if outFields is None all fields in “tableName” are returned if limit is not False, the given limit is set inValues are properly escaped using the _escape_string method, they can be single values or lists of values. if useLikeQuery=True, then conDict can return matched rows if “%” is defined inside conDict.

insertFields(tableName, inFields=None, inValues=None, conn=None, inDict=None): Insert a new row in “tableName” assigning the values “inValues” to the fields “inFields”. String type values will be appropriately escaped.

transactionCommit()

transactionRollback()

transactionStart()

updateFields(tableName, updateFields=None, updateValues=None, condDict=None, limit=False, conn=None, updateDict=None, older=None, newer=None, timeStamp=None, orderAttribute=None, greater=None, smaller=None): Update “updateFields” from “tableName” with “updateValues”. updateDict alternative way to provide the updateFields and updateValues N records can match the condition return S_OK( number of updated rows ) if limit is not False, the given limit is set String type values will be appropriately escaped.