How to use query parameters with latest sqlalchemy dialect?

[noctuid]

This may be a dumb question, but it's not exactly clear how to do query parameters specifically with sqlalchemy 2 from docs/parameters.md. For the old sqlalchemy dialect on databricks-sql-connector 2.7.0, something like conn.execute(sqlalchemy.text("CREATE TABLE foo (col1 STRING COMMENT :comment1, ...) ..."), {"comment1": "foo", ...}) works fine. With sqlalchemy 2 and databricks-sql-connector 3.3.0, this gives an error like [PARSE_SYNTAX_ERROR] Syntax error at or near ':'. I tried alternate syntaxes, text(...).bindparams, etc. but wasn't able to get it working.

[kravets-levko]

Hi @noctuid! Since v3.0.0 databricks-sql-connector switched from using inline parameters (when library substitutes values and then sends SQL for execution) to native parameters (when SQL and parameters are sent separately, and server then does its magic). Native parameters are safer and allow server to do more optimizations, therefore they are enabled by default in databricks-sql-connector and SQLAlchemy dialect. It is still possible to use old approach in databricks-sql-connector itself, but not in SQLAlchemy dialect.

I need to check why native parameters are not recognized in your case. If this is blocker for you - the only workaround I can suggest for now is to rollback to databricks-sql-connector v2 (which uses old parameters approach)

[noctuid]

For now we will wait to upgrade. Do you have an example of a code using parameters with sqlalchemy? Did mine look fine? I also tried without sqlalchemy.text and with exec_driver_sql but got the same error.

[susodapop]

Hey @noctuid quite strange that this isn't working for you. The :named parameter approach is the default in databricks-sql-connector==3.0.0 and above. Can you share a minimal reproduction? It almost seems like you're getting that error because you're using :named style with the older dialect in connector version 2.x