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