0
# Table Operations and Sessions
1
2
Comprehensive table operations including session management, transaction handling, query execution, table creation/modification, bulk operations, and point reads.
3
4
## Capabilities
5
6
### Session Pool Management
7
8
Session pool provides efficient session management with automatic retry, failover, and resource pooling.
9
10
```python { .api }
11
class SessionPool:
12
def __init__(
13
self,
14
driver: Driver,
15
size: int = None,
16
creation_timeout: float = None,
17
**kwargs
18
):
19
"""
20
Create session pool for database operations.
21
22
Args:
23
driver (Driver): YDB driver instance
24
size (int, optional): Maximum pool size
25
creation_timeout (float, optional): Session creation timeout
26
"""
27
28
def retry_operation_sync(
29
self,
30
callee: callable,
31
retry_settings: RetrySettings = None,
32
*args,
33
**kwargs
34
):
35
"""
36
Execute operation with automatic retry and session management.
37
38
Args:
39
callee (callable): Function to execute with session as first argument
40
retry_settings (RetrySettings, optional): Custom retry configuration
41
*args: Additional arguments for callee
42
**kwargs: Additional keyword arguments for callee
43
"""
44
45
def acquire(
46
self,
47
timeout: float = None,
48
settings: SessionCheckoutSettings = None
49
) -> Session:
50
"""
51
Acquire session from pool.
52
53
Args:
54
timeout (float, optional): Acquisition timeout
55
settings (SessionCheckoutSettings, optional): Checkout settings
56
57
Returns:
58
Session: Database session
59
"""
60
61
def release(self, session: Session):
62
"""
63
Return session to pool.
64
65
Args:
66
session (Session): Session to return
67
"""
68
69
def stop(self, timeout: float = None):
70
"""
71
Stop session pool and close all sessions.
72
73
Args:
74
timeout (float, optional): Shutdown timeout
75
"""
76
```
77
78
### Database Session Operations
79
80
Session provides the interface for executing queries and managing transactions.
81
82
```python { .api }
83
class Session:
84
def execute_query(
85
self,
86
query: str,
87
parameters: dict = None,
88
settings: ExecuteQuerySettings = None
89
):
90
"""
91
Execute YQL query in autocommit mode.
92
93
Args:
94
query (str): YQL query text
95
parameters (dict, optional): Query parameters
96
settings (ExecuteQuerySettings, optional): Execution settings
97
98
Returns:
99
List of result sets
100
"""
101
102
def transaction(
103
self,
104
tx_mode: TxMode = None,
105
settings: BeginTxSettings = None
106
) -> TxContext:
107
"""
108
Begin transaction context.
109
110
Args:
111
tx_mode (TxMode, optional): Transaction mode
112
settings (BeginTxSettings, optional): Transaction settings
113
114
Returns:
115
TxContext: Transaction context manager
116
"""
117
118
def prepare_query(
119
self,
120
query: str,
121
settings: PrepareQuerySettings = None
122
) -> DataQuery:
123
"""
124
Prepare query for multiple executions.
125
126
Args:
127
query (str): YQL query text
128
settings (PrepareQuerySettings, optional): Preparation settings
129
130
Returns:
131
DataQuery: Prepared query object
132
"""
133
134
def create_table(
135
self,
136
path: str,
137
table_description: TableDescription,
138
settings: CreateTableSettings = None
139
):
140
"""
141
Create new table.
142
143
Args:
144
path (str): Table path
145
table_description (TableDescription): Table schema
146
settings (CreateTableSettings, optional): Creation settings
147
"""
148
149
def alter_table(
150
self,
151
path: str,
152
alter_table_settings: AlterTableSettings
153
):
154
"""
155
Modify existing table schema.
156
157
Args:
158
path (str): Table path
159
alter_table_settings (AlterTableSettings): Modification settings
160
"""
161
162
def drop_table(
163
self,
164
path: str,
165
settings: DropTableSettings = None
166
):
167
"""
168
Delete table.
169
170
Args:
171
path (str): Table path
172
settings (DropTableSettings, optional): Deletion settings
173
"""
174
175
def describe_table(
176
self,
177
path: str,
178
settings: DescribeTableSettings = None
179
) -> TableDescription:
180
"""
181
Get table schema information.
182
183
Args:
184
path (str): Table path
185
settings (DescribeTableSettings, optional): Description settings
186
187
Returns:
188
TableDescription: Table schema details
189
"""
190
191
def bulk_upsert(
192
self,
193
path: str,
194
rows: List[dict],
195
settings: BulkUpsertSettings = None
196
):
197
"""
198
Bulk insert/update rows.
199
200
Args:
201
path (str): Table path
202
rows (List[dict]): Row data
203
settings (BulkUpsertSettings, optional): Upsert settings
204
"""
205
206
def read_table(
207
self,
208
path: str,
209
key_range: KeyRange = None,
210
columns: List[str] = None,
211
settings: ReadTableSettings = None
212
):
213
"""
214
Read table data with streaming.
215
216
Args:
217
path (str): Table path
218
key_range (KeyRange, optional): Key range filter
219
columns (List[str], optional): Columns to read
220
settings (ReadTableSettings, optional): Read settings
221
222
Yields:
223
Result sets with table data
224
"""
225
```
226
227
### Transaction Context
228
229
Transaction context manager for executing multiple operations atomically.
230
231
```python { .api }
232
class TxContext:
233
def execute(
234
self,
235
query: str,
236
parameters: dict = None,
237
commit_tx: bool = False,
238
settings: ExecuteQuerySettings = None
239
):
240
"""
241
Execute query within transaction.
242
243
Args:
244
query (str): YQL query text
245
parameters (dict, optional): Query parameters
246
commit_tx (bool): Auto-commit after execution
247
settings (ExecuteQuerySettings, optional): Execution settings
248
249
Returns:
250
List of result sets
251
"""
252
253
def commit(self, settings: CommitTxSettings = None):
254
"""
255
Commit transaction.
256
257
Args:
258
settings (CommitTxSettings, optional): Commit settings
259
"""
260
261
def rollback(self, settings: RollbackTxSettings = None):
262
"""
263
Rollback transaction.
264
265
Args:
266
settings (RollbackTxSettings, optional): Rollback settings
267
"""
268
```
269
270
### Prepared Queries
271
272
Prepared queries for efficient repeated execution.
273
274
```python { .api }
275
class DataQuery:
276
def execute(
277
self,
278
parameters: dict = None,
279
settings: ExecuteQuerySettings = None
280
):
281
"""
282
Execute prepared query.
283
284
Args:
285
parameters (dict, optional): Query parameters
286
settings (ExecuteQuerySettings, optional): Execution settings
287
288
Returns:
289
List of result sets
290
"""
291
292
@property
293
def query_id(self) -> str:
294
"""Get query identifier for caching."""
295
```
296
297
### Table Schema Definitions
298
299
Classes for defining table structure and constraints.
300
301
```python { .api }
302
class TableDescription:
303
def __init__(self):
304
"""Create empty table description."""
305
306
def with_column(self, column: TableColumn) -> 'TableDescription':
307
"""
308
Add column to table definition.
309
310
Args:
311
column (TableColumn): Column definition
312
313
Returns:
314
TableDescription: Self for chaining
315
"""
316
317
def with_primary_key(self, *key_names: str) -> 'TableDescription':
318
"""
319
Set primary key columns.
320
321
Args:
322
*key_names (str): Primary key column names
323
324
Returns:
325
TableDescription: Self for chaining
326
"""
327
328
def with_index(self, index: TableIndex) -> 'TableDescription':
329
"""
330
Add secondary index.
331
332
Args:
333
index (TableIndex): Index definition
334
335
Returns:
336
TableDescription: Self for chaining
337
"""
338
339
class TableColumn:
340
def __init__(self, name: str, type_: Type, family: str = None):
341
"""
342
Define table column.
343
344
Args:
345
name (str): Column name
346
type_ (Type): Column data type
347
family (str, optional): Column family name
348
"""
349
350
class TableIndex:
351
def __init__(
352
self,
353
name: str,
354
index_columns: List[str],
355
data_columns: List[str] = None
356
):
357
"""
358
Define secondary index.
359
360
Args:
361
name (str): Index name
362
index_columns (List[str]): Indexed columns
363
data_columns (List[str], optional): Additional data columns
364
"""
365
```
366
367
### Transaction Modes
368
369
Transaction isolation levels and modes.
370
371
```python { .api }
372
class TxMode:
373
"""Transaction modes for different consistency levels."""
374
375
SERIALIZABLE_RW: TxMode # Serializable read-write
376
ONLINE_RO: TxMode # Online read-only
377
STALE_RO: TxMode # Stale read-only
378
SNAPSHOT_RO: TxMode # Snapshot read-only
379
380
class OnlineReadOnlyTxMode:
381
"""Online read-only transaction modes."""
382
383
def __init__(self, allow_inconsistent_reads: bool = False):
384
"""
385
Configure online read-only mode.
386
387
Args:
388
allow_inconsistent_reads (bool): Allow inconsistent reads
389
"""
390
391
class StalenessMode:
392
"""Staleness modes for stale read-only transactions."""
393
394
def __init__(self, max_staleness: int, unit: Unit = Unit.SECONDS):
395
"""
396
Configure staleness parameters.
397
398
Args:
399
max_staleness (int): Maximum staleness value
400
unit (Unit): Time unit for staleness
401
"""
402
```
403
404
### Key Range Operations
405
406
Define key ranges for point reads and scans.
407
408
```python { .api }
409
class KeyRange:
410
def __init__(
411
self,
412
from_bound: KeyBound = None,
413
to_bound: KeyBound = None
414
):
415
"""
416
Define key range for table operations.
417
418
Args:
419
from_bound (KeyBound, optional): Lower bound
420
to_bound (KeyBound, optional): Upper bound
421
"""
422
423
@classmethod
424
def prefix(cls, prefix_tuple: tuple) -> 'KeyRange':
425
"""
426
Create range for key prefix.
427
428
Args:
429
prefix_tuple (tuple): Key prefix values
430
431
Returns:
432
KeyRange: Range matching prefix
433
"""
434
435
@classmethod
436
def point(cls, key_tuple: tuple) -> 'KeyRange':
437
"""
438
Create range for single key.
439
440
Args:
441
key_tuple (tuple): Exact key values
442
443
Returns:
444
KeyRange: Point range
445
"""
446
447
class KeyBound:
448
def __init__(self, key_tuple: tuple, is_inclusive: bool = True):
449
"""
450
Define key boundary.
451
452
Args:
453
key_tuple (tuple): Key values
454
is_inclusive (bool): Include boundary in range
455
"""
456
```
457
458
## Usage Examples
459
460
### Basic Table Operations
461
462
```python
463
import ydb
464
465
# Setup driver and session pool
466
driver = ydb.Driver(
467
endpoint="grpc://localhost:2136",
468
database="/local",
469
credentials=ydb.AnonymousCredentials()
470
)
471
driver.wait(fail_fast=True)
472
473
session_pool = ydb.SessionPool(driver)
474
475
def create_and_populate_table(session):
476
# Create table
477
session.create_table(
478
'/local/users',
479
ydb.TableDescription()
480
.with_column(ydb.TableColumn('id', ydb.OptionalType(ydb.PrimitiveType.Uint64)))
481
.with_column(ydb.TableColumn('name', ydb.OptionalType(ydb.PrimitiveType.Utf8)))
482
.with_column(ydb.TableColumn('email', ydb.OptionalType(ydb.PrimitiveType.Utf8)))
483
.with_primary_key('id')
484
)
485
486
# Insert data
487
session.transaction().execute(
488
"""
489
INSERT INTO users (id, name, email)
490
VALUES (1, "Alice", "alice@example.com"),
491
(2, "Bob", "bob@example.com");
492
""",
493
commit_tx=True
494
)
495
496
# Execute with retry
497
session_pool.retry_operation_sync(create_and_populate_table)
498
```
499
500
### Query Execution with Parameters
501
502
```python
503
def query_users(session):
504
# Parameterized query
505
result_sets = session.transaction().execute(
506
"""
507
DECLARE $min_id AS Uint64;
508
SELECT id, name, email
509
FROM users
510
WHERE id >= $min_id
511
ORDER BY id;
512
""",
513
parameters={'$min_id': 1},
514
commit_tx=True
515
)
516
517
for result_set in result_sets:
518
for row in result_set.rows:
519
print(f"User: {row.name} ({row.email})")
520
521
session_pool.retry_operation_sync(query_users)
522
```
523
524
### Bulk Operations
525
526
```python
527
def bulk_insert_users(session):
528
# Bulk upsert for efficient large data inserts
529
users_data = [
530
{'id': 3, 'name': 'Charlie', 'email': 'charlie@example.com'},
531
{'id': 4, 'name': 'Diana', 'email': 'diana@example.com'},
532
{'id': 5, 'name': 'Eve', 'email': 'eve@example.com'},
533
]
534
535
session.bulk_upsert('/local/users', users_data)
536
537
session_pool.retry_operation_sync(bulk_insert_users)
538
```
539
540
### Transaction Management
541
542
```python
543
def transfer_operation(session):
544
# Multi-statement transaction
545
tx = session.transaction(ydb.SerializableReadWrite())
546
547
try:
548
# Check balance
549
result_sets = tx.execute(
550
"""
551
DECLARE $from_id AS Uint64;
552
SELECT balance FROM accounts WHERE id = $from_id;
553
""",
554
parameters={'$from_id': 1}
555
)
556
557
balance = result_sets[0].rows[0].balance
558
if balance < 100:
559
raise ValueError("Insufficient funds")
560
561
# Perform transfer
562
tx.execute(
563
"""
564
DECLARE $from_id AS Uint64;
565
DECLARE $to_id AS Uint64;
566
DECLARE $amount AS Uint64;
567
568
UPDATE accounts SET balance = balance - $amount WHERE id = $from_id;
569
UPDATE accounts SET balance = balance + $amount WHERE id = $to_id;
570
""",
571
parameters={'$from_id': 1, '$to_id': 2, '$amount': 100}
572
)
573
574
# Commit transaction
575
tx.commit()
576
577
except Exception:
578
# Rollback on error
579
tx.rollback()
580
raise
581
582
session_pool.retry_operation_sync(transfer_operation)
583
```
584
585
### Prepared Queries
586
587
```python
588
def use_prepared_query(session):
589
# Prepare query once
590
prepared_query = session.prepare_query(
591
"""
592
DECLARE $user_id AS Uint64;
593
SELECT name, email FROM users WHERE id = $user_id;
594
"""
595
)
596
597
# Execute multiple times with different parameters
598
for user_id in [1, 2, 3]:
599
result_sets = prepared_query.execute(
600
parameters={'$user_id': user_id}
601
)
602
603
for result_set in result_sets:
604
for row in result_set.rows:
605
print(f"User {user_id}: {row.name}")
606
607
session_pool.retry_operation_sync(use_prepared_query)
608
```
609
610
### Table Streaming Reads
611
612
```python
613
def stream_table_data(session):
614
# Stream large table efficiently
615
for result_set in session.read_table('/local/large_table'):
616
for row in result_set.rows:
617
# Process row
618
process_row(row)
619
620
def process_row(row):
621
# Process individual row
622
print(f"Processing: {row}")
623
624
session_pool.retry_operation_sync(stream_table_data)
625
```
626
627
## Types
628
629
```python { .api }
630
from typing import List, Dict, Any, Optional, Iterator
631
632
# Settings classes for operations
633
class SessionCheckoutSettings:
634
def __init__(self, timeout: float = None): ...
635
636
class ExecuteQuerySettings:
637
def __init__(self, **kwargs): ...
638
639
class PrepareQuerySettings:
640
def __init__(self, **kwargs): ...
641
642
class CreateTableSettings:
643
def __init__(self, **kwargs): ...
644
645
class AlterTableSettings:
646
def __init__(self, **kwargs): ...
647
648
class DropTableSettings:
649
def __init__(self, **kwargs): ...
650
651
class DescribeTableSettings:
652
def __init__(self, **kwargs): ...
653
654
class BulkUpsertSettings:
655
def __init__(self, **kwargs): ...
656
657
class ReadTableSettings:
658
def __init__(self, **kwargs): ...
659
660
class BeginTxSettings:
661
def __init__(self, **kwargs): ...
662
663
class CommitTxSettings:
664
def __init__(self, **kwargs): ...
665
666
class RollbackTxSettings:
667
def __init__(self, **kwargs): ...
668
669
# Type aliases
670
QueryText = str
671
Parameters = Dict[str, Any]
672
TablePath = str
673
ColumnName = str
674
```