snowflake-labs / django-snowflake

Snowflake backend for Django

Install and usage

Use the version of django-snowflake that corresponds to your version of Django. For example, to get the latest compatible release for Django 5.0.x:

pip install django-snowflake==5.0.*

The minor release number of Django doesn't correspond to the minor release number of django-snowflake. Use the latest minor release of each.

Configure the Django DATABASES setting similar to this:

DATABASES = {
    'default': {
        'ENGINE': 'django_snowflake',
        'NAME': 'MY_DATABASE',
        'SCHEMA': 'MY_SCHEMA',
        'WAREHOUSE': 'MY_WAREHOUSE',
        'USER': 'my_user',
        'PASSWORD': 'my_password',
        'ACCOUNT': 'my_account',
        # Include 'OPTIONS' if you need to specify any other
        # snowflake.connector.connect() parameters, documented at:
        # https://docs.snowflake.com/en/user-guide/python-connector-api.html#connect
        'OPTIONS': {
            # Examples:
            'role': 'MY_ROLE',
            # To use native Okta authenticators:
            # https://docs.snowflake.com/en/user-guide/admin-security-fed-auth-use#native-sso-okta-only
            'authenticator': 'https://example.okta.com',
            # To use private key authentication:
            'private_key_file': '<path>/rsa_key.p8',
            'private_key_file_pwd': 'my_passphrase',
        },
    },
}

Persistent connections

To use persisent connections, set Django's CONN_MAX_AGE and Snowflake Python Connector's client_session_keep_alive:

DATABASES = {
    'default': {
        # ...
        'CONN_MAX_AGE': None,
        'OPTIONS': {
            'client_session_keep_alive': True,
        },
    },
}

Notes on Django fields

• Consistent with Snowflake's convention, this backend uppercases all database identifiers (table names, column names, etc.) unless they are quoted, e.g. db_table='"table_name"'.

• Snowflake supports defining foreign key and unique constraints, however, it doesn't enforce them. Thus, Django manages these constraints and inspectdb detects them, but Django won't raise IntegrityError if they're violated.

• Snowflake doesn't support indexes. Thus, Django ignores any indexes defined on models or fields.

• Snowflake doesn't support check constraints, so the various PositiveIntegerField model fields allow negative values (though validation at the form level still works).

Notes on Django QuerySets

• Snowflake has limited support for subqueries.

• Valid values for QuerySet.explain()'s format parameter are 'json', 'tabular', and 'text'. The default is 'tabular'.

Known issues and limitations

This list isn't exhaustive. If you run into a problem, consult django_snowflake/features.py to see if a similar test is skipped. Please create an issue on GitHub if you encounter an issue worth documenting.

• Snowflake doesn't support last_insert_id to retrieve the ID of a newly created object. Instead, this backend issues the query SELECT MAX(pk_name) FROM table_name to retrieve the ID. This is subject to race conditions if objects are created concurrently. This makes this backend inappropriate for use in web app use cases where multiple clients could be creating objects at the same time. Further, you should not manually specify an ID (e.g. MyModel(id=1)) when creating an object.

• Snowflake only supports single layer transactions, but Django's TestCase requires that the database supports nested transactions. Therefore, Django's TestCase operates like TransactionTestCase, without the benefit of transactions to speed it up. Starting in Django 5.1, TestCase uses Snowflake's single layer transaction support to give some speed up.

• Due to snowflake-connector-python's lack of VARIANT support, 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"}',)))

  In addition, QuerySet.bulk_update() isn't supported for JSONField.

• Interval math where the interval is a column is not supported.

• Interval math with a null interval crashes.

Troubleshooting

Debug logging

To troubleshoot issues with connectivity to Snowflake, you can enable Snowflake Connector for Python's logging using Django's LOGGING setting.

This is a minimal addition to Django's default "loggers" configuration that enables the connector's DEBUG logging:

LOGGING = {
    …
    "loggers": {
        …
        "snowflake.connector": {
            "level": "DEBUG",
            "handlers": ["console"],
        },
    },
}

django-snowflake's Issues

Consider changing NAME to DB_NAME

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.

Error: The session does not have a current database

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?

Unable to use the connector with external browser authentication

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

Document how to authenticate with a private key

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,
        }
    }
}

Add support for resetting autoincrement sequences

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.

declare django as a dependencies

We had several issues when upgrading django where we would then have error in the form of You must use the latest version of django-snowflake X.Y.z with Django X.Y.z (found django-snowflake A.B.C). occasioning runtime error on staging/preprod.
Why not declare django as a requirement, where the required django version would be expressed, so this can be avoid at dependency installation time?

Slow performance

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

Add support for introspecting lower case identifiers

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

Switch to using upper case identifiers (table names, column names, etc.)

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.

ROLE should be a top level config

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).

Add support for JSONField

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).

Persistent DB connection (a la `CONN_MAX_AGE`)

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.

