def transaction(session): tx = session.transaction(ydb.SerializableReadWrite()).begin() prepared_query = session.prepare(query) rs = tx.execute(prepared_query, parameters={'$cnt': cnt}, commit_tx=True) return rs[0].rows
def select_prepared(session, path, series_id, season_id, episode_id): query = """ PRAGMA TablePathPrefix("{}"); DECLARE $seriesId AS Uint64; DECLARE $seasonId AS Uint64; DECLARE $episodeId AS Uint64; $format = DateTime::Format("%Y-%m-%d"); SELECT title, $format(DateTime::FromSeconds(CAST(DateTime::ToSeconds(DateTime::IntervalFromDays(CAST(air_date AS Int16))) AS Uint32))) AS air_date FROM episodes WHERE series_id = $seriesId AND season_id = $seasonId AND episode_id = $episodeId; """.format(path) prepared_query = session.prepare(query) result_sets = session.transaction(ydb.SerializableReadWrite()).execute( prepared_query, { '$seriesId': series_id, '$seasonId': season_id, '$episodeId': episode_id, }, commit_tx=True) print("\n> select_prepared_transaction:") for row in result_sets[0].rows: print("episode title:", row.title, ", air date:", row.air_date) return result_sets[0]
def explicit_tcl(session, path, series_id, season_id, episode_id): query = """ PRAGMA TablePathPrefix("{}"); DECLARE $seriesId AS Uint64; DECLARE $seasonId AS Uint64; DECLARE $episodeId AS Uint64; UPDATE episodes SET air_date = CAST(CurrentUtcDate() AS Uint64) WHERE series_id = $seriesId AND season_id = $seasonId AND episode_id = $episodeId; """.format(path) prepared_query = session.prepare(query) # Get newly created transaction id tx = session.transaction(ydb.SerializableReadWrite()).begin() # Execute data query. # Transaction control settings continues active transaction (tx) tx.execute(prepared_query, { '$seriesId': series_id, '$seasonId': season_id, '$episodeId': episode_id }) print("\n> explicit TCL call") # Commit active transaction(tx) tx.commit()
def callee(session): session.transaction(ydb.SerializableReadWrite()).execute( session.prepare(FILL_DATA_QUERY.format(path)), commit_tx=True, parameters={ "$seriesData": get_series_data(), "$usersData": get_users_data(), })
def transaction(session): tx = session.transaction(ydb.SerializableReadWrite()).begin() prepared_query = session.prepare(query) tx.execute(prepared_query, parameters={ '$dt': dt, '$phone': phone.encode() }, commit_tx=True)
def get_next_page(self): result_sets = self.session.transaction( ydb.SerializableReadWrite()).execute( self.prepared_next_page_query, { "$limit": self.limit, "$lastCity": self.last_city, "$lastNumber": self.last_number, }, commit_tx=True) return result_sets[0]
def __init__(self, endpoint=None, host=None, port=None, db=None, database=None, autocommit=False): self.endpoint = endpoint or self._create_endpoint(host, port) self.database = database or db if not self.database.startswith('/'): self.database = '/' + self.database self.driver = self._create_driver(self.endpoint, self.database) self.session = ydb.retry_operation_sync(lambda: self.driver.table_client.session().create()) self.tx = self.session.transaction(ydb.SerializableReadWrite()) self.autocommit = autocommit
def callee(session): result_sets = session.transaction(ydb.SerializableReadWrite()).execute( session.prepare(SELECT_SERIES_BY_UPLOADER.format(path)), commit_tx=True, parameters={"$userName": username}) series_rows = map(lambda x: Series(**x), result_sets[0].rows) print("Series by %s" % username) for series in series_rows: print("Series %s" % str(series)) return series_rows
def callee(session): global FillDataQuery prepared_query = session.prepare(FillDataQuery.format(full_path)) session.transaction(ydb.SerializableReadWrite()).execute( prepared_query, commit_tx=True, parameters={ '$seriesData': basic_example_data.get_series_data(), '$seasonsData': basic_example_data.get_seasons_data(), '$episodesData': basic_example_data.get_episodes_data(), })
def get_task(event, context): print(event) task_id = event['params']['task_id'] driver_config = ydb.DriverConfig( YDB_ENDPOINT, DATABASE, credentials=ydb.construct_credentials_from_environ(), root_certificates=ydb.load_ydb_root_certificate(), ) with ydb.Driver(driver_config) as driver: try: driver.wait(timeout=5) except TimeoutError: print("Connect failed to YDB") print("Last reported errors by discovery:") print(driver.discovery_debug_details()) exit(1) session = driver.table_client.session().create() result_sets = session.transaction(ydb.SerializableReadWrite()).execute( f""" SELECT task_id, status, result_url FROM tasks WHERE task_id = "{task_id}"; """, commit_tx=True, ) body = None if result_sets[0].rows[0].status.decode("utf-8") == "DONE": body = { "task_id": result_sets[0].rows[0].task_id.decode("utf-8"), "status": result_sets[0].rows[0].status.decode("utf-8"), "result_url": result_sets[0].rows[0].result_url.decode("utf-8") } else: body = { "task_id": result_sets[0].rows[0].task_id.decode("utf-8"), "status": result_sets[0].rows[0].status.decode("utf-8") } return { 'statusCode': 200, 'body': json.dumps(body), }
def delete(self, series_id): """ :type series_id: int """ result_sets = self._session.session.transaction( ydb.SerializableReadWrite()).execute( self._session.queries[queries.DELETE_QUERY], { "$seriesId": series_id, }, commit_tx=True) if len(result_sets[0].rows) < 1: raise RuntimeError("Query count was not returned") return result_sets[0].rows[0].cnt
def insert(self, series): """ :type series: Series """ self._session.session.transaction(ydb.SerializableReadWrite()).execute( self._session.queries[queries.INSERT_QUERY], { "$seriesId": series.series_id, "$title": series.title, "$seriesInfo": series.series_info, "$releaseDate": model.to_days(series.release_date), "$views": series.views, }, commit_tx=True)
def fill_tables_with_data(session, path): global FillDataQuery prepared_query = session.prepare(FillDataQuery.format(path)) session.transaction(ydb.SerializableReadWrite()).execute( prepared_query, { '$seriesData': basic_example_data.get_series_data(), '$seasonsData': basic_example_data.get_seasons_data(), '$episodesData': basic_example_data.get_episodes_data(), }, commit_tx=True, )
def transaction(session): tx = session.transaction(ydb.SerializableReadWrite()).begin() prepared_query = session.prepare(query) tx.execute(prepared_query, parameters={ '$dt': int(dt.timestamp()), '$cnt': cnt, '$table_id': table_id, '$description': '' if description is None else description, '$phone': phone.encode() }, commit_tx=True)
def execute(config, query, params): with ydb.Driver(config) as driver: try: driver.wait(timeout=5) except TimeoutError: print("Connect failed to YDB") print("Last reported errors by discovery:") print(driver.discovery_debug_details()) return None session = driver.table_client.session().create() prepared_query = session.prepare(query) return session.transaction(ydb.SerializableReadWrite()).execute( prepared_query, params, commit_tx=True)
def callee(session): prepared_query = session.prepare(query) result_sets = session.transaction(ydb.SerializableReadWrite()).execute( prepared_query, commit_tx=True, parameters={ '$seriesId': series_id, '$seasonId': season_id, '$episodeId': episode_id, }) print("\n> select_prepared_transaction:") for row in result_sets[0].rows: print("episode title:", row.title, ", air date:", row.air_date) return result_sets[0]
def find_by_id(self, series_id): """ :type series_id: int :rtype: Series or None """ tx = self._session.session.transaction( ydb.SerializableReadWrite()).begin() result_sets = tx.execute( self._session.queries[queries.FIND_BY_ID_QUERY], { "$seriesId": series_id, }, commit_tx=True) if len(result_sets[0].rows) < 1: return None return self._extract_series(result_sets[0].rows[0])
def get_first_page(self): query = """ PRAGMA TablePathPrefix("{path}"); DECLARE $limit AS Uint32; SELECT * FROM schools ORDER BY city, number LIMIT $limit; """.format(path=self.path) prepared_query = self.session.prepare(query) result_sets = self.session.transaction( ydb.SerializableReadWrite()).execute(prepared_query, {"$limit": self.limit}, commit_tx=True) return result_sets[0]
def transaction(session): tx = session.transaction(ydb.SerializableReadWrite()).begin() prepared_query = session.prepare(query) rs = tx.execute(prepared_query, parameters={ '$cnt': cnt, '$dt': int(dt.timestamp()), '$reservation_period_minutes': Config.reservation_period_minutes() }, commit_tx=True) if len(rs[0].rows) > 0: return rs[0].rows return []
def update_views(self, series_id, new_views): """ :type series_id: int :type new_views: int """ tx = self._session.session.transaction( ydb.SerializableReadWrite()).begin() result_sets = tx.execute( self._session.queries[queries.UPDATE_VIEWS_QUERY], { "$seriesId": series_id, "$newViews": new_views, }, commit_tx=True) if len(result_sets[0].rows) < 1: raise RuntimeError("Query count was not returned") return result_sets[0].rows[0].cnt
def callee(session): prepared_query = session.prepare(query) # Get newly created transaction id tx = session.transaction(ydb.SerializableReadWrite()).begin() # Execute data query. # Transaction control settings continues active transaction (tx) tx.execute( prepared_query, { '$seriesId': series_id, '$seasonId': season_id, '$episodeId': episode_id }) print("\n> explicit TCL call") # Commit active transaction(tx) tx.commit()
def callee(session): # new transaction in serializable read write mode # if query successfully completed you will get result sets. # otherwise exception will be raised result_sets = session.transaction(ydb.SerializableReadWrite()).execute( """ PRAGMA TablePathPrefix("{}"); SELECT series_id, title, DateTime::ToDate(DateTime::FromDays(release_date)) AS release_date FROM series WHERE series_id = 1; """.format(path), commit_tx=True, ) print("\n> select_simple_transaction:") for row in result_sets[0].rows: print("series, id: ", row.series_id, ", title: ", row.title, ", release date: ", row.release_date) return result_sets[0]
def list_tasks(event, context): print(event) driver_config = ydb.DriverConfig( YDB_ENDPOINT, DATABASE, credentials=ydb.construct_credentials_from_environ(), root_certificates=ydb.load_ydb_root_certificate(), ) with ydb.Driver(driver_config) as driver: try: driver.wait(timeout=5) except TimeoutError: print("Connect failed to YDB") print("Last reported errors by discovery:") print(driver.discovery_debug_details()) exit(1) session = driver.table_client.session().create() result_sets = session.transaction(ydb.SerializableReadWrite()).execute( f""" SELECT task_id, status FROM tasks; """, commit_tx=True, ) result = {} for row in result_sets[0].rows: task_id = row.task_id.decode("utf-8") status = row.status.decode("utf-8") result[task_id] = status return { 'statusCode': 200, 'body': json.dumps(result), }
def fill_table(session, path): query = """ PRAGMA TablePathPrefix("{path}"); DECLARE $schoolsData AS "List<Struct< city: Utf8, number: Uint32, address: Utf8>>"; REPLACE INTO schools SELECT city, number, address FROM AS_TABLE($schoolsData); """.format(path=path) prepared_query = session.prepare(query) session.transaction(ydb.SerializableReadWrite()).execute( prepared_query, {"$schoolsData": get_schools_data()}, commit_tx=True, )
def find_all(self, limit, last_series_id=None): """ :type limit: int :type last_series_id: int or None :rtype: list(Series) """ tx = self._session.session.transaction( ydb.SerializableReadWrite()).begin() if last_series_id is None: result_sets = tx.execute( self._session.queries[queries.FIND_ALL_QUERY], { "$limit": limit, }, commit_tx=True) else: result_sets = tx.execute( self._session.queries[queries.FIND_ALL_NEXT_QUERY], { "$limit": limit, "$lastSeriesId": last_series_id }, commit_tx=True) return [self._extract_series(row) for row in result_sets[0].rows]
class Connection(object): isolation_level = ydb.SerializableReadWrite() driver = None def __init__(self, endpoint=None, host=None, port=None, db=None, database=None, autocommit=False): self.endpoint = endpoint or self._create_endpoint(host, port) self.database = database or db if not self.database.startswith('/'): self.database = '/' + self.database self.driver = self._create_driver(self.endpoint, self.database) self.session = ydb.retry_operation_sync(lambda: self.driver.table_client.session().create()) self.tx = self.session.transaction(ydb.SerializableReadWrite()) self.autocommit = autocommit def cursor(self): return Cursor(self) def execute(self, sql, parameters=None): return self.cursor().execute(sql, parameters) def executemany(self, sql, parameters): return self.cursor().executemany(sql, parameters) def commit(self): if self.tx.tx_id: self.tx.commit() self.tx = self.session.transaction(self.isolation_level) def rollback(self): if self.tx.tx_id: self.tx.rollback() self.tx = self.session.transaction(self.isolation_level) def close(self): pass @staticmethod def _create_endpoint(host, port): return '%s:%d' % (host, port) @staticmethod def _create_driver(endpoint, database): driver_id = (endpoint, database, ) driver = _DRIVERS.get(driver_id) if not driver: driver_config = ydb.DriverConfig( endpoint, database=database, credentials=Connection._create_credentials()) try: driver = ydb.Driver(driver_config) driver.wait(timeout=5) except Exception: raise DatabaseError('Failed to connect to YDB') _DRIVERS[driver_id] = driver return driver @staticmethod def _create_credentials(): if os.getenv('YDB_TOKEN') is not None: return ydb.AuthTokenCredentials(os.getenv('YDB_TOKEN')) if os.getenv('SA_ID') is not None: with open(os.getenv('SA_PRIVATE_KEY_FILE')) as private_key_file: root_certificates_file = os.getenv('SSL_ROOT_CERTIFICATES_FILE', None) iam_channel_credentials = {} if root_certificates_file is not None: with open(root_certificates_file, 'rb') as root_certificates_file: root_certificates = root_certificates_file.read() iam_channel_credentials = {'root_certificates': root_certificates} return iam.ServiceAccountCredentials( iam_endpoint=os.getenv('IAM_ENDPOINT', 'iam.api.cloud.yandex.net:443'), iam_channel_credentials=iam_channel_credentials, access_key_id=os.getenv('SA_ACCESS_KEY_ID'), service_account_id=os.getenv('SA_ID'), private_key=private_key_file.read() )