SNOW-1050043: compile with snowdialect does not work (AttributeError: type object 'SnowflakeDialect' has no attribute 'positional')

[jacobsieradzki]

1. What version of Python are you using?

Tried with both 3.9 and 3.11

2. What operating system and processor architecture are you using?

macOS-14.2.1-arm64-arm-64bit

3. What are the component versions in the environment (pip freeze)?

asn1crypto==1.5.1
certifi==2024.2.2
cffi==1.16.0
charset-normalizer==3.3.2
cryptography==41.0.7
filelock==3.13.1
idna==3.6
packaging==23.2
platformdirs==3.11.0
pycparser==2.21
PyJWT==2.8.0
pyOpenSSL==23.3.0
pytz==2024.1
requests==2.31.0
snowflake-connector-python==3.7.0
snowflake-sqlalchemy==1.5.1
sortedcontainers==2.4.0
SQLAlchemy==1.4.51
tomlkit==0.12.3
typing_extensions==4.9.0
urllib3==1.26.18

4. What did you do?

I want to use SQLAlchemy just to generate a SQL string, not run it. However, whenever trying to use snowdialect I see a number of errors:

from sqlalchemy import Column, select, text
from snowflake.sqlalchemy import snowdialect

print("---- statement\n")
result = select(text("*")) \
    .select_from(text("table_name")) \
    .where(Column("foo") == "bar")

compiled = result.compile()

print(compiled) # SELECT * FROM table_name WHERE foo = :foo_1
print(compiled.params) # {'foo_1': 'bar'}

# This fails!
print(result.compile(dialect=snowdialect.dialect)) # AttributeError: type object 'SnowflakeDialect' has no attribute 'positional'

I also tried to be a bit hacky to see if I could resolve, but it seems like many of the properties required for compile function are not present:

d = snowdialect.dialect

d.positional = False 
d.paramstyle = 'qmark'
d.label_length = None

print(result.compile(dialect=d))
# AttributeError: type object 'SnowflakeDialect' has no attribute 'positional'
# AttributeError: type object 'SnowflakeDialect' has no attribute 'paramstyle'
# AttributeError: type object 'SnowflakeDialect' has no attribute 'label_length'
# AttributeError: type object 'SnowflakeDialect' has no attribute 'identifier_preparer'
# and so on ...

5. What did you expect to see?

I expected the error not to fail and output a SQL string with qmark bind variables.:

from sqlalchemy import Column, select, text
from snowflake.sqlalchemy import snowdialect

print("---- statement\n")
result = select(text("*")) \
    .select_from(text("table_name")) \
    .where(Column("foo") == "bar")

compiled = result.compile(dialect=snowdialect.dialect)

print(compiled) # SELECT * FROM table_name WHERE foo = ?
print(compiled.params) # ['bar']

6. Can you set logging to DEBUG and collect the logs?

No logs output

[jacobsieradzki]

I was using the dialect object incorrectly. This script worked for me:

from sqlalchemy import create_engine, Column, select, text
import snowflake.connector

result = select(text("*")) \
    .select_from(text("table_name")) \
    .where(Column("foo") == "bar")

snowflake.connector.paramstyle = "qmark"
engine = create_engine("snowflake://")

compiled = result.compile(dialect=engine.dialect)
# SELECT * FROM table_name WHERE foo = ?

params = [param for param in compiled.params.values()]
# ['bar']