Unable to perform inspectdb on tables containing dot (".") in the name

[ClausSander]

Hi,

Due to legacy reasons, I need to deal with Snowflake tables having a name containing the schema in the format DBO.TABLENAME. However, I am unable to perform the inspectdb command. Tables without dots give no issue.

This is the output with extended logging enabled:

> python manage.py inspectdb --database snowflake "DBO.TABLENAME"
INFO 2024-02-09 16:10:20,904 snowflake.connector.connection Snowflake Connector for Python Version: 3.7.0, Python Version: 3.11.6, Platform: Windows-10-10.0.19045-SP0
INFO 2024-02-09 16:10:20,905 snowflake.connector.connection This connection is in OCSP Fail Open Mode. TLS Certificates would be checked for validity and revocation status. Any other Certificate Revocation 
related exceptions or OCSP Responder failures would be disregarded in favor of connectivity.
INFO 2024-02-09 16:10:22,830 snowflake.connector.cursor query: [ALTER SESSION SET autocommit=True]
INFO 2024-02-09 16:10:22,906 snowflake.connector.cursor query execution done
INFO 2024-02-09 16:10:22,907 snowflake.connector.cursor Number of results in first chunk: 1
INFO 2024-02-09 16:10:22,909 snowflake.connector.cursor query: [SHOW PARAMETERS LIKE 'TIMEZONE']
INFO 2024-02-09 16:10:23,001 snowflake.connector.cursor query execution done
INFO 2024-02-09 16:10:23,001 snowflake.connector.cursor Number of results in first chunk: 1
INFO 2024-02-09 16:10:23,002 snowflake.connector.cursor query: [ALTER SESSION SET TIMEZONE='UTC']
INFO 2024-02-09 16:10:23,113 snowflake.connector.cursor query execution done
INFO 2024-02-09 16:10:23,113 snowflake.connector.cursor Number of results in first chunk: 1
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#   * Rearrange models' order
#   * Make sure each model has one field with primary_key=True
#   * Make sure each ForeignKey and OneToOneField has `on_delete` set to the desired behavior
#   * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or field names.
from django.db import models
INFO 2024-02-09 16:10:23,116 snowflake.connector.cursor query: [SHOW TABLES]
INFO 2024-02-09 16:10:23,199 snowflake.connector.cursor query execution done
INFO 2024-02-09 16:10:23,203 snowflake.connector.cursor Number of results in first chunk: 112
INFO 2024-02-09 16:10:23,204 snowflake.connector.cursor query: [SHOW VIEWS]
INFO 2024-02-09 16:10:23,283 snowflake.connector.cursor query execution done
INFO 2024-02-09 16:10:23,284 snowflake.connector.cursor Number of results in first chunk: 1
INFO 2024-02-09 16:10:23,285 snowflake.connector.cursor query: [SHOW IMPORTED KEYS IN TABLE "DBO"."TABLENAME"]
INFO 2024-02-09 16:10:23,349 snowflake.connector.cursor query execution done
# Unable to inspect table 'DBO.TABLENAME'
# The error was: SQL compilation error:
Schema 'DATABASENAME.DBO' does not exist or not authorized.
INFO 2024-02-09 16:10:23,368 snowflake.connector.connection closed
INFO 2024-02-09 16:10:23,420 snowflake.connector.connection No async queries seem to be running, deleting session

Other variations of passing the table name, like '"DBO.TABLENAME"', won't work either.

However, when creating the model manually as below with the name between '"..."', the database can be queried without issues.

class Tablename(models.Model):
    [list of fields]

    class Meta:
        managed = False
        db_table = '"DBO.TABLENAME"'

[timgraham]

The issue is that table names from SHOW TABLES (e.g. DBO.TABLENAME) are lowercased by DatabaseIntrospection.identifier_converter() (dbo.tablename) and then later uppercased by DatabaseOperations.quote_name() which also performs quoting of periods ("DBO"."TABLENAME").

This patch seemed to work for me to introspect a table with a dot in the name:

diff --git a/django_snowflake/introspection.py b/django_snowflake/introspection.py
index 36dff42..c8064f3 100644
--- a/django_snowflake/introspection.py
+++ b/django_snowflake/introspection.py
@@ -194,7 +194,7 @@ class DatabaseIntrospection(BaseDatabaseIntrospection):
         cursor.execute('SHOW TABLES')
         tables = [
             TableInfo(
-                self.identifier_converter(row[1]),  # table name
+                '"' + row[1] + '"',  # table name
                 't',  # 't' for table
                 row[5],  # comment
             ) for row in cursor.fetchall()

output:

class DboTablename(models.Model):
    col1 = models.BigIntegerField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = '"DBO.TABLENAME"'

but only use it temporarily while running inspectdb! A proper solution needs more thought, likely as part of #43.

[ClausSander]

Thanks for looking into this. However, this patch appears to be working only when running inspectdb on the whole database. python manage.py inspectdb --database snowflake But not when inspecting one specific table python manage.py inspectdb --database snowflake DBO.TABLE python manage.py inspectdb --database snowflake 'DBO.TABLE' python manage.py inspectdb --database snowflake '"DBO.TABLE"'