Python sqlalchemy.exc.IntegrityError() Examples

def update_distances(src_rse_id=None, dest_rse_id=None, parameters=None, session=None):
    """
    Update distances with the given RSE ids.

    :param src_rse_id: The source RSE ID.
    :param dest_rse_id: The destination RSE ID.
    :param  parameters: A dictionnary with property
    :param session: The database session to use.
    """
    params = {}
    for key in parameters:
        if key in ['ranking', 'agis_distance', 'geoip_distance', 'active', 'submitted', 'finished', 'failed', 'transfer_speed', 'packet_loss', 'latency', 'mbps_file', 'mbps_link', 'queued_total', 'done_1h', 'done_6h']:
            params[key] = parameters[key]
    try:
        query = session.query(Distance)
        if src_rse_id:
            query = query.filter(Distance.src_rse_id == src_rse_id)
        if dest_rse_id:
            query = query.filter(Distance.dest_rse_id == dest_rse_id)
        query.update(params)
    except IntegrityError as error:
        raise exception.RucioException(error.args) 

def delete_messages(messages, session=None):
    """
    Delete all messages with the given IDs, and archive them to the history.

    :param messages: The messages to delete as a list of dictionaries.
    """
    message_condition = []
    for message in messages:
        message_condition.append(Message.id == message['id'])
        if len(message['payload']) > 4000:
            message['payload_nolimit'] = message.pop('payload')

    try:
        if message_condition:
            session.query(Message).\
                with_hint(Message, "index(messages MESSAGES_ID_PK)", 'oracle').\
                filter(or_(*message_condition)).\
                delete(synchronize_session=False)

            session.bulk_insert_mappings(MessageHistory, messages)
    except IntegrityError as e:
        raise RucioException(e.args) 

def add_account(account, type, email, session=None):
    """ Add an account with the given account name and type.

    :param account: the name of the new account.
    :param type: the type of the new account.
    :param email: The Email address associated with the account.
    :param session: the database session in use.
    """
    new_account = models.Account(account=account, account_type=type, email=email,
                                 status=AccountStatus.ACTIVE)
    try:
        new_account.save(session=session)
    except IntegrityError:
        raise exception.Duplicate('Account ID \'%s\' already exists!' % account)
    # Create the account counters for this account
    rucio.core.account_counter.create_counters_for_new_account(account=account, session=session) 

def __touch_request(request_id, session=None):
    """
    Update the timestamp of a request. Fails silently if the request_id does not exist.

    :param request_id:  Request-ID as a 32 character hex string.
    :param session:     Database session to use.
    """

    record_counter('core.request.touch_request')

    try:
        rowcount = session.query(models.Request).filter_by(id=request_id).update({'updated_at': datetime.datetime.utcnow()}, synchronize_session=False)
    except IntegrityError as error:
        raise RucioException(error.args)
    if not rowcount:
        raise UnsupportedOperation("Request %s cannot be touched." % request_id) 

def test_unique_identities(self):
        """Check if there is only one tuple with the same values"""

        id1 = Identity(id='A', name='John Smith', email='jsmith@example.com',
                       username='jsmith', source='scm')
        id2 = Identity(id='B', name='John Smith', email='jsmith@example.com',
                       username='jsmith', source='scm')

        with self.assertRaisesRegex(IntegrityError, DUP_CHECK_ERROR):
            self.session.add(id1)
            self.session.add(id2)
            self.session.commit()

        self.session.rollback()

        # Changing an property should not raise any error
        id2.source = 'mls'
        self.session.add(id1)
        self.session.add(id2)
        self.session.commit()

        self.assertNotEqual(id1.id, id2.id) 

def test_unique_enrollments(self):
        """Check if there is only one tuple with the same values"""

        with self.assertRaisesRegex(IntegrityError, DUP_CHECK_ERROR):
            uid = UniqueIdentity(uuid='John Smith')
            self.session.add(uid)

            org = Organization(name='Example')
            self.session.add(org)

            rol1 = Enrollment(uidentity=uid, organization=org)
            rol2 = Enrollment(uidentity=uid, organization=org)

            self.session.add(rol1)
            self.session.add(rol2)
            self.session.commit() 

