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()
)
