Esempio n. 1
0
    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()
Esempio n. 2
0
 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
Esempio n. 3
0
  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()
Esempio n. 4
0
    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()
Esempio n. 5
0
  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()
Esempio n. 6
0
    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()
Esempio n. 7
0
 def get_first(query: Query) -> BaseModel:
     return query.first()