App in Python

This page contains a detailed description of the code of a test app that is available as part of the YDB Python SDK.

Downloading and starting

The start scenario given below uses git and Python3. Be sure to install the YDB Python SDK first.

Create a working directory and use it to run from the command line the command to clone the GitHub repository and install the necessary Python packages:

  1. git clone https://github.com/ydb-platform/ydb-python-sdk.git
  2. python3 -m pip install iso8601

Python - 图1

Next, from the same working directory, run the command to start the test app. The command will differ depending on the database to connect to.

Local Docker

Any database

To connect to a locally deployed YDB database according to the Docker use case, run the following command in the default configuration:

  1. YDB_ANONYMOUS_CREDENTIALS=1 \
  2. python3 ydb-python-sdk/examples/basic_example_v1/ -e grpc://localhost:2136 -d /local

Python - 图2

To run the example using any available YDB database, you need to know the Endpoint and Database location.

If authentication is enabled in the database, you also need to choose the authentication mode and obtain secrets: a token or username/password.

Run the command as follows:

  1. <auth_mode_var>="<auth_mode_value>" \
  2. python3 ydb-python-sdk/examples/basic_example_v1/ -e <endpoint> -d <database>

Python - 图3

, where

  • <endpoint> is the Endpoint
  • <database> is the DB location.
  • <auth_mode_var> is the Environment variable that determines the authentication mode.
  • <auth_mode_value> is the authentication parameter value for the selected mode.

For example:

  1. YDB_ACCESS_TOKEN_CREDENTIALS="t1.9euelZqOnJuJlc..." \
  2. python3 ydb-python-sdk/examples/basic_example_v1/ -e grpcs://ydb.example.com:2135 -d /path/db )

Python - 图4

Note

If you previously reviewed the articles of the “Getting started” section, you must have used the necessary parameters when getting started with the YDB CLI and can get them from the profile:

  1. ydb config profile get db1

Python - 图5

Initializing a database connection

To interact with YDB, create an instance of the driver, client, and session:

  • The YDB driver lets the app and YDB interact at the transport layer. The driver must exist throughout the YDB access lifecycle and be initialized before creating a client or session.
  • The YDB client runs on top of the YDB driver and enables the handling of entities and transactions.
  • The YDB session contains information about executed transactions and prepared queries, and is part of the YDB client context.

App code snippet for driver initialization:

  1. def run(endpoint, database, path):
  2.     driver_config = ydb.DriverConfig(
  3.         endpoint, database, credentials=ydb.construct_credentials_from_environ(),
  4.         root_certificates=ydb.load_ydb_root_certificate(),
  5.     )
  6.     with ydb.Driver(driver_config) as driver:
  7.         try:
  8.             driver.wait(timeout=5)
  9.         except TimeoutError:
  10.             print("Connect failed to YDB")
  11.             print("Last reported errors by discovery:")
  12.             print(driver.discovery_debug_details())
  13.             exit(1)

Python - 图6

App code snippet for creating a session:

  1. session = driver.table_client.session().create()

Python - 图7

Creating tables

Creating tables to be used in operations on a test app. This step results in the creation of DB tables of the series directory data model:

  • Series
  • Seasons
  • Episodes

Once the tables are created, the method for getting information about data schema objects is called and the result of its execution is output.

To create tables, use the session.create_table() method:

  1. def create_tables(session, path):
  2.     session.create_table(
  3.         os.path.join(path, 'series'),
  4.         ydb.TableDescription()
  5.         .with_column(ydb.Column('series_id', ydb.OptionalType(ydb.PrimitiveType.Uint64)))
  6.         .with_column(ydb.Column('title', ydb.OptionalType(ydb.PrimitiveType.Utf8)))
  7.         .with_column(ydb.Column('series_info', ydb.OptionalType(ydb.PrimitiveType.Utf8)))
  8.         .with_column(ydb.Column('release_date', ydb.OptionalType(ydb.PrimitiveType.Uint64)))
  9.         .with_primary_key('series_id')
  10.     )

Python - 图8

The absolute path from the root is passed in the path parameter:

  1. full_path = os.path.join(database, path)

Python - 图9

You can use the session.describe_table() method to output information about the table structure and make sure that it was properly created:

  1. def describe_table(session, path, name):
  2.     result = session.describe_table(os.path.join(path, name))
  3.     print("\n> describe table: series")
  4.     for column in result.columns:
  5.         print("column, name:", column.name, ",", str(column.type.item).strip())

Python - 图10

The given code snippet outputs the following text to the console at startup:

  1. > describe table: series
  2. ('column, name:', 'series_id', ',', 'type_id: UINT64')
  3. ('column, name:', 'title', ',', 'type_id: UTF8')
  4. ('column, name:', 'series_info', ',', 'type_id: UTF8')
  5. ('column, name:', 'release_date', ',', 'type_id: UINT64')

Python - 图11

Adding data

Adding data to the created tables using an UPSERT statement of YQL. A data update request is sent within a single request to the server with transaction auto-commit mode enabled.

Code snippet for inserting and updating data:

  1. def upsert_simple(session, path):
  2.     session.transaction().execute(
  3.         """
  4.         PRAGMA TablePathPrefix("{}");
  5.         UPSERT INTO episodes (series_id, season_id, episode_id, title) VALUES
  6.             (2, 6, 1, "TBD");
  7.         """.format(path),
  8.         commit_tx=True,
  9.     )

Python - 图12

PRAGMA TablePathPrefix adds a specified prefix to the database table paths. It uses standard file system path concatenation: i.e., it supports parent folder referencing and requires no trailing slash. For example:

  1. PRAGMA TablePathPrefix = "/cluster/database";
  2. SELECT * FROM episodes;

