def test_bound_connection(self): users, User = self.tables.users, self.classes.User self.mapper_registry.map_imperatively(User, users) c = testing.db.connect() sess = Session(bind=c) sess.begin() transaction = sess._legacy_transaction() u = User(name="u1") sess.add(u) sess.flush() assert ( transaction._connection_for_bind(testing.db, None) is transaction._connection_for_bind(c, None) is c ) assert_raises_message( sa.exc.InvalidRequestError, "Session already has a Connection " "associated", transaction._connection_for_bind, testing.db.connect(), None, ) transaction.rollback() assert len(sess.query(User).all()) == 0 sess.close()
def create_items(db: Session): """ inserts seed into db """ db.begin() for item in catalog: db.add(item) db.commit()
def pg_session(pg_scoped_session: Session) -> Generator[Session, None, None]: """Initiate a transaction before each test function""" pg_scoped_session.begin() try: yield pg_scoped_session finally: pg_scoped_session.rollback()
def create(self, db: Session, *, obj_in: UserCreate) -> User: db.begin(subtransactions=True) db_obj = User( email=obj_in.email, ) db.add(db_obj) db.commit() db.refresh(db_obj) return db_obj
def delete_subscribers(db: Session, subscribers: List[Subscriber]): email = subscribers[0].email db.begin() data = db.query(DBSubscriber)\ .filter(DBSubscriber.email == email)\ .filter(DBSubscriber.state_id.in_([sub.state_id for sub in subscribers]))\ .filter(DBSubscriber.district_id.in_([sub.district_id for sub in subscribers]))\ .filter(DBSubscriber.min_age.in_([sub.min_age for sub in subscribers]))\ .delete() db.commit() return data > 0
def delete_pincode_subscribers(db: Session, subscribers: List[SubscriberPincodeModel]): email = subscribers[0].email db.begin() data = db.query(DBSubscriber)\ .filter(DBSubscriber.email == email)\ .filter(DBSubscriber.pincode.in_([sub.pincode for sub in subscribers]))\ .filter(DBSubscriber.min_age.in_([sub.min_age for sub in subscribers]))\ .delete() db.commit() return data > 0
async def post_person(person_in: PersonaIn, db: Session = Depends(get_db)): person_in_aux = person_in db.begin() db.add(PersonaInDB(**person_in_aux.dict())) try: db.commit() person_in_db = db.query(PersonaInDB).order_by(desc( PersonaInDB.per_id)).first() return person_in_db except exc.SQLAlchemyError as e: raise HTTPException(status_code=404, detail=mensaje(5, str(e.__dict__['orig']), "BD")) db.rollback() return None
def destroy_test_session(sess: Session) -> None: """ Remove the member and society records auto-generated in `create_test_session`, and revert the global session of `srcf.database.queries` to the default state. """ soc = queries.get_society("unittest", sess) mem = queries.get_member("spqr2", sess) sess.begin() soc.admins.remove(mem) sess.delete(soc) sess.delete(mem) sess.commit() queries._global_session = None queries._auto_create_global_session = True
def test_transaction_lifetime(session: Session) -> None: state = session_repository.app_state root_transaction = state.get_transaction(session) assert isinstance(root_transaction, RepositoryTransaction) assert root_transaction._parent is None # create sub-transaction (db savepoint) session.begin(nested=True) transaction = state.get_transaction(session) assert isinstance(transaction, RepositoryTransaction) assert transaction._parent is root_transaction session.flush() transaction = state.get_transaction(session)
def create_user_full(db_session: Session, user_data: UserData, profile_data: ProfileDataType) -> User: with db_session.begin(): user_type = user_data.user_type user = create_user(db_session, user_data) _ = create_profile(db_session, user.id, user_type, profile_data) return user
def test_contextmanager_commit(self): users, User = self.tables.users, self.classes.User mapper(User, users) sess = Session(autocommit=True) with sess.begin(): sess.add(User(name='u1')) sess.rollback() eq_(sess.query(User).count(), 1)
def create(self, db: Session, *, obj_in: WalletCreate) -> models.Wallet: db.begin(subtransactions=True) db_obj = models.Wallet( user_id=obj_in.user_id, balance=obj_in.balance, ) db.add(db_obj) db.flush() # write history db.add( models.WalletHistory( wallet_id=db_obj.id, user_id=db_obj.user_id, balance=db_obj.balance, )) db.commit() db.refresh(db_obj) return db_obj
def wrapper(*args, **kw): session = Session() if session.transaction is None: session.begin() try: ret = func(db_session=session, *args, **kw) session.commit() return ret except self.exceptions_tuple as e: session.rollback() if self.on_exception_handler: try: handler_res = self.on_exception_handler(e) except: raise else: warn('Handler swallowed exception "%s"' % str(e)) return handler_res else: raise
def test_preflush_no_accounting(self): User, users = self.classes.User, self.tables.users sess = Session(_enable_transaction_accounting=False, autocommit=True, autoflush=False) u1 = User(name='ed') sess.add(u1) sess.flush() sess.begin() u1.name = 'edwardo' u2 = User(name="some other user") sess.add(u2) sess.rollback() sess.begin() assert testing.db.execute(select([users.c.name])).fetchall() == \ [('ed',)]
def test_preflush_no_accounting(self): User, users = self.classes.User, self.tables.users sess = Session( _enable_transaction_accounting=False, autocommit=True, autoflush=False) u1 = User(name='ed') sess.add(u1) sess.flush() sess.begin() u1.name = 'edwardo' u2 = User(name="some other user") sess.add(u2) sess.rollback() sess.begin() assert testing.db.execute(select([users.c.name])).fetchall() == \ [('ed',)]
def test_index_only_after_final_commit(app: Application, session: Session, svc: WhooshIndexService) -> None: contact = IndexedContact(name="John Doe") state = svc.app_state session.begin(nested=True) assert state.to_update == [] session.add(contact) # no commit: model is in wait queue session.flush() assert state.to_update == [("new", contact)] # commit but in a sub transaction: model still in wait queue session.commit() assert state.to_update == [("new", contact)] # 'final' commit: models sent for indexing update session.commit() assert state.to_update == []
def persist_service_channel(Session, service, channel): session = Session() with session.begin(): session.add(service) session.commit() _service = session.query(Service).filter_by(name=service.name).one() channel.service_id = _service.id session.add(channel) session.commit() session.close()
class BaseRepo(): def __init__(self, model): self.model = model self.session = Session(engine) def insert(self, **kwargs): return insert(self.model).values(kwargs) def select(self): return select(self.model) # Core expression def execute_many(self, stmt_list: list) -> list: result_list = [] with self.session.begin(): for stmt in stmt_list: result = self.session.execute(stmt) result_list.append(result) return result_list def execute(self, stmt): result_list = self.execute_many([stmt]) return result_list[0] # ORM expression def check_object(self, model): if not isinstance(model, self.model): self.session.close() raise Exception(f'Wrong Model: expecting {self.model} received {type(model)}') def add_object(self, model): self.check_object(model) self.session.add(model) def delete_object(self, model): self.check_object(model) self.session.delete(model) def flush(self): return self.session.flush() def commit(self): self.flush() return self.session.commit() def rollback(self): self.flush() return self.session.rollback()
def test_contextmanager_rollback(self): users, User = self.tables.users, self.classes.User mapper(User, users) sess = Session(autocommit=True) def go(): with sess.begin(): sess.add(User()) # name can't be null assert_raises(sa_exc.DBAPIError, go) eq_(sess.query(User).count(), 0) with sess.begin(): sess.add(User(name='u1')) eq_(sess.query(User).count(), 1)
def test_contextmanager_rollback(self): users, User = self.tables.users, self.classes.User mapper(User, users) sess = Session(autocommit=True) def go(): with sess.begin(): sess.add(User()) # name can't be null assert_raises( sa_exc.DBAPIError, go ) eq_(sess.query(User).count(), 0) with sess.begin(): sess.add(User(name='u1')) eq_(sess.query(User).count(), 1)
def test_transaction_path(session: Session) -> None: """Test RepositoryTransaction create storage only when needed.""" u = uuid.uuid4() state = session_repository.app_state root_transaction = state.get_transaction(session) # assert not root_transaction.path.exists() with session.begin(subtransactions=True): transaction = state.get_transaction(session) assert not transaction.path.exists() session_repository.set(session, u, b"my file content") assert transaction.path.exists() assert root_transaction.path.exists() content = session_repository.get(session, u).open("rb").read() assert content == b"my file content" assert root_transaction.path.exists()
async def adding_money(*, balance_id: int, data: AddBalanceSchema, pg: Session = Depends(get_db)): """Зачисление денежных средств на кошелек клиента Args: balance_id: баланс на который зачислить средства data: данные о кол-ве зачисляемых средств pg: сессия к БД postgres Returns: BalanceSchema """ db_balance = pg.query(Balance).join(User).filter( Balance.id == balance_id).first() if not db_balance: raise HTTPException(status_code=400, detail=f"Not found balance id: {balance_id}") # patch if db_balance: pg.begin(subtransactions=True) try: system_balance = Balance.get_system_balance(pg) operation_from = Operations( amount=data.amount, operation_type=Operations.OperationsType.DEBIT, owner_balance=system_balance, more_balance=db_balance, created=datetime.now()) pg.add(operation_from) operation_in = Operations( amount=data.amount, operation_type=Operations.OperationsType.CREDIT, owner_balance=db_balance, more_balance=system_balance, created=datetime.now()) pg.add(operation_in) pg.commit() # transaction is committed here # pg.flush() if not system_balance.check_operation( db_balance, pg) or not db_balance.check_operation( system_balance, pg): raise ValueError('Error when checking operations') except Exception as e: pg.rollback() # rolls back the transaction raise HTTPException(status_code=400, detail=str(e)) system_balance.set_amount(pg) db_balance.set_amount(pg) pg.add(system_balance) pg.add(db_balance) pg.commit() pg.refresh(system_balance) pg.refresh(db_balance) return AddBalanceResponceShema(id=db_balance.id, username=db_balance.user.username, total=db_balance.amount, added=data.amount, currency=db_balance.currency.name)
async def transfer_money(*, balance_id: int, data: TransferBalanceSchema, pg: Session = Depends(get_db)): """Перевод денежных средств с одного кошелька на другой Args: balance_id: баланс c которого списываем средства data: данные о перечислении pg: сессия к БД postgres Returns: TransferBalanceSchema """ db_balance = pg.query(Balance).join(User).filter( Balance.id == balance_id).first() if not db_balance: raise HTTPException(status_code=400, detail=f"Not found balance id: {balance_id}") if db_balance.amount < data.amount: raise HTTPException( status_code=400, detail=f"Insufficient funds on the balance: {balance_id}") db_to_balance = pg.query(Balance).filter( Balance.id == data.to_balance).first() if not db_to_balance: raise HTTPException( status_code=400, detail=f"Not found recipient's balance id: {data.to_balance}") # patch if db_balance: pg.begin(subtransactions=True) try: operation_from = Operations( amount=data.amount, operation_type=Operations.OperationsType.DEBIT, owner_balance=db_balance, more_balance=db_to_balance, created=datetime.now()) pg.add(operation_from) operation_in = Operations( amount=data.amount, operation_type=Operations.OperationsType.CREDIT, owner_balance=db_to_balance, more_balance=db_balance, created=datetime.now()) pg.add(operation_in) pg.commit() # transaction is committed here # pg.flush() if not db_to_balance.check_operation( db_balance, pg) or not db_balance.check_operation( db_to_balance, pg): raise ValueError('Error when checking operations') except Exception as e: pg.rollback() # rolls back the transaction raise HTTPException(status_code=400, detail=str(e)) db_to_balance.set_amount(pg) db_balance.set_amount(pg) pg.add(db_to_balance) pg.add(db_balance) pg.commit() pg.refresh(db_to_balance) pg.refresh(db_balance) return TransferBalanceResponceShema(id=db_balance.id, recipient_balance=db_to_balance.id, amount=data.amount, currency=db_balance.currency.name)
id = Column('id', Integer, primary_key=True) title = Column(Text) text = Column(Text) def __repr__(self): return "<Entry('%s','%s','%s')>" \ % (self.id, self.title, self.text) for (k, v) in engines.items(): init_db(v) print '############################### master insertion ################################' with db_session.begin(subtransactions=True): db_session.add_all([ Entries(title='entry_a', text='hoge'), Entries(title='entry_b', text='fuga'), Entries(title='entry_c', text='piyo'), ]) print '###################### select (slave) ######################' rs = db_session.query(Entries).all() print_rs(rs) rs = db_session.query(Entries).all() print_rs(rs) rs = db_session.query(Entries).all() print_rs(rs) print '######################## master insersion ############################'
def main(): db_engine = db.db_connect(DB_CONF) while True: try: check_schedule(start_time=START_TIME, end_time=END_TIME, enabled=SCHEDULE_ENABLED) check_connectivity() except KeyboardInterrupt: logging.info(f'Bye.') return logging.info('Fetching a new job...') session = Session(bind=db_engine) uri = None tries = 0 while True: try: session.begin() job: models.Data = session \ .query(models.Data) \ .with_for_update(of=models.Data, skip_locked=True) \ .filter_by(download_state=models.Data.DOWNLOAD_PENDING) \ .first() if job is None: logging.info( 'No unclaimed job found. This program is about to exit.' ) session.close() return uri = job.uri job.started_at = datetime.datetime.now( tz=pytz.timezone(TIMEZONE)) job.download_state = models.Data.DOWNLOAD_DOWNLOADING session.add(job) session.commit() logging.info(f'New job fetched: ' f'{colorama.Fore.LIGHTCYAN_EX}' f'{{id={job.id}, uri={job.uri}}}' f'{colorama.Fore.RESET}' f'.') session.close() except Exception as e: if tries < RETRIES: session.rollback() logging.error(f'{colorama.Fore.LIGHTRED_EX}' f'An error has occurred: {e}' f'{colorama.Fore.RESET}') logging.info( f'Retry after {RETRY_INTERVAL} seconds ({RETRIES - tries} left)).' ) time.sleep(RETRY_INTERVAL) tries += 1 else: panic(f'{colorama.Fore.LIGHTRED_EX}' f'Failed to fetch a new job after {RETRIES} retries.' f'{colorama.Fore.RESET}') continue break url = f'{URL_BASE}/{uri}' logging.info(f'Download from ' f'{colorama.Fore.LIGHTCYAN_EX}' f'{url}' f'{colorama.Fore.RESET}') session = Session(bind=db_engine) try: file = pathlib.Path(DOWNLOAD_PATH).joinpath(uri) file.parent.mkdir(parents=True, exist_ok=True) tries = 0 while True: try: progbar = utils.DownloadProgBar() wget.download(url, out=str(file), bar=progbar.update) job = find_job_by_uri(session=session, uri=uri) job.worker = find_worker_by_name(session=session, name=WORKER_NAME) job.finished_at = datetime.datetime.now( tz=pytz.timezone(TIMEZONE)) job.size = int( urlopen(url).info().get('Content-Length', -1)) job.download_state = models.Data.DOWNLOAD_FINISHED logging.info(f'Job ' f'{colorama.Back.GREEN}{colorama.Fore.BLACK}' f'succeeded' f'{colorama.Fore.RESET}{colorama.Back.RESET}' f'.') break except KeyboardInterrupt: raise KeyboardInterrupt except Exception as e: if tries < RETRIES: logging.error(f'{colorama.Fore.LIGHTRED_EX}' f'An error has occurred: {e}' f'{colorama.Fore.RESET}') logging.info( f'Retry after {RETRY_INTERVAL} seconds ({RETRIES - tries} left)).' ) time.sleep(RETRY_INTERVAL) tries += 1 else: job = find_job_by_uri(session=session, uri=uri) job.download_state = models.Data.DOWNLOAD_FAILED logging.error(f'Job ' f'{colorama.Back.RED}' f'failed' f'{colorama.Back.RESET}' f'.') break session.add(job) session.commit() session.close() except KeyboardInterrupt: job = find_job_by_uri(session=session, uri=uri) job.started_at = None job.download_state = models.Data.DOWNLOAD_PENDING logging.warning(f'Job ' f'{colorama.Back.YELLOW}{colorama.Fore.BLACK}' f'cancelled' f'{colorama.Fore.RESET}{colorama.Back.RESET}' f'.') session.add(job) session.commit() session.close() return
class SQLAlchemyController(DBController): def __init__(self): """ Konstruktor """ self.session: Optional[Session] = None def connect(self, host: str, database: str, user: str, password: str, port: int, echo: bool = False): """ Funkce pro připojení k databázi. :param host: adresa host databáze :param database: jméno databáze :param user: jméno uživatele :param password: heslo pro přihlášení :param port: port :param echo: Boolean hodnota určující, zda má mají být logovány informace z databáze :return: None """ engine = create_engine( f'postgresql://{user}:{password}@{host}:{port}/{database}', encoding='utf-8', echo=echo) Session = sessionmaker(bind=engine) self.session = Session() self.session.autoflush = True self.session.autocommit = False engine.connect() def disconnect(self) -> None: """ Funkce pro odpojení od databáze. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :return: None """ assert self.session is not None self.session.close() def begin_transaction(self) -> None: """ Funkce pro spuštění nové transakce. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :return: None """ assert self.session is not None self.session.begin() def commit(self) -> None: """ Funkce pro potvrzení změn. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :return: None """ assert self.session is not None self.session.commit() def rollback(self) -> None: """ Funkce pro vrácení změn poslední transakce. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :return: """ assert self.session is not None self.session.rollback() # contract def get_contract(self, contract_id: int) -> Optional[Contract]: """ Funkce pro získání smlouvy z databáze pomocí id. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param contract_id: ID smlouvy v databázi. :return: Contract, s odpovídajícím id, pokud existuje. None, pokud neexistuje. """ assert self.session is not None contract = self.session.query(Contract).filter( Contract.contract_id == contract_id).first() return contract def insert_contract(self, contract: Contract) -> None: """ Funkce pro vložení smlouvy do databáze. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param contract: Contract, který chceme vložit. :return: None """ assert self.session is not None self.session.add(contract) return def update_contract(self, contract: Contract) -> None: """ Funkce pro aktualizaci smlouvy v databázi. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param contract: Contract, kde contract_id musí odpovídat smlouvě v databázi. :return: None """ assert self.session is not None self.session.merge(contract) return def remove_contract(self, contract: Contract) -> None: """ Funkce pro odstranění smlouvy z databáze. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param contract: Contract, který chceme odstranit. :return: None """ assert self.session is not None self.session.delete(contract) return def get_contracts( self, custom_filter: Callable[[Contract], bool] = None ) -> Optional[List[Contract]]: """ Funkce pro získání více smluv. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param custom_filter: Nepovinný filtr, který bude aplikován na každou smlouvu. :return: List[Contract] pokud smlouvy existují None, pokud nejsou dostupné žádné smlouvy """ assert self.session is not None rows = self.session.query(Contract) if custom_filter: result = [x for x in rows if custom_filter(x)] return result else: return list(rows) #invoice def get_invoice(self, invoice_id: int) -> Optional[Invoice]: """ Funkce pro získání faktury z databáze pomocí id. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param invoice_id: ID faktury v databázi. :return: Invoice, s odpovídajícím id, pokud existuje. None, pokud neexistuje. """ assert self.session is not None invoice = self.session.query(Invoice).filter( Invoice.invoice_id == invoice_id).first() return invoice def insert_invoice(self, invoice: Invoice) -> None: """ Funkce pro vložení faktury do databáze. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param invoice: Invoice, který chceme vložit. :return: None """ assert self.session is not None self.session.add(invoice) return def update_invoice(self, invoice: Invoice) -> None: """ Funkce pro aktualizaci faktury v databázi. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param invoice: Invoice, kde invoice_id musí odpovídat faktuře v databázi. :return: None """ assert self.session is not None self.session.merge(invoice) return def remove_invoice(self, invoice: Invoice) -> None: """ Funkce pro odstranění faktury z databáze. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param invoice: Invoice, který chceme odstranit. :return: None """ assert self.session is not None self.session.delete(invoice) return def get_invoices(self, page: int = None, page_size: int = None, custom_filter: Callable[[Invoice], bool] = None) -> \ Optional[List[Invoice]]: """ Funkce pro získání více faktur. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param page: číslo stránky, kterou chceme získat. :param page_size: Velikost stránky :param custom_filter: Filtr, který bude použit pro filtrování faktur. :return: List[Invoice] pokud smlouvy existují. None, pokud nejsou dostupné žádné smlouvy. """ assert self.session is not None rows = self.session.query(Invoice).order_by( Invoice.invoice_id).limit(page_size).offset(page * page_size) if custom_filter: result = [x for x in rows if custom_filter(x)] return result else: return list(rows) # possible_relation def get_possible_relation( self, possible_relation_id: int) -> Optional[PossibleRelation]: """ Funkce pro získání spojení z databáze pomocí id. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param possible_relation_id: ID spojení v databázi. :return: PossibleRelation, s odpovídajícím id, pokud existuje. None, pokud neexistuje. """ assert self.session is not None possible_relation = self.session.query(PossibleRelation).filter( PossibleRelation.invoice_id == possible_relation_id).first() return possible_relation def insert_possible_relation(self, relation: PossibleRelation) -> None: """ Funkce pro vložení spojení do databáze. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param relation: PossibleRelation, který chceme vložit. :return: None """ assert self.session is not None self.session.add(relation) return def update_possible_relation(self, possible_relation: PossibleRelation) -> None: """ Funkce pro aktualizaci spojení v databázi. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param possible_relation: PossibleRelation, kde possible_relation_id musí odpovídat spojení v databázi. :return: None """ assert self.session is not None self.session.merge(possible_relation) return def remove_possible_relation(self, possible_relation: PossibleRelation) -> None: """ Funkce pro odstranění spojení z databáze. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param possible_relation: PossibleRelation, který chceme odstranit. :return: None """ assert self.session is not None self.session.delete(possible_relation) return # test_result def get_test_result(self, test_result_id: int) -> Optional[TestResult]: """ Funkce pro získání výsledku testu z databáze pomocí id. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param test_result_id: ID výsledku testu v databázi. :return: TestResult, s odpovídajícím id, pokud existuje. None, pokud neexistuje. """ assert self.session is not None test_result = self.session.query(TestResult).filter( TestResult.test_result_id == test_result_id).first() return test_result def insert_test_result(self, test_result: TestResult) -> None: """ Funkce pro vložení výsledku testu do databáze. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param test_result: TestResult, který chceme vložit. :return: None """ assert self.session is not None self.session.add(test_result) return None def update_test_result(self, test_result: TestResult) -> None: """ Funkce pro aktualizaci výsledku testu v databázi. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param test_result: TestResult, kde possible_relation_id musí odpovídat spojení v databázi. :return: None """ assert self.session is not None self.session.merge(test_result) return def remove_test_result(self, test_result: TestResult) -> None: """ Funkce pro odstranění výsledku testu z databáze. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param test_result: TestResult, který chceme odstranit. :return: None """ assert self.session is not None self.session.delete(test_result) return # blocked supplier def get_blocked_supplier(self, blocked_supplier_id: int) -> BlockedSupplier: """ Funkce pro získání záznamu o blokovaném dodavateli z databáze pomocí id. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param blocked_supplier_id: ID výsledku testu v databázi. :return: BlockedSupplier, s odpovídajícím id, pokud existuje. None, pokud neexistuje. """ assert self.session is not None blocked_supplier = self.session.query(BlockedSupplier).filter( BlockedSupplier.blocked_supplier_id == blocked_supplier_id).first() return blocked_supplier def insert_blocked_supplier(self, blocked_supplier: BlockedSupplier) -> None: """ Funkce pro vložení záznamu o blokovaném dodavateli do databáze. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param blocked_supplier: BlockedSupplier, který chceme vložit. :return: None """ assert self.session is not None self.session.add(blocked_supplier) return None def update_blocked_supplier(self, blocked_supplier: BlockedSupplier) -> None: """ Funkce pro aktualizaci záznamu o blokovaném dodavateli testu v databázi. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param blocked_supplier: BlockedSupplier, kde possible_relation_id musí odpovídat spojení v databázi. :return: None """ assert self.session is not None self.session.merge(blocked_supplier) return def remove_blocked_supplier(self, blocked_supplier: BlockedSupplier) -> None: """ Funkce pro odstranění záznamu o blokovaném dodavateli z databáze. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param blocked_supplier: BlockedSupplier, který chceme odstranit. :return: None """ assert self.session is not None self.session.delete(blocked_supplier) return def get_blocked_suppliers(self) -> Optional[List[BlockedSupplier]]: """ Funkce pro získání všech záznamů o blokovaných dodavatelích. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :return: List[BlockedSupplier] pokud zázanmy existují None, pokud nejsou dostupné žádné záznamy """ assert self.session is not None rows = self.session.query(BlockedSupplier) return list(rows) # other def execute_query(self, command: str, params: Dict = None) -> Optional[List]: """ Funkce pro vykonání SQL příkazu nad databází, u kterého očekáváme výstup. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param command: SQL příkaz :param params: parametry ve formě slovníku, kde klíč je jméno parametru a value je hodnota :return: List s výsledky, pokud jsou dostupné None, pokud nejsou vráceny žádné výsledky """ assert self.session is not None rows = self.session.execute(command, params=params) return rows def execute_non_query(self, command: str, params: Dict = None) -> None: """ Funkce pro vykonání SQL příkazu, u kterého neočekáváme výslup. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param command: SQL příkaz :param params: parametry ve formě slovníku, kde klíč je jméno parametru a value je hodnota :return: None """ assert self.session is not None self.session.execute(command, params=params) def get_contracts_for_invoice( self, invoice: Invoice) -> Optional[List[Contract]]: """ Funkce pro získání smluv, které mohou být napárovány na fakturu podle IČO ministerstva a dodavatele. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param invoice: Invoice, pro kterou chceme vrátit navázané faktury. :return: List[Contract] pokud takové smlouvy existují. None, pokud takové smlouvy neexistují. """ assert self.session is not None rows = self.session.query(Contract).filter( Contract.ministry_ico == invoice.ministry_ico, Contract.supplier_ico == invoice.supplier_ico).all() return list(rows) def create_warnings(self, minimal_percentage_diff: float, maximal_percentage_diff: float) -> None: """ Funkce pro vytvoření záznamu o podezřelé zákázce na základě hodnoty smlouvy a součtu hodnot faktur. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :param minimal_percentage_diff: minimální procentuelní přírustek oproti hodnotě smlouvy. :param maximal_percentage_diff: maximální procentuelní přírustek oproti hodnotě smlouvy. :return: None """ assert self.session is not None command = """ delete from contract_warning; insert into contract_warning (contract_id, contract_amount, invoices_amount, difference) select res.contract_id, res.contract_amount, res.invoices_amount, round(res.difference_percentage * 100) as difference_percentage from ( select war.contract_id, war.contract_amount, war.contract_amount_diff_curr, sum(war.invoice_amount) as invoices_amount, sum(war.invoice_amount_diff_curr) as invoices_amount_diff_curr, sum(war.invoice_amount) - war.contract_amount as difference, sum(war.invoice_amount_diff_curr) - war.contract_amount_diff_curr as difference_diff_curr, (sum(war.invoice_amount) - war.contract_amount)/war.contract_amount as difference_percentage, (sum(war.invoice_amount_diff_curr) - war.contract_amount_diff_curr)/war.contract_amount_diff_curr as difference_diff_percentage from ( select c.contract_id, case when coalesce(c.amount_with_dph,0) > coalesce(c.amount_without_dph, 0) then coalesce(c.amount_with_dph,0) else coalesce(c.amount_without_dph,0) end as contract_amount, c.amount_different_currency as contract_amount_diff_curr, case when coalesce(i.amount_with_dph,0) > coalesce(i.amount_without_dph,0) then coalesce(i.amount_with_dph,0) else coalesce(i.amount_without_dph,0) end as invoice_amount, i.amount_different_currency as invoice_amount_diff_curr from contract c join ministry m on c.ministry_ico = m.ministry_ico join possible_relation pr on c.contract_id = pr.contract_id and pr.real = true join invoice i on pr.invoice_id = i.invoice_id where case when coalesce(c.amount_with_dph,0) > coalesce(c.amount_without_dph, 0) then coalesce(c.amount_with_dph,0) else coalesce(c.amount_without_dph,0) end != 0 ) war group by war.contract_id, war.contract_amount, war.contract_amount_diff_curr ) res where difference_percentage > 0 and round(res.difference_percentage * 100) >= :mininal_diff and round(res.difference_percentage * 100) <= :maximal_diff """ self.execute_non_query(command, params={ "mininal_diff": minimal_percentage_diff, "maximal_diff": maximal_percentage_diff }) return def refresh_statistics(self) -> None: """ Funkce pro vytvoření statistik na základě dat, které jsou dostupné v databázi. Předpokládá, že session je připojena k databázi. Pokud ne, vyvolá AssertionError. :return: None """ assert self.session is not None command = """ delete from statistics where type like 'num%'; insert into statistics (type, text_attribute, text_attribute_2, int_attribute, date_attribute) select 'num_of_contracts' as type, null as text_attribute, null as text_attribute_2, count(*) as int_attribute, now() as date_Attribute from contract join ministry m2 on contract.ministry_ico = m2.ministry_ico union select 'num_of_invoices' as type, null as text_attribute, null as text_attribute_2, count(*) as int_attribute, now() as date_Attribute from invoice join ministry m3 on invoice.ministry_ico = m3.ministry_ico union select 'num_of_linked' as type, null as text_attribute, null as text_attribute_2, count(*) as int_attribute, now() as date_Attribute from invoice i join possible_relation pr on i.invoice_id = pr.invoice_id and pr.real = true union select 'num_of_warnings' as type, null as text_attribute, null as text_attribute_2, count(*) as int_attribute, now() as date_Attribute from contract_warning union select 'num_contracts_per_ministry' as type, m.ministry_name as text_attribute, null as text_attribute_2, count(*) as int_attribute, now() as date_Attribute from contract c join ministry m on c.ministry_ico = m.ministry_ico group by m.ministry_name union select 'num_invoices_per_ministry' as type, m.ministry_name as text_attribute, null as text_attribute_2, count(*) as int_attribute, now() as date_Attribute from invoice i join ministry m on i.ministry_ico = m.ministry_ico group by m.ministry_name union select 'num_contracts_per_month' as type, to_char(co.date_published, 'YYYY_MM') as text_attribute, null as text_attribute_2, count(co.contract_id) as int_attribute, now() as date_Attribute from contract co join ministry m on co.ministry_ico = m.ministry_ico group by to_char(co.date_published, 'YYYY_MM') union select 'num_invoices_per_month' as type, to_char(iv.date_issue, 'YYYY_MM') as text_attribute, null as text_attribute_2, count(iv.invoice_id) as int_attribute, now() as date_Attribute from invoice iv join ministry m on iv.ministry_ico = m.ministry_ico group by to_char(iv.date_issue, 'YYYY_MM') union select 'num_invoices_per_month_ministry' as type, m.shortcut as text_attribute, to_char(iv.date_issue, 'YYYY') as text_attribute_2, count(iv.invoice_id) as int_attribute, now() as date_Attribute from invoice iv join ministry m on iv.ministry_ico = m.ministry_ico group by to_char(iv.date_issue, 'YYYY'), m.shortcut union select 'num_contracts_per_month_ministry' as type, m.shortcut as text_attribute, to_char(co.date_agreed, 'YYYY') as text_attribute_2, count(co.contract_id) as int_attribute, now() as date_Attribute from contract co join ministry m on co.ministry_ico = m.ministry_ico group by to_char(co.date_agreed, 'YYYY'), m.shortcut union select 'num_of_contracts_ministry' as type, m2.shortcut as text_attribute, null as text_attribute_2, count(*) as int_attribute, now() as date_Attribute from contract join ministry m2 on contract.ministry_ico = m2.ministry_ico group by m2.shortcut union select 'num_of_invoices_ministry' as type, m3.shortcut as text_attribute, null as text_attribute_2, count(*) as int_attribute, now() as date_Attribute from invoice join ministry m3 on invoice.ministry_ico = m3.ministry_ico group by m3.shortcut union select 'num_of_linked_ministry' as type, m3.shortcut as text_attribute, null as text_attribute_2, count(*) as int_attribute, now() as date_Attribute from invoice i join ministry m3 on i.ministry_ico = m3.ministry_ico join possible_relation pr on i.invoice_id = pr.invoice_id and pr.real = true group by m3.shortcut union select 'num_of_warnings_ministry' as type, shortcut as text_attribute, null as text_attribute_2, count(*) as int_attribute, now() as date_Attribute from ( select * from contract_warning cw join contract c on cw.contract_id = c.contract_id join ministry m on c.ministry_ico = m.ministry_ico ) as res group by res.shortcut order by 1 """ self.execute_non_query(command) return
os.remove(db) # 8a. Create tables - first in the leader/follower... for eng in 'leader', 'follower1', 'follower2': DefaultBase.metadata.create_all(engines[eng]) # 8b. then in "other"... OtherBase.metadata.create_all(engines['other']) # 9. Set up the Session Session = scoped_session(sessionmaker(autocommit=True, class_=RoutingSession)) # 10. then let's use it s = Session() with s.begin(): # 11. Writes go to "leader".... s.add_all([ Model1(data='m1_a'), Model2(data='m2_a'), Model1(data='m1_b'), Model2(data='m2_b'), Model3(data='m3_a'), Model3(data='m3_b'), ]) # 12. Pretend we're using a more substantial database backend # and "leader' is replicating to "follower1", "follower2" ##### PRETEND PRETEND PRETEND ###### shutil.copy("leader.db", "follower1.db") shutil.copy("leader.db", "follower2.db")
def test_transaction(session: Session) -> None: u = uuid.uuid4() repository.set(u, b"first draft") assert session_repository.get(session, u).open("rb").read() == b"first draft" session_repository.set(session, u, b"new content") # test nested (savepoint) # delete content but rollback transaction db_tr = session.begin(nested=True) session_repository.delete(session, u) assert session_repository.get(session, u) is None db_tr.rollback() assert session_repository.get(session, u).open("rb").read() == b"new content" # delete and commit with session.begin(nested=True): session_repository.delete(session, u) assert session_repository.get(session, u) is None assert session_repository.get(session, u) is None assert repository.get(u) is not None session.commit() assert repository.get(u) is None # delete: now test subtransactions (sqlalchemy) repository.set(u, b"first draft") db_tr = session.begin(subtransactions=True) session_repository.delete(session, u) assert session_repository.get(session, u) is None db_tr.rollback() assert session_repository.get(session, u).open("rb").read() == b"first draft" session.rollback() with session.begin(subtransactions=True): session_repository.delete(session, u) assert session_repository.get(session, u) is None assert session_repository.get(session, u) is None assert repository.get(u) is not None session.commit() assert repository.get(u) is None # now test 'set' session_repository.set(session, u, b"new content") session.commit() assert repository.get(u) is not None # test "set" in two nested transactions. This tests a specific code # branch, when a subtransaction overwrite data set in parent # transaction with session.begin(nested=True): session_repository.set(session, u, b"transaction 1") with session.begin(nested=True): session_repository.set(session, u, b"transaction 2") assert session_repository.get(session, u).open("rb").read() == b"transaction 2"
from sqlalchemy import Column, Integer from sqlalchemy.orm import declarative_base, Session class Test(declarative_base()): __tablename__ = 'test' id = Column(Integer, primary_key=True, autoincrement=True) state = Column(Integer, nullable=False) config = configs.config('configs') db_conf = db.get_database_config(config) db_engine = db.db_connect(db_conf) session = Session(bind=db_engine) session.begin() test: Test = session \ .query(Test) \ .with_for_update(skip_locked=True) \ .filter_by(state=1) \ .first() if test is None: print('No entry found.') else: print(test.id) test.state = 2 time.sleep(30) session.add(test) session.commit() session.close()
def transaction(session: Session): if not session.transaction: with session.begin(): yield else: yield