async def get_by_subject_id(db: Database, subject_id: int,) -> Tuple[list, list]:
s = await subject.get(
db, sa.Subject.id == subject_id, sa.Subject.locked == 0, sa.Subject.map != 0,
)
nodes = [
dict(x)
async for x in db.iterate(
sa.select(
[
sa.Subject.id,
sa.Subject.map,
sa.Subject.name,
sa.Subject.info,
sa.Subject.image,
sa.Subject.name_cn,
sa.Subject.subject_type,
]
)
.where(sa.Subject.map == s.map)
.where(s.locked == 0)
)
]
edges = [
dict(x)
async for x in db.iterate(
sa.select([sa.Relation]).where(sa.Relation.map == s.map)
)
]
return nodes, edges
async def get_birthdays(import_id: int, database: Database) -> dict:
"""Get number of birthdays by every month for particular import."""
agg_presents = func.count(relations.c.relative).label("presents")
month = func.date_part("month", citizens.c.birth_date)
month = cast(month, Integer).label("month")
query = (select(
[month, relations.c.citizen.label("citizen_id"),
agg_presents]).select_from(
citizens.join(
relations,
and_(
relations.c.import_id == citizens.c.import_id,
relations.c.relative == citizens.c.citizen_id,
),
)).where(relations.c.import_id == import_id).group_by(
month,
relations.c.citizen,
))
res = {str(i): [] for i in range(1, 13)}
async for row in database.iterate(query):
month = str(row[0])
res[month].append({"citizen_id": row[1], "presents": row[2]})
return res
async def get_data(
command: str) -> typing.AsyncGenerator[typing.Mapping, None]:
"""从数据库查询数据"""
db = Database(_CONNECTION_URL)
await db.connect()
return db.iterate(query=command)
async def get_multi(async_db: Database,
*,
skip=0,
limit=100) -> List[Optional[UserBaseInDB]]:
users = []
async for user in async_db.iterate(
select([UserORM]).offset(skip).limit(limit)):
user_rec = user.__dict__.get("_row")
users.append(UserBaseInDB(**user_rec))
return users
async def get_finalized_suggestions(
db: Database,
status: Sequence[FigmentatorStatus] = (FigmentatorStatus.active, )
) -> AsyncGenerator[Mapping, None]:
""" Load the finalized suggestions """
async with aiofiles.open(os.path.join("static", "game_blacklist.txt"),
"rt") as blacklist_file:
blacklist = [l.strip() for l in await blacklist_file.readlines()]
async for row in db.iterate(
await load_query("finalized_suggestions.sql"),
{
"status": status,
"blacklist": blacklist
},
):
yield row
async def get_age_statistics(import_id: int, database: Database) -> List[dict]:
"""Get age percentiles by each town."""
age = func.date_part("year", func.age(citizens.c.birth_date)).label("age")
query = (select([
citizens.c.town,
func.percentile_cont(0.5).within_group(age).label("p50"),
func.percentile_cont(0.75).within_group(age).label("p75"),
func.percentile_cont(0.99).within_group(age).label("p99"),
]).where(citizens.c.import_id == import_id).group_by(citizens.c.town))
res = []
async for row in database.iterate(query):
obj = {
"town": row[0],
"p50": row[1],
"p75": row[2],
"p99": row[3],
}
res.append(obj)
return res
async def select_judgement_contexts(
db: Database,
limit: int = 0,
status: Sequence[FigmentatorStatus] = (FigmentatorStatus.active, ),
) -> AsyncGenerator[Mapping, None]:
""" Load the finalized suggestions """
async with aiofiles.open(os.path.join("static", "game_blacklist.txt"),
"rt") as blacklist_file:
blacklist = [l.strip() for l in await blacklist_file.readlines()]
async for row in db.iterate(
await load_query("judgement_contexts.sql"),
{
"status": status,
"blacklist": blacklist,
"limit": float("inf") if not limit else limit,
},
):
yield row
class TileSetDB(object):
# TODO Too much code, need to abstract.
meta = MetaData()
table = sqlalchemy.Table(
'tileset', meta,
Column("uuid", String(100), primary_key=True),
Column("name", String(100)),
Column("datafile", String(100)),
Column('datatype', String(100)),
Column('filetype', String(100)),
Column('tileset', JSON)
)
uuid_tileset_query = select([table.c.uuid, table.c.tileset])
columns = [column
for column in table.columns.keys()
if column != 'tileset']
def __init__(self, store_uri="sqlite:////tmp/test.db"):
self.store_uri = store_uri
self.db = None
async def connect(self, store_uri=None):
if store_uri is not None:
self.store_uri = store_uri
try:
engine = None
engine = sqlalchemy.create_engine(self.store_uri)
if not self.table.exists(engine):
self.table.metadata.create_all(engine)
if not self.table.exists(engine):
raise RuntimeError("Table error.")
except Exception as e:
raise e
finally:
if engine is not None:
engine.dispose()
if self.db is None or not self.db.is_connected:
self.db = Database(self.store_uri)
await self.db.connect()
async def disconnect(self):
if self.db is not None and self.db.is_connected:
await self.db.disconnect()
async def query(self, query=None, **kwargs):
if query is None:
query = select([self.table.c.uuid, self.table.c.tileset])
for key, value in kwargs.items():
if key not in self.columns:
raise KeyError(key)
if not value:
continue
if isinstance(value, list):
query = getattr(query, 'where')(
getattr(self.table.c, key).in_(value))
else:
query = getattr(query, 'where')(
getattr(self.table.c, key) == value)
return {uuid: tileset
async for uuid, tileset in self.db.iterate(query)}
# TODO handling ordered by
async def items(self, query=None, **kwargs):
return (await self.query(query, **kwargs)).items()
async def update(self, tilesets):
wrapped_tilesets = []
for uuid, tileset in tilesets.items():
wrapped_tileset = {
column: tileset[column] for column in self.columns
}
wrapped_tileset['tileset'] = tileset
wrapped_tilesets.append(wrapped_tileset)
return await self.db.execute_many(
query=self.table.insert(),
values=wrapped_tilesets
)
async def remove(self, uuids):
if not isinstance(uuids, list):
uuids = [uuids]
return await self.db.execute(
self.table
.delete()
.where(self.table.c.uuid.in_(uuids))
)