Add support for interval math with NULL

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.

Support for Snowflake Hybrid Tables

I see in the README under known issues:

"Snowflake doesn’t support last_insert_id to retrieve the ID of a newly created object. Instead, this backend issues the query SELECT MAX(pk_name) FROM table_name to retrieve the ID. "

I am currently struggling with this, and came across Snowflake hybrid tables. I'm wondering if this issue could be resolved by supporting Hybrid Tables creation?

https://docs.snowflake.com/user-guide/tables-hybrid

authenticator:externalbrowser issue

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.

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

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

Option private_key does not work when launching SnowSQL

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?

JSONField queries with complex JSON parameters don't work

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"}',)))

Datetime should be Timestamp_ntz

django-snowflake/django_snowflake/base.py

Line 32 in 25c6034

'DateTimeField': 'TIMESTAMP_LTZ',

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.

Receiving 'Error detecting the version of libcrypto' when connecting to Django

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

Exists database function generates invalid Snowflake SQL

Using Django 'Exists' generates invalid SQL:

qa = Notification.objects.filter( ~Exists( NotificationUser.objects.filter(notification_id=OuterRef("notification_id")) ), valid_from_date__lte=now, valid_to_date__gte=now, )

Generates the following SQL:

SELECT "PUBLIC"."NOTIFICATION"."NOTIFICATION_ID",       "PUBLIC"."NOTIFICATION"."MESSAGE",       "PUBLIC"."NOTIFICATION"."URL",       "PUBLIC"."NOTIFICATION"."URL_DESCRIPTION",       "PUBLIC"."NOTIFICATION"."VALID_FROM_DATE",       "PUBLIC"."NOTIFICATION"."VALID_TO_DATE",       "PUBLIC"."NOTIFICATION"."INSERT_TIMESTAMP",       "PUBLIC"."NOTIFICATION"."INSERT_USER",       "PUBLIC"."NOTIFICATION"."UPDATE_TIMESTAMP",       "PUBLIC"."NOTIFICATION"."UPDATE_USER"  FROM "PUBLIC"."NOTIFICATION" WHERE (NOT EXISTS(SELECT 1 AS "A" FROM "PUBLIC"."NOTIFICATION_USER" U0 WHERE U0."NOTIFICATION_ID" = ("PUBLIC"."NOTIFICATION"."NOTIFICATION_ID") LIMIT 1) AND "PUBLIC"."NOTIFICATION"."VALID_FROM_DATE" <= '2024-03-25 11:05:58.376722+00:00' AND "PUBLIC"."NOTIFICATION"."VALID_TO_DATE" >= '2024-03-25 11:05:58.376722+00:00') ORDER BY "PUBLIC"."NOTIFICATION"."VALID_FROM_DATE" ASC

The use of LIMIT in the sub-query is not valid in Snowflake:

SQL Error [2031] [42601]: SQL compilation error:
Unsupported subquery type cannot be evaluated

Erroneously Looking for Config.toml File when using DATABASES dictionary in settings.py

I have been using django-snowflake for awhile and just encountered this error when making a call to the Snowflake backend, per READMe instructions.

{"status": "error", "message": "[Errno 13] Permission denied: '/root/.config/snowflake/config.toml'"}

It is my understanding that since I am referencing all of my environment variables from the DATABASES dictionary (using os.getenv), that the config.toml is not necessary. So I was confused as to why all of a sudden this was file was being searched for. And to be clear, it's not just that Permission denied, it is that the file and directory did not exist at all.

How I fixed the issue:
I added snowfalke-connector-python==3.8.1 to my requirements.txt file before django-snowflake==4.2.*

Why?
I found this in the dist-info/METADATA for snowflake-connector-python 3.10.1:

• v3.9.0(April 20,2024)

  • Added easy logging configuration so that users can easily generate log file by setup log config in $SNOWFLAKE_HOME/config.toml.
  • Improved s3 acceleration logic when connecting to China endpoint.

I'm not sure if this is something that django-snowflake contributors want to address, or if it just might be useful for someone searching for solutions to find this.

Add support for INTERVAL math where the interval is a column

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

Use last_query_id when retrieving last_insert_id

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?

instead of last_insert_id, can we set "can_return_columns_from_insert" as False?

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

Switch to REGEXP_INSTR to avoid implicit anchoring

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;

Making user field optional

I'm facing issues connecting my Django application to a Snowflake warehouse using Snowpark Container Service and OAuth authentication. While the application can connect via the external network using username and password, it requires username when using OAuth within the internal network. I'm using Django-snowflake version 4.1 and would like to establish a connection without external network access.

"The error was: too many values to unpack (expected 11)" while running inspectdb

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.

UUIDs being stripped of hyphens

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,)

ERROR in RawQueryset [Raw SQL ]

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'

Add support for database caching

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).

Backport fix for crash in `DatabaseIntrospection.get_table_description()` to Django 3.2.x

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?