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)
attempt to get sorted data
fails with following error message:
although this works
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.