snowflake-labs / django-snowflake Goto Github PK

I note in this project:
https://github.com/benryan2010/django-snowflake-backend/blob/master/django-snowflake-backend/operations.py

They use the last_query_id to ensure that the max query relates only to the last query in the current session.
I guess this only works if the database session is specific to the current web session and is not shared.

        try:
            with self.connection.cursor() as cursor:
                cursor.execute('SET qid = last_query_id()')
        # TODO: map exception here
        except Database.errors.ProgrammingError as e:
            # default error message
            print(e)
            # customer error message
            print('Error {0} ({1}): {2} ({3})'.format(e.errno, e.sqlstate, e.msg, e.sfqid))
            return False

        set_statement_id_sql = 'select max("{0}") from "{1}" AT(statement=>$qid)'.format(pk_name, table_name)

Would this help alleviate the race condition?

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"'

The Python Connector accepts an option private_key to perform auth. For a server backend such as a Django app this is often the most preferable option.

The value for private_key is accepted only as a bytes object that represents a decrypted key's bytes.

The OPTIONS clause accepts passing a value for private_key, but it attempts to also reuse the same value as a file path when launching snowsql. Since the valid value can only be a raw bytes object, launching SnowSQL with its value for --private-key-path will fail (or conversely the regular connections in the server will fail if its a path):