def generate_fake(count=100):
        from sqlalchemy.exc import IntegrityError
        from random import seed
        import forgery_py

        seed()
        for i in range(count):
            u = User(email=forgery_py.internet.email_address(),
                     username=forgery_py.internet.user_name(True),
                     password=forgery_py.lorem_ipsum.word(),
                     confirmed=True,
                     name=forgery_py.name.full_name(),
                     location=forgery_py.address.city(),
                     about_me=forgery_py.lorem_ipsum.sentence(),
                     member_since=forgery_py.date.date(True))
            db.session.add(u)
            try:
                db.session.commit()
            except IntegrityError:
                db.session.rollback() 

def delete_alias(alias: Alias, user: User):
    Alias.delete(alias.id)
    db.session.commit()

    # save deleted alias to either global or domain trash
    if alias.custom_domain_id:
        try:
            DomainDeletedAlias.create(
                user_id=user.id, email=alias.email, domain_id=alias.custom_domain_id
            )
            db.session.commit()
        except IntegrityError:
            LOG.error(
                "alias %s domain %s has been added before to DeletedAlias",
                alias.email,
                alias.custom_domain_id,
            )
            db.session.rollback()
    else:
        try:
            DeletedAlias.create(email=alias.email)
            db.session.commit()
        except IntegrityError:
            LOG.error("alias %s has been added before to DeletedAlias", alias.email)
            db.session.rollback() 

def get_requests_by_transfer(external_host, transfer_id, session=None):
    """
    Retrieve requests by its transfer ID.

    :param request_host:  Name of the external host.
    :param transfer_id:   External transfer job id as a string.
    :param session:       Database session to use.
    :returns:             List of Requests.
    """

    try:
        tmp = session.query(models.Request).filter_by(external_id=transfer_id).all()

        if tmp:
            result = []
            for t in tmp:
                t2 = dict(t)
                t2.pop('_sa_instance_state')
                t2['request_id'] = t2['id']
                t2['attributes'] = json.loads(str(t2['attributes']))
                result.append(t2)
            return result
        return
    except IntegrityError as error:
        raise RucioException(error.args) 

def delete(cls, obj_id):
        # Put all aliases belonging to this mailbox to global trash
        try:
            for alias in Alias.query.filter_by(mailbox_id=obj_id):
                # special handling for alias that has several mailboxes and has mailbox_id=obj_id
                if len(alias.mailboxes) > 1:
                    # use the first mailbox found in alias._mailboxes
                    first_mb = alias._mailboxes[0]
                    alias.mailbox_id = first_mb.id
                    alias._mailboxes.remove(first_mb)
                else:
                    # only put aliases that have mailbox as a single mailbox into trash
                    DeletedAlias.create(email=alias.email)
                db.session.commit()
        # this can happen when a previously deleted alias is re-created via catch-all or directory feature
        except IntegrityError:
            LOG.error("Some aliases have been added before to DeletedAlias")
            db.session.rollback()

        cls.query.filter(cls.id == obj_id).delete()
        db.session.commit() 

def get_request(request_id, session=None):
    """
    Retrieve a request by its ID.

    :param request_id:  Request-ID as a 32 character hex string.
    :param session:     Database session to use.
    :returns:           Request as a dictionary.
    """

    try:
        tmp = session.query(models.Request).filter_by(id=request_id).first()

        if not tmp:
            return
        else:
            tmp = dict(tmp)
            tmp.pop('_sa_instance_state')
            return tmp
    except IntegrityError as error:
        raise RucioException(error.args) 

def set_requests_state(request_ids, new_state, session=None):
    """
    Bulk update the state of requests. Fails silently if the request_id does not exist.

    :param request_ids:  List of (Request-ID as a 32 character hex string).
    :param new_state:    New state as string.
    :param session:      Database session to use.
    """

    record_counter('core.request.set_requests_state')

    try:
        for request_id in request_ids:
            set_request_state(request_id, new_state, session=session)
    except IntegrityError as error:
        raise RucioException(error.args) 

