The ydb-sqlalchemy from ydb-platform

code

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.dialects import registry
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

registry.register("yql.ydb", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")
registry.register("ydb", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")
registry.register("yql", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")

engine = create_engine("yql+ydb://localhost:myport/mydb")
Base = declarative_base()
Session = sessionmaker(bind=engine)

class Table(Base):
    __tablename__ = "myexampletable"
    cstr = Column(String(), primary_key=True)
    cint = Column(Integer(), primary_key=True)

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

session = Session()

values_list = [
    Table(cstr="aaa", cint=111),
    Table(cstr="ccc", cint=111),
    Table(cstr="bbb", cint=222),
]

session.add_all(values_list)
session.commit()

q = session.query(Table).filter(Table.cstr.ilike("a%")).all()
for c in q:
    print(c.cstr, c.cint)

results in following error:

sqlalchemy.exc.DataError: (ydb_sqlalchemy.dbapi.errors.DataError) position { row: 3 column: 39 } message: "Unknown builtin: lower" end_position { row: 3 column: 39 } severity: 1 ,position { row: 3 column: 7 } message: "Unknown builtin: lower" end_position { row: 3 column: 7 } severity: 1 (server_code: 400080)
[SQL: SELECT myexampletable.cstr AS myexampletable_cstr, myexampletable.cint AS myexampletable_cint 
FROM myexampletable 
WHERE lower(myexampletable.cstr) LIKE lower(%(cstr_1)s)]
[parameters: {'cstr_1': 'a%'}]

i suggest it happens somewhere here:
https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/sql/compiler.py#L3451

maybe should be rewritten like its done for postgresql:
https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/dialects/postgresql/base.py#L1791

there is lower function support in ydb:
https://ydb.tech/docs/ru/yql/reference/udf/list/unicode

Support YDB as a data source for SuperSet (now uses sqlalchemy <2)
SuperSet connects to data sources through SQLAlchemy, also we need to add support for alembic migrations tool.
ClickHouse, for example: https://github.com/apache/superset/blob/master/superset/db_engine_specs/clickhouse.py

order_by is failing

attempt to get sorted data

from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.dialects import registry
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

registry.register("yql.ydb", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")
registry.register("ydb", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")
registry.register("yql", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")

engine = create_engine("yql+ydb://localhost:2136/local")
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

class Table(Base):
    __tablename__ = "mytable"
    col = Column(Integer, primary_key=True)

Base.metadata.create_all(engine)

session.add(Table(col=1))
session.commit()

q = session.query(Table).order_by(Table.col).first()
print(q.col)

fails with following error message:

sqlalchemy.exc.DataError: (ydb_sqlalchemy.dbapi.errors.DataError) position { row: 2 column: 23 } message: "Column col is not in source column set" end_position { row: 2 column: 23 } severity: 1 (server_code: 400080)
[SQL: SELECT mytable.col AS mytable_col 
FROM mytable ORDER BY mytable.col
 LIMIT CAST(%(param_1)s AS UInt64)]
[parameters: {'param_1': 1}]
(Background on this error at: https://sqlalche.me/e/20/9h9h)

although this works

q = session.query(Table.col.label("labelled_col")).order_by("labelled_col").first()
print(q.labelled_col)

but its inconvenient.

it may be related to label style:
https://docs.sqlalchemy.org/en/14/core/selectable.html#label-style-constants

if we change LABEL_STYLE_TABLENAME_PLUS_COL to LABEL_STYLE_DISAMBIGUATE_ONLY in this row:
https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/orm/context.py#L1073
order_by works without having to use label.

tried to use set_label_style or _label_style in MySession as in:
https://stackoverflow.com/questions/75322094/sqlalchemy-relationship-fields-name-constructor
but it doesnt help (need label)

using sqlalchemy 2+.

postgresql dialect works fine without relabelling.

Support Decimal/Numeric data type

Run Tests: $ pytest -vv test/test_suite.py::NumericTest

Support sqlalchemy ORM

use "dialect compliance suite" for testing

https://github.com/sqlalchemy/sqlalchemy/blob/main/README.dialects.rst

Validate exception ydb.driver to dbapi mapping

authorization is not supported

if in connection.py in driver we add credentials=ydb.credentials_from_env_variables() like this:

155     def _create_driver(self):
156         driver_config = ydb.DriverConfig(
157             endpoint=self.endpoint,
158             database=self.database,
159             table_client_settings=self._get_table_client_settings(),
160             credentials=ydb.credentials_from_env_variables(),
161         )

we can use: export YDB_ACCESS_TOKEN_CREDENTIALS=my_token

Support Binary/String datatype

Run Tests:
$ pytest -vv test/test_suite.py::BinaryTest
Failed with:
sqlalchemy.exc.StatementError: (builtins.AttributeError) module 'ydb_sqlalchemy.dbapi' has no attribute 'Binary'

Support match by regexpr

implement regexpr_match operator
https://github.com/ydb-platform/ydb-sqlalchemy/blob/main/tests/test_core.py#L102

update query is failing

cannot update table:

from sqlalchemy import create_engine, Column, Boolean, Integer
from sqlalchemy.dialects import registry
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker

registry.register("yql.ydb", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")
registry.register("ydb", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")
registry.register("yql", "ydb_sqlalchemy.sqlalchemy", "YqlDialect")

engine = create_engine("yql+ydb://localhost:2136/local")
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

class Table(Base):
    __tablename__ = "dir/table"
    pk = Column(Integer, primary_key=True)
    col = Column(Boolean)

Base.metadata.create_all(engine)

session.add(Table(pk=1, col=False))
session.commit()

row = session.query(Table).first()
row.col = True
session.commit()

fails with following message:

sqlalchemy.exc.DataError: (ydb_sqlalchemy.dbapi.errors.DataError) position { row: 1 column: 57 } message: "Unknown name: $dir" end_position { row: 1 column: 57 } severity: 1 (server_code: 400080)
[SQL: UPDATE `dir/table` SET col=%(col)s WHERE `dir/table`.col = %(dir/table_col)s]
[parameters: {'col': True, 'dir/table_col': False}]
(Background on this error at: https://sqlalche.me/e/20/9h9h)

support NULL

Support alembic migration tool

Support migration for ydb-sqlalchemy via alembic
https://alembic.sqlalchemy.org/en/latest/

ydb-platform / ydb-sqlalchemy Goto Github PK

ydb-sqlalchemy's Introduction

YDB Dialect for SQLAlchemy

Installation

Getting started

Development

Run Tests:

ydb-sqlalchemy's People

Contributors

Stargazers

Watchers

Forkers

ydb-sqlalchemy's Issues

Recommend Projects

Recommend Topics

Recommend Org