def test_run_external(cur, user, user2, tr_external): try: # fixed all data cur.connection.commit() tr_external.run() assert tr_external.get_status() == SUCCESS inv_from_new = Invoice.find_by_id(tr_external.invoice_id_from) inv_to_new = Invoice.find_by_id(tr_external.invoice_id_to) user_invoice_usd = user.invoices.get(USD, None) context = Context(prec=1, rounding=ROUND_DOWN) com = context.create_decimal_from_float(COMMISSION) assert inv_from_new.balance == ( user_invoice_usd.balance - (tr_external.amount + tr_external.amount * com)).quantize(TWOPLACES) user_invoice_eur = user2.invoices.get(EUR, None) assert inv_to_new.balance == (user_invoice_eur.balance + tr_external.convert_amount(user_invoice_usd, user_invoice_eur)).quantize( TWOPLACES) finally: with get_db_cursor(commit=True) as cur: cur.execute(f'''DELETE FROM public.transaction WHERE id={tr_external.id}::int;''') cur.execute(f'''DELETE FROM public.invoice WHERE user_id={user.id}::int;''') cur.execute(f'''DELETE FROM public.invoice WHERE user_id={user2.id}::int;''') cur.execute(f'''DELETE FROM public.user WHERE id={user.id}::int;''') cur.execute(f'''DELETE FROM public.user WHERE id={user2.id}::int;''')
def find_by_id(u_id: int): """ find user by id :param u_id: int - id user :return: User object """ with get_db_cursor(commit=True) as cur: cur.execute( '''SELECT u.email, u.created_at, u.name, u.password, array_agg(invoice.id) FROM "user" u LEFT JOIN invoice ON u.id = invoice.user_id WHERE u.id = %s::INT GROUP BY u.email, u.created_at, u.name, u.password LIMIT 1''', (u_id, )) data = cur.fetchone() if data: email, created_at, name, password, invoice_ids = data user = User(u_id=u_id, name=name, email=email, password=password, created_at=created_at, invoice_ids=invoice_ids) return user return None
def find_by_email(email: str): """ find user by email. email - unique param :param email: srt - email user :return: User object """ with get_db_cursor(commit=True) as cur: cur.execute( '''SELECT u.id, u.created_at, u.name, u.password, array_agg(invoice.id) FROM public."user" u LEFT JOIN invoice ON u.id = invoice.user_id WHERE u.email=%s::TEXT GROUP BY u.id, u.created_at, u.name, u.password LIMIT 1''', (email, )) data = cur.fetchone() if data: u_id, created_at, name, password, invoice_ids = data return User(u_id=u_id, name=name, email=email, password=password, created_at=created_at, invoice_ids=invoice_ids) return None
def run(self): """ run transaction with special logic """ tr = Transaction.find_by_id(self.id) if not tr: raise Exception('Transaction had to fix (created in db) ') if tr.get_status() != IN_PROCESS: raise Exception( f'Transaction completed yet with status {tr.get_status()}') if tr != self: raise Exception("Transaction object not synchronize with db") with get_db_cursor(commit=False) as cur: try: invoice_from = Invoice.find_by_id(self.invoice_id_from) invoice_to = Invoice.find_by_id(tr.invoice_id_to) if invoice_from.user_id == invoice_to.user_id: self.__move_internal(invoice_from, invoice_to, cur) else: self.__move_external(invoice_from, invoice_to, cur) self.__update_status(SUCCESS, cur) cur.connection.commit() return self except Exception as err: cur.connection.rollback() self.canceled() raise err
def find(flt=None, srt=None, nav=None): """ transaction select by id :param flt: dict - include filtering rules {"<table>":{"<field>":[{'operation': <name>, 'value':<value>}]}} For example: {"composite": {"invoice_from": {"user_id": [{'operation': 'eq', 'value':243}]}, "invoice_to": {"user_id": [{'operation': 'eq', 'value':243}]}}}, "transaction": {"created_at": [{'operation': 'lt', 'value':'2020-01-10'}, {'operation': 'gt', 'value':'2020-01-10'}]}}, } :param srt: dict - include sorting rules {"<table>": {"<field>":"<value>"}} For example: {"transaction": {"created_at": "asc"}} :param nav: dict - include navigation rules {"limit": "<value>", "offset": "value"} For example: {"limit": 10, "offset":10} :return: list Transactions object with shorten information about invoices """ aliases = { 'transaction': 'tr', 'invoice_from': 'inv_from', 'invoice_to': 'inv_to', 'currency_from': 'cur_from', 'currency_to': 'cur_to' } params = [] flt_str = "" if flt: flt_str, flt_params = Transaction.__build_from_flt(flt, aliases) params.extend(flt_params) srt_str = Transaction.__build_from_srt(srt, aliases) if srt else "" # todo: доделать навигацию nav_str = Transaction.__build_from_nav(nav) if nav else "" trans_list = [] with get_db_cursor(commit=True) as cur: sql = f'''SELECT tr.* FROM transaction tr left join invoice inv_from on tr.invoice_id_from = inv_from.id left join currency cur_from on inv_from.currency_id = cur_from.id left join invoice inv_to on tr.invoice_id_to = inv_to.id left join currency cur_to on inv_to.currency_id = cur_to.id {flt_str} {srt_str}; ''' cur.execute(sql, params) result = cur.fetchall() if result: for item in result: id, uid, inv_id_from, inv_id_to, created_at, updated_at, amount, status = item trans_list.append( Transaction(inv_id_from, inv_id_to, amount, id=id, uid=uid, created_at=created_at, updated_at=updated_at, status=status)) return trans_list return []
def user_fixed2(): user_name = "new_user_fixed2_test" user = User(name=user_name, email="*****@*****.**", password="******").create() try: yield user finally: with get_db_cursor(commit=True) as cur: cur.execute(f'''DELETE FROM public.invoice WHERE user_id={user.id}::int;''') cur.execute(f'''DELETE FROM public.user WHERE id={user.id}::int;''')
def canceled(self, cur=None): """ cancel run transaction, add status CANCELED :param cur: """ if cur: self.__update_status(CANCELED, cur) with get_db_cursor(commit=True) as cur: self.__update_status(CANCELED, cur)
def test_currency_cny(currency_cny): with get_db_cursor(commit=False) as cur: cur.execute( f'''SELECT id, name, rate_usd FROM public.currency WHERE name = '{CNY}' limit 1;''' ) id, name, rate_usd = cur.fetchone() assert currency_cny is not None assert currency_cny.id == id assert currency_cny.name == name assert currency_cny.rate_usd == rate_usd
def update_balance_add(self, amount, cur=None): """ update balace in db in invoice :param amount: Decimal :param cur: psycopg2.Cursor """ if cur is None: with get_db_cursor(commit=True) as cur: self.__update_balance_add(cur, amount) else: self.__update_balance_add(cur, amount)
def create(self, cur=None): """ create user in db :param cur: psycopg2.Cursor - need for postgresql transaction :return: self """ if cur is None: with get_db_cursor(commit=True) as cur: self.__create(cur) else: self.__create(cur) return self
def create(self, cur=None): """ create invoice in db :param cur: psycopg2.cursor :return: self """ if cur is None: with get_db_cursor(commit=True) as cur: created_at, id = self.__create(cur) else: created_at, id = self.__create(cur) self.id = id self.created_at = created_at return self
def test_run_inner(cur, user, tr_inner): try: # fixed all data cur.connection.commit() tr_inner.run() assert tr_inner.get_status() == SUCCESS inv_from_new = Invoice.find_by_id(tr_inner.invoice_id_from) inv_to_new = Invoice.find_by_id(tr_inner.invoice_id_to) user_invoice_usd = user.invoices.get(USD, None) assert inv_from_new.balance == user_invoice_usd.balance - tr_inner.amount user_invoice_eur = user.invoices.get(EUR, None) assert inv_to_new.balance == user_invoice_eur.balance + tr_inner.convert_amount(user_invoice_usd, user_invoice_eur) finally: with get_db_cursor(commit=True) as cur: cur.execute(f'''DELETE FROM public.transaction WHERE id={tr_inner.id}::int;''') cur.execute(f'''DELETE FROM public.invoice WHERE user_id={user.id}::int;''') cur.execute(f'''DELETE FROM public.user WHERE id={user.id}::int;''')
def tr_list_completed(user_fixed, user_fixed2): tr_lst = [] invoice_usd = user_fixed.invoices.get(USD, None) invoice_eur = user_fixed2.invoices.get(EUR, None) tr_lst.append(Transaction(invoice_usd.id, invoice_eur.id, invoice_usd.balance // 10).create().run()) tr_lst.append(Transaction(invoice_usd.id, invoice_eur.id, invoice_usd.balance // 10).create().run()) tr_bad = Transaction(invoice_usd.id, invoice_eur.id, invoice_usd.balance).create() try: tr_bad.run() except Exception as err: tr_lst.append(tr_bad) print("Badly transaction created: ", err) try: yield tr_lst finally: with get_db_cursor(commit=True) as cur: for tr in tr_lst: cur.execute(f'''DELETE FROM public.transaction WHERE id={tr.id}::int;''')
def create(self, cur=None): """ create transactions in db :param cur: psycopg2.cursor """ if not self.invoice_id_from: raise Exception("Bad invoice from") if not self.invoice_id_to: raise Exception("Bad invoice to") if self.invoice_id_from == self.invoice_id_to: raise Exception("Transaction don't create between one account") if cur is None: with get_db_cursor(commit=True) as cur: created_at, id = self.__create(cur) else: created_at, id = self.__create(cur) self.id = id self.created_at = created_at return self
def find_by_id(id): """ transaction select by id :param id: transaction id :return: Transaction object or None """ with get_db_cursor(commit=True) as cur: cur.execute( '''SELECT uuid, invoice_id_from, invoice_id_to, created_at, updated_at, amount, status FROM public.transaction WHERE id=%s::INT LIMIT 1''', (id, )) result = cur.fetchone() if result: uid, inv_id_from, inv_id_to, created_at, updated_at, amount, status = result return Transaction(inv_id_from, inv_id_to, amount, uid=uid, created_at=created_at, updated_at=updated_at, id=id, status=status) return None
def test_create(user): with get_db_cursor(commit=False) as cur: user.create(cur) cur.execute( f'''SELECT id, name, created_at FROM public.user WHERE id = {user.id} limit 1;''' ) u_id, name, created_at = cur.fetchone() assert u_id == user.id, "Bad id in saved user" assert name == user.name, "Bad name in saved user" assert created_at is not None, "Bad created_at in saved user" invoice_usd = user.invoices.get(USD, None) assert invoice_usd is not None, "Bad invoice USD in saved user" assert invoice_usd.balance == Decimal( 100), "Bad invoice USD balance in saved user" invoice_eur = user.invoices.get(EUR, None) assert invoice_eur is not None, "Bad invoice EUR in saved user" assert invoice_eur.balance == Decimal( 0), "Bad invoice EUR balance in saved user" invoice_cny = user.invoices.get(CNY, None) assert invoice_cny is not None, "Bad invoice CNY in saved user" assert invoice_cny.balance == Decimal( 0), "Bad invoice CNY balance in saved user"
def cur(): with get_db_cursor(commit=False) as cur: yield cur
def find_by_id(id, cur=None): if cur is None: with get_db_cursor(commit=True) as cur: return Invoice.__find_by_id(cur, id) else: return Invoice.__find_by_id(cur, id)