def trigger_event(scope, name, event_type, payload, session=None):
    """
    Records changes occuring in the did to the FollowEvents table

    :param scope: The scope name.
    :param name: The data identifier name.
    :param event_type: The type of event affecting the did.
    :param payload: Any message to be stored along with the event.
    :param session: The database session in use.
    """
    try:
        dids = session.query(models.DidsFollowed).filter_by(scope=scope, name=name).all()

        for did in dids:
            # Create a new event using teh specified parameters.
            new_event = models.FollowEvents(scope=scope, name=name, account=did.account,
                                            did_type=did.did_type, event_type=event_type, payload=payload)
            new_event.save(session=session, flush=False)

        session.flush()
    except IntegrityError as error:
        raise exception.RucioException(error.args) 

def add_dids_to_followed(dids, account, session=None):
    """
    Bulk mark datasets as followed

    :param dids: A list of dids.
    :param account: The account owner.
    :param session: The database session in use.
    """
    try:
        for did in dids:
            # Get the did details corresponding to the scope and name passed.
            did = session.query(models.DataIdentifier).filter_by(scope=did['scope'], name=did['name']).one()
            # Add the queried to the followed table.
            new_did_followed = models.DidsFollowed(scope=did.scope, name=did.name, account=account,
                                                   did_type=did.did_type)

            new_did_followed.save(session=session, flush=False)

        session.flush()
    except IntegrityError as error:
        raise exception.RucioException(error.args) 

def execute_query(instance: PluginInstance, sql_query: Union[str, Query],
                  rows_as_dict: bool = False) -> web.Response:
    try:
        res: ResultProxy = instance.inst_db.execute(sql_query)
    except exc.IntegrityError as e:
        return resp.sql_integrity_error(e, sql_query)
    except exc.OperationalError as e:
        return resp.sql_operational_error(e, sql_query)
    data = {
        "ok": True,
        "query": str(sql_query),
    }
    if res.returns_rows:
        row: RowProxy
        data["rows"] = [({key: check_type(value) for key, value in row.items()}
                         if rows_as_dict
                         else [check_type(value) for value in row])
                        for row in res]
        data["columns"] = res.keys()
    else:
        data["rowcount"] = res.rowcount
    if res.is_insert:
        data["inserted_primary_key"] = res.inserted_primary_key
    return web.json_response(data) 

def __set_transfer_state(external_host, transfer_id, new_state, session=None):
    """
    Update the state of a transfer. Fails silently if the transfer_id does not exist.
    :param external_host:  Selected external host as string in format protocol://fqdn:port
    :param transfer_id:    External transfer job id as a string.
    :param new_state:      New state as string.
    :param session:        Database session to use.
    """

    record_counter('core.request.set_transfer_state')

    try:
        rowcount = session.query(models.Request).filter_by(external_id=transfer_id).update({'state': new_state, 'updated_at': datetime.datetime.utcnow()}, synchronize_session=False)
    except IntegrityError as error:
        raise RucioException(error.args)

    if not rowcount:
        raise UnsupportedOperation("Transfer %s on %s state %s cannot be updated." % (transfer_id, external_host, new_state)) 

def touch_requests_by_rule(rule_id, session=None):
    """
    Update the update time of requests in a rule. Fails silently if no requests on this rule.

    :param rule_id:  Rule-ID as a 32 character hex string.
    :param session:  Database session to use.
    """

    record_counter('core.request.touch_requests_by_rule')

    try:
        session.query(models.Request).with_hint(models.Request, "INDEX(REQUESTS REQUESTS_RULEID_IDX)", 'oracle')\
                                     .filter_by(rule_id=rule_id)\
                                     .filter(models.Request.state.in_([RequestState.FAILED, RequestState.DONE, RequestState.LOST, RequestState.NO_SOURCES, RequestState.ONLY_TAPE_SOURCES]))\
                                     .filter(models.Request.updated_at < datetime.datetime.utcnow())\
                                     .update({'updated_at': datetime.datetime.utcnow() + datetime.timedelta(minutes=20)}, synchronize_session=False)
    except IntegrityError as error:
        raise RucioException(error.args) 