Traceback (most recent call last):
  File "/django-tests/mysite/manage.py", line 22, in <module>
    main()
  File "/django-tests/mysite/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/django-tests/denv/lib/python3.10/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/django-tests/denv/lib/python3.10/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/django-tests/denv/lib/python3.10/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/django-tests/denv/lib/python3.10/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
  File "/django-tests/denv/lib/python3.10/site-packages/django/core/management/commands/dbshell.py", line 30, in handle
    connection.client.runshell(options["parameters"])
  File "/django-tests/denv/lib/python3.10/site-packages/django_snowflake/client.py", line 68, in runshell
    super().runshell(parameters)
  File "/django-tests/denv/lib/python3.10/site-packages/django/db/backends/base/client.py", line 28, in runshell
    subprocess.run(args, env=env, check=True)
  File "/3.10/lib/python3.10/subprocess.py", line 503, in run
    with Popen(*popenargs, **kwargs) as process:
  File "/3.10/lib/python3.10/subprocess.py", line 971, in __init__
    self._execute_child(args, executable, preexec_fn, close_fds,
  File "/3.10/lib/python3.10/subprocess.py", line 1796, in _execute_child
    self.pid = _posixsubprocess.fork_exec(
ValueError: embedded null byte

I propose introducing special options private_key_file and private_key_passphrase that are specific to this project. Use of this internally auto-decrypts the file to the bytes format required by the connector (injecting private_key automatically into connector calls), and uses the same file path for its SnowSQL launch args.

Perhaps it makes sense to implement these directly into the connector itself (rather than in this plugin).

Happy to send a PR implementing this. Thoughts?

When I try to run migrate for the Snowflake database, I get the following error:

django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table
  (Cannot perform CREATE TABLE. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.)

It seems like a database is not being selected before trying to create a table. My DATABASES config looks like this:

DATABASES = {
    'default': ...
    'snowflake': {
        'ENGINE': 'django_snowflake',
        'NAME': '<my database name>',
        'SCHEMA': '<my schema name>',
        'WAREHOUSE': 'COMPUTE_WH',
        'USER': '<snowflake username>',
        'PASSWORD': '<snowflake password>',
        'ACCOUNT': '<snowflake account locator>',
    }
}

I tried adding database to OPTIONS like this:

DATABASES = {
    'default': ...
    'snowflake': {
        ...,
        'OPTIONS': {
            'database': '<my database name>',
        }
    }
}

but it didn't help.

Have I configured something wrong? Or is this an issue with django-snowflake? Does django-snowflake support migrations and creating tables?

settings.py

"snowflake":{
'ENGINE': 'django_snowflake',
'NAME': 'DB_NAME,
'SCHEMA': 'SCHEMA_NAME',
'WAREHOUSE': 'WH',
'USER': 'USER',
'PASSWORD': 'password,
'ACCOUNT': 'account'
}

QuerySet
db = 'snowflake'
Cars.SQL.using(db)

models.py

class BaseSQLTemplateRawManager(models.Manager):
    template_name = None
    template_context_data = None
    
    def get_template_name(self):
        if self.template_name is None:
            raise ImproperlyConfigured(
                "SQLRawTemplateBase requires either a definition of "
                "'template_name' or an implementation of 'get_template_name()'"
            )
        return self.template_name

    def get_template_context_data(self, **kwargs):
        if self.template_context_data is not None:
            kwargs.update(self.template_context_data)
        return kwargs
    
    def get_queryset(self):
        
        return self._queryset_class(model=self.model, using=self._db, hints=self._hints)\
            .raw(
                render_to_string(
                    self.get_template_name(),
                    self.get_template_context_data()
                )
            )

class CarsSQLTemplateRawManager(BaseSQLTemplateRawManager):
    template_name = "dealer/SQL/truebil-cars.sql"


class Cars(AbsBaseCar):
    SQL = CarsSQLTemplateRawManager()
    
    APPOINTMENT_ID = models.CharField(max_length=100)
    STORE_NAME = models.CharField(max_length=100)
    BOUGHT_DATE = models.CharField(max_length=100)
    BOUGHT_MONTH = models.CharField(max_length=100)
    WEEK = models.CharField(max_length=100)
    WEEK_OF_MONTH = models.CharField(max_length=100)
    STATE = models.CharField(max_length=100)
    UNSOLD = models.CharField(max_length=100)
    DEALER_CODE = models.CharField(max_length=100)
    DEALER_NAME = models.CharField(max_length=100)
    BOUGHT = models.CharField(max_length=100)
    DELAER_REGION = models.CharField(max_length=100)
    MDBP = models.CharField(max_length=100)
    SALE_DATE = models.CharField(max_length=100)
    FIRST_SI = models.CharField(max_length=100)
    LATEST_SO = models.CharField(max_length=100)
    
    @property
    def MMV(self):
        return f"{self.MAKE}{self.MODEL}{self.VARIANT}".upper() 
    
    @property
    def MV(self):
        return f"{self.MODEL}{self.VARIANT}".upper() 
    
    
    class Meta:
        managed = False

Exception:

Traceback (most recent call last):
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/snowflake/connector/cursor.py", line 791, in execute
Error.errorhandler_wrapper(
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/snowflake/connector/errors.py", line 272, in errorhandler_wrapper
handed_over = Error.hand_to_other_handler(
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/snowflake/connector/errors.py", line 327, in hand_to_other_handler
cursor.errorhandler(connection, cursor, error_class, error_value)
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/snowflake/connector/errors.py", line 206, in default_errorhandler
raise error_class(
snowflake.connector.errors.ProgrammingError: 000904 (42000): SQL compilation error: error line 1 at position 7
invalid identifier 'SALES_TRANSACTIONS.ID'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/Users/chandanojha/vsprojects/spider/dealer/management/commands/dealers_match.py", line 40, in handle
Cars24VsSpinnyMatch().match(),
File "/Users/chandanojha/vsprojects/spider/dealer/match/mixing.py", line 89, in match
self.df = self.get_merged_dataframe()
File "/Users/chandanojha/vsprojects/spider/dealer/match/mixing.py", line 81, in get_merged_dataframe
right=self.get_dataframe_x(),
File "/Users/chandanojha/vsprojects/spider/dealer/match/mixing.py", line 41, in get_dataframe_x
read_frame(
File "/Users/chandanojha/vsprojects/spider/dealer/dataframe.py", line 27, in read_frame
[r for r in recs],
File "/Users/chandanojha/vsprojects/spider/dealer/dataframe.py", line 27, in
[r for r in recs],
File "/Users/chandanojha/vsprojects/spider/dealer/dataframe.py", line 6, in get_records
yield tuple(getattr(row, col) for col in fields)
File "/Users/chandanojha/vsprojects/spider/dealer/dataframe.py", line 6, in
yield tuple(getattr(row, col) for col in fields)
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/models/query_utils.py", line 150, in get
instance.refresh_from_db(fields=[field_name])
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/models/base.py", line 637, in refresh_from_db
db_instance = db_instance_qs.get()
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/models/query.py", line 431, in get
num = len(clone)
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/models/query.py", line 262, in len
self._fetch_all()
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/models/query.py", line 1324, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/models/query.py", line 51, in iter
results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1169, in execute_sql
cursor.execute(sql, params)
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 98, in execute
return super().execute(sql, params)
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/utils.py", line 90, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/snowflake/connector/cursor.py", line 791, in execute
Error.errorhandler_wrapper(
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/snowflake/connector/errors.py", line 272, in errorhandler_wrapper
handed_over = Error.hand_to_other_handler(
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/snowflake/connector/errors.py", line 327, in hand_to_other_handler
cursor.errorhandler(connection, cursor, error_class, error_value)
File "/Users/chandanojha/vsprojects/spider/.venv/lib/python3.9/site-packages/snowflake/connector/errors.py", line 206, in default_errorhandler
raise error_class(
django.db.utils.ProgrammingError: SQL compilation error: error line 1 at position 7
invalid identifier 'SALES_TRANSACTIONS.ID'

I see @sfc-gh-hachouraria is adding some examples to OPTIONS for authentication:

#66

As we find the best way to include this code, I wanted to document a hack to authenticate with a private key in the meantime.

In settings.py I just add an ugly block to decrypt the key:

#### --Fh
import os
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives.asymmetric import rsa
from cryptography.hazmat.primitives.asymmetric import dsa
from cryptography.hazmat.primitives import serialization

with open("./rsa_key.p8", "rb") as key:
    p_key= serialization.load_pem_private_key(
        key.read(),
        password=None,
        backend=default_backend()
    )

pkb = p_key.private_bytes(
    encoding=serialization.Encoding.DER,
    format=serialization.PrivateFormat.PKCS8,
    encryption_algorithm=serialization.NoEncryption())
### --Fh

So then in DATABASES in settings.py I can use that pkb value:

DATABASES = {
    'default': {
        'ENGINE': 'django_snowflake',
        'NAME': 'DJANGO',
        'SCHEMA': 'PUBLIC',
        'WAREHOUSE': 'S',
        'USER': 'DJANGO',
        'PASSWORD': '#',
        'ACCOUNT': 'my_account',
        # Include 'OPTIONS' if you need to specify any other
        # snowflake.connector.connect() parameters.
        # https://docs.snowflake.com/en/user-guide/python-connector-api.html#connect
        'OPTIONS': {
            'private_key': pkb,
        }
    }
}

When loading fixtures that have primary key values of autoincrement columns specified, Snowflake doesn't automatically update the sequence to the next available value. For example, after loading fixtures with pk=1 and pk=2 into a new table, the next object will be created with pk=1 (duplicating an existing value) instead of pk=3. PostgreSQL has the same issue and Django's DatabaseOperations.sequence_reset_sql() method is a hook to generate SQL that updates the next value of the sequence for each table:

SELECT setval(pg_get_serial_sequence(<table>, <column>), coalesce(max(<column>)), 1), max(column) IS NOT null)
FROM <table>

Since Snowflake's ALTER SEQUENCE doesn't provide a way to set the next value of the sequence, I'm not sure if this is feasible.

We use UUIDs as our primary key and django-snowflake seems to be stripping out the hyphens making it unusable.

What I'm seeing:

>>> Customer.objects.filter(company_id='8e0694de-8d2d-4ffd-8cef-76e9a06ac650').count()
...
2022-01-26 16:47:37,209 snowflake.connector.connection DEBUG    parameters: ("'8e0694de8d2d4ffd8cef76e9a06ac650'",)
...
0

What I expect to see:

>>> Customer.objects.filter(company_id='8e0694de-8d2d-4ffd-8cef-76e9a06ac650').count()
...
2022-01-26 16:47:37,209 snowflake.connector.connection DEBUG    parameters: ("'8e0694de-8d2d-4ffd-8cef-76e9a06ac650'",)
...
1809519

I believe this is in django-snowflake and not in the snowflake connector because I can run this just fine:

>>> with con.cursor() as cur:
...     cur.execute("select count(*) from customers where company_id=%s", ['8e0694de-8d2d-4ffd-8cef-76e9a06ac650'])
...     print(cur.fetchone())
...
2022-01-26 16:45:57,240 snowflake.connector.connection DEBUG    parameters: ("'8e0694de-8d2d-4ffd-8cef-76e9a06ac650'",)
...
(1809519,)

The README says that "In Snowflake, the regex lookup pattern is implicitly anchored at both ends".

Switching to regexp_instr()>0 avoids this difference in behavior:

with data(s) as (select 'abcdefgh')

select s, s regexp '.*bcd.*', regexp_instr(s, 'bcd')>0
from data;

A queryset like:

Experiment.objects.filter(start=F('start') + F('estimated_time')

where estimated_time is a DurationField, generates SQL:

WHERE "experiment"."start" = ("experiment"."start" + INTERVAL '"expressions_ExPeRiMeNt"."estimated_time" MICROSECONDS')

but INTERVAL doesn't support column names. (SQL compilation error: syntax error line 1 at position 0 unexpected '"experiment"'.)

Perhaps Snowflake could add support for this. For example, MySQL allows:

INTERVAL `experiment`.`estimated_time` MICROSECOND

The current implementation of DatabaseOperations.quote_name() adds quotes around all identifiers like table and column names. This makes Snowflake treat the names case-sensitively, and Django uses lower case identifiers unless otherwise specified.

Using lower case names has some disadvantages, namely that quotes are subsequently always required around the identifires, otherwise, Snowflake will look for upper case names. One area this came up is in the raw SQL queries in Django's tests that don't include quotes. These have been skipped for now.

Cedar has a use case where table names are synced from PostgreSQL, apparently without quotes, so all these table names are treated as uppercase in Snowflake. To accommodate this use case and avoid the requirement of quotes in raw SQL, it might be best to adopt a similar approach as the Oracle backend, which has quote_name() uppercase all identifiers. (This would be a breaking change for anyone who has used django-snowflake 3.2 alpha 1 to create their tables and thus has lowercased names, but since the package is at alpha stage, backward compatibility isn't critical.)

Another solution to accommodate Cedar's use case could be to add an option to make DatabaseWrapper.init_connection_state() make the query: ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = true.

Python version= 3.8.17
I am running Django 4.1.10 and django-snowflake =4.1b1. After setting my settings.py DATABASES with the right credentials and running python manage.py runserver I get the following error :

raise LibraryNotFoundError('Error detecting the version of libcrypto')
oscrypto.errors.LibraryNotFoundError: Error detecting the version of libcrypto

my pip freeze looks like this

asgiref==3.7.2 asn1crypto==1.5.1 backports.zoneinfo==0.2.1 binutils==0.2.dev0 certifi==2023.7.22 cffi==1.15.1 charset-normalizer==3.2.0 cryptography==41.0.3 Django==4.1.10 django-snowflake==4.1b1 filelock==3.12.2 idna==3.4 oscrypto==1.3.0 packaging==23.1 platformdirs==3.8.1 pycparser==2.21 pycryptodomex==3.18.0 PyJWT==2.8.0 pyOpenSSL==23.2.0 pytz==2023.3 requests==2.31.0 snowflake-connector-python==3.1.0 sortedcontainers==2.4.0 sqlparse==0.4.4 tomlkit==0.12.1 typing_extensions==4.7.1 urllib3==1.26.16

what should be the cause of this?

Edit: Also tested with pyOpenSSL (23.1.1, 23.1.0, 22.0.0)

Thank you

I am unable to get the externalbrowser window to pop up indicating successful connection. When I runserver, I get
DatabaseError: Failed to connect to DB: <my_account>.privatelink.snowflakecomputing.com:443. Incorrect username or password was specified.

In settings.py (using dummy variables here, real ones are used in production):

DATABASES = {
    'default': {  
        'ENGINE': 'django_snowflake',
        'DATABASE': 'SANDBOX_DB',
        'SCHEMA': '<my_schema>',
        'WAREHOUSE': 'COMPUTE_WH',
        'USER': '<my_username>',
        'PASSWORD': '<my_password>',
        'ACCOUNT': '<my_account>.privatelink',
        'OPTIONS': {
            'AUTHENTICATOR': 'externalbrowser',
            'ROLE': '<my_role>'
        }
    }
}

Also tried placing 'AUTHENTICATOR': 'externalbrowser' as its own key in default, outside of OPTIONS.

relevant package versions:

django==4.1
django-snowflake==4.1b1
snowflake-connector-python==3.6.0

system:
Windows 10 Enterprise version 21H2
Intel(R) Core(TM) i5-8350U CPU @ 1.70 GHz

As additional background I am able to successfully connect to snowflake using these same exact credentials when using snowflake-connector-python package only, outside of django, just running regular queries, and can also connect and update tables using the same exact credentials in Node.js. But I can't get it to work with django-snowflake. When I runserver, the expected web browser window never appears, it just indicates incorrect username or password, but I know they're correct.

My snowflake DB admin has access to what I am doing and he has advised me that he is seeing failed connections with snowflake-connector-python 3.6.0 and that it appears as though externalbrowser is not being used, that it may be attempting to access using my username and password only, which we cannot do as we're behind company enterprise agreement with snowflake and MUST use externalbrowser authentication. Since I never see the browser window appear in django, just the incorrect username or password error in the terminal, perhaps I am doing something wrong to prevent django-snowflake from recognizing the authenticator key. But I have tried all combinations, including the exact pattern as in the quickstart docs, and still no luck.

Has anyone encountered this and could anyone advise what I am doing wrong?

Thanks in advance.

Hi,

I'm trying to perform the Django inspectdb command using my snowflake connection, but I get the following error:

> python manage.py inspectdb --database snowflake table_name
# 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
# Unable to inspect table 'table_name'
# The error was: too many values to unpack (expected 11)

I'm using:

• python=3.11.6
• django=4.2.7
• django-snowflake=4.2

The database connection in settings.py is configured like this:

DATABASES = {
    ...
    'snowflake': {
        'ENGINE': env("SNOWFLAKE_ENGINE"),
        'NAME': env("SNOWFLAKE_NAME"),
        'SCHEMA': env("SNOWFLAKE_SCHEMA"),
        'WAREHOUSE': env("SNOWFLAKE_WAREHOUSE"),
        'USER': env("SNOWFLAKE_USER"),
        'PASSWORD': env("SNOWFLAKE_PASSWORD"),
        'ACCOUNT': env("SNOWFLAKE_ACCOUNT"),
        'OPTIONS': {
            'role': env("SNOWFLAKE_ROLE"),
        },
    }
}

I am able to access the data using the Django Shell, so I know the the connection is working.

>>> from django.db import connections
>>> conn = connections['snowflake']
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT * FROM table_name").fetchall()
[(...), (...), ...]

Am I missing something here?
Thanks in advance.

Hello all,

Recently I discovered this Snowflake backend for Django so let me thank you the effort because it's something I've been waiting for months.

The reason of this issue is because I've been playing with it and I experience slow performance. I would like to know if others are facing similar issues and if there are any recommended solutions or workarounds.

I did two tests with the following environments:

python                     3.11.3
Django                     4.2.7
django-snowflake           4.2b1

and

python                     3.10.4
Django                     4.2.1
django-snowflake           4.2b1

In both cases I followed the same steps, I created a fresh new conda enviroment and I configured the Django admin-site to use an small table (10 records) where to perform CRUD operations. Every operation takes around 10 seconds. It doesn't matter if I list all the records of the table or if I try to update a single record... it always takes 9s, 10s, 11s approximately.

Another comparison that I did was using snowflake-sqlalchemy package, against the same table, and there I didn't have any performance issue.

I would appreciate any guidance or recommendation.

Thanks in advance

I believe VARIANT is the data type to use with Django's JSONField, however, Snowflake requires using INSERT INTO ... SELECT to insert data, which I'm not sure Django has hooks to handle.

There's a snowflake-connector-python issue (snowflakedb/snowflake-connector-python#244) to add support for INSERT INTO... (without SELECT).

Django's database cache backend doesn't work with Snowflake because it includes SQL queries that don't quote all of the fields as Snowflake requires (e.g. expires).

Also, the createcachetable management command creates an index (which isn't supported on Snowflake).

A queryset like:

Experiment.objects.annotate(shifted=ExpressionWrapper(
    F('completed') - Value(None, output_field=DurationField()),
    output_field=DateField(),
))

generates

SELECT ("experiment"."completed" - INTERVAL 'NULL MICROSECONDS') AS "shifted" FROM "experiment"

which fails with syntax error line 1 at position 0 unexpected 'NULL'.

On other databases, interval math with a null interval results in NULL. Perhaps this could be fixed in Snowflake.

I'm wondering if we can delete notes and implementation related to last_insert_id.

I see the main tests accepts databases that set can_return_columns_from_insert to false:

https://github.com/django/django/blob/main/django/db/backends/base/features.py

Which used to be called can_return_id_from_insert:

https://github.com/django/django/blob/7414704e88d73dafbcfbb85f9bc54cb6111439d3/docs/releases/3.0.txt#L417

I see @timgraham was involved with removing last_insert_id from the Postgres implementation in 2017:

django/django@5d9034b

Due to snowflake-connector-python's lack of VARIANT support (I think it's difficult if not impossible to fix this otherwise), some JSONField queries with complex JSON parameters don't work.

For example, if value is a JSONField, this won't work:

>>> JSONModel.objects.filter(value__k={"l": "m"})

A workaround is:

>>> from django.db.models.expressions import RawSQL
>>> JSONModel.objects.filter(value__k=RawSQL("PARSE_JSON(%s)", ('{"l": "m"}',)))

I have the following settings:

DATABASES = {
    'default': {
        'ENGINE': 'django_snowflake',
        'ACCOUNT': 'my account',
        'NAME': 'my database',
        'SCHEMA': "my schema",
        'WAREHOUSE': 'my warehouse',
        'USER': "my user name"
        # Include 'OPTIONS' if you need to specify any other
        # snowflake.connector.connect() parameters.
        # https://docs.snowflake.com/en/user-guide/python-connector-api.html#connect
        'OPTIONS': {
            "role": "my role",
            "authenticator": "externalbrowser",
        },
    },
}

Getting the connection fails with the following error:

django.core.exceptions.ImproperlyConfigured: settings.DATABASES is missing 'PASSWORD' for 'django_snowflake'.

DatabaseIntrospection.identifier_converter() doesn't handle lower case or mixed case identifiers properly.

In practice, the concept of a USER (i.e. their DEFAULT_ROLE) is ambiguous in Snowflake.

Currently role can be passed through OPTIONS but this approach does not surface the control very well.

It should be a top level config to be explicit on what role will be used to perform all database activity.

For convenience the config can remain optional (to rely on DEFAULT_ROLE of the USER when unset).

Does django-snowflake support a persistent DB connection, in the same vein as Django's usual CONN_MAX_AGE?

I tried setting client_session_keep_alive, but from what I can glean from the logs the app is still creating and closing a new Snowflake connection for every request.

timestamp_ltz is converting the time and inserting, whereas timestamp_ntz inserts the time as it is, so better use timestamp_ntz. Is there any reason in using timestamp_ltz.

It isn't immediately obvious when filling the DATABASES config section on what the value of NAME should be. The example helps but the key name by itself (as NAME) is implicit in requesting a database name.

Could this config key name be changed from NAME to DB_NAME or DATABASE? This would align it with the other explicit key names.

I'm running 3.2.latest, and when attempting to use inspectdb to update model definitions I'm getting the ValueError: too many values to unpack (expected 11) error as in #84 because it's expecting an 11-tuple rather than the new 12-tuple.

Django 3.2.x is LTS until the end of this quarterApril: https://www.djangoproject.com/download/#supported-versions

Could the 4.x/5.x fix for this please be backported to 3.2.x?