YDB Dialect for SQLAlchemy

This repository contains YQL dialect for SqlAlchemy 2.0.

Documentation: https://ydb-platform.github.io/ydb-sqlalchemy

Note: Dialect also works with SqlAlchemy 1.4, but it is not fully tested.

Installation

Via PyPI

To install ydb-sqlalchemy from PyPI:

$ pip install ydb-sqlalchemy

Installation from source code

To work with current ydb-sqlalchemy version clone this repo and run from source root:

$ pip install -U .

Getting started

Connect to local YDB using SqlAlchemy:

import sqlalchemy as sa


engine = sa.create_engine("yql+ydb://localhost:2136/local")

with engine.connect() as conn:
  rs = conn.execute(sa.text("SELECT 1 AS value"))
  print(rs.fetchone())

Authentication

To specify credentials, you should pass credentials object to connect_args argument of create_engine method.

Static Credentials

To use static credentials you should specify username and password as follows:

engine = sa.create_engine(
    "yql+ydb://localhost:2136/local",
    connect_args = {
        "credentials": {
            "username": "...",
            "password": "..."
        }
    }
)

Token Credentials

To use access token credentials you should specify token as follows:

engine = sa.create_engine(
    "yql+ydb://localhost:2136/local",
    connect_args = {
        "credentials": {
            "token": "..."
        }
    }
)

Service Account Credentials

To use service account credentials you should specify service_account_json as follows:

engine = sa.create_engine(
    "yql+ydb://localhost:2136/local",
    connect_args = {
        "credentials": {
            "service_account_json": {
                "id": "...",
                "service_account_id": "...",
                "created_at": "...",
                "key_algorithm": "...",
                "public_key": "...",
                "private_key": "..."
            }
        }
    }
)

Credentials from YDB SDK

To use any credentials that comes with ydb package, just pass credentials object as follows:

import ydb.iam

engine = sa.create_engine(
    "yql+ydb://localhost:2136/local",
    connect_args = {
        "credentials": ydb.iam.MetadataUrlCredentials()
    }
)

Migrations

To setup alembic to work with YDB please check this example.

Development

Run Tests:

Run the command from the root directory of the repository to start YDB in a local docker container.

$ docker-compose up

To run all tests execute the command from the root directory of the repository:

$ tox -e test-all

Run specific test:

$ tox -e test -- test/test_core.py

Check code style:

$ tox -e style

Reformat code:

$ tox -e isort
$ tox -e black-format

Run example (needs running local YDB):

$ python -m pip install virtualenv
$ virtualenv venv
$ source venv/bin/activate
$ pip install -r requirements.txt
$ python examples/example.py

Additional Notes

Pandas

It is possible to use YDB SA engine with pandas fuctions to_sql() and read_sql. However, there are some limitations:

• to_sql method can not be used with column tables, since it is impossible to specify NOT NULL columns with current to_sql arguments. YDB requires column tables to have NOT NULL attribute on PK columns.

• to_sql is not fully optimized to load huge datasets. It is recommended to use method="multi" and avoid setting a very large chunksize.

• read_sql is not fully optimized to load huge datasets and could lead to significant memory consumptions.