def set_transfer_update_time(external_host, transfer_id, update_time=datetime.datetime.utcnow(), session=None):
    """
    Update the state of a request. Fails silently if the transfer_id does not exist.
    :param external_host:  Selected external host as string in format protocol://fqdn:port
    :param transfer_id:    External transfer job id as a string.
    :param update_time:    Time stamp.
    :param session:        Database session to use.
    """

    record_counter('core.request.set_transfer_update_time')

    try:
        rowcount = session.query(models.Request).filter_by(external_id=transfer_id, state=RequestState.SUBMITTED).update({'updated_at': update_time}, synchronize_session=False)
    except IntegrityError as error:
        raise RucioException(error.args)

    if not rowcount:
        raise UnsupportedOperation("Transfer %s doesn't exist or its status is not submitted." % (transfer_id)) 

def add_user(cls, identifier: str, password: str, password_encrypted: bool = False) -> None:
        """新增用户。当用户已存在时，抛出ValueError。

        :param identifier: 学号或教工号
        :param password: 密码
        :param password_encrypted: 密码是否已经被加密过了（否则会被二次加密）
        """
        if not password_encrypted:
            password_hash = generate_password_hash(password)
        else:
            password_hash = password

        user = User(identifier=identifier, password=password_hash, create_time=datetime.datetime.now())

        db_session.add(user)
        try:
            db_session.commit()
        except IntegrityError as e:
            raise AlreadyRegisteredError from e 

def insert_to_db(self):
        db.session.add(self)
        try:
            db.session.commit()
        except IntegrityError:
            db.session.rollback()
            flag = False
            while flag == False:
                integration = Integration.query.filter_by(integration_id=self.integration_id).first()
                if integration is not None:
                    self.integration_id = generate_integration_id()
                    db.session.add(self)
                    try:
                        db.session.commit()
                        flag = True
                    except IntegrityError:
                        db.session.rollback()
                        flag = False
                else:
                    flag = True
        return True 

def delete_distances(src_rse_id=None, dest_rse_id=None, session=None):
    """
    Delete distances with the given RSE ids.

    :param src_rse_id: The source RSE ID.
    :param dest_rse_id: The destination RSE ID.
    :param session: The database session to use.
    """

    try:
        query = session.query(Distance)

        if src_rse_id:
            query = query.filter(Distance.src_rse_id == src_rse_id)
        if dest_rse_id:
            query = query.filter(Distance.dest_rse_id == dest_rse_id)

        query.delete()
    except IntegrityError as error:
        raise exception.RucioException(error.args) 

def add_naming_convention(scope, regexp, convention_type, session=None):
    """
    add a naming convention for a given scope

    :param scope: the name for the scope.
    :param regexp: the regular expression to validate the name.
    :param convention_type: the did_type on which the regexp should apply.
    :param session: The database session in use.
    """
    # validate the regular expression
    try:
        compile(regexp)
    except error:
        raise RucioException('Invalid regular expression %s!' % regexp)

    new_convention = models.NamingConvention(scope=scope,
                                             regexp=regexp,
                                             convention_type=convention_type)
    try:
        new_convention.save(session=session)
    except IntegrityError:
        raise Duplicate('Naming convention already exists!')
    except:
        raise RucioException(str(format_exc())) 

