SNOW-952817: Reflect doesn't work with mixed case schema

[fredrike]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

Python 3.8.16 (default, Jan 17 2023, 23:13:24) 
[GCC 11.2.0]

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

   Linux-5.4.0-1074-azure-x86_64-with-glibc2.17

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

snowflake-connector-python==2.7.9
snowflake-sqlalchemy==1.4.6
SQLAlchemy==1.4.35

4. What did you do?

   from snowflake.sqlalchemy import URL
   from sqlalchemy import create_engine
   from sqlalchemy import MetaData
   
   engine = create_engine(
         URL(
             account=ACCOUNT,
             user=secrets[USR_KEY],
             password=secrets[PASS_KEY],
             database=DATABASE_NAME,
             schema=SCHEMA_NAME,
             warehouse=warehouse,
             role=ROLE,
         )
     )
   meta_data = MetaData(engine)
   meta_data.reflect(only=lambda l, _: 'control' in l, views=True, schema="EPS_CO_Technical")
   print(meta_data.tables.keys())
   

5. What did you expect to see?

   The tables containing control should be listed. Instead i get this error:

   ProgrammingError: (snowflake.connector.errors.ProgrammingError) 002043 (02000): SQL compilation error:
   Object does not exist, or operation cannot be performed.
   [SQL: SHOW /* sqlalchemy:_get_table_comment */ TABLES LIKE '_controlTable_MapPocPrice' IN SCHEMA EPS_CO_Technical]
   (Background on this error at: https://sqlalche.me/e/14/f405)
   

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

   Better, I have a suggested patch:

   diff --git a/snowdialect.py b/anaconda/envs/azureml_py38/lib/python3.8/site-packages/snowflake/sqlalchemy/snowdialect.py
   --- a/snowdialect.py
   +++ b/anaconda/envs/azureml_py38/lib/python3.8/site-packages/snowflake/sqlalchemy/snowdialect.py
   @@ -813,7 +813,7 @@ class SnowflakeDialect(default.DefaultDialect):
                "SHOW /* sqlalchemy:_get_table_comment */ "
                "TABLES LIKE '{}'{}".format(
                    table_name,
   -                f" IN SCHEMA {self.normalize_name(schema)}" if schema else "",
   +                f" IN SCHEMA \"{self.denormalize_name(schema)}\"" if schema else "",
                )
            )
            cursor = connection.execute(text(sql_command))
   @@ -827,7 +827,7 @@ class SnowflakeDialect(default.DefaultDialect):
                "SHOW /* sqlalchemy:_get_view_comment */ "
                "VIEWS LIKE '{}'{}".format(
                    table_name,
   -                f" IN SCHEMA {self.normalize_name(schema)}" if schema else "",
   +                f" IN SCHEMA \"{self.denormalize_name(schema)}\"" if schema else "",
                )
            )
            cursor = connection.execute(text(sql_command))

   So, qoute the schema name seems to do the trick.

[fordhoka]

I am also experiencing this issue. The suggested patch works for me, for mixed-case, lower-case, and case-insensitive schema names, but should also be applied to SnowflakeDialect.get_sequence_names().

I believe this patch would also fix #276 and #388.

[sfc-gh-dszmolka]

hi and thank you for submitting this issue and especially for sharing the patch ! i believe it could be caused by the same underlying cause which causes #388

can this be a possible duplicate ?

[fordhoka]

This is a duplicate of both #388 and #276. We have been using the patch and it is working for us, with the same change applied to get_sequence_names():

    @reflection.cache
    def get_sequence_names(self, connection, schema=None, **kw):
        sql_command = "SHOW SEQUENCES {}".format(
            f" IN SCHEMA \"{self.denormalize_name(schema)}\"" if schema else "",
        )
        try:
            cursor = connection.execute(text(sql_command))

[sfc-gh-dszmolka]

thank you for confirming it @fordhoka (and also happy to hear you have a working workaround)
marking this as closed so the tracking could be focused on the existing tickets