sqlalchemy.exc.DataError: Unknown name: $series_id_m0"

[NightStr]

Issue Description

I tried running example.py:

/home/strix/src/ydb-sqlalchemy/.venv/bin/python /home/strix/src/ydb-sqlalchemy/examples/example.py

However, I encountered the following error:

sqlalchemy.exc.DataError: (ydb_sqlalchemy.dbapi.errors.DataError) position { row: 1 column: 74 } message: "Unknown name: $series_id_m0" end_position { row: 1 column: 74 } severity: 1 (server_code: 400080)
[SQL: INSERT INTO series (series_id, title, series_info, release_date) VALUES (%(series_id_m0)s, %(title_m0)s, %(series_info_m0)s, %(release_date_m0)s), (%(series_id_m1)s, %(title_m1)s, %(series_info_m1)s, %(release_date_m1)s)]
[parameters: {'series_id_m0': 1, 'title_m0': 'IT Crowd', 'series_info_m0': "The IT Crowd is a British sitcom produced by Channel 4, written by Graham Linehan, produced by Ash Atalla and starring Chris O'Dowd, Richard Ayoade, Katherine Parkinson, and Matt Berry.", 'release_date_m0': 13182, 'series_id_m1': 2, 'title_m1': 'Silicon Valley', 'series_info_m1': 'Silicon Valley is an American comedy television series created by Mike Judge, John Altschuler and Dave Krinsky. The series focuses on five young men who founded a startup company in Silicon Valley.', 'release_date_m1': 16166}]
(Background on this error at: https://sqlalche.me/e/20/9h9h)

Process finished with exit code 1

Environment

• OS: Ubuntu 22.05
• Python: 3.12

Installed Packages:

Package	Version
aiohappyeyeballs	2.4.0
aiohttp	3.10.5
aiosignal	1.3.1
attrs	24.2.0
bcrypt	4.2.0
black	23.3.0
certifi	2024.8.30
cffi	1.17.1
charset-normalizer	3.3.2
click	8.1.7
coverage	7.6.1
cryptography	43.0.1
Cython	0.29.37
docker	6.0.1
flake8	3.9.2
grpcio	1.66.1
idna	3.10
iso8601	2.1.0
paramiko	3.5.0
protobuf	4.25.5
PyNaCl	1.5.0
requests	2.28.2
setuptools	75.1.0
six	1.16.0
SQLAlchemy	2.0.7
websocket-client	0.59.0
ydb	3.11.3
ydb-sqlalchemy	0.0.1b22

---

It seems there is an issue with the SQL query, especially regarding the variable series_id_m0. The error reports an "Unknown name" for this placeholder. Could you please help in resolving this issue?

Thank you!

[MikhailGalkov]

Missing Declare Section in YqlDialect SQL Statement

The SQL statement produced by the YqlDialect is missing the DECLARE section, which is required for parameterized YQL queries.

I have noticed that the parameter _add_declare_for_yql_stmt_vars can be set to True to fix this issue.

ydb-sqlalchemy/ydb_sqlalchemy/sqlalchemy/__init__.py

Line 674 in 3ff29ac

_add_declare_for_yql_stmt_vars=False,

References:

• DECLARE Operator Documentation

  All specified parameters must be declared in the YQL query using the DECLARE operator; otherwise, you will get an error "Query does not contain parameter".

[paqstd-dev]

I'm getting a similar error. I just do a migration using alembic.

sqlalchemy.exc.DataError: (ydb_dbapi.errors.DataError) position { row: 1 column: 71 } message: "Unknown name: $id" end_position { row: 1 column: 71 } severity: 1 (server_code: 400080)
[SQL: INSERT INTO alembic_version (version_num, id) VALUES ('3428ff075094', %(id)s)] [parameters: {'id': None}]
(Background on this error at: https://sqlalche.me/e/20/9h9h)

I changed _add_declare_for_yql_stmt_vars=True - that worked. Although in the source code it says that from version 24 this is not necessary:

ydb-sqlalchemy/ydb_sqlalchemy/sqlalchemy/__init__.py

Lines 192 to 194 in 2eacadb

	# NOTE: _add_declare_for_yql_stmt_vars is temporary and is soon to be removed.
	# no need in declare in yql statement here since ydb 24-1
	self._add_declare_for_yql_stmt_vars = _add_declare_for_yql_stmt_vars