def set_rse_transfer_limits(rse_id, activity, rse_expression=None, max_transfers=0, transfers=0, waitings=0, volume=0, deadline=1, strategy='fifo', session=None):
    """
    Set RSE transfer limits.

    :param rse_id: The RSE id.
    :param activity: The activity.
    :param rse_expression: RSE expression string.
    :param max_transfers: Maximum transfers.
    :param transfers: Current number of tranfers.
    :param waitings: Current number of waitings.
    :param volume: Maximum transfer volume in bytes.
    :param deadline: Maximum waiting time in hours until a datasets gets released.
    :param strategy: Stragey to handle datasets `fifo` or `grouped_fifo`.
    :param session: The database session in use.

    :returns: True if successful, otherwise false.
    """
    try:
        rse_tr_limit = models.RSETransferLimit(rse_id=rse_id, activity=activity, rse_expression=rse_expression,
                                               max_transfers=max_transfers, transfers=transfers,
                                               waitings=waitings, volume=volume, strategy=strategy, deadline=deadline)
        rse_tr_limit = session.merge(rse_tr_limit)
        rowcount = rse_tr_limit.save(session=session)
        return rowcount
    except IntegrityError as error:
        raise exception.RucioException(error.args) 

def delete_rse_limit(rse_id, name=None, session=None):
    """
    Delete RSE limit.

    :param rse_id: The RSE id.
    :param name: The name of the limit.
    """
    try:
        session.query(models.RSELimit).filter_by(rse_id=rse_id, name=name).delete()
    except IntegrityError as error:
        raise exception.RucioException(error.args) 

def get_rse_transfer_limits(rse_id=None, activity=None, session=None):
    """
    Get RSE transfer limits.

    :param rse_id: The RSE id.
    :param activity: The activity.

    :returns: A dictionary with the limits {'limit.activity': {'limit.rse_id': {'max_transfers': limit.max_transfers, 'transfers': 0, 'waitings': 0, 'volume': 1}}}.
    """
    try:
        query = session.query(models.RSETransferLimit)
        if rse_id:
            query = query.filter_by(rse_id=rse_id)
        if activity:
            query = query.filter_by(activity=activity)

        limits = {}
        for limit in query:
            if limit.activity not in limits:
                limits[limit.activity] = {}
            limits[limit.activity][limit.rse_id] = {'max_transfers': limit.max_transfers,
                                                    'transfers': limit.transfers,
                                                    'waitings': limit.waitings,
                                                    'volume': limit.volume,
                                                    'strategy': limit.strategy,
                                                    'deadline': limit.deadline}
        return limits
    except IntegrityError as error:
        raise exception.RucioException(error.args) 

def report_unavailable_room(room_id: str, date: datetime.date, time: str, user_type: str, user_id: str):
    """反馈实际不可用的教室"""
    try:
        return UnavailableRoomReport.new(room_id, date, time, user_type, user_id)
    except IntegrityError as e:
        raise AlreadyReported from e 

def bulk_add_bad_replicas(replicas, account, state=BadFilesStatus.TEMPORARY_UNAVAILABLE, reason=None, expires_at=None, session=None):
    """
    Bulk add new bad replicas.

    :param replicas: the list of bad replicas.
    :param account: The account who declared the bad replicas.
    :param state: The state of the file (SUSPICIOUS, BAD or TEMPORARY_UNAVAILABLE).
    :param session: The database session in use.

    :returns: True is successful.
    """
    for replica in replicas:
        insert_new_row = True
        if state == BadFilesStatus.TEMPORARY_UNAVAILABLE:
            query = session.query(models.BadReplicas).filter_by(scope=replica['scope'], name=replica['name'], rse_id=replica['rse_id'], state=state)
            if query.count():
                query.update({'state': BadFilesStatus.TEMPORARY_UNAVAILABLE, 'updated_at': datetime.utcnow(), 'account': account, 'reason': reason, 'expires_at': expires_at}, synchronize_session=False)
                insert_new_row = False
        if insert_new_row:
            new_bad_replica = models.BadReplicas(scope=replica['scope'], name=replica['name'], rse_id=replica['rse_id'], reason=reason,
                                                 state=state, account=account, bytes=None, expires_at=expires_at)
            new_bad_replica.save(session=session, flush=False)
    try:
        session.flush()
    except IntegrityError as error:
        raise exception.RucioException(error.args)
    except DatabaseError as error:
        raise exception.RucioException(error.args)
    except FlushError as error:
        if match('New instance .* with identity key .* conflicts with persistent instance', error.args[0]):
            raise exception.DataIdentifierAlreadyExists('Data Identifier already exists!')
        raise exception.RucioException(error.args)
    return True 

def add_bad_pfns(pfns, account, state, reason=None, expires_at=None, session=None):
    """
    Add bad PFNs.

    :param pfns: the list of new files.
    :param account: The account who declared the bad replicas.
    :param state: One of the possible states : BAD, SUSPICIOUS, TEMPORARY_UNAVAILABLE.
    :param reason: A string describing the reason of the loss.
    :param expires_at: Specify a timeout for the TEMPORARY_UNAVAILABLE replicas. None for BAD files.
    :param session: The database session in use.

    :returns: True is successful.
    """
    if isinstance(state, string_types):
        rep_state = BadPFNStatus.from_sym(state)
    else:
        rep_state = state

    pfns = clean_surls(pfns)
    for pfn in pfns:
        new_pfn = models.BadPFNs(path=str(pfn), account=account, state=rep_state, reason=reason, expires_at=expires_at)
        new_pfn = session.merge(new_pfn)
        new_pfn.save(session=session, flush=False)

    try:
        session.flush()
    except IntegrityError as error:
        raise exception.RucioException(error.args)
    except DatabaseError as error:
        raise exception.RucioException(error.args)
    except FlushError as error:
        if match('New instance .* with identity key .* conflicts with persistent instance', error.args[0]):
            raise exception.Duplicate('One PFN already exists!')
        raise exception.RucioException(error.args)
    return True 

def _insert_or_update(self, timestamp, values, lastseen=None):
        stmt = insert(self.tables.passive)\
            .values(dict(values, addr=utils.force_int2ip(values['addr'])))
        try:
            self.db.execute(stmt)
        except IntegrityError:
            whereclause = and_(
                self.tables.passive.addr == values['addr'],
                self.tables.passive.sensor == values['sensor'],
                self.tables.passive.recontype == values['recontype'],
                self.tables.passive.source == values['source'],
                self.tables.passive.value == values['value'],
                self.tables.passive.targetval == values['targetval'],
                self.tables.passive.info == values['info'],
                self.tables.passive.port == values['port']
            )
            upsert = {
                'firstseen': func.least(
                    self.tables.passive.firstseen,
                    timestamp,
                ),
                'lastseen': func.greatest(
                    self.tables.passive.lastseen,
                    lastseen or timestamp,
                ),
                'count': self.tables.passive.count + values['count'],
            }
            updt = update(
                self.tables.passive
            ).where(whereclause).values(upsert)
            self.db.execute(updt) 

def __bulk_add_new_file_dids(files, account, dataset_meta=None, session=None):
    """
    Bulk add new dids.

    :param dids: the list of new files.
    :param account: The account owner.
    :param session: The database session in use.
    :returns: True is successful.
    """
    for file in files:
        new_did = models.DataIdentifier(scope=file['scope'], name=file['name'],
                                        account=file.get('account') or account,
                                        did_type=DIDType.FILE, bytes=file['bytes'],
                                        md5=file.get('md5'), adler32=file.get('adler32'),
                                        is_new=None)
        for key in file.get('meta', []):
            new_did.update({key: file['meta'][key]})
        for key in dataset_meta or {}:
            new_did.update({key: dataset_meta[key]})

        new_did.save(session=session, flush=False)
    try:
        session.flush()
    except IntegrityError as error:
        raise exception.RucioException(error.args)
    except DatabaseError as error:
        raise exception.RucioException(error.args)
    except FlushError as error:
        if match('New instance .* with identity key .* conflicts with persistent instance', error.args[0]):
            raise exception.DataIdentifierAlreadyExists('Data Identifier already exists!')
        raise exception.RucioException(error.args)
    return True