Python sqlalchemy.update() Examples

def release_waiting_requests_per_deadline(rse_id=None, deadline=1, session=None):
    """
    Release waiting requests that were waiting too long and exceeded the maximum waiting time to be released.
    If the DID of a request is attached to a dataset, the oldest requested_at date of all requests related to the dataset will be used for checking and all requests of this dataset will be released.
    :param rse_id:           The source RSE id.
    :param deadline:         Maximal waiting time in hours until a dataset gets released.
    :param session:          The database session.
    """
    amount_released_requests = 0
    if deadline:
        grouped_requests_subquery, filtered_requests_subquery = create_base_query_grouped_fifo(rse_id, filter_by_rse='source', session=session)
        old_requests_subquery = session.query(grouped_requests_subquery.c.name,
                                              grouped_requests_subquery.c.scope,
                                              grouped_requests_subquery.c.oldest_requested_at)\
                                       .filter(grouped_requests_subquery.c.oldest_requested_at < datetime.datetime.now() - datetime.timedelta(hours=deadline))\
                                       .subquery()
        old_requests_subquery = session.query(filtered_requests_subquery.c.id)\
                                       .join(old_requests_subquery, and_(filtered_requests_subquery.c.dataset_name == old_requests_subquery.c.name, filtered_requests_subquery.c.dataset_scope == old_requests_subquery.c.scope))
        old_requests_subquery = old_requests_subquery.subquery()
        statement = update(models.Request).where(models.Request.id.in_(old_requests_subquery)).values(state=RequestState.QUEUED)
        amount_released_requests = session.execute(statement).rowcount
    return amount_released_requests 

def inline(self):
        """Make this :class:`_expression.Update` construct "inline" .

        When set, SQL defaults present on :class:`_schema.Column`
        objects via the
        ``default`` keyword will be compiled 'inline' into the statement and
        not pre-executed.  This means that their values will not be available
        in the dictionary returned from
        :meth:`_engine.CursorResult.last_updated_params`.

        .. versionchanged:: 1.4 the :paramref:`_expression.update.inline`
           parameter
           is now superseded by the :meth:`_expression.Update.inline` method.

        """
        self._inline = True 

def _unreserve_agent_slots(
    db_conn: SAConnection,
    sess_ctx: PendingSession,
    agent_ctx: AgentAllocationContext,
) -> None:
    # Un-reserve agent slots, using a separate db txn.
    query = (
        sa.select([agents.c.occupied_slots], for_update=True)
        .select_from(agents)
        .where(agents.c.id == agent_ctx.agent_id))
    current_occupied_slots = await db_conn.scalar(query)
    query = (
        sa.update(agents)
        .values({
            'occupied_slots': current_occupied_slots - sess_ctx.requested_slots
        })
        .where(agents.c.id == agent_ctx.agent_id))
    await db_conn.execute(query) 

def isolation_read_sa_transaction(conn, conn2):
    await conn.execute(sa.insert(users).values(id=1, name='test1'))
    t1 = await conn.begin()

    where = users.c.id == 1
    q_user = users.select().where(where)
    user = await (await conn.execute(q_user)).fetchone()

    assert await (await conn2.execute(q_user)).fetchone() == user

    await conn.execute(sa.update(users).values({'name': 'name2'}).where(where))

    t2 = await conn2.begin()
    assert await (await conn2.execute(q_user)).fetchone() == user

    await t1.commit()

    await conn2.execute(sa.update(users).values(user).where(where))
    await t2.commit()

    assert await (await conn2.execute(q_user)).fetchone() == user

    await conn.execute(sa.delete(users))
    assert len(await (await conn.execute(users.select())).fetchall()) == 0 

def recalc_concurrency_used(db_conn: SAConnection, access_key: AccessKey) -> None:
    query = (
        sa.update(keypairs)
        .values(
            concurrency_used=(
                sa.select([sa.func.count(kernels.c.id)])
                .select_from(kernels)
                .where(
                    (kernels.c.access_key == access_key) &
                    (kernels.c.status.in_(USER_RESOURCE_OCCUPYING_KERNEL_STATUSES))
                )
                .as_scalar()
            ),
        )
        .where(keypairs.c.access_key == access_key)
    )
    await db_conn.execute(query) 

