def test_init(self, async_engine):
ss = AsyncSession(bind=async_engine)
is_(ss.bind, async_engine)
binds = {Table: async_engine}
ss = AsyncSession(binds=binds)
is_(ss.binds, binds)
async def test_join_to_external_transaction(self, async_engine):
User = self.classes.User
async with async_engine.connect() as conn:
t1 = await conn.begin()
async_session = AsyncSession(conn)
aconn = await async_session.connection()
eq_(aconn.get_transaction(), t1)
eq_(aconn, conn)
is_(aconn.sync_connection, conn.sync_connection)
u1 = User(id=1, name="u1")
async_session.add(u1)
await async_session.commit()
assert conn.in_transaction()
await conn.rollback()
async with AsyncSession(async_engine) as async_session:
result = await async_session.execute(select(User))
eq_(result.all(), [])
async def get_guild(self, guild_id: int) -> Union[GuildSetting, None]:
"""ギルドの情報をGuildSettingで返す関数
Args:
guild_id (int): サーバーID
Returns:
GuildSetting: サーバの設定のデータクラス
"""
async with AsyncSession(engine, expire_on_commit=True) as session:
async with session.begin():
stmt = select(GuildSettingDB).where(
GuildSettingDB.guild_id == guild_id)
result = await session.execute(stmt)
result = result.fetchone()
if result is None:
return None
guildsetting = GuildSetting(result[0].guild_id,
result[0].dispander,
result[0].welcome_msg,
result[0].black_server)
return guildsetting
async def setup(connectionstring: str, network_name: str):
'''
Factory for DnsDb class
:param connectionstring: connectionstring for the Postgres DB
:param network_name: domain for the network, ie. 'byoda.net'
:returns:
:raises:
'''
dnsdb = DnsDb(network_name)
dnsdb._engine = create_async_engine(
connectionstring, echo=False, isolation_level='AUTOCOMMIT',
future=True, poolclass=NullPool
)
# Base = declarative_base()
dnsdb.async_session = sessionmaker(
dnsdb._engine, class_=AsyncSession, expire_on_commit=False
)
# Ensure the 'accounts' subdomain for the network exists
subdomain = f'accounts.{network_name}'
async with AsyncSession(dnsdb._engine) as db_session:
domain_id = await dnsdb._get_domain_id(db_session, subdomain)
dnsdb._domain_ids[subdomain] = domain_id
return dnsdb
async def register_aggregation(self, message_id: int, command_id: int, guild_id: int, channel_id: int, target_value: int, author_id: int, created_at: datetime, ping_id: str) -> None:
"""リアクション集計のパラメータを登録する関数
Args:
message_id (int): メッセージID
command_id (int): コマンドID
guild_id (int): サーバID
channel_id (int): チャンネルID
target_value (int): 目標値
author_id (int): 集計者のID
created_at (datetime): 作成日時
ping_id (str): 対象のID
"""
async with AsyncSession(engine) as session:
async with session.begin():
new_aggregation = ReactionAggregation(
message_id=message_id,
command_id=command_id,
guild_id=guild_id,
channel_id=channel_id,
target_value=target_value,
author_id=author_id,
created_at=created_at,
ping_id=ping_id)
session.add(new_aggregation)
async def invite_agent(invitation: AgentInvitationSchema):
"""
Creates an invitation token for a new agent and sends it to her email address.
The invitation expires in 7 days.
"""
token = tokenize(invitation.dict(), expires=timedelta(days=7))
async with AsyncSession(engine) as session:
async with session.begin():
obj = AgentInvitation(
email=invitation.email, role=invitation.role, token=token
)
await session.merge(obj)
invitation_url = "{}/agent/register/{}".format(SITE_URL, token)
message = MessageSchema(
subject="Fastapi-Mail module",
recipients=[invitation.email],
body="""
<p>You have been invited. Token: {}</p>
""".format(
invitation_url
),
subtype="html",
)
mailer = get_mailer()
await mailer.send_message(message)
return {}
async def decode_token(token, group=False):
error_detail = HTTPException(
status_code=401,
detail="Bad data",
headers={"WWW-Authenticate": "Bearer"}
)
try:
payload = jwt.decode(token, SECRET_KEY, algorithms=[ALGORITHM])
username: str = payload.get("sub")
if username is None:
raise error_detail
if group is True:
user_group: str = payload.get("group")
return user_group
valid_user = UserValidation(username=username)
except jwt.PyJWTError as ex:
raise error_detail
engine = await async_return_engine()
async with AsyncSession(engine) as session:
stmt = select(UserInDB).where(UserInDB.username == valid_user.username)
result = await session.execute(stmt)
user = result.scalar_one()
if not user:
raise error_detail
return user
async def get_scp_random(self, tag: str) -> Optional[SCPArticleDatacls]:
"""scpタグ付きの記事をランダムで返す関数
Args:
tag (str): [支部タグを想定
Returns:
Optional[SCPArticleDatacls]: ランダムで一個
"""
tags = ['scp']
if tag != 'all':
tags.append(tag)
async with AsyncSession(engine) as session:
async with session.begin():
stmt = select(SCPArticle).filter(
SCPArticle.tags.contains(tags)).order_by(
func.random()).limit(1)
result = await session.execute(stmt)
result = result.fetchone()
if result is not None:
data = self.return_dataclass(result)
return data
async def async_main():
"""Main program function."""
engine = create_async_engine(
"postgresql+asyncpg://scott:tiger@localhost/test",
echo=True,
)
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.drop_all)
await conn.run_sync(Base.metadata.create_all)
async with AsyncSession(engine) as session:
async with session.begin():
session.add_all(
[
A(bs=[B(), B()], data="a1"),
A(bs=[B()], data="a2"),
A(bs=[B(), B()], data="a3"),
]
)
# we have the option to run a function written in sync style
# within the AsyncSession.run_sync() method. The function will
# be passed a synchronous-style Session object and the function
# can use traditional ORM patterns.
await session.run_sync(run_queries)
await session.commit()
def __init__(self, conn):
super().__init__(conn)
self.session = AsyncSession(bind=self.engine)
self.Base = declarative_base(self.engine)
self.async_session = sessionmaker(self.engine,
expire_on_commit=False,
class_=AsyncSession)
async def get_data_from_url_and_tag(
self, url: str, tags: Union[List[str], typing.Tuple[str, ...]]
) -> Union[List[SCPArticleDatacls], None]:
"""tagと著者から該当するデータのリストを作成する関数
Args:
url (str): fullname(部分一致)
tags (List[str]): Tag(一致検索)
Returns:
Union[List[SCPArticleDatacls], None]: あればlistなければNone
"""
url = url.casefold()
async with AsyncSession(engine) as session:
async with session.begin():
stmt = select(SCPArticle).filter(
and_(SCPArticle.tags.contains(tags),
SCPArticle.fullname.ilike(f'%{url}%')))
result = await session.execute(stmt)
result = result.fetchall()
if len(result) == 0:
return None
data_list = [self.return_dataclass(data) for data in result]
return data_list
async def check_period_no_work(self,
month: int = 3) -> Optional[List[int]]:
"""一定期間連続で投稿しなかったメンバーを返す関数
Args:
month (int, optional): 閾値期間. Defaults to 3.
Returns:
Optional[List[int]]: 一定期間連続で投稿しなかったメンバーIDのリスト
"""
now = datetime.utcnow()
month_ago = now - relativedelta(months=month)
stmt = select([InactiveDetectorDB.user_id
]).where(InactiveDetectorDB.notified == False).filter(
or_(InactiveDetectorDB.last_posted < month_ago,
InactiveDetectorDB.last_react < month_ago))
async with AsyncSession(engine) as session:
async with session.begin():
result = await session.execute(stmt)
result = result.fetchall()
result = [member.user_id for member in result]
if len(result) == 0:
return None
else:
return result
async def get_data_from_title_and_tag(
self, title: str, tags: Union[List[str], typing.Tuple[str, ...]]
) -> Union[List[SCPArticleDatacls], None]:
"""tagとtitleから該当するデータのリストを作成する関数
Args:
title (str): title(部分一致)
tags (List[str]): Tag(一致検索)
Returns:
Union[List[SCPArticleDatacls], None]: あればlistなければNone
"""
async with AsyncSession(engine) as session:
async with session.begin():
stmt = select(SCPArticle).filter(
and_(
SCPArticle.tags.contains(tags),
or_(
SCPArticle.title.ilike(f'%{title}%'),
SCPArticle.metatitle.ilike(f'%{title}%'),
)))
result = await session.execute(stmt)
result = result.fetchall()
if len(result) == 0:
return None
data_list = [self.return_dataclass(data) for data in result]
return data_list
async def get_guild_list(self, guild_id: int) -> Union[List[ReactionParameter], None]:
"""ギルドごとのリアクションのデータオブジェクトをリストで返す関数
Args:
guild_id (int): サーバーID
Returns:
list: ReactionParameterのリスト
"""
guild_list = []
async with AsyncSession(engine) as session:
async with session.begin():
stmt = select(ReactionAggregation).where(
ReactionAggregation.guild_id == guild_id)
result = await session.execute(stmt)
result = result.fetchall()
# guild_list_raw = [guild[0] for guild in result]
for guild in result:
guild_raw = self.return_dataclass(guild)
guild_list.append(guild_raw)
if len(guild_list) == 0:
return None
else:
return guild_list
async def get_by_user_and_server(cls, user_id, server_id):
async with AsyncSession(engine) as session:
return (await session.execute(
select(XpPoint)
.where(XpPoint.user_id == user_id)
.where(XpPoint.server_id == server_id)
)).scalars().first()
async def person(self, connection):
# Add some base data to the database here (if necessary for your application)
async with AsyncSession(connection, expire_on_commit=False) as session:
person = Person(name="Test person")
session.add(person)
await session.commit()
return person
async def create_all(en):
async with en.begin() as conn:
await conn.run_sync(Base.metadata.drop_all)
await conn.run_sync(Base.metadata.create_all)
async with AsyncSession(en) as session:
user = User(
is_active=True,
email="*****@*****.**",
username="******",
api_key="test",
)
user.set_password("password")
device = Device(type=DeviceType.DOOR,
traits=[TraitType.OpenClose],
name='door',
nicknames=[],
attributes={},
states={},
user=user)
session.add(user)
session.add(device)
await session.commit()
async def set_role(
cls,
engine: AsyncEngine,
role_type: str,
guild: t.Union[str, int, Guild],
role: t.Union[str, int, Role],
) -> None:
"""Store given `role` as `role_type` role for on `guild` into the database."""
session = AsyncSession(bind=engine)
role_type = cls._get_normalized_role_type(role_type)
guild = get_str_guild(guild)
role = get_str_role(role)
logger.debug(f"Setting {role_type} on {guild} to {role}")
await upsert(session,
cls,
conflict_columns=["guild"],
values={
"guild": guild,
role_type: role
})
await session.commit()
await session.close()
async def get(cls, chess_game_id, preload_players=False):
query = select(ChessGame).where(ChessGame.id == chess_game_id)
if preload_players:
query = query.options(subqueryload(ChessGame.player1),
subqueryload(ChessGame.player2))
async with AsyncSession(engine) as session:
return (await session.execute(query)).scalars().first()
async def get_data_from_all_and_tag(
self, all_: str, tags: Union[List[str], typing.Tuple[str, ...]]
) -> Union[List[SCPArticleDatacls], None]:
"""tagとurl・タイトル・著者から該当するデータのリストを作成する関数
Args:
all_ (str): Created_by_unixとcreated_byとtitleとfullname(部分一致)
tags (List[str]): Tag(一致検索)
Returns:
Union[List[SCPArticleDatacls], None]: あればlistなければNone
"""
all_small = all_.casefold()
async with AsyncSession(engine) as session:
async with session.begin():
stmt = select(SCPArticle).filter(
and_(
SCPArticle.tags.contains(tags),
or_(SCPArticle.created_by_unix.ilike(f'%{all_small}%'),
SCPArticle.created_by.ilike(f'%{all_}%'),
SCPArticle.title.ilike(f'%{all_}%'),
SCPArticle.metatitle.ilike(f'%{all_}%'),
SCPArticle.fullname.ilike(f'%{all_small}%'))))
result = await session.execute(stmt)
result = result.fetchall()
if len(result) == 0:
return None
data_list = [self.return_dataclass(data) for data in result]
return data_list
async def get(cls, item_id):
try:
async with AsyncSession(engine) as session:
return (await session.execute(
select(ProfileItem).where(ProfileItem.id == item_id)
)).scalars().first()
except:
return None
async def list_by_server(cls, server_id):
async with AsyncSession(engine) as session:
return (await session.execute(
select(XpPoint)
.where(XpPoint.server_id == server_id)
.order_by(XpPoint.points.desc())
.options(joinedload(XpPoint.user))
)).scalars().fetchall()
async def get(cls, user_id, preload_profile_items=False):
query = select(User).where(User.id == user_id)
if preload_profile_items:
query = query.options(
subqueryload(User.profile_items).subqueryload(UserProfileItem.profile_item)
)
async with AsyncSession(engine) as session:
return (await session.execute(query)).scalars().first()
async def test_get_connection_connection_bound(self, async_engine):
async with async_engine.begin() as conn:
async_session = AsyncSession(conn)
c1 = await async_session.connection()
is_(c1, conn)
is_(c1.engine, conn.engine)
async def select_customers():
engine = create_async_engine("mysql+pymysql://elko:[email protected]/elko",
echo=True)
async with AsyncSession(engine) as session:
async with session.begin():
customers = await session.execute(select(RozetkaCustomer))
for customer in customers:
print(customer)
async def execute(self, sql, **kwargs):
async with AsyncSession(self.engine) as session:
try:
result = await session.execute(sql, **kwargs)
await session.commit()
return result
except Exception as e:
await session.rollback()
raise e
async def test_inspect_session(self, async_engine):
User = self.classes.User
s1 = AsyncSession(async_engine)
s2 = AsyncSession(async_engine)
u1 = await s1.get(User, 7)
u2 = User(name="n1")
s2.add(u2)
u3 = User(name="n2")
is_(inspect(u1).async_session, s1)
is_(inspect(u2).async_session, s2)
is_(inspect(u3).async_session, None)
async def session():
session = AsyncSession(bind=engine, binds={StudentData: postgres_engine})
yield session
# Close session and
# dispose engine so
# that we can run
# pytest.main() many
# times in row
await session.close()
await engine.dispose()
async def get_number_of_victories(cls, user_id):
async with AsyncSession(engine) as session:
return (await session.execute(
select(func.count()).select_from(ChessGame).where(
or_(
and_(ChessGame.result == 1,
ChessGame.player1_id == user_id),
and_(ChessGame.result == -1,
ChessGame.player2_id == user_id)))
)).scalars().first()
async def remove_tweetdata(self, message_id: int) -> None:
"""キューを削除するコマンド
Args:
message_id (int): メッセージID
"""
async with AsyncSession(engine) as session:
async with session.begin():
stmt = delete(TweetObj).where(
TweetObj.message_id == message_id)
await session.execute(stmt)