SNOW-1745924: Underscore as column name not properly escaped

[ifoukarakis]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

Python 3.10.10

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

macOS-14.5-arm64-arm-64bit

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

snowflake-connector-python==3.12.2 snowflake-sqlalchemy==1.5.1 SQLAlchemy==1.4.51

4. What did you do?

Created a table where a column name is _. Then tried to insert into the column.

import os

from sqlalchemy import Table, MetaData, Column, Integer, VARCHAR, create_engine, insert
from snowflake.sqlalchemy import URL

metadata = MetaData(schema="test_schema")
test_table = Table(
    'test_table',
    metadata,
    Column('col_a', Integer),
    Column('col_b', VARCHAR(100)),
    Column('_', VARCHAR(100)),
)

engine = create_engine(...)

with engine.connect() as conn:
    conn.execute(insert(test_table).values(col_a=1, col_b='test', _='test'))

Instead I got an exception:

sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001003 (42000): SQL compilation error:
syntax error line 1 at position 54 unexpected '_'.
[SQL: INSERT INTO test_schema.test_table (col_a, col_b, _) VALUES (%(col_a)s, %(col_b)s, %(_)s)]
[parameters: {'col_a': 1, 'col_b': 'test', '_': 'test'}]
(Background on this error at: https://sqlalche.me/e/14/f405)

5. What did you expect to see?

A row inserted in the table.

[ifoukarakis]

A workaround is to manually set engine.dialect.preparer.reserved_words.add('_') to force escaping of _.

[sfc-gh-dszmolka]

hi , thank you for raising this issue and also for suggesting a workaround. We'll take a look.