sqlalchemy.update.where

Here are the examples of the python api sqlalchemy.update.where taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

42 Examples 7

3 Source : db_manager.py
with MIT License
from arch4ngel

    def disable_username_records(self, container):
        '''Set the actionable attribute on each record in the container
        to False, removing them from further guesses.
        '''

        self.main_db_sess.execute(
            update(sql.Username)
                .where(
                    sql.Username.value.in_(container),
                    sql.Username.actionable == True)
                .values(actionable = False))

        self.main_db_sess.commit()

    def enable_username_records(self, container):

3 Source : db_manager.py
with MIT License
from arch4ngel

    def enable_username_records(self, container):
        '''Set the actionable attribute on each record in the container
        to True, ensuring they will be targeted for further guesses.
        '''

        self.main_db_sess.execute(
            update(sql.Username)
                .where(
                    sql.Username.value.in_(container),
                    sql.Username.actionable == False)
                .values(actionable = True))

        self.main_db_sess.commit()

    # ===========================
    # PASSWORD MANAGEMENT METHODS
    # ===========================

    def insert_password_records(self, container,

3 Source : setting_manager.py
with MIT License
from being24

    async def set_black_list(self, server_id: int):
        """ブラックリストのサーバーを追加する関数

        Args:
            server_id (int): ブラックリストのサーバーのID
        """
        async with AsyncSession(engine) as session:
            async with session.begin():
                stmt = update(GuildSettingDB).where(
                    GuildSettingDB.guild_id == server_id).values(
                    black_server=True)
                await session.execute(stmt)

    async def remove_black_list(self, server_id: int):

3 Source : setting_manager.py
with MIT License
from being24

    async def remove_black_list(self, server_id: int):
        """ブラックリストのサーバーを削除する関数

        Args:
            server_id (int): ブラックリストのサーバーのID
        """
        async with AsyncSession(engine) as session:
            async with session.begin():
                stmt = update(GuildSettingDB).where(
                    GuildSettingDB.guild_id == server_id).values(
                    black_server=False)
                await session.execute(stmt)

    async def is_dispand(self, guild_id: int) -> bool:

3 Source : setting_manager.py
with MIT License
from being24

    async def set_dispand(self, guild_id: int, tf: bool) -> None:
        """dispand(メッセージ展開)を設定する関数

        Args:
            guild_id (int): サーバーID
            tf (bool): 有効/無効
        """
        async with AsyncSession(engine) as session:
            async with session.begin():
                stmt = update(GuildSettingDB).where(
                    GuildSettingDB.guild_id == guild_id).values(dispander=tf)
                await session.execute(stmt)


if __name__ == "__main__":

3 Source : passivessldb.py
with GNU Affero General Public License v3.0
from D4-project

    def setRSAPrime(self, match):
        """ set a prime number once recovered """
        set = update(self.pkTable).where(self.pkTable.c.modulus==str(match[0])).values(P=str(match[1]), misp=True)
        self.conn.execute(set)

    def pushResults(self, processid):

3 Source : dmdb.py
with GNU General Public License v3.0
from Dark-Sword-22

async def task_status_daemon(engine, table, qid):
    await asyncio.sleep(180)
    async_session = sessionmaker(engine, expire_on_commit=True, class_=AsyncSession)
    async with async_session() as session:
        item = (await session.execute(select(table).where(table.id == qid))).scalars().first()
        if item:
            if item.status   <   3:
                item.fail_count += 1
                if item.fail_count >= _MAXRETRY:
                    item.status = 4
                else:
                    item.status = 0
        # table finished?
        unfinished_items = (await session.execute(select(table).where(table.status  <  3).limit(1))).scalars().all()
        if not unfinished_items:
            stmt = update(BVStatus).where(BVStatus.bvid == select(BVRelations.bvid).where(BVRelations.tname == table.__tablename__)).values(finished = True)
        await session.commit()

class DAL:

3 Source : dals.py
with MIT License
from engineer-man

    async def update_spam_rule(self, id: str, member: str, regex: str):
        """Update existing spam rule by ID"""
        query = update(Spam).where(Spam.id == id)
        query = query.values(member=member, regex=regex)
        query.execution_options(synchronize_session='fetch')
        await self.db_session.execute(query)


class SpammerDAL():

3 Source : topic_oracle_template.py
with MIT License
from Indexical-Metrics-Measure-Advisory

    def topic_data_update_one(self, id_: str, one: any, topic_name: str):
        table_name = build_collection_name(topic_name)
        table = self.get_topic_table_by_name(table_name)
        stmt = update(table).where(eq(table.c['id_'], id_))
        one_dict = capital_to_lower(convert_to_dict(one))
        value = self.build_oracle_updates_expression(table, one_dict, "update")
        stmt = stmt.values(value)
        with self.engine.begin() as conn:
            result = conn.execute(stmt)
        return result.rowcount

    def topic_data_update_one_with_version(self, id_: str, version_: int, one: any, topic_name: str):

3 Source : topic_oracle_template.py
with MIT License
from Indexical-Metrics-Measure-Advisory

    def topic_data_update_one_with_version(self, id_: str, version_: int, one: any, topic_name: str):
        table_name = build_collection_name(topic_name)
        table = self.get_topic_table_by_name(table_name)
        stmt = update(table).where(and_(eq(table.c['id_'], id_), eq(table.c['version_'], version_)))
        one_dict = capital_to_lower(convert_to_dict(one))
        value = self.build_oracle_updates_expression(table, one_dict, "update")
        stmt = stmt.values(value)
        with self.engine.begin() as conn:
            result = conn.execute(stmt)
        if result.rowcount == 0:
            raise OptimisticLockError("Optimistic lock error")

    def topic_data_update_(self, query_dict, instances: list, topic_name):

3 Source : topic_oracle_template.py
with MIT License
from Indexical-Metrics-Measure-Advisory

    def topic_data_update_(self, query_dict, instances: list, topic_name):
        table_name = build_collection_name(topic_name)
        table = self.get_topic_table_by_name(table_name)
        stmt = (update(table).
                where(self.build_oracle_where_expression(table, query_dict)))
        values = []
        for instance in instances:
            one_dict = capital_to_lower(convert_to_dict(instance))
            value = self.build_oracle_updates_expression(table, one_dict,"update")
            values.append(value)
        stmt = stmt.values(values)
        with self.engine.begin() as conn:
            result = conn.execute(stmt)

    def topic_data_find_by_id(self, id_: str, topic_name: str) -> any:

3 Source : db.py
with MIT License
from KoalaBotUK

def update_guild_welcome_message(guild_id, new_message: str):
    """
    Update guild welcome message for a given guild

    :param guild_id: Discord guild ID for a given server
    :param new_message: The new guild welcome message to be set
    """
    with session_manager() as session:
        session.execute(update(GuildWelcomeMessages)
                        .where(GuildWelcomeMessages.guild_id == guild_id)
                        .values(welcome_message=new_message))
        session.commit()
    return new_message


def remove_guild_welcome_message(guild_id):

3 Source : test_db.py
with MIT License
from KoalaBotUK

async def test_remove_team_from_ta_deletes_messages(twitch_alert_db_manager_tables):
    await test_update_team_members(twitch_alert_db_manager_tables)

    test = update(UserInTwitchTeam)\
        .where(and_(UserInTwitchTeam.team_twitch_alert_id == 604,
                    UserInTwitchTeam.twitch_username == 'monstercat')).values(message_id=1)
    with session_manager() as session:
        session.execute(test)
        session.commit()

    with mock.patch.object(TwitchAlertDBManager, 'delete_message') as mock1:
        await twitch_alert_db_manager_tables.remove_team_from_ta(605, "monstercat")
    mock1.assert_called_with(1, 605)


@pytest.mark.asyncio()

3 Source : usermaint.py
with GNU General Public License v2.0
from makedeb

def _main():
    limit_to = time.utcnow() - 86400 * 7

    update_ = (
        update(User).where(User.LastLogin   <   limit_to).values(LastLoginIPAddress=None)
    )
    db.get_session().execute(update_)

    update_ = (
        update(User)
        .where(User.LastSSHLogin  <  limit_to)
        .values(LastSSHLoginIPAddress=None)
    )
    db.get_session().execute(update_)


def main():

3 Source : asset_service.py
with Apache License 2.0
from mensch272

    def update_asset_path(self, asset: Asset):
        self.session.execute(
            update(Asset).where(Asset.id == asset.id).values(path=asset.path)
        )
        self.session.commit()

    def delete_assets_of_novel(self, novel: Novel):

3 Source : novel_service.py
with Apache License 2.0
from mensch272

    def update_content(self, chapter_dto: ChapterDTO):
        stmt = (
            update(Chapter)
            .where(Chapter.url == chapter_dto.url)
            .values(content=chapter_dto.content)
        )
        self.session.execute(stmt)
        self.session.commit()

    def add_url(self, novel: Novel, url: str):

3 Source : novel_service.py
with Apache License 2.0
from mensch272

    def delete_content(self, novel: Novel):
        volumes = [v.id for v in self.get_volumes(novel)]
        stmt = (
            update(Chapter).where(Chapter.volume_id.in_(volumes)).values(content=None)
        )

        self.session.execute(stmt)
        self.session.commit()

    def delete_novel(self, novel: Novel):

3 Source : test_update.py
with MIT License
from sqlalchemy

    def test_correlated_update_four(self):
        table1 = self.tables.mytable
        table2 = self.tables.myothertable

        # test a non-correlated WHERE clause
        s = select(table2.c.othername).where(table2.c.otherid == 7)
        u = update(table1).where(table1.c.name == s.scalar_subquery())
        self.assert_compile(
            u,
            "UPDATE mytable SET myid=:myid, name=:name, "
            "description=:description WHERE mytable.name = "
            "(SELECT myothertable.othername FROM myothertable "
            "WHERE myothertable.otherid = :otherid_1)",
        )

    def test_correlated_update_five(self):

0 Source : _sqlmodel.py
with Apache License 2.0
from amisadmin

    def route_update(self) -> Callable:
        async def route(request: Request,
                        item_id: List[str] = Depends(parser_item_id),
                        data: self.schema_update = Body(...),  # type: ignore
                        session: AsyncSession = Depends(self.session_factory)
                        ):
            if not await self.has_update_permission(request, item_id, data):
                return self.error_no_router_permission(request)
            stmt = update(self.model).where(self.pk.in_(item_id))
            data = await self.on_update_pre(request, data)
            if not data:
                return self.error_data_handle(request)
            stmt = stmt.values(data)
            result = await session.execute(stmt)
            if result.rowcount:  # type: ignore
                await session.commit()
                return BaseApiOut(data=result.rowcount)  # type: ignore

        return route

    @property

0 Source : db_manager.py
with MIT License
from arch4ngel

    def manage_priorities(self, usernames:list=None,
            passwords:list=None, prioritize:bool=False,
            logger=None):
        '''Prioritize or deprioritize database values.

        Args:
            usernames: A list of string username values.
            passwords: A list of string password values.
            prioritize: Boolean determining if the values should be
              prioritized or deprioritized.
        '''

        if not usernames and not passwords:
            raise ValueError('usernames or passwords required')

        usernames = usernames if usernames != None else []
        passwords = passwords if passwords != None else []

        if usernames:

            # Manage username priorities
            self.main_db_sess.execute(
                update(sql.Username)
                    .where(
                        sql.Username.value.in_(usernames))
                    .values(priority = True))

        if passwords:

            # Manage password priorities
            self.main_db_sess.execute(
                update(sql.Password)
                    .where(
                        sql.Password.value.in_(passwords))
                    .values(priority = True))

        self.main_db_sess.commit()

        # This method commits :)
        self.sync_lookup_tables(logger=logger)

    def manage_db_values(self, insert=True, usernames=None,

0 Source : passivessldb.py
with GNU Affero General Public License v3.0
from D4-project

    def setPublished(self, match):
        """ touch published date after publishing """
        set = update(self.pkTable).where(and_(self.pkTable.c.modulus==str(match[0]), self.pkTable.c.modulus==True)).values(published=datetime.datetime.now())
        self.conn.execute(set)

0 Source : dmdb.py
with GNU General Public License v3.0
from Dark-Sword-22

async def clean_task_daemon(engine, msg_core):
    while True:
        async_session = sessionmaker(engine, expire_on_commit=True, class_=AsyncSession)
        async with async_session() as session:
            stmt = select(BVRelations.tname).where(BVRelations.bvid==BVStatus.bvid).where(BVStatus.finished==False).order_by(BVStatus.create_time.desc()).limit(44)
            table_names_to_check = set((await session.execute(stmt)).scalars().all())
            table_to_check = AbstractTable.__subclasses__() | Filter(lambda x: x.__tablename__ in table_names_to_check) | list
        for table in table_to_check:
            async with GlobLock:
                async_session = sessionmaker(engine, expire_on_commit=True, class_=AsyncSession)
                async with async_session() as session:
                    hit = (await session.execute(select(table).where(table.status   <   3).limit(1))).scalars().all()
                    if not hit:
                        stmt = update(BVStatus).where(BVStatus.bvid == select(BVRelations.bvid).where(BVRelations.tname == table.__tablename__).limit(1)).values(finished = True)
                        await session.execute(stmt, execution_options={"synchronize_session": 'fetch'})
                        await session.commit()
                        continue
                    latest_update = (await session.execute(select(table.id).where(table.status >= 3).order_by(table.cmt_time.desc()).limit(1))).scalars().first()
                    if latest_update:
                        stmt = select(table).where(table.id == 1 and table.id  <  max(0, last_update_time-500))
                        archives = (await session.execute(stmt)).scalars().all()
                        if archives:
                            for item in archives:
                                item.fail_count += 1
                                if item.fail_count >= _MAXRETRY:
                                    item.status = 4
                                else:
                                    item.status = 0
                    await session.commit()
        await asyncio.sleep(random.randint(900, 1500))

async def task_status_daemon(engine, table, qid):

0 Source : dmdb.py
with GNU General Public License v3.0
from Dark-Sword-22

    async def get_archive_earliest(self, mode: str, bvid: str):
        if mode == 'specified':
            table = self.table_porj.get(bvid)
            if bvid=='None':
                return -4
            if not table:
                stmt = select(BVRelations).where(BVRelations.bvid == bvid).order_by(BVRelations.tname.desc()).limit(1)
                rlitem = (await self.session.execute(stmt)).scalars().first()
                if not rlitem:
                    # 错误的bvid
                    return -4
                table = Base.get_model_by_table_name(rlitem.tname)
                if table == None:
                    return -4
                self.table_porj[bvid] = table
        else:
            # mode = 'auto'
            stmt = select(BVRelations).filter(BVStatus.finished==False).filter(BVStatus.bvid==BVRelations.bvid).order_by(BVStatus.create_time.desc()).limit(1)
            rlitem = (await self.session.execute(stmt)).scalars().first()
            if rlitem == None:
                # 所有工作均已完成
                return True
            table = Base.get_model_by_table_name(rlitem.tname)
            if table == None:
                return -4
            bvid = rlitem.bvid
            self.table_porj[bvid] = table

        stmt = select(table).where(table.status == 0).order_by(table.cmt_time).limit(44)
        item_set = (await self.session.execute(stmt)).scalars().all()
        if item_set:
            item = random.sample(item_set, 1)[0]
            return (
                item.id, 
                item.send_time, 
                item.cid, 
                item.bvid, 
                item.content, 
                self.create_token(f"{item.send_time}-{item.rnd}"),
                1 # 补位为0表示无任务
            ) 
        else:
            stmt = select(table).where(table.status == 1).order_by(table.cmt_time).limit(88)
            item_set = (await self.session.execute(stmt)).scalars().all()
            if len(item_set) > 10:
                item = random.sample(item_set, 1)[0]
                item.fail_count += 1
                if item.fail_count >= _MAXRETRY:
                    item.status = 4
                else:
                    item.status = 0
            else:
                # 确实没有任务了
                for item in item_set:
                    item.status = 4
                    item.fail_count = _MAXRETRY
                await self.session.execute(update(BVStatus).where(BVStatus.bvid == bvid).values(finished=True))
            await self.session.commit()
            return True

    async def client_confirm_quest(self, bvid: str, qid: int, token: str):

0 Source : dmdb.py
with GNU General Public License v3.0
from Dark-Sword-22

    async def client_declare_succeeded(self, bvid: str, qid: int, token: str, wdcr: str):
        table = self.table_porj.get(bvid)
        if not table:
            return -2
        stmt = select(table).where(table.id == qid)
        item = (await self.session.execute(stmt)).scalars().first()
        if item and item.status == 1:
            if not compare_digest(self.create_token(f"{item.send_time}-{item.rnd}"), token):
                return -3
            else:
                async with ClientSession() as http_session:
                    '''
                    目前实际使用中出现约0.8%概率的客户端发送成功,服务器端获取不到的情况。会导致弹幕刷双份,体验一般。
                    不知道产生原因,可能是cdn刷新导致,挂上更新延迟也没用。干脆停了看看效果吧
                    '''
                    # for _ in range(3):
                    #     res = await determine_if_cmt_public(http_session, item.send_time, item.cid, item.content)
                    #     if res:break
                    #     if _   <   2: await asyncio.sleep(3)
                    # else:
                    #     res = False
                    res = True
                    if res:
                        item.status = 3
                        # table finish?
                        table_finish = await self.check_finished(table)
                        if table_finish:
                            stmt = update(BVStatus).where(BVStatus.bvid == bvid).values(finished=True)
                            await self.session.execute(stmt)
                        stmt = select(Contributors).where(Contributors.uname == wdcr).limit(1)
                        person = (await self.session.execute(stmt)).scalars().first()
                        _ctime = datetime.datetime.now() + datetime.timedelta(seconds=3600)
                        if person:
                            person.total_count += 1
                            person.total_chars += len(item.content)
                            person.last_update_time = _ctime
                            await self.session.commit()
                        else:
                            self.session.add(Contributors(uname=wdcr, last_update_time=_ctime))
                            await self.session.commit()
                        return True
                    else:
                        item.fail_count = item.fail_count + 1
                        item.status = 0
                        if item.fail_count >= _MAXRETRY:
                            item.status = 4
                        table_finish = await self.check_finished(table)
                        if table_finish:
                            stmt = update(BVStatus).where(BVStatus.bvid == bvid).values(finished=True)
                            await self.session.execute(stmt)
                        await self.session.commit()
                        return -5
        else:
            return -1

    async def check_finished(self, table):

0 Source : revision.py
with MIT License
from hetida

def update_tr(
    session: SQLAlchemySession, transformation_revision: TransformationRevision
) -> None:
    try:
        db_model = transformation_revision.to_orm_model()
        session.execute(
            update(TransformationRevisionDBModel)
            .where(TransformationRevisionDBModel.id == db_model.id)
            .values(
                revision_group_id=db_model.revision_group_id,
                name=db_model.name,
                description=db_model.description,
                category=db_model.category,
                version_tag=db_model.version_tag,
                state=db_model.state,
                type=db_model.type,
                documentation=db_model.documentation,
                workflow_content=db_model.workflow_content,
                component_code=db_model.component_code,
                io_interface=db_model.io_interface,
                test_wiring=db_model.test_wiring,
                released_timestamp=db_model.released_timestamp,
                disabled_timestamp=db_model.disabled_timestamp,
            )
        )

    except IntegrityError as e:
        msg = (
            f"Integrity Error while trying to update "
            f"transformation revision with id {transformation_revision.id}.\n"
            f"Error was:\n{str(e)}"
        )
        logger.error(msg)
        raise DBIntegrityError(msg) from e


def pass_on_deprecation(session: SQLAlchemySession, transformation_id: UUID) -> None:

0 Source : cog.py
with MIT License
from KoalaBotUK

    async def loop_check_live(self):
        """
        A loop that continually checks the live status of users and
        sends alerts when online, removing them when offline
        :return:
        """
        start = time.time()
        # logger.info("TwitchAlert: User Loop Started")
        sql_find_users = select(UserInTwitchAlert.twitch_username) \
            .join(TwitchAlerts, UserInTwitchAlert.channel_id == TwitchAlerts.channel_id) \
            .join(GuildExtensions, TwitchAlerts.guild_id == GuildExtensions.guild_id) \
            .where(or_(GuildExtensions.extension_id == 'TwitchAlert', GuildExtensions.extension_id == 'All'))
        # "SELECT twitch_username " \
        #              "FROM UserInTwitchAlert " \
        #              "JOIN TwitchAlerts TA on UserInTwitchAlert.channel_id = TA.channel_id " \
        #              "JOIN (SELECT extension_id, guild_id FROM GuildExtensions " \
        #              "WHERE extension_id = 'twitch_alert' OR extension_id = 'All') GE on TA.guild_id = GE.guild_id;"
        with session_manager() as session:
            users = session.execute(sql_find_users).all()

        usernames = [str.lower(user.twitch_username) for user in users]

        if not usernames:
            return

        user_streams = self.ta_database_manager.twitch_handler.get_streams_data(usernames)
        if user_streams is None:
            return

        # Deals with online streams
        for streams_details in user_streams:
            try:
                if streams_details.get('type') == "live":
                    current_username = str.lower(streams_details.get("user_login"))
                    old_len = len(usernames)
                    usernames.remove(current_username)
                    if len(usernames) == old_len:
                        logger.error(f"TwitchAlert: {streams_details.get('user_login')} not found in the user list")

                    sql_find_message_id = select(UserInTwitchAlert.channel_id,
                                                 UserInTwitchAlert.message_id,
                                                 UserInTwitchAlert.custom_message,
                                                 TwitchAlerts.default_message) \
                        .join(TwitchAlerts, UserInTwitchAlert.channel_id == TwitchAlerts.channel_id) \
                        .join(GuildExtensions, TwitchAlerts.guild_id == GuildExtensions.guild_id) \
                        .where(and_(and_(or_(GuildExtensions.extension_id == 'TwitchAlert',
                                             GuildExtensions.extension_id == 'All'),
                                         UserInTwitchAlert.twitch_username == current_username),
                                    UserInTwitchAlert.message_id == null()))
                    # "SELECT UserInTwitchAlert.channel_id, message_id, custom_message, default_message " \
                    # "FROM UserInTwitchAlert " \
                    # "JOIN TwitchAlerts TA on UserInTwitchAlert.channel_id = TA.channel_id " \
                    # "JOIN (SELECT extension_id, guild_id FROM GuildExtensions " \
                    # "WHERE extension_id = 'TwitchAlert' " \
                    # "  OR extension_id = 'All') GE on TA.guild_id = GE.guild_id " \
                    # "WHERE twitch_username = ?;"

                    results = session.execute(sql_find_message_id).all()

                    new_message_embed = None

                    for result in results:
                        channel_id = result.channel_id
                        message_id = result.message_id
                        custom_message = result.custom_message
                        channel_default_message = result.default_message

                        channel = self.bot.get_channel(id=channel_id)
                        try:
                            # If no Alert is posted
                            if message_id is None:
                                if new_message_embed is None:
                                    if custom_message is not None:
                                        message = custom_message
                                    else:
                                        message = channel_default_message

                                    new_message_embed = await self.create_alert_embed(streams_details, message)

                                if new_message_embed is not None and channel is not None:
                                    new_message = await channel.send(embed=new_message_embed)
                                    sql_update_message_id = update(UserInTwitchAlert).where(and_(
                                        UserInTwitchAlert.channel_id == channel_id,
                                        UserInTwitchAlert.twitch_username == current_username)) \
                                        .values(message_id=new_message.id)
                                    session.execute(sql_update_message_id)
                                    session.commit()
                        except discord.errors.Forbidden as err:
                            logger.warning(f"TwitchAlert: {err}  Name: {channel} ID: {channel.id}")
                            sql_remove_invalid_channel = delete(TwitchAlerts).where(
                                TwitchAlerts.channel_id == channel.id)
                            session.execute(sql_remove_invalid_channel)
                            session.commit()

            except Exception as err:
                logger.error(f"TwitchAlert: User Loop error {err}")

        # Deals with remaining offline streams
        await self.ta_database_manager.delete_all_offline_streams(usernames)
        time_diff = time.time() - start
        if time_diff > 5:
            logger.warning(f"TwitchAlert: User Loop Finished in > 5s | {time_diff}s")

    async def create_alert_embed(self, stream_data, message):

0 Source : cog.py
with MIT License
from KoalaBotUK

    async def loop_check_team_live(self):
        """
        A loop to repeatedly send messages if a member of a team is live, and remove it when they are not

        :return:
        """
        start = time.time()
        # logger.info("TwitchAlert: Team Loop Started")

        # Select all twitch users & team names where TwitchAlert is enabled
        sql_select_team_users = select(UserInTwitchTeam.twitch_username, TeamInTwitchAlert.twitch_team_name) \
            .join(TeamInTwitchAlert, UserInTwitchTeam.team_twitch_alert_id == TeamInTwitchAlert.team_twitch_alert_id) \
            .join(TwitchAlerts, TeamInTwitchAlert.channel_id == TwitchAlerts.channel_id) \
            .join(GuildExtensions, TwitchAlerts.guild_id == GuildExtensions.guild_id) \
            .where(or_(GuildExtensions.extension_id == 'TwitchAlert', GuildExtensions.extension_id == 'All'))
        with session_manager() as session:
            users_and_teams = session.execute(sql_select_team_users).all()
        # sql_select_team_users = "SELECT twitch_username, twitch_team_name " \
        #                         "FROM UserInTwitchTeam " \
        #                         "JOIN TeamInTwitchAlert TITA " \
        #                         "  ON UserInTwitchTeam.team_twitch_alert_id = TITA.team_twitch_alert_id " \
        #                         "JOIN TwitchAlerts TA on TITA.channel_id = TA.channel_id " \
        #                         "JOIN (SELECT extension_id, guild_id FROM GuildExtensions " \
        #                         "WHERE extension_id = 'TwitchAlert' " \
        #                         "  OR extension_id = 'All') GE on TA.guild_id = GE.guild_id "

        usernames = [str.lower(user.twitch_username) for user in users_and_teams]

        if not usernames:
            return

        streams_data = self.ta_database_manager.twitch_handler.get_streams_data(usernames)

        if streams_data is None:
            return
        # Deals with online streams
        for stream_data in streams_data:
            try:
                if stream_data.get('type') == "live":
                    current_username = str.lower(stream_data.get("user_login"))
                    old_len = len(usernames)
                    usernames.remove(current_username)
                    if len(usernames) == old_len:
                        logger.error(f"TwitchAlert: {stream_data.get('user_login')} not found in the user teams list")
                    sql_find_message_id = select(TeamInTwitchAlert.channel_id,
                                                 UserInTwitchTeam.message_id,
                                                 TeamInTwitchAlert.team_twitch_alert_id,
                                                 TeamInTwitchAlert.custom_message,
                                                 TwitchAlerts.default_message) \
                        .join(TeamInTwitchAlert,
                              UserInTwitchTeam.team_twitch_alert_id == TeamInTwitchAlert.team_twitch_alert_id) \
                        .join(TwitchAlerts, TeamInTwitchAlert.channel_id == TwitchAlerts.channel_id) \
                        .join(GuildExtensions, TwitchAlerts.guild_id == GuildExtensions.guild_id) \
                        .where(and_(and_(or_(GuildExtensions.extension_id == 'TwitchAlert',
                                             GuildExtensions.extension_id == 'All'),
                                         UserInTwitchTeam.twitch_username == current_username),
                                    UserInTwitchTeam.message_id == null()))

                    # sql_find_message_id = """
                    # SELECT TITA.channel_id, UserInTwitchTeam.message_id, TITA.team_twitch_alert_id, custom_message,
                    #   default_message
                    # FROM UserInTwitchTeam
                    # JOIN TeamInTwitchAlert TITA on UserInTwitchTeam.team_twitch_alert_id = TITA.team_twitch_alert_id
                    # JOIN TwitchAlerts TA on TITA.channel_id = TA.channel_id
                    # JOIN (SELECT extension_id, guild_id
                    #       FROM GuildExtensions
                    #       WHERE extension_id = 'TwitchAlert' OR extension_id = 'All') GE ON TA.guild_id = GE.guild_id
                    # WHERE twitch_username = ?"""

                    results = session.execute(sql_find_message_id).all()

                    new_message_embed = None

                    for result in results:
                        channel_id = result.channel_id
                        message_id = result.message_id
                        team_twitch_alert_id = result.team_twitch_alert_id
                        custom_message = result.custom_message
                        channel_default_message = result.default_message
                        channel: discord.TextChannel = self.bot.get_channel(id=channel_id)
                        try:
                            # If no Alert is posted
                            if message_id is None:
                                if new_message_embed is None:
                                    if custom_message is not None:
                                        message = custom_message
                                    else:
                                        message = channel_default_message

                                    new_message_embed = await self.create_alert_embed(stream_data, message)

                                if new_message_embed is not None and channel is not None:
                                    new_message = await channel.send(embed=new_message_embed)

                                    sql_update_message_id = update(UserInTwitchTeam) \
                                        .where(and_(UserInTwitchTeam.team_twitch_alert_id == team_twitch_alert_id,
                                                    UserInTwitchTeam.twitch_username == current_username)) \
                                        .values(message_id=new_message.id)
                                    session.execute(sql_update_message_id)
                                    session.commit()
                        except discord.errors.Forbidden as err:
                            logger.warning(f"TwitchAlert: {err}  Name: {channel} ID: {channel.id}")
                            sql_remove_invalid_channel = delete(TwitchAlerts).where(
                                TwitchAlerts.channel_id == channel.id)
                            session.execute(sql_remove_invalid_channel)
                            session.commit()
            except Exception as err:
                logger.error(f"TwitchAlert: Team Loop error {err}")

        # Deals with remaining offline streams
        await self.ta_database_manager.delete_all_offline_team_streams(usernames)
        time_diff = time.time() - start
        if time_diff > 5:
            logger.warning(f"TwitchAlert: Teams Loop Finished in > 5s | {time_diff}s")


def setup(bot: koalabot) -> None:

0 Source : test_db.py
with MIT License
from KoalaBotUK

async def test_delete_all_offline_streams_team(twitch_alert_db_manager_tables, bot: discord.ext.commands.Bot):
    await test_update_all_teams_members(twitch_alert_db_manager_tables)

    sql_add_message = update(UserInTwitchTeam).where(and_(or_(
        UserInTwitchTeam.team_twitch_alert_id == 614, UserInTwitchTeam.team_twitch_alert_id == 616),
        UserInTwitchTeam.twitch_username == 'monstercat')).values(message_id=1)
    with session_manager() as session:
        session.execute(sql_add_message)
        session.commit()

        await twitch_alert_db_manager_tables.delete_all_offline_team_streams(['monstercat'])

        sql_select_messages = select(UserInTwitchTeam.message_id, UserInTwitchTeam.twitch_username).where(
            and_(or_(UserInTwitchTeam.team_twitch_alert_id == 614, UserInTwitchTeam.team_twitch_alert_id == 616),
                 UserInTwitchTeam.twitch_username == 'monstercat'))
        result = session.execute(sql_select_messages).fetchall()

        assert len(result) == 2
        assert result[0].message_id is None
        assert result[1].message_id is None

0 Source : games.py
with MIT License
from lexicalunit

    def add_player(self, player_xid: int) -> None:
        assert self.game

        rows = DatabaseSession.query(User).filter(User.game_id == self.game.id).count()
        assert rows + 1   <  = self.game.seats

        query = (
            update(User)
            .where(User.xid == player_xid)
            .values(game_id=self.game.id)
            .execution_options(synchronize_session=False)
        )
        DatabaseSession.execute(query)
        DatabaseSession.commit()

        # This operation should "dirty" the Game, so we need to update its updated_at.
        query = (
            update(Game)
            .where(Game.id == self.game.id)
            .values(updated_at=datetime.utcnow())
            .execution_options(synchronize_session=False)
        )
        DatabaseSession.execute(query)
        DatabaseSession.commit()

    @sync_to_async

0 Source : users.py
with MIT License
from lexicalunit

    def leave_game(self) -> None:
        assert self.user
        assert self.user.game

        left_game_id = self.user.game_id

        self.user.game_id = None  # type: ignore
        DatabaseSession.commit()

        # This operation should "dirty" the Game, so we need to update its updated_at.
        query = (
            update(Game)
            .where(Game.id == left_game_id)
            .values(updated_at=datetime.utcnow())
            .execution_options(synchronize_session=False)
        )
        DatabaseSession.execute(query)
        DatabaseSession.commit()

    @sync_to_async

0 Source : novel_service.py
with Apache License 2.0
from mensch272

    def update_chapters(self, novel: Novel, volume_dtos: List[VolumeDTO]):
        volumes = (
            self.session.execute(select(Volume).where(Volume.novel_id == novel.id))
            .scalars()
            .all()
        )
        chapters = self.get_chapters(novel)
        volume_mapped_chapters = self.dto_adapter.volumes_from_dto(novel, volume_dtos)

        indexed_volumes = {v.index: v for v in volumes}
        volumes_to_add = []
        for v in list(volume_mapped_chapters.keys()):
            try:
                existing_volume = indexed_volumes.pop(v.index)

                # update names of volumes that already exist
                if existing_volume.name != v.name:
                    logger.debug(
                        f"Updating volume: '{existing_volume.name}' -> '{v.name}' ({v.index})."
                    )
                    self.session.execute(
                        update(Volume)
                        .where(Volume.id == existing_volume.id)
                        .values(name=v.name)
                    )

                # index exists so map the chapters into existing volume
                volume_mapped_chapters[existing_volume] = volume_mapped_chapters.pop(v)
            except KeyError:
                volumes_to_add.append(v)

        # add new volume rows that are new
        logger.debug(f"Adding {len(volumes_to_add)} newly found volumes.")
        self.session.add_all(volumes_to_add)
        self.session.flush()

        # indexed by url which is unique across table
        indexed_chapters = {c.url: c for c in chapters}
        chapters_to_add = []

        # iterate all new chapters
        for volume, chapter_dtos in volume_mapped_chapters.items():
            for chapter_dto in chapter_dtos:
                chapter = self.dto_adapter.chapter_from_dto(volume, chapter_dto)
                try:
                    # existing chapter equivalent
                    ece = indexed_chapters.pop(chapter.url)

                    # update chapters that need to be updated
                    if ece.volume_id != volume.id or ece.index != chapter.index:
                        logger.debug(
                            f"Updating '{chapter.title}': index {ece.index} -> {chapter.index}, "
                            f"volume_id {ece.volume_id} -> {volume.id})"
                        )
                        self.session.execute(
                            update(Chapter)
                            .where(Chapter.url == chapter.url)
                            .values(index=chapter.index, volume_id=volume.id)
                        )
                except KeyError:
                    chapters_to_add.append(chapter)

        # add all new chapters
        logger.debug(f"Adding {len(chapters_to_add)} newly found chapters.")
        self.session.add_all(chapters_to_add)

        # delete chapters that dont exist anymore
        logger.debug(
            f"Deleting {len(indexed_chapters)} chapter rows that dont exist anymore."
        )
        for old in indexed_chapters.values():
            self.session.delete(old)

        # delete volumes that dont exist anymore
        logger.debug(
            f"Deleting {len(indexed_volumes)} volume rows that dont exist anymore."
        )
        for old in indexed_volumes.values():
            self.session.delete(old)

        self.session.commit()

    def update_metadata(self, novel: Novel, metadata_dtos: List[MetaDataDTO]):

0 Source : sqlite_func.py
with GNU General Public License v3.0
from Patrick-DE

def update_element(cls, **kwargs):
    session = SESSION()
    try:
        session.execute(
            update(cls).
            where(cls.id == kwargs["id"]).
            values(kwargs)
            )
        session.commit()
    except exc.IntegrityError:
        pass
    except Exception as ex:
        log(f"update_element() Failed: {ex}", "e")
    finally:
        session.close()

    return get_element_by_id(cls, kwargs["id"])


def delete_element(cls, id):

0 Source : append.py
with GNU Affero General Public License v3.0
from Redlnn

async def add_whitelist_to_qq(qq: int, mc_id: str, admin: bool) -> MessageChain:
    try:
        real_mc_id, mc_uuid = await get_uuid(mc_id)
    except Exception as e:
        logger.error(f'向 mojang 查询【{mc_id}】的 uuid 时发生了意料之外的错误')
        logger.exception(e)
        return MessageChain.create(Plain(f'向 mojang 查询【{mc_id}】的 uuid 时发生了意料之外的错误:  👇\n{e}'))
    if not isinstance(real_mc_id, str):
        if real_mc_id.status == 204:
            return MessageChain.create(Plain('你选择的不是一个正版ID'))
        else:
            return MessageChain.create(Plain(f'向 mojang 查询【{mc_id}】的 uuid 时获得意外内容:  👇\n{await real_mc_id.text()}'))

    player = await query_whitelist_by_uuid(mc_uuid)
    if player is None:
        pass
    elif int(player.qq) == qq:
        return MessageChain.create(Plain('这个id本来就是你哒'))
    else:
        return MessageChain.create(
            Plain('你想要这个吗?\n这个是 '),
            At(int(player.qq)),
            Plain(f' 哒~'),
        )

    player = await query_uuid_by_qq(qq)
    if player is None:
        app = get_running(Ariadne)
        member: Member = await app.getMember(config.serverGroup, qq)
        await Database.add(PlayerInfo(qq=str(member.id), join_time=member.joinTimestamp))
    elif player.blocked:
        return MessageChain.create(Plain(f'你的账号已被封禁,封禁原因:{player.block_reason}'))
    elif player.uuid1 is None and player.uuid2 is None:
        await Database.exec(
            update(PlayerInfo)
            .where(PlayerInfo.qq == str(qq))
            .values(uuid1=UUID(mc_uuid).hex, uuid1_add_time=int(time.time()))
        )
    elif player.uuid1 is not None and player.uuid2 is None:
        if admin:
            await Database.exec(
                update(PlayerInfo)
                .where(PlayerInfo.qq == str(qq))
                .values(uuid2=UUID(mc_uuid).hex, uuid2_add_time=int(time.time()))
            )
        else:
            return MessageChain.create(
                Plain('你已有一个白名单,如要申请第二个白名单请联系管理员'),
            )
    elif player.uuid2 is not None and player.uuid1 is None:
        if admin:
            await Database.exec(
                update(PlayerInfo)
                .where(PlayerInfo.qq == str(qq))
                .values(uuid1=UUID(mc_uuid).hex, uuid1_add_time=int(time.time()))
            )
        else:
            return MessageChain.create(
                Plain('你已有一个白名单,如要申请第二个白名单请联系管理员'),
            )
    else:
        if admin:
            return MessageChain.create(
                Plain('目标玩家已有两个白名单,如需添加白名单请删除至少一个'),
            )
        else:
            return MessageChain.create(
                Plain('你已经有两个白名单了噢'),
            )

    try:
        res: str = await execute_command(f'whitelist add {real_mc_id}')
    except TimeoutError:
        return MessageChain.create(Plain('添加白名单时已写入数据库,但连接服务器超时,请联系管理解决'))
    except Exception as e:
        logger.exception(e)
        return MessageChain.create(Plain(f'添加白名单时已写入数据库但无法连接到服务器,请联系管理解决: 👇\n{e}'))

    if res.startswith('Added'):
        return MessageChain.create(At(qq), Plain(' 呐呐呐,白名单给你!'))
    else:
        return MessageChain.create(Plain(f'添加白名单时已写入数据库但服务器返回预料之外的内容: 👇\n{res}'))

0 Source : delete.py
with GNU Affero General Public License v3.0
from Redlnn

async def del_whitelist_by_qq(qq: int) -> MessageChain:
    player = await query_uuid_by_qq(qq)
    if player is None:
        return MessageChain.create(At(qq), Plain(f' 好像一个白名单都没有呢~'))

    await Database.exec(
        update(PlayerInfo)
        .where(PlayerInfo.qq == str(qq))
        .values(uuid1=None, uuid1_add_time=None, uuid2=None, uuid2_add_time=None)
    )
    flag1 = flag2 = False
    if player.uuid1:
        flag1 = await del_whitelist_from_server(player.uuid1)
    if player.uuid2:
        flag2 = await del_whitelist_from_server(player.uuid2)
    if flag1 is True and isinstance(flag2, MessageChain):
        return MessageChain.create(Plain('只从服务器上删除了 '), At(qq), Plain(f' 的部分白名单 👇\n')) + flag2
    elif flag2 is True and isinstance(flag1, MessageChain):
        return MessageChain.create(Plain('只从服务器上删除了 '), At(qq), Plain(f' 的部分白名单 👇\n')) + flag1
    elif isinstance(flag1, MessageChain) and isinstance(flag2, MessageChain):
        return (
            MessageChain.create(Plain('从服务器上删除 '), At(qq), Plain(f' 的白名单时失败 👇\n\n'))
            + flag1
            + MessageChain.create('\n')
            + flag2
        )
    else:
        return MessageChain.create(At(qq), Plain(f' 的白名单都删掉啦~'))


async def del_whitelist_by_id(mc_id: str) -> MessageChain:

0 Source : delete.py
with GNU Affero General Public License v3.0
from Redlnn

async def del_whitelist_by_uuid(mc_uuid: str) -> MessageChain:
    player = await query_whitelist_by_uuid(mc_uuid)
    if player is None:
        return MessageChain.create(Plain('没有使用这个 uuid 的玩家'))
    if str(player.uuid1).replace('-', '') == mc_uuid.replace('-', ''):
        await Database.exec(
            update(PlayerInfo).where(PlayerInfo.qq == player.qq).values(uuid1=None, uuid1_add_time=None)
        )
        del_result = await del_whitelist_from_server(mc_uuid)
        if del_result is True:
            return MessageChain.create(Plain('已从服务器删除 '), At(int(player.qq)), Plain(f' 的 uuid 为 {mc_uuid} 的白名单'))
        else:
            return del_result
    elif str(player.uuid2).replace('-', '') == mc_uuid.replace('-', ''):
        await Database.exec(
            update(PlayerInfo).where(PlayerInfo.qq == player.qq).values(uuid2=None, uuid2_add_time=None)
        )
        del_result = await del_whitelist_from_server(mc_uuid)
        if del_result is True:
            return MessageChain.create(Plain('已从服务器删除 '), At(int(player.qq)), Plain(f' 的 uuid 为 {mc_uuid} 的白名单'))
        else:
            return del_result
    else:
        return MessageChain.create('发生了异常的内部逻辑错误,请联系管理员')

0 Source : test_lambdas.py
with MIT License
from sqlalchemy

    def test_update(self):
        User, Address = self.classes("User", "Address")

        s = Session(testing.db, future=True)

        def go(ids, values):
            stmt = lambda_stmt(lambda: update(User).where(User.id.in_(ids)))
            s.execute(
                stmt,
                values,
                # note this currently just unrolls the lambda on the statement.
                # so lambda caching for updates is not actually that useful
                # unless synchronize_session is turned off.
                # evaluate is similar just doesn't work for IN yet.
                execution_options={"synchronize_session": "fetch"},
            )

        go([1, 2], {"name": "jack2"})
        eq_(
            s.execute(select(User.id, User.name).order_by(User.id)).all(),
            [(1, "jack2"), (2, "jack2"), (3, "jill"), (4, "jane")],
        )

        go([3], {"name": "jane2"})
        eq_(
            s.execute(select(User.id, User.name).order_by(User.id)).all(),
            [(1, "jack2"), (2, "jack2"), (3, "jane2"), (4, "jane")],
        )

0 Source : test_update_delete.py
with MIT License
from sqlalchemy

    def test_update_future_lambda(self):
        User, users = self.classes.User, self.tables.users

        sess = Session(testing.db, future=True)

        john, jack, jill, jane = (
            sess.execute(select(User).order_by(User.id)).scalars().all()
        )

        sess.execute(
            lambda_stmt(
                lambda: update(User)
                .where(User.age > 29)
                .values({"age": User.age - 10})
                .execution_options(synchronize_session="evaluate")
            ),
        )

        eq_([john.age, jack.age, jill.age, jane.age], [25, 37, 29, 27])
        eq_(
            sess.execute(select(User.age).order_by(User.id)).all(),
            list(zip([25, 37, 29, 27])),
        )

        sess.execute(
            lambda_stmt(
                lambda: update(User)
                .where(User.age > 29)
                .values({User.age: User.age - 10})
                .execution_options(synchronize_session="evaluate")
            )
        )
        eq_([john.age, jack.age, jill.age, jane.age], [25, 27, 29, 27])
        eq_(
            sess.query(User.age).order_by(User.id).all(),
            list(zip([25, 27, 29, 27])),
        )

        sess.query(User).filter(User.age > 27).update(
            {users.c.age_int: User.age - 10}, synchronize_session="evaluate"
        )
        eq_([john.age, jack.age, jill.age, jane.age], [25, 27, 19, 27])
        eq_(
            sess.query(User.age).order_by(User.id).all(),
            list(zip([25, 27, 19, 27])),
        )

        sess.query(User).filter(User.age == 25).update(
            {User.age: User.age - 10}, synchronize_session="fetch"
        )
        eq_([john.age, jack.age, jill.age, jane.age], [15, 27, 19, 27])
        eq_(
            sess.query(User.age).order_by(User.id).all(),
            list(zip([15, 27, 19, 27])),
        )

    @testing.combinations(

0 Source : test_update_delete.py
with MIT License
from sqlalchemy

    def test_update_fetch_returning_lambda(self):
        User = self.classes.User

        sess = Session(testing.db, future=True)

        john, jack, jill, jane = (
            sess.execute(select(User).order_by(User.id)).scalars().all()
        )

        with self.sql_execution_asserter() as asserter:
            stmt = lambda_stmt(
                lambda: update(User)
                .where(User.age > 29)
                .values({"age": User.age - 10})
            )
            sess.execute(
                stmt, execution_options={"synchronize_session": "fetch"}
            )

            # these are simple values, these are now evaluated even with
            # the "fetch" strategy, new in 1.4, so there is no expiry
            eq_([john.age, jack.age, jill.age, jane.age], [25, 37, 29, 27])

        if testing.db.dialect.full_returning:
            asserter.assert_(
                CompiledSQL(
                    "UPDATE users SET age_int=(users.age_int - %(age_int_1)s) "
                    "WHERE users.age_int > %(age_int_2)s RETURNING users.id",
                    [{"age_int_1": 10, "age_int_2": 29}],
                    dialect="postgresql",
                ),
            )
        else:
            asserter.assert_(
                CompiledSQL(
                    "SELECT users.id FROM users "
                    "WHERE users.age_int > :age_int_1",
                    [{"age_int_1": 29}],
                ),
                CompiledSQL(
                    "UPDATE users SET age_int=(users.age_int - :age_int_1) "
                    "WHERE users.age_int > :age_int_2",
                    [{"age_int_1": 10, "age_int_2": 29}],
                ),
            )

    @testing.requires.full_returning

0 Source : test_update_delete.py
with MIT License
from sqlalchemy

    def test_update_returns_rowcount(self):
        User = self.classes.User

        sess = fixture_session()

        rowcount = (
            sess.query(User)
            .filter(User.age > 29)
            .update({"age": User.age + 0})
        )
        eq_(rowcount, 2)

        rowcount = (
            sess.query(User)
            .filter(User.age > 29)
            .update({"age": User.age - 10})
        )
        eq_(rowcount, 2)

        # test future
        result = sess.execute(
            update(User).where(User.age > 19).values({"age": User.age - 10})
        )
        eq_(result.rowcount, 4)

    @testing.fails_if(lambda: not testing.db.dialect.supports_sane_rowcount)

0 Source : test_update_delete.py
with MIT License
from sqlalchemy

    def test_load_from_update(self, connection):
        User = self.classes.User

        stmt = (
            update(User)
            .where(User.name.in_(["jack", "jill"]))
            .values(age=User.age + 5)
            .returning(User)
        )

        stmt = select(User).from_statement(stmt)

        with Session(connection) as sess:
            rows = sess.execute(stmt).scalars().all()

            eq_(
                rows,
                [User(name="jack", age=52), User(name="jill", age=34)],
            )

    def test_load_from_insert(self, connection):

0 Source : test_roles.py
with MIT License
from sqlalchemy

    def test_correlated_update_three(self, update_from_fixture):
        table1, table2 = update_from_fixture

        # test against a regular constructed subquery
        s = select(table2).where(table2.c.otherid == table1.c.myid)
        with testing.expect_warnings(
            "implicitly coercing SELECT object to scalar subquery"
        ):
            u = (
                update(table1)
                .where(table1.c.name == "jack")
                .values({table1.c.name: s})
            )
        self.assert_compile(
            u,
            "UPDATE mytable SET name=(SELECT myothertable.otherid, "
            "myothertable.othername FROM myothertable WHERE "
            "myothertable.otherid = mytable.myid) "
            "WHERE mytable.name = :name_1",
        )

0 Source : test_update.py
with MIT License
from sqlalchemy

    def test_correlated_update_three(self):
        table1 = self.tables.mytable
        table2 = self.tables.myothertable

        # test against a regular constructed subquery
        s = (
            select(table2)
            .where(table2.c.otherid == table1.c.myid)
            .scalar_subquery()
        )
        u = (
            update(table1)
            .where(table1.c.name == "jack")
            .values({table1.c.name: s})
        )
        self.assert_compile(
            u,
            "UPDATE mytable SET name=(SELECT myothertable.otherid, "
            "myothertable.othername FROM myothertable WHERE "
            "myothertable.otherid = mytable.myid) "
            "WHERE mytable.name = :name_1",
        )

    def test_correlated_update_four(self):