Skip to content

Latest commit

 

History

History
163 lines (124 loc) · 4.77 KB

README.md

File metadata and controls

163 lines (124 loc) · 4.77 KB

YDB Python DBAPI

Introduction

Python DBAPI to YDB, which provides both sync and async drivers and complies with PEP249.

Installation

pip install ydb-dbapi

Usage

To establish a new DBAPI connection you should provide host, port and database:

import ydb_dbapi

connection = ydb_dbapi.connect(
    host="localhost", port="2136", database="/local"
) # sync connection

async_connection = await ydb_dbapi.async_connect(
    host="localhost", port="2136", database="/local"
) # async connection

For TLS connections, pass PEM certificate contents as bytes through root_certificates, or use root_certificates_path to load them from a file. Shared session pools must match the connection API: ydb.QuerySessionPool for connect() and ydb.aio.QuerySessionPool for async_connect().

Usage of connection:

with connection.cursor() as cursor:
    cursor.execute("SELECT id, val FROM table")

    row = cursor.fetchone()
    rows = cursor.fetchmany(size=5)
    rows = cursor.fetchall()

Usage of async connection:

async with async_connection.cursor() as cursor:
    await cursor.execute("SELECT id, val FROM table")

    row = await cursor.fetchone()
    rows = await cursor.fetchmany(size=5)
    rows = await cursor.fetchall()

Query parameters

Standard mode (pyformat=True)

Pass pyformat=True to connect() to enable familiar Python DB-API parameter syntax such as %(name)s and %s. This mode is opt-in: the default connection mode (pyformat=False) uses YDB-style $name placeholders instead, so %(name)s and %s do not work unless pyformat=True is set explicitly. The driver will convert placeholders and infer YDB types from Python values automatically.

Named parameters — %(name)s with a dict:

connection = ydb_dbapi.connect(
    host="localhost", port="2136", database="/local",
    pyformat=True,
)

with connection.cursor() as cursor:
    cursor.execute(
        "SELECT * FROM users WHERE id = %(id)s AND active = %(active)s",
        {"id": 42, "active": True},
    )

Positional parameters — %s with a list or tuple:

with connection.cursor() as cursor:
    cursor.execute(
        "INSERT INTO users (id, name, score) VALUES (%s, %s, %s)",
        [1, "Alice", 9.8],
    )

Use %% to insert a literal % character in the query.

The driver validates pyformat placeholders before executing the query:

  • do not mix named %(name)s and positional %s placeholders in one query;
  • named placeholders require a dict with bare keys like {"id": 1};
  • positional placeholders require a list or tuple;
  • missing or extra parameters raise ProgrammingError;
  • keys starting with $ are not allowed in pyformat=True mode.

Automatic type mapping:

Python type YDB type
bool Bool
int Int64
float Double
str Utf8
bytes String
datetime.datetime Timestamp
datetime.date Date
datetime.timedelta Interval
decimal.Decimal Decimal(22, 9)
None NULL (passed as-is)

Explicit types with ydb.TypedValue:

When automatic inference is not suitable (e.g. you need Int32 instead of Int64, or Json), wrap the value in ydb.TypedValue — it will be passed through unchanged:

import ydb

with connection.cursor() as cursor:
    cursor.execute(
        "INSERT INTO events (id, payload) VALUES (%(id)s, %(payload)s)",
        {
            "id": ydb.TypedValue(99, ydb.PrimitiveType.Int32),
            "payload": ydb.TypedValue('{"key": "value"}', ydb.PrimitiveType.Json),
        },
    )

If the driver cannot infer a YDB type for a Python value, it raises TypeError. Use ydb.TypedValue for such values or when you need an explicit YDB type.

Native YDB mode (default, deprecated)

Deprecated. Native YDB mode is the current default for backwards compatibility, but it will be removed in a future release. Migrate to pyformat=True at your earliest convenience.

By default (pyformat=False) the driver passes the query and parameters directly to the YDB SDK without any transformation. Passing a list or tuple in native mode raises ProgrammingError. Use pyformat=True with execute() for positional parameters.

Use $name placeholders in the query and supply a dict with $-prefixed keys:

connection = ydb_dbapi.connect(
    host="localhost", port="2136", database="/local",
)

with connection.cursor() as cursor:
    cursor.execute(
        "SELECT * FROM users WHERE id = $id",
        {"$id": ydb.TypedValue(42, ydb.PrimitiveType.Int64)},
    )