snowflakedb
SNOW-1232488: Table reflection methods using .format do not respect quoted_name
Hello--thanks for your work on this library! I maintain a tool called siuba that can execute SQL analyses across many different databases (using sqlalchemy). While working on snowflake support, I noticed that table reflection often does not work for case sensitive identifiers, even if they're quoted.
Below is an example that I think shows the root cause: queries in the dialect that are built using .format, do not perform the necessary escaping of quoted_name objects.
Example
from snowflake.sqlalchemy import __version__
from snowflake.sqlalchemy.snowdialect import SnowflakeDialect
__version__ # 1.3.3
dialect = SnowflakeDialect()
# far so good
nn_schema = dialect.normalize_name("some_schema")
type(nn_schema) # sqlalchemy.sql.elements.quoted_name
nn_schema.quote # True
# Does not correctly quote schema name
"SHOW TABLES IN SCHEMA {}".format(nn_schema)
# output is 'SHOW TABLES IN SCHEMA some_schema'
What did you expect to see?
SHOW TABLES IN SCHEMA "some_schema"
What did you see instead?
SHOW TABLES IN SCHEMA some_schema
(and an error because SOME_SCHEMA does not exist)
@machow We worked around this broken behavior in ibis in https://github.com/ibis-project/ibis/pull/5741 by effectively monkey patching dialect.normalize_name.
Yeah, it's hack, but it's not clear if anyone is fixing bugs in this project so feel free to crib from the above as needed.
hi folks - apologies for leaving this unanswered for so long; we're changing that going forward. for now, possibly
- https://github.com/snowflakedb/snowflake-sqlalchemy/issues/157
- https://github.com/snowflakedb/snowflake-sqlalchemy/issues/276
- https://github.com/snowflakedb/snowflake-sqlalchemy/issues/388
might be all originating from the same gap in snowflake-sqlalchemy. At this time, I cannot promise any timeline for taking care of this, but rest assured we're aware of the issue and i'll keep this thread posted.
