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
3
Source : db_manager.py
with MIT License
from arch4ngel
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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):