search

Snowflake-Labs / django-snowflake

MIT License
61 stars 16 forks source link

ERROR in RawQueryset [Raw SQL ] #44

Closed ojha-chandan closed 2 years ago

ojha-chandan commented 2 years ago

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'