async def save_import(import_obj: Import, database: Database) -> Union[int, None]: """Create import and corresponding citizens and relations.""" async with database.transaction(): insert_import_query = imports.insert().values().returning( imports.c.import_id) import_id = await database.fetch_val(insert_import_query) if import_obj: max_citizens_per_insert = MAX_QUERY_ARGS // len(citizens.columns) citizens_rows = make_citizens_rows(import_obj, import_id) chunked_citizens = chunk_list(citizens_rows, max_citizens_per_insert) insert_citizens_query = citizens.insert() for chunk in chunked_citizens: await database.execute( insert_citizens_query.values(list(chunk))) max_relations_per_insert = MAX_QUERY_ARGS // len(relations.columns) relations_rows = make_relations_rows(import_obj, import_id) chunked_relations = chunk_list(relations_rows, max_relations_per_insert) insert_relations_query = relations.insert() for chunk in chunked_relations: await database.execute( insert_relations_query.values(list(chunk))) return import_id
async def transfer_money(database: Database, redis_pool: ConnectionPool, user: dict, target_user: dict, value: Decimal) -> int: context = getcontext() context.prec = 5 async with database.transaction(): currencies_data = await get_currencies(database, redis_pool) currencies = { data['id']: curr for curr, data in currencies_data.items() } currency_form = currencies[user['currency_id']] currency_to = currencies[target_user['currency_id']] query = users.update().where(users.c.id == user['id']) balance = Decimal(user['_balance']) sender_new_balance = balance - value await database.execute(query, {'_balance': sender_new_balance}) value_in_target_currency = await convert(value, currency_form, currency_to, database) query = users.update().where(users.c.id == target_user['id']) target_balance = Decimal(target_user['_balance']) target_new_balance = target_balance + value_in_target_currency await database.execute(query, {'_balance': target_new_balance}) transactions.insert().values(sender_id=user['id'], sender_new_balance=sender_new_balance, target_id=target_user['id'], target_new_balance=target_new_balance, currency_id=user['currency_id']) return await database.execute(query, {'_balance': target_new_balance})
async def patch_citizen(import_id: int, citizen_id: int, citizen_patch: CitizenPatch, database: Database) -> dict: """Update citizen.""" async with database.transaction(): await _update_citizen(import_id, citizen_id, citizen_patch, database) if isinstance(citizen_patch.relatives, list): curr_relatives_query = select([relations]).where( and_(relations.c.import_id == import_id, relations.c.citizen == citizen_id)) current_relatives = await database.fetch_all(curr_relatives_query) current_relatives = [r["relative"] for r in current_relatives] relatives_to_add = [ r for r in citizen_patch.relatives if r not in current_relatives ] if relatives_to_add: await _add_relatives(import_id, citizen_id, relatives_to_add, database) relatives_to_remove = [ r for r in current_relatives if r not in citizen_patch.relatives ] if relatives_to_remove: await _remove_relatives(import_id, citizen_id, relatives_to_remove, database) citizen = await _get_citizen(import_id=import_id, citizen_id=citizen_id, database=database) return citizen
class DbImport: IMPORT_CLASS = { "PrihlaskaPohledavky": PrihlaskaImporter, "PrehledovyList": PrehledovyListImporter, "ZpravaProOddluzeni": ZpravaProOddluzeniImporter, "ZpravaPlneniOddluzeni": ZpravaPlneniOddluzeniImporter, "ZpravaSplneniOddluzeni": ZpravaSplneniOddluzeniImporter, } def __init__(self, config, db=None): self.config = config if db is None: self.db = Database(self.config['db.dsn']) else: self.db = db self.metadata = {} async def importDocument(self, doc): try: typ = doc["Metadata"]["Typ"] importerCls = self.IMPORT_CLASS[typ] except KeyError: raise UnknownDocument() importer = importerCls(self.db, doc) if "isir_record" in self.metadata: importer.addIsirRecord(self.metadata["isir_record"]) if "pdf_file_size" in self.metadata: importer.pdf_file_size = round(self.metadata["pdf_file_size"], 3) if "db_conn" in self.metadata: importer.db_conn = self.metadata["db_conn"] await importer.startImport() async def run(self, filename): with open(filename, 'r') as f: fileContent = f.read() obj = json.loads(fileContent) # json muze obsahovat jeden nebo vice dokumentu documents = [] if isinstance(obj, list): documents = obj else: documents.append(obj) await self.db.connect() async with self.db.transaction(): for document in documents: await self.importDocument(document) await self.db.disconnect()
async def test_initial_migrations_deleted(create_command): with cleaner("alembic_env"): make_migrations = create_command.make_migrations() make_migrations() config = create_command.config migration_assertions(config) migrate = Commands(["app_one.conf", True, {}]).migrate() migrate() check = await check_table_exists(create_command.default_connection, "orders") assert check is True connection = create_command.default_connection if "mysql+pymysql" in connection: connection = connection.replace("mysql+pymysql", "mysql") database = Database(connection) if "sqlite" not in connection: query = ("DROP TABLE IF EXISTS orders, customer_a, customer_b," " vendor, customer_discount, vendor_discount;") await database.connect() await database.execute(query=query) await database.disconnect() else: async with database.transaction(): await database.execute(query="DROP TABLE IF EXISTS orders;") await database.execute( query="DROP TABLE IF EXISTS customer_a; ") await database.execute( query="DROP TABLE IF EXISTS customer_b; ") await database.execute(query="DROP TABLE IF EXISTS vendor; ") await database.execute( query="DROP TABLE IF EXISTS customer_discount; ") await database.execute( query="DROP TABLE IF EXISTS vendor_discount; ") shutil.rmtree("app_one/migrations") shutil.rmtree("app_two/migrations") shutil.rmtree("app_three/migrations") if os.path.exists("../crax/auth/migrations"): shutil.rmtree("../crax/auth/migrations") assert not os.path.exists("app_one/migrations") assert not os.path.exists("app_two/migrations") assert not os.path.exists("app_three/migrations") make_migrations = Commands(["app_one.conf", True, {}]).make_migrations() make_migrations() assert os.path.exists("app_one/migrations") assert os.path.exists("app_two/migrations") assert os.path.exists("app_three/migrations")
async def create_resource(db: Database, resource: "DomainResource", fhir_version: FHIR_VERSION): """ https://github.com/MagicStack/asyncpg/issues/526 :param db: :param resource: :param fhir_version: :return: """ es_id = "{0}_{1}_{2}".format( fhir_version.name.lower(), resource.resource_type.lower(), resource.id.replace("-", ""), ) resource_id = resource.id status = "created" resource_type = resource.resource_type resource_version = resource.meta.versionId resource = resource.json() fhir_version = fhir_version.value timestamp = datetime.utcnow() query1 = ( f"INSERT INTO {ResourceTransactionModel.__tablename__}(es_id, resource_id, status, resource_type, " "resource_version, resource, fhir_version, timestamp) VALUES (:es_id, :resource_id, " ":status, :resource_type, :resource_version, :resource, :fhir_version, :timestamp) RETURNING id" ) data1 = { "es_id": es_id, "resource_id": resource_id, "status": status, "resource_type": resource_type, "resource_version": resource_version, "resource": resource, "fhir_version": fhir_version, "timestamp": timestamp, } async with db.transaction(): primary_key = await db.execute(query1, values=data1) data1["id"] = primary_key return data1
async def reassign_figmentator( suggestion: Suggestion, figmentator: Figmentator, *, db: Database, session: ClientSession, ) -> Optional[Figmentator]: """ Reassign the story to a new figmentator for the given suggestion """ story = await Story.select(db, where={"hash": suggestion.story_hash}) if not story: logger.error("Story %s not found in database!", suggestion.story_hash) return None figmentators = [ f for f in await select_figmentators(db=db) if f.type == suggestion.type ] if not figmentators: raise InsufficientCapacityError("No preprocessors available") story.status = StoryStatus.ready context = {"story_id": story.hash, "story": story.story} completed, new_figmentator = await preprocess(context, figmentators.pop(), session=session) async with db.transaction(): if completed: where = {"model_id": figmentator.id, "story_hash": story.hash} await FigmentatorForStory(**where).delete(db, where=where) await FigmentatorForStory(model_id=new_figmentator.id, story_hash=story.hash).insert(db) else: story.status = StoryStatus.failed await story.update(db) logger.info("Reprocessed story=%s, status=%s", story.hash, story.status) return new_figmentator
async def add_resource_history( db: Database, record: typing.Dict[str, typing.Any], first: bool = True ): """ """ data = record.copy() data["txid"] = data.pop("id") data["prev_id"] = None del data["es_id"] if first is False: previous_pk = await db.fetch_val( ( f"SELECT id FROM {ResourceHistoryModel.__tablename__} " "WHERE txid=:txid ORDER BY timestamp DESC LIMIT 1" ), values={"txid": data["txid"]}, column="id", ) if previous_pk: data["prev_id"] = previous_pk async with db.transaction(): query1 = ( f"INSERT INTO {ResourceHistoryModel.__tablename__}" "(txid, resource_id, prev_id, status, resource_type, " "resource_version, resource, fhir_version, timestamp) " "VALUES (:txid, :resource_id, :prev_id, :status, :resource_type, " ":resource_version, :resource, :fhir_version, :timestamp) RETURNING id" ) pk = await db.execute(query1, values=data) if data["prev_id"]: query2 = ( f"UPDATE {ResourceHistoryModel.__tablename__} " "SET next_id=:next_id " "WHERE id=:id" ) await db.execute(query2, values={"next_id": pk, "id": data["prev_id"]})
async def migrate(db: Database, src_dir: Path = Path(__file__).absolute().parent / 'migrations', namespace: str = 'default'): """ Apply forward migration on database. :param db: async database connection :param src_dir: source directory with *.sql files, ordered by name (ex: 0001_abc.sql, 0002_def.sql) :param namespace: migration namespace (useful if several projects are using same db) """ logger = getLogger("db-migration") await db.execute(''' CREATE TABLE IF NOT EXISTS _migration ( name TEXT NOT NULL, namespace TEXT NOT NULL, PRIMARY KEY(name,namespace) )''') row = await db.fetch_one( 'SELECT name FROM _migration WHERE namespace = :namespace ORDER BY name DESC LIMIT 1', values={"namespace": namespace}) for file in sorted(src_dir.glob("*.sql")): if row is not None and file.name <= row[0]: logger.info("skipping %s", file.name) continue async with db.transaction(): logger.info("applying migration from %s", file.name) for statement in file.read_text().split(';'): logger.info("applying: %s", statement) await db.execute(statement) await db.execute( 'INSERT INTO _migration(name, namespace) VALUES(:name, :namespace)', values={ 'name': file.name, 'namespace': namespace }) logger.info("migration complete, namespace = %s", namespace)
class SQLiteDatasource(object): create_tbl_sub_tasks = """ CREATE TABLE IF NOT EXISTS Tasks ( id INTEGER PRIMARY KEY AUTOINCREMENT, uuid VARCHAR(100) NOT NULL UNIQUE, type VARCHAR(100) NOT NULL, url VARCHAR(1000), status INTEGER, profile VARCHAR(1000), submitted_ts VARCHAR(100) ) """ def __init__(self, db_path: str): self.database = Database(db_path) @staticmethod def _row_to_download_task( row: Optional[Mapping]) -> Optional[DownloadTask]: if row is not None: uuid = row["uuid"] url = row["url"] task_type = row["type"] profile = row["profile"] submitted_ts = row["submitted_ts"] status = TaskStatus(row["status"]) submitted_task = SubmittedTaskModel.create(url, uuid, submitted_ts, status, profile) if task_type == repr(TaskType.YTDL): return YTDLDownloadTask(submitted_task) else: return None else: return None async def connect(self): await self.database.connect() async def initialize(self): await self.database.execute(query=self.create_tbl_sub_tasks) async def disconnect(self): await self.database.disconnect() async def put(self, task: DownloadTask): task_type = repr(task.type) query = """ INSERT INTO Tasks(uuid, type, url, profile, submitted_ts, status) VALUES (:uuid, :type, :url, :profile, :submitted_ts, :status) """ params = { "uuid": task.submitted_task.uuid, "type": task_type, "url": task.submitted_task.url, "submitted_ts": task.submitted_task.submitted_ts, "profile": task.submitted_task.profile, "status": task.submitted_task.status } await self.database.execute(query, params) async def get(self) -> Optional[DownloadTask]: select_query = """ SELECT * FROM Tasks WHERE status = {} ORDER BY id ASC LIMIT 1 """.format(TaskStatus.Created) update_query = """ UPDATE Tasks SET status = {} WHERE id = :id """.format(TaskStatus.Processing) async with self.database.transaction(): row = await self.database.fetch_one(select_query, None) if row is not None: param = {"id": row["id"]} await self.database.execute(update_query, param) task = self._row_to_download_task(row) if task is not None: task.submitted_task.status = TaskStatus.Processing return task else: return None async def retry_processed(self): update_query = """ UPDATE Tasks SET status = {} WHERE status = {} """.format(TaskStatus.Created, TaskStatus.Processing) await self.database.execute(update_query, None) async def retry(self, _uuid: str): update_query = """ UPDATE Tasks SET status = {} WHERE uuid = :uuid """.format(TaskStatus.Created) params = {"uuid": _uuid} await self.database.execute(update_query, params) async def cleanup(self): delete_query = """ DELETE FROM Tasks WHERE status IN ({}, {}, {}) """.format(TaskStatus.Done, TaskStatus.Cancelled, TaskStatus.Failed) await self.database.execute(delete_query, None) async def set_status(self, uuid: str, status: TaskStatus): update_query = """ UPDATE Tasks SET status = :status WHERE uuid = :uuid """ params = {"status": status, "uuid": uuid} await self.database.execute(update_query, params) async def cancel(self, uuid: str): update_query = """ UPDATE Tasks SET status = {} WHERE uuid = :uuid """.format(TaskStatus.Cancelled) params = {"uuid": uuid} await self.database.execute(update_query, params) async def get_by_uuid(self, uuid: str) -> Optional[DownloadTask]: param = {"uuid": uuid} query = """ SELECT * FROM Tasks WHERE uuid = :uuid """ row = await self.database.fetch_one(query, param) return self._row_to_download_task(row) async def _list_by(self, query) -> List[DownloadTask]: list_rows = await self.database.fetch_all(query, None) maybe_tasks: Iterator[Optional[DownloadTask]] = map( self._row_to_download_task, list_rows) return [task for task in maybe_tasks if task is not None] async def list_all(self) -> List[DownloadTask]: query = "SELECT * FROM Tasks" return await self._list_by(query) async def list_running_or_queued(self) -> List[DownloadTask]: query = "SELECT * FROM Tasks WHERE status IN ({}, {})".format( TaskStatus.Processing, TaskStatus.Created) return await self._list_by(query) async def list_running(self) -> List[DownloadTask]: query = "SELECT * FROM Tasks WHERE status = {}".format( TaskStatus.Processing) return await self._list_by(query) async def list_queued(self) -> List[DownloadTask]: query = "SELECT * FROM Tasks WHERE status = {}".format( TaskStatus.Created) return await self._list_by(query)
class DB: """ `databases` wrapper that implements actual DB operations. """ def __init__(self, db_uri: DatabaseURL, *args, **kwargs): self._db = Database(db_uri, *args, **kwargs) def __call__(self) -> "DB": # Hook for FastAPI dependency injection return self @property def dbapi(self): # Shortcut for accessing dialect module, e.g. for handling errors. # DBAPI errors can be caught using `db.dbapi.Error` (or one of the more specific errors) dbapi = type(self._db._backend._dialect).dbapi() if hasattr(dbapi, "err"): return dbapi.err if hasattr(dbapi, "error"): return dbapi.error return dbapi async def get_account( self, id_or_username: Union[int, str]) -> Optional[models.Account]: if isinstance(id_or_username, int): query = account.select().where(account.c.id == id_or_username) else: query = account.select().where( account.c.username == id_or_username) res = await self._db.fetch_one(query) if res is not None: return models.Account.parse_obj(res) return None async def create_account(self, username: str, password: str): async with self._db.transaction(): await self._db.execute( account.insert(), values={ "username": username, "password": password }, ) async def create_transaction( self, id_: str, account_from: int, account_to: int, amount: int, type_: models.TransactionType, ): async with self._db.transaction(): if type_ is models.TransactionType.transfer: await self._db.execute(account.update().where( account.c.id == account_from).values( balance=account.c.balance - amount)) await self._db.execute(account.update().where( account.c.id == account_to).values(balance=account.c.balance + amount)) await self._db.execute( transaction.insert(), values={ "id": id_, "account_from": account_from, "account_to": account_to, "amount": amount, "type": type_, "created": datetime.utcnow().replace(microsecond=0), }, ) async def get_transaction( self, transaction_id: str, account_id: Optional[int] = None) -> Optional[models.Transaction]: query = self._transaction_query(for_account=account_id).where( transaction.c.id == transaction_id) res = await self._db.fetch_one(query) if res is not None: return self._build_transaction_model(res) return None async def get_transactions( self, account_id: int, dt_from: Optional[datetime] = None, dt_to: Optional[datetime] = None, limit: Optional[int] = None, order: Optional[models.Ordering] = None, ) -> list[models.Transaction]: query = self._transaction_query(for_account=account_id) if dt_from is not None: query = query.where(transaction.c.created >= dt_from) if dt_to is not None: query = query.where(transaction.c.created <= dt_to) if limit is not None: query = query.limit(limit) if order is not None: query = query.order_by(transaction.c.created.asc( ) if order is models.Ordering.asc else transaction.c.created.desc( )) res = await self._db.fetch_all(query) return [self._build_transaction_model(row) for row in res] # Helpers for selecting transactions @staticmethod def _transaction_query( for_account: Optional[int] = None, ) -> sqlalchemy.sql.expression.Select: account_from_alias = account.alias() account_to_alias = account.alias() query = (sqlalchemy.sql.select([ transaction, account_from_alias.c.username.label("account_from_username"), account_to_alias.c.username.label("account_to_username"), ]).where( transaction.c.account_from == account_from_alias.c.id, ).where( transaction.c.account_to == account_to_alias.c.id, )) if for_account is not None: query = query.where((transaction.c.account_from == for_account) | (transaction.c.account_to == for_account)) return query @staticmethod def _build_transaction_model( row: sqlalchemy.engine.RowProxy, ) -> models.Transaction: row = dict(row) row["account_from"] = { "id": row.pop("account_from"), "username": row.pop("account_from_username"), } row["account_to"] = { "id": row.pop("account_to"), "username": row.pop("account_to_username"), } return models.Transaction.construct(**row)