def read_only_read_sa_transaction(conn, deferrable):
    await conn.execute(sa.insert(users).values(id=1, name='test1'))
    t1 = await conn.begin(
        isolation_level='SERIALIZABLE',
        readonly=True,
        deferrable=deferrable
    )

    where = users.c.id == 1

    try:
        await conn.execute(sa.update(users).values({'name': 't'}).where(where))
    except InternalError as e:
        assert e.pgcode == '25006'

    await t1.commit()

    await conn.execute(sa.delete(users))
    assert len(await (await conn.execute(users.select())).fetchall()) == 0 

def set_kernel_status(self, kernel_id: KernelId,
                                status: KernelStatus,
                                reason: str = '', *,
                                db_conn: SAConnection = None):
        assert status != KernelStatus.TERMINATED, \
               'TERMINATED status update must be handled in ' \
               'mark_kernel_terminated()'
        data = {
            'status': status,
            'status_info': reason,
            'status_changed': datetime.now(tzutc()),
        }
        async with reenter_txn(self.dbpool, db_conn) as conn:
            query = (
                sa.update(kernels)
                .values(data)
                .where(kernels.c.id == kernel_id)
            )
            await conn.execute(query) 

def update_invitation(request: web.Request, params: Any) -> web.Response:
    '''
    Update sent invitation's permission. Other fields are not allowed to be updated.
    '''
    dbpool = request.app['dbpool']
    access_key = request['keypair']['access_key']
    inv_id = request.match_info['inv_id']
    perm = params['perm']
    log.info('VFOLDER.UPDATE_INVITATION (ak:{}, inv:{})', access_key, inv_id)
    async with dbpool.acquire() as conn:
        query = (sa.update(vfolder_invitations)
                   .values(permission=perm)
                   .where(vfolder_invitations.c.id == inv_id)
                   .where(vfolder_invitations.c.inviter == request['user']['email'])
                   .where(vfolder_invitations.c.state == VFolderInvitationState.PENDING))
        await conn.execute(query)
    resp = {'msg': f'vfolder invitation updated: {inv_id}.'}
    return web.json_response(resp, status=200) 

def reschedule(self, name):
            state = yield from self.get(name)
            if state.manual:
                raise ValueError("Can't reschedule")
            else:
                local_offset = pytz.timezone(state.timezone).utcoffset(dt.datetime.utcnow())
                cron = croniter(state.crontab, dt.datetime.utcnow() + local_offset)
                reschedule = cron.get_next(dt.datetime) - local_offset

            with (yield from self.db.engine) as conn:
                yield from conn.execute(update(
                    Job.__table__
                ).where(
                    Job.name==name
                ).values(
                    active=True,
                    scheduled=reschedule,
                )) 

def __init__(self, table, name):
        self.table = table
        self.name = name
        self.info = {}

        if name is not None:
            op = self.table.migration.operation
            with cnx(self.table.migration) as conn:
                columns = op.impl.dialect.get_columns(
                    conn, self.table.name, schema=table.schema)

            for c in columns:
                if c['name'] == name:
                    self.info.update(c)

            if not self.info:
                raise MigrationException(
                    "No column %r found on %r" % (name, self.table.name)) 

def touch_collection_replicas(collection_replicas, session=None):
    """
    Update the accessed_at timestamp of the given collection replicas.

    :param collection_replicas: the list of collection replicas.
    :param session: The database session in use.

    :returns: True, if successful, False otherwise.
    """

    now = datetime.utcnow()
    for collection_replica in collection_replicas:
        try:
            session.query(models.CollectionReplica).filter_by(scope=collection_replica['scope'], name=collection_replica['name'], rse_id=collection_replica['rse_id']).\
                update({'accessed_at': collection_replica.get('accessed_at') or now}, synchronize_session=False)
        except DatabaseError:
            return False

    return True 