Python - 图13

For more information about PRAGMA YQL, see the YQL documentation.

Retrieving data with a Select

Retrieving data using a SELECT statement in YQL. Handling the retrieved data selection in the app.

To execute YQL queries, use the session.transaction().execute() method.
The SDK lets you explicitly control the execution of transactions and configure the transaction execution mode using the TxControl class.

In the code snippet below, the transaction is executed using the transaction().execute() method. The transaction execution mode set is ydb.SerializableReadWrite(). When all the queries in the transaction are completed, the transaction is automatically committed by explicitly setting the flag: commit_tx=True. The query body is described using the YQL syntax and is passed to the execute method as a parameter.

  1. def select_simple(session, path):
  2.     result_sets = session.transaction(ydb.SerializableReadWrite()).execute(
  3.         """
  4.         PRAGMA TablePathPrefix("{}");
  5.         $format = DateTime::Format("%Y-%m-%d");
  6.         SELECT
  7.             series_id,
  8.             title,
  9.             $format(DateTime::FromSeconds(CAST(DateTime::ToSeconds(DateTime::IntervalFromDays(CAST(release_date AS Int16))) AS Uint32))) AS release_date
  10.         FROM series
  11.         WHERE series_id = 1;
  12.         """.format(path),
  13.         commit_tx=True,
  14.     )
  15.     print("\n> select_simple_transaction:")
  16.     for row in result_sets[0].rows:
  17.         print("series, id: ", row.series_id, ", title: ", row.title, ", release date: ", row.release_date)
  19.     return result_sets[0]

Python - 图14

When the query is executed, result_set is returned whose iteration outputs the following text to the console:

  1. > SelectSimple:
  2. series, Id: 1, title: IT Crowd, Release date: 2006-02-03

Python - 图15

Parameterized prepared queries

Parameterized prepared queries are saved as templates where specially formatted names are replaced by relevant parameter values each time you execute the query. Use parameterized queries to improve performance by reducing how often queries that only differ in parameter values are compiled and recompiled. The prepared query is stored in the session context.

Code snippet for parameterized prepared queries:

  1. def select_prepared(session, path, series_id, season_id, episode_id):
  2.     query = """
  3.     PRAGMA TablePathPrefix("{}");
  4.     DECLARE $seriesId AS Uint64;
  5.     DECLARE $seasonId AS Uint64;
  6.     DECLARE $episodeId AS Uint64;
  7.     $format = DateTime::Format("%Y-%m-%d");
  8.     SELECT
  9.         title,
  10.         $format(DateTime::FromSeconds(CAST(DateTime::ToSeconds(DateTime::IntervalFromDays(CAST(air_date AS Int16))) AS Uint32))) AS air_date
  11.     FROM episodes
  12.     WHERE series_id = $seriesId AND season_id = $seasonId AND episode_id = $episodeId;
  13.     """.format(path)
  15.     prepared_query = session.prepare(query)
  16.     result_sets = session.transaction(ydb.SerializableReadWrite()).execute(
  17.         prepared_query, {
  18.             '$seriesId': series_id,
  19.             '$seasonId': season_id,
  20.             '$episodeId': episode_id,
  21.         },
  22.         commit_tx=True
  23.     )
  24.     print("\n> select_prepared_transaction:")
  25.     for row in result_sets[0].rows:
  26.         print("episode title:", row.title, ", air date:", row.air_date)
  28.     return result_sets[0]

Python - 图16

The given code snippet outputs the following text to the console at startup:

  1. > select_prepared_transaction:
  2. ('episode title:', u'To Build a Better Beta', ', air date:', '2016-06-05')

Python - 图17

Scan queries

Making a scan query that results in a data stream. Streaming lets you read an unlimited number of rows and amount of data.

  1. def executeScanQuery(driver):
  2.   query = ydb.ScanQuery("""
  3.     SELECT series_id, season_id, COUNT(*) AS episodes_count
  4.     FROM episodes
  5.     GROUP BY series_id, season_id
  6.     ORDER BY series_id, season_id
  7.   """, {})
  9.   it = driver.table_client.scan_query(query)
  11.   while True:
  12.     try:
  13.         result = next(it)
  14.         print result.result_set.rows
  15.     except StopIteration:
  16.         break

Python - 图18

Managing transactions

Transactions are managed through TCL Begin and Commit calls.

In most cases, instead of explicitly using Begin and Commit calls, it’s better to use transaction control parameters in execute calls. This helps you avoid unnecessary requests to YDB and run your queries more efficiently.

Code snippet for transaction().begin() and tx.Commit() calls:

  1. def explicit_tcl(session, path, series_id, season_id, episode_id):
  2.     query = """
  3.     PRAGMA TablePathPrefix("{}");
  5.     DECLARE $seriesId AS Uint64;
  6.     DECLARE $seasonId AS Uint64;
  7.     DECLARE $episodeId AS Uint64;
  9.     UPDATE episodes
  10.     SET air_date = CAST(CurrentUtcDate() AS Uint64)
  11.     WHERE series_id = $seriesId AND season_id = $seasonId AND episode_id = $episodeId;
  12.     """.format(path)
  13.     prepared_query = session.prepare(query)
  15.     tx = session.transaction(ydb.SerializableReadWrite()).begin()
  17.     tx.execute(
  18.         prepared_query, {
  19.             '$seriesId': series_id,
  20.             '$seasonId': season_id,
  21.             '$episodeId': episode_id
  22.         }
  23.     )
  25.     print("\n> explicit TCL call")
  27.     tx.commit()

Python - 图19