def addIfNotThere(self, table, params):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is not there, it is inserted as a new entry.
:param table: table where to add or modify
:type table: str
:param params: dictionary of what to add or modify
:type params: dict
:return: S_OK() || S_ERROR()
'''
session = self.sessionMaker_o()
table_c = getattr(__import__(__name__, globals(), locals(), [table]),
table)
primaryKeys = [key.name for key in class_mapper(table_c).primary_key]
try:
select = Query(table_c, session=session)
for columnName, columnValue in params.iteritems():
if not columnValue or columnName not in primaryKeys:
continue
column_a = getattr(table_c, columnName.lower())
if isinstance(columnValue, (list, tuple)):
select = select.filter(column_a.in_(list(columnValue)))
elif isinstance(columnValue, basestring):
select = select.filter(column_a == columnValue)
else:
self.log.error("type(columnValue) == %s" %
type(columnValue))
res = select.first() # the selection is done via primaryKeys only
if not res: # if not there, let's insert it
return self.insert(table, params)
session.commit()
return S_OK()
except exc.SQLAlchemyError as e:
session.rollback()
self.log.exception("addIfNotThere: unexpected exception",
lException=e)
return S_ERROR("addIfNotThere: unexpected exception %s" % e)
finally:
session.close()
def findByFieldsValues(cls,fields,values,session=None,onlyOne=True,notNoneFields=None,orderByFields=None,ascending=True,operators=None,groupByFields=None,selectFieldsAndFunctions=None,nestedOperators=None,returnDataframe=False,distinct=False,printQuery=False):
if operators is None:
operators = [SQLOperator.equalOperator] * 1000
close = False
if session is None:
session = cls.database.Session()
close = True
o = None
if selectFieldsAndFunctions is None:
o = session.query(cls)
else:
o = Query(selectFieldsAndFunctions, session=session)
for field,value,operator in zip(fields,values,operators):
operator.field = field
operator.value = value
o = operator.filter(o)
if nestedOperators is not None:
for operator in nestedOperators:
o = operator.filter(o)
if notNoneFields is not None:
for f in notNoneFields:
o = o.filter(f != None)
if groupByFields is not None:
for f in groupByFields:
o = o.group_by(f)
if orderByFields is not None:
for f in orderByFields:
if ascending:
o = o.order_by(f)
else:
o = o.order_by(f.desc())
if printQuery:
print(o)
if onlyOne:
o = o.first()
else:
if distinct:
o = o.distinct()
if returnDataframe:
o = cls.queryToDataframe(o)
else:
o = o.all()
if close:
session.close()
return o
def addIfNotThere( self, table, params ):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is not there, it is inserted as a new entry.
:param table: table where to add or modify
:type table: str
:param params: dictionary of what to add or modify
:type params: dict
:return: S_OK() || S_ERROR()
'''
session = self.sessionMaker_o()
table_c = getattr(__import__(__name__, globals(), locals(), [table]), table)
primaryKeys = [key.name for key in class_mapper(table_c).primary_key]
try:
select = Query(table_c, session = session)
for columnName, columnValue in params.iteritems():
if not columnValue or columnName not in primaryKeys:
continue
column_a = getattr(table_c, columnName.lower())
if isinstance(columnValue, (list, tuple)):
select = select.filter(column_a.in_(list(columnValue)))
elif isinstance(columnValue, basestring):
select = select.filter(column_a == columnValue)
else:
self.log.error("type(columnValue) == %s" %type(columnValue))
res = select.first() # the selection is done via primaryKeys only
if not res: # if not there, let's insert it
return self.insert(table, params)
session.commit()
return S_OK()
except exc.SQLAlchemyError as e:
session.rollback()
self.log.exception( "addIfNotThere: unexpected exception", lException = e )
return S_ERROR( "addIfNotThere: unexpected exception %s" % e )
finally:
session.close()
def addOrModify(self, table, params):
"""
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is there, it is updated, if not, it is inserted as a new entry.
:param table: table where to add or modify
:type table: str
:param params: dictionary of what to add or modify
:type params: dict
:return: S_OK() || S_ERROR()
"""
session = self.sessionMaker_o()
found = False
for ext in self.extensions:
try:
table_c = getattr(
__import__(ext + __name__, globals(), locals(), [table]),
table)
found = True
break
except (ImportError, AttributeError):
continue
# If not found in extensions, import it from DIRAC base (this same module).
if not found:
table_c = getattr(
__import__(__name__, globals(), locals(), [table]), table)
columns = [key.name for key in class_mapper(table_c).columns]
primaryKeys = [key.name for key in class_mapper(table_c).primary_key]
try:
select = Query(table_c, session=session)
for columnName, columnValue in params.iteritems():
if not columnValue or columnName not in primaryKeys:
continue
column_a = getattr(table_c, columnName.lower())
if isinstance(columnValue, (list, tuple)):
select = select.filter(column_a.in_(list(columnValue)))
elif isinstance(columnValue, basestring):
select = select.filter(column_a == columnValue)
else:
self.log.error("type(columnValue) == %s" %
type(columnValue))
res = select.first() # the selection is done via primaryKeys only
if not res: # if not there, let's insert it
return self.insert(table, params)
# Treating case of time value updates
if 'LastCheckTime' in columns and not params.get('LastCheckTime'):
params['LastCheckTime'] = None
if 'DateEffective' in columns and not params.get('DateEffective'):
params['DateEffective'] = None
# now we assume we need to modify
for columnName, columnValue in params.iteritems():
if columnName == 'LastCheckTime' and not columnValue: # we always update lastCheckTime
columnValue = datetime.datetime.utcnow().replace(
microsecond=0)
if columnName == 'DateEffective' and not columnValue: # we always update DateEffective, if there
columnValue = datetime.datetime.utcnow().replace(
microsecond=0)
if columnValue:
setattr(res, columnName.lower(), columnValue)
session.commit()
return S_OK()
except exc.SQLAlchemyError as e:
session.rollback()
self.log.exception("addOrModify: unexpected exception",
lException=e)
return S_ERROR("addOrModify: unexpected exception %s" % e)
finally:
session.close()
def addOrModify( self, table, params ):
"""
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is there, it is updated, if not, it is inserted as a new entry.
:param table: table where to add or modify
:type table: str
:param params: dictionary of what to add or modify
:type params: dict
:return: S_OK() || S_ERROR()
"""
session = self.sessionMaker_o()
found = False
for ext in self.extensions:
try:
table_c = getattr(__import__(ext + __name__, globals(), locals(), [table]), table)
found = True
break
except (ImportError, AttributeError):
continue
# If not found in extensions, import it from DIRAC base (this same module).
if not found:
table_c = getattr(__import__(__name__, globals(), locals(), [table]), table)
columns = [key.name for key in class_mapper(table_c).columns]
primaryKeys = [key.name for key in class_mapper(table_c).primary_key]
try:
select = Query(table_c, session = session)
for columnName, columnValue in params.iteritems():
if not columnValue or columnName not in primaryKeys:
continue
column_a = getattr(table_c, columnName.lower())
if isinstance(columnValue, (list, tuple)):
select = select.filter(column_a.in_(list(columnValue)))
elif isinstance(columnValue, basestring):
select = select.filter(column_a == columnValue)
else:
self.log.error("type(columnValue) == %s" %type(columnValue))
res = select.first() # the selection is done via primaryKeys only
if not res: # if not there, let's insert it
return self.insert(table, params)
# Treating case of time value updates
if 'LastCheckTime' in columns and not params.get('LastCheckTime'):
params['LastCheckTime'] = None
if 'DateEffective' in columns and not params.get('DateEffective'):
params['DateEffective'] = None
# now we assume we need to modify
for columnName, columnValue in params.iteritems():
if columnName == 'LastCheckTime' and not columnValue: # we always update lastCheckTime
columnValue = datetime.datetime.utcnow().replace(microsecond = 0)
if columnName == 'DateEffective' and not columnValue: # we always update DateEffective, if there
columnValue = datetime.datetime.utcnow().replace(microsecond = 0)
if columnValue:
setattr(res, columnName.lower(), columnValue)
session.commit()
return S_OK()
except exc.SQLAlchemyError as e:
session.rollback()
self.log.exception( "addOrModify: unexpected exception", lException = e )
return S_ERROR( "addOrModify: unexpected exception %s" % e )
finally:
session.close()
def addOrModify(self, table, params):
'''
Using the PrimaryKeys of the table, it looks for the record in the database.
If it is there, it is updated, if not, it is inserted as a new entry.
:param table: table where to add or modify
:type table: str
:param params: dictionary of what to add or modify
:type params: dict
:return: S_OK() || S_ERROR()
'''
session = self.sessionMaker_o()
found = False
for ext in self.extensions:
try:
table_c = getattr(
__import__(ext + __name__, globals(), locals(), [table]),
table)
found = True
break
except (ImportError, AttributeError):
continue
# If not found in extensions, import it from DIRAC base (this same module).
if not found:
table_c = getattr(
__import__(__name__, globals(), locals(), [table]), table)
primaryKeys = [key.name for key in class_mapper(table_c).primary_key]
try:
select = Query(table_c, session=session)
for columnName, columnValue in params.iteritems():
if not columnValue or columnName not in primaryKeys:
continue
column_a = getattr(table_c, columnName.lower())
if isinstance(columnValue, (list, tuple)):
select = select.filter(column_a.in_(list(columnValue)))
elif isinstance(columnValue, six.string_types):
select = select.filter(column_a == columnValue)
else:
self.log.error("type(columnValue) == %s" %
type(columnValue))
res = select.first() # the selection is done via primaryKeys only
if not res: # if not there, let's insert it (and exit)
return self.insert(table, params)
# From now on, we assume we need to modify
# Treating case of time value updates
if not params.get('LastCheckTime'):
params['LastCheckTime'] = None
if not params.get('DateEffective'):
params['DateEffective'] = None
# Should we change DateEffective?
changeDE = False
if params.get('Status'):
if params.get(
'Status'
) != res.status: # we update dateEffective iff we change the status
changeDE = True
for columnName, columnValue in params.iteritems():
if columnName == 'LastCheckTime' and not columnValue: # we always update lastCheckTime
columnValue = datetime.datetime.utcnow().replace(
microsecond=0)
if changeDE and columnName == 'DateEffective' and not columnValue:
columnValue = datetime.datetime.utcnow().replace(
microsecond=0)
if columnValue:
if isinstance(columnValue, datetime.datetime):
columnValue = columnValue.replace(microsecond=0)
setattr(res, columnName.lower(), columnValue)
session.commit()
# and since we modified, we now insert a new line in the log table
return self.insert(table.replace('Status', '') + 'Log', params)
# The line inserted will maybe become a History line thanks to the SummarizeLogsAgent
except exc.SQLAlchemyError as e:
session.rollback()
self.log.exception("addOrModify: unexpected exception",
lException=e)
return S_ERROR("addOrModify: unexpected exception %s" % e)
finally:
session.close()
def get_first(query: Query) -> BaseModel:
return query.first()