def delete(request: web.Request, params: Any) -> web.Response:
    dbpool = request.app['dbpool']
    template_id = request.match_info['template_id']

    requester_access_key, owner_access_key = await get_access_key_scopes(request, params)
    log.info('DELETE (ak:{0}/{1})',
             requester_access_key, owner_access_key if owner_access_key != requester_access_key else '*')

    async with dbpool.acquire() as conn, conn.begin():
        query = (sa.select([session_templates.c.id])
                   .select_from(session_templates)
                   .where((session_templates.c.id == template_id) &
                          (session_templates.c.is_active)
                          ))
        result = await conn.scalar(query)
        if not result:
            raise TaskTemplateNotFound

        query = (sa.update(session_templates)
                   .values(is_active=False)
                   .where((session_templates.c.id == template_id)))
        result = await conn.execute(query)
        assert result.rowcount == 1

        return web.json_response({'success': True}) 

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 set_tombstone(rse_id, scope, name, tombstone=OBSOLETE, session=None):
    """
    Sets a tombstone on a replica.

    :param rse_id: ID of RSE.
    :param scope: scope of the replica DID.
    :param name: name of the replica DID.
    :param tombstone: the tombstone to set. Default is OBSOLETE
    :param session: database session in use.
    """
    stmt = update(models.RSEFileAssociation).where(and_(models.RSEFileAssociation.rse_id == rse_id, models.RSEFileAssociation.name == name, models.RSEFileAssociation.scope == scope,
                                                        ~session.query(models.ReplicaLock).filter_by(scope=scope, name=name, rse_id=rse_id).exists()))\
                                            .values(tombstone=tombstone)
    result = session.execute(stmt)
    if not result.rowcount:
        try:
            session.query(models.RSEFileAssociation).filter_by(scope=scope, name=name, rse_id=rse_id).one()
            raise exception.ReplicaIsLocked('Replica %s:%s on RSE %s is locked.' % (scope, name, get_rse_name(rse_id=rse_id, session=session)))
        except NoResultFound:
            raise exception.ReplicaNotFound('Replica %s:%s on RSE %s could not be found.' % (scope, name, get_rse_name(rse_id=rse_id, session=session))) 

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 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 take(self, name):
            state = yield from self.get(name)
            if state.active == True:
                raise ValueError("In progress already")

            with (yield from self.db.engine) as conn:
                if state.manual is False:
                    yield from self.reschedule(name)

                result = yield from conn.execute(update(
                    Job.__table__
                ).where(
                    Job.name==name
                ).values(
                    active=True
                )) 

def complete(self, name):
            state = yield from self.get(name)
            if state.active == False:
                raise ValueError("Done already!")

            with (yield from self.db.engine) as conn:
                yield from conn.execute(update(
                    Job.__table__
                ).where(
                    Job.name==name
                ).values(
                    active=False
                )) 

def reschedule_now(self, name):
            state = yield from self.get(name)
            with (yield from self.db.engine) as conn:
                yield from conn.execute(update(
                    Job.__table__
                ).where(
                    Job.name==name
                ).values(
                    active=False,
                    scheduled=dt.datetime.utcnow(),
                )) 

def _extra_froms(self):
        # TODO: this could be made memoized
        # if the memoization is reset on each generative call.
        froms = []
        seen = set([self.table])

        if self._whereclause is not None:
            for item in _from_objects(self._whereclause):
                if not seen.intersection(item._cloned_set):
                    froms.append(item)
                seen.update(item._cloned_set)

        return froms 

def where(self, whereclause):
        """return a new update() construct with the given expression added to
        its WHERE clause, joined to the existing clause via AND, if any.

        """
        if self._whereclause is not None:
            self._whereclause = and_(self._whereclause,
                                     _literal_as_text(whereclause))
        else:
            self._whereclause = _literal_as_text(whereclause) 

