async def get_accounts(owner: Dict, conn: SAConnection) -> Dict:
accounts = {}
params = table.c.owner_id == owner.get('id')
async for row in conn.execute(sqlalchemy.select([table]).where(params)):
account = dict(zip(row.keys(), row.values()))
accounts[account['id']] = account
total = await conn.scalar(
sqlalchemy.select([sqlalchemy.func.count()]).select_from(
table).where(params)
)
balance = []
query = sqlalchemy.select([balance_table]).where(
balance_table.c.account_id.in_(accounts.keys())).order_by(
balance_table.c.date.desc())
async for row in conn.execute(query):
item = dict(zip(row.keys(), row.values()))
balance.append(item)
for key, group in groupby(balance, lambda b: b.get('account_id')):
accounts[key]['balance'] = list(group)
return accounts, total
async def fetch_many(cls, conn: SAConnection, cte: CTE = None):
results = OrderedDict()
if cte is None:
cte = cls.Options.db_table
query = (
cte
.outerjoin(tbl.books_stores)
.outerjoin(tbl.books,
tbl.books_stores.c.book_id == tbl.books.c.id)
.select(use_labels=True)
)
books = {}
async for row in conn.execute(query):
store_id = row[cte.c.id]
store = results.get(store_id)
if store is None:
store = Store.from_row(row, books=[], alias=cte)
results[store_id] = store
book_id = row[tbl.books.c.id]
if book_id:
book = books.get(book_id, Book.not_populated(book_id))
if book not in store.books:
store.books.append(book)
return results
async def fetch_many(cls, conn: SAConnection, cte: CTE = None):
results = OrderedDict()
imageable_map = {
'authors': Author,
'books': Book,
'series': Series
}
if cte is None:
cte = cls.Options.db_table
query = cte.select()
async for row in conn.execute(query):
photo_id = row[cte.c.id]
photo = results.get(photo_id)
if photo is None:
imageable_id = row[cte.c.imageable_id]
imageable_type = row[cte.c.imageable_type]
imageable_model = imageable_map[imageable_type]
imageable = imageable_model.not_populated(imageable_id)
photo = Photo.from_row(row, imageable=imageable, alias=cte)
results[photo_id] = photo
return results
async def update_balance(balance: List, account: Dict, conn: SAConnection):
existed = {}
query = sqlalchemy.select([table]).where(
table.c.account_id == account.get('id')).order_by(table.c.date.asc())
async for item in conn.execute(query):
key = datetime.combine(item.date, datetime.min.time())
existed[key] = {
'id': item.id,
'income': item.income,
'expense': item.expense,
'remain': item.remain,
'date': item.date
}
for item in balance:
if item['date'] in existed:
query = sqlalchemy.update(table).where(
table.c.id == existed[item['date']]['id']).values(**item)
else:
query = sqlalchemy.insert(table, values={
'account_id': account['id'], **item
})
await conn.execute(query)
async def safe_insert_order(conn: SAConn, in_tx: Tx, out_tx: Tx, order: Order):
async with conn.begin("SERIALIZABLE") as transaction:
try:
_in_res = await insert_tx(conn, in_tx)
_out_res = await insert_tx(conn, out_tx)
_order_res = await insert_order(conn, order)
assert _in_res
assert _out_res
assert _order_res
return True
except Exception as ex:
return False
async def fetch_many(cls, conn: SAConnection, cte: CTE = None):
results = OrderedDict()
if cte is None:
cte = cls.Options.db_table
query = (
cte
.join(tbl.authors)
.outerjoin(tbl.series)
.outerjoin(tbl.chapters)
.outerjoin(tbl.photos,
sa.and_(
tbl.photos.c.imageable_id == cte.c.id,
tbl.photos.c.imageable_type == 'books'
))
.select(use_labels=True)
)
photos = {}
chapters = {}
async for row in conn.execute(query):
book_id = row[cte.c.id]
book = results.get(book_id)
if book is None:
book_author = Author.not_populated(row[tbl.authors.c.id])
book_series_id = row[tbl.series.c.id]
book_series = Series.not_populated(book_series_id) \
if book_series_id \
else None
book = Book.from_row(row, author=book_author, chapters=[],
series=book_series,
photos=[], alias=cte)
results[book_id] = book
photo_id = row[tbl.photos.c.id]
if photo_id:
photo = photos.get(photo_id, Photo.not_populated(photo_id))
if photo not in book.photos:
book.photos.append(photo)
chapter_id = row[tbl.chapters.c.id]
if chapter_id:
chapter = chapters.get(chapter_id,
Chapter.not_populated(chapter_id))
if chapter not in book.chapters:
book.chapters.append(chapter)
return results
async def safe_insert_order(conn: SAConn, in_tx: Tx, out_tx: Tx, order: Order):
async with conn.begin("SERIALIZABLE") as transaction:
try:
_in_res = await insert_tx(conn, in_tx)
_out_res = await insert_tx(conn, out_tx)
_order_res = await insert_order(conn, order)
assert _in_res
assert _out_res
assert _order_res
return True
except Exception as ex:
log.info(f"Serialization inserting order {order.id} fail: {ex}")
return False
async def calculate_balance(account: Dict, until: datetime,
conn: SAConnection) -> List:
query = '''
SELECT
SUM(transactions.amount),
transactions.type,
to_char(transactions.created_on, 'MM-YYYY') as date
FROM transactions
WHERE (
transactions.account_id = {account_id}
)
GROUP BY transactions.type, transactions.created_on
ORDER BY transactions.created_on ASC;
'''.format(account_id=account.get('id'))
total_by_month = {}
min_date = until
async for item in conn.execute(query):
date = datetime.strptime(item.date, '%m-%Y')
min_date = min(date, min_date)
if item.date in total_by_month:
total_by_month[item.date][item.type] = item.sum
else:
total_by_month[item.date] = {item.type: item.sum}
if not total_by_month:
t = min_date
date = datetime(year=t.year, month=t.month, day=1)
total_by_month[date.strftime('%m-%Y')] = {'income': 0, 'expense': 0}
balance = []
remain = account.get('original_amount', 0)
if not isinstance(remain, Decimal):
remain = Decimal(remain)
for d in rrule(MONTHLY, dtstart=min_date, until=until): # type: datetime
key = d.strftime('%m-%Y')
expense = Decimal(0)
income = Decimal(0)
if key in total_by_month:
expense = total_by_month[key].get('expense', Decimal(0))
income = total_by_month[key].get('income', Decimal(0))
balance.append({
'expense': expense, 'income': income, 'remain': remain, 'date': d
})
remain = remain + income - expense
return balance
async def liveness_handler(
response: Response,
conn: SAConnection = Depends(pg_connection),
) -> HealthCheckResponse:
coroutines = {'postgres': conn.scalar('select True;')}
service_status = {}
for service, coroutine in coroutines.items():
try:
await coroutine
except: # noqa
service_status[service] = False
else:
service_status[service] = True
status_code = 200
if not all(service_status.values()):
status_code = 500
response.status_code = status_code
return HealthCheckResponse(**service_status)
async def fetch_many(cls, conn: SAConnection, cte: CTE = None):
results = OrderedDict()
if cte is None:
cte = cls.Options.db_table
query = (
cte
.outerjoin(tbl.books,
tbl.books.c.author_id == cte.c.id)
.outerjoin(tbl.photos,
sa.and_(
tbl.photos.c.imageable_id == cte.c.id,
tbl.photos.c.imageable_type == 'authors'
))
.select(use_labels=True)
)
books = {}
photos = {}
async for row in conn.execute(query):
author_id = row[cte.c.id]
author = results.get(author_id)
if author is None:
author = Author.from_row(row, books=[], photos=[], alias=cte)
results[author_id] = author
book_id = row[tbl.books.c.id]
if book_id:
book = books.get(book_id, Book.not_populated(book_id))
if book not in author.books:
author.books.append(book)
photo_id = row[tbl.photos.c.id]
if photo_id:
photo = photos.get(photo_id, Photo.not_populated(photo_id))
if photo not in author.photos:
author.photos.append(photo)
return results
async def fetch_many(cls, conn: SAConnection, cte: CTE = None):
results = OrderedDict()
if cte is None:
cte = cls.Options.db_table
query = cte.select()
async for row in conn.execute(query):
chapter_id = row[cte.c.id]
chapter = results.get(chapter_id)
if chapter is None:
chapter = \
cls.from_row(row,
book=Book.not_populated(row[cte.c.book_id]),
alias=cte)
results[chapter_id] = chapter
return results
async def get_image_colors(connection: SAConnection,
image_id: int) -> List[Color]:
"""
Retrieve a list of image colors from DB.
"""
sql = sa.text("""
SELECT * FROM image_color WHERE image_id = :image_id;
""")
colors: List[Color] = []
async for row in connection.execute(sql, {"image_id": image_id}):
color = Color(
L=row["L"],
a=row["a"],
b=row["b"],
percentage=row["percentage"],
name=row["name"],
name_distance=row["name_distance"],
)
colors.append(color)
return colors
async def safe_update_order(conn: SAConn, order: OrderDTO):
async with conn.begin("SERIALIZABLE") as transaction:
cursor = await conn.execute(
select([Order]).where(Order.id == order.order_id).as_scalar())
_db_order_instance = await cursor.fetchone()
_txs_to_update = [
tx for tx in (order.in_tx, order.out_tx) if tx is not None
]
for tx in _txs_to_update:
if tx == order.in_tx:
tx_uuid = _db_order_instance.in_tx
elif tx == order.out_tx:
tx_uuid = _db_order_instance.out_tx
else:
raise
_tx = Tx(id=tx_uuid, **dataclasses.asdict(tx))
await update_tx(conn, _tx)
return True
async def save_history_chunk(*, songs, conn: asa.SAConnection):
"""In charge of saving a chunck of continuous songs."""
# {'__v': 0,
# '_id': '583bf4a9d9abb248008a698a',
# '_song': {
# '__v': 0,
# '_id': '5637c2cf7d7d3f2200b05659',
# 'created': '2015-11-02T20:08:47.588Z',
# 'fkid': 'eOwwLhMPRUE',
# 'images': {
# 'thumbnail': 'https://i.ytimg.com/vi/eOwwLhMPRUE/hqdefault.jpg',
# 'youtube': {
# 'default': {
# 'height': 90,
# 'url': 'https://i.ytimg.com/vi/eOwwLhMPRUE/default.jpg',
# 'width': 120
# },
# 'high': {
# 'height': 360,
# 'url': 'https://i.ytimg.com/vi/eOwwLhMPRUE/hqdefault.jpg',
# 'width': 480
# },
# 'maxres': {
# 'height': 720,
# 'url': 'https://i.ytimg.com/vi/eOwwLhMPRUE/maxresdefault.jpg',
# 'width': 1280
# },
# 'medium': {
# 'height': 180,
# 'url': 'https://i.ytimg.com/vi/eOwwLhMPRUE/mqdefault.jpg',
# 'width': 320
# },
# 'standard': {
# 'height': 480,
# 'url': 'https://i.ytimg.com/vi/eOwwLhMPRUE/sddefault.jpg',
# 'width': 640
# }
# }
# },
# 'name': 'Craig Armstrong - Dream Violin',
# 'songLength': 204000,
# 'type': 'youtube'
# },
# '_user': {
# '__v': 0,
# '_id': '57595c7a16c34f3d00b5ea8d',
# 'created': 1465474170519,
# 'dubs': 0,
# 'profileImage': {
# 'bytes': 72094,
# 'etag': 'fdcdd43edcaaec225a6dcd9701e62be1',
# 'format': 'png',
# 'height': 500,
# 'public_id': 'user/57595c7a16c34f3d00b5ea8d',
# 'resource_type': 'image',
# 'secure_url':
# 'https://res.cloudinary.com/hhberclba/image/upload/v1465474392/user'
# '/57595c7a16c34f3d00b5ea8d.png',
# 'tags': [],
# 'type': 'upload',
# 'url': 'http://res.cloudinary.com/hhberclba/image/upload/v1465474392/user'
# '/57595c7a16c34f3d00b5ea8d.png',
# 'version': 1465474392,
# 'width': 500
# },
# 'roleid': 1,
# 'status': 1,
# 'username': '******'
# },
# 'created': 1480324264803,
# 'downdubs': 0,
# 'isActive': True,
# 'isPlayed': True,
# 'order': 243,
# 'played': 1480464322618,
# 'roomid': '561b1e59c90a9c0e00df610b',
# 'skipped': False,
# 'songLength': 204000,
# 'songid': '5637c2cf7d7d3f2200b05659',
# 'updubs': 1,
# 'userid': '57595c7a16c34f3d00b5ea8d'
# }
song_played = None
previous_song, previous_playback_id = {}, None
async with conn.begin():
for song in songs:
# Generate Action skip for the previous Playback entry
song_played = datetime.datetime.utcfromtimestamp(song['played'] / 1000)
if previous_song.get('skipped'):
await history_import_skip_action(
previous_playback_id=previous_playback_id,
song_played=song_played,
conn=conn,
)
# Query or create the User for the Playback entry
user = await get_or_create_user(song=song, conn=conn)
user_id = user['id']
# Query or create the Track entry for this Playback entry
track = await get_or_create_track(song=song, conn=conn)
track_id = track['id']
# Query or create the Playback entry
playback_id = await get_or_create_playback(
song_played=song_played,
track_id=track_id,
user_id=user_id,
conn=conn
)
# Query or create the UserAction<upvote> UserAction<downvote> entries
await update_user_actions(
song=song,
song_played=song_played,
playback_id=playback_id,
conn=conn,
)
previous_song, previous_playback_id = song, playback_id
logger.info(f'Saved songs up to {song_played}')
await conn.close()
