ydb-platform / ydb-sqlalchemy Goto Github PK
View Code? Open in Web Editor NEWYQL Dialect for SQLAlchemy
License: Apache License 2.0
YQL Dialect for SQLAlchemy
License: Apache License 2.0
Run Tests: $ pytest -vv test/test_suite.py::NumericTest
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
implement regexpr_match operator
https://github.com/ydb-platform/ydb-sqlalchemy/blob/main/tests/test_core.py#L102
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
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)
Run Tests:
$ pytest -vv test/test_suite.py::BinaryTest
Failed with:
sqlalchemy.exc.StatementError: (builtins.AttributeError) module 'ydb_sqlalchemy.dbapi' has no attribute 'Binary'
For allow to use it without sqlalchemy.
ydb-sqlalchemy/ydb_sqlalchemy/dbapi/cursor.py
Line 117 in 48cc1c2
Now it can work with some additional mark for ddl queries and it should work fine after switch the dbapi to ydb query service.
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.
Roadmap:
Now support simple no auth connection only:
Workaround: initialize ydb.Driver and pass it by ydb_session_pool
option.
Support migration for ydb-sqlalchemy via alembic
https://alembic.sqlalchemy.org/en/latest/
It should return self on __enter__
and call close method on __exit__
with cursor:
...
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.