def apply_default_value(self, column):
        if column.default:
            execute = self.table.migration.conn.execute
            val = column.default.arg
            table = self.table.migration.metadata.tables[self.table.name]
            table.append_column(column)
            cname = getattr(table.c, column.name)
            if column.default.is_callable:
                Table = self.table.migration.metadata.tables['system_model']
                Column = self.table.migration.metadata.tables['system_column']
                j1 = join(Table, Column, Table.c.name == Column.c.model)
                query = select([Column.c.name]).select_from(j1)
                query = query.where(Column.c.primary_key.is_(True))
                query = query.where(Table.c.table == self.table.name)
                columns = [x[0] for x in execute(query).fetchall()]

                query = select([func.count()]).select_from(table)
                query = query.where(cname.is_(None))
                nb_row = self.table.migration.conn.execute(query).fetchone()[0]
                for offset in range(nb_row):
                    query = select(columns).select_from(table)
                    query = query.where(cname.is_(None)).limit(1)
                    res = execute(query).fetchone()
                    where = and_(
                        *[getattr(table.c, x) == res[x] for x in columns])
                    query = update(table).where(where).values(
                        {cname: val(None)})
                    execute(query)

            else:
                query = update(table).where(cname.is_(None)).values(
                    {cname: val})
                execute(query) 

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 test_rowcount_flag(self):
        metadata = self.metadata
        engine = engines.testing_engine(options={"enable_rowcount": True})
        assert engine.dialect.supports_sane_rowcount
        metadata.bind = engine
        t = Table("t1", metadata, Column("data", String(10)))
        metadata.create_all()
        r = t.insert().execute({"data": "d1"}, {"data": "d2"}, {"data": "d3"})
        r = t.update().where(t.c.data == "d2").values(data="d3").execute()
        eq_(r.rowcount, 1)
        r = t.delete().where(t.c.data == "d3").execute()
        eq_(r.rowcount, 2)
        r = t.delete().execution_options(enable_rowcount=False).execute()
        eq_(r.rowcount, -1)
        engine.dispose()
        engine = engines.testing_engine(options={"enable_rowcount": False})
        assert not engine.dialect.supports_sane_rowcount
        metadata.bind = engine
        r = t.insert().execute({"data": "d1"}, {"data": "d2"}, {"data": "d3"})
        r = t.update().where(t.c.data == "d2").values(data="d3").execute()
        eq_(r.rowcount, -1)
        r = t.delete().where(t.c.data == "d3").execute()
        eq_(r.rowcount, -1)
        r = t.delete().execution_options(enable_rowcount=True).execute()
        eq_(r.rowcount, 1)
        r.close()
        engine.dispose() 

def _engine(self, type_, **kw):
        dbapi = self._dbapi()
        kw.update(dict(module=dbapi, _initialize=False))
        engine = engines.testing_engine("firebird+%s://" % type_, options=kw)
        return engine 

def release_all_waiting_requests(rse_id, activity=None, account=None, direction='destination', session=None):
    """
    Release all waiting requests per destination RSE.

    :param rse_id:           The RSE id.
    :param activity:         The activity.
    :param account:          The account name whose requests to release.
    :param direction:        Direction if requests are grouped by source RSE or destination RSE.
    :param session:          The database session.
    """
    try:
        rowcount = 0

        query = session.query(models.Request)
        if direction == 'destination':
            query = query.filter_by(dest_rse_id=rse_id, state=RequestState.WAITING)
        elif direction == 'source':
            query = query.filter_by(src_rse_id=rse_id, state=RequestState.WAITING)

        if activity:
            query = query.filter_by(activity=activity)
        if account:
            query = query.filter_by(account=account)
        rowcount = query.update({'state': RequestState.QUEUED}, synchronize_session=False)
        return rowcount
    except IntegrityError as error:
        raise RucioException(error.args) 

def release_waiting_requests_per_free_volume(rse_id, volume=None, session=None):
    """
    Release waiting requests if they fit in available transfer volume. If the DID of a request is attached to a dataset, the volume will be checked for the whole dataset as all requests related to this dataset will be released.

    :param rse_id:  The destination RSE id.
    :param volume:  The maximum volume in bytes that should be transfered.
    :param session: The database session.
    """

    dialect = session.bind.dialect.name
    sum_volume_active_subquery = None
    if dialect == 'mysql' or dialect == 'sqlite':
        sum_volume_active_subquery = session.query(func.ifnull(func.sum(models.Request.bytes), 0).label('sum_bytes'))\
                                            .filter(and_(or_(models.Request.state == RequestState.SUBMITTED, models.Request.state == RequestState.QUEUED),
                                                         models.Request.dest_rse_id == rse_id))
    elif dialect == 'postgresql':
        sum_volume_active_subquery = session.query(func.coalesce(func.sum(models.Request.bytes), 0).label('sum_bytes'))\
                                            .filter(and_(or_(models.Request.state == RequestState.SUBMITTED, models.Request.state == RequestState.QUEUED),
                                                         models.Request.dest_rse_id == rse_id))
    elif dialect == 'oracle':
        sum_volume_active_subquery = session.query(func.nvl(func.sum(models.Request.bytes), 0).label('sum_bytes'))\
                                            .filter(and_(or_(models.Request.state == RequestState.SUBMITTED, models.Request.state == RequestState.QUEUED),
                                                         models.Request.dest_rse_id == rse_id))
    sum_volume_active_subquery = sum_volume_active_subquery.subquery()

    grouped_requests_subquery, filtered_requests_subquery = create_base_query_grouped_fifo(rse_id, filter_by_rse='destination', session=session)

    cumulated_volume_subquery = session.query(grouped_requests_subquery.c.name,
                                              grouped_requests_subquery.c.scope,
                                              func.sum(grouped_requests_subquery.c.volume).over(order_by=grouped_requests_subquery.c.oldest_requested_at).label('cum_volume'))\
                                       .filter(grouped_requests_subquery.c.volume <= volume - sum_volume_active_subquery.c.sum_bytes)\
                                       .subquery()

    cumulated_volume_subquery = session.query(filtered_requests_subquery.c.id)\
                                       .join(cumulated_volume_subquery, and_(filtered_requests_subquery.c.dataset_name == cumulated_volume_subquery.c.name, filtered_requests_subquery.c.dataset_scope == cumulated_volume_subquery.c.scope))\
                                       .filter(cumulated_volume_subquery.c.cum_volume <= volume - sum_volume_active_subquery.c.sum_bytes)\
                                       .subquery()

    statement = update(models.Request).where(models.Request.id.in_(cumulated_volume_subquery)).values(state=RequestState.QUEUED)
    amount_released_requests = session.execute(statement).rowcount
    return amount_released_requests 

def test_update_returning(self):
        dialect = postgresql.dialect()
        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String(128)),
            column("description", String(128)),
        )
        u = update(table1, values=dict(name="foo")).returning(
            table1.c.myid, table1.c.name
        )
        self.assert_compile(
            u,
            "UPDATE mytable SET name=%(name)s "
            "RETURNING mytable.myid, mytable.name",
            dialect=dialect,
        )
        u = update(table1, values=dict(name="foo")).returning(table1)
        self.assert_compile(
            u,
            "UPDATE mytable SET name=%(name)s "
            "RETURNING mytable.myid, mytable.name, "
            "mytable.description",
            dialect=dialect,
        )
        u = update(table1, values=dict(name="foo")).returning(
            func.length(table1.c.name)
        )
        self.assert_compile(
            u,
            "UPDATE mytable SET name=%(name)s "
            "RETURNING length(mytable.name) AS length_1",
            dialect=dialect,
        ) 

def serializable_sa_transaction(conn, conn2):
    isolation_level = 'SERIALIZABLE'
    await conn.execute(sa.insert(users).values(id=1, name='test1'))
    t1 = await conn.begin(isolation_level=isolation_level)

    where = users.c.id == 1
    q_user = users.select().where(where)
    user = await (await conn.execute(q_user)).fetchone()

    assert await (await conn2.execute(q_user)).fetchone() == user

    await conn.execute(sa.update(users).values({'name': 'name2'}).where(where))

    t2 = await conn2.begin(isolation_level=isolation_level)
    assert await (await conn2.execute(q_user)).fetchone() == user

    await t1.commit()

    try:
        await conn2.execute(users.insert().values({'id': 2, 'name': 'test'}))
    except TransactionRollbackError as e:
        assert e.pgcode == '40001'

    try:
        await conn2.execute(users.update().values({'name': 't'}).where(where))
    except InternalError as e:
        assert e.pgcode == '25P02'

    await t2.commit()

    user = dict(await (await conn2.execute(q_user)).fetchone())
    assert user == {'name': 'name2', 'id': 1}

    await conn.execute(sa.delete(users))
    assert len(await (await conn.execute(users.select())).fetchall()) == 0 

def test_update_array_element(self):
        m = MetaData()
        t = Table("t", m, Column("data", postgresql.ARRAY(Integer)))
        self.assert_compile(
            t.update().values({t.c.data[5]: 1}),
            "UPDATE t SET data[%(data_1)s]=%(param_1)s",
            checkparams={"data_1": 5, "param_1": 1},
        )