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()