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 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 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 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