Django ORM query fails when executed as query in postgresql

Question

I have a model something like this

class model(models.Model):
    order_created_time = models.DateTimeField(blank=False, null=False)

I have a django query which compares datetime like this -

filters = {'order_created_on__gte':'2018-10-10'}
queryset = model.objects.filter(**filters)
query = str(queryset.query)

It creates a query - select ... where order_created_on >= 2018-10-10 00:00:00

When fired on db, it gives an error - syntax error at or near "00".

If I fire the same query in db manually by replacing the date by '2018-10-10' it works.

Now I actually tried the following ways, but all queries give the same text in the db query

filters = {'order_created_on__gte':datetime(2018-10-10)}
filters = {'order_created_on__year__gte':2018, 'order_created_on__month__gte':10, 'order_created_on__day__gte':10}

Also tried to use it as a string like this -

filters['order_created_on__gte'] = "'{0}'".format(filters['order_created_on__gte'])

It states as invalid format, it expects 2018-10-10 00:00[:00][TZ]

Also used the range filter, all of the above insert this text in the final query -

where order_created_on >= 2018-10-10 00:00:00

Updating the time zone too didnt have any effect rather than just removing a +5:30 from the query.

Answer 1

You can generate the query using the below formula:-

import datetime

sql, params = queryset.query.sql_with_params()

params_with_quotes = []
for x in params:
    if isinstance(x, (str, list, datetime.date, datetime.datetime)):
        x = "'%s'" % str(x).replace("'", "\"").replace("[", "{").replace("]", "}")
    params_with_quotes.append(x)

query = sql % tuple(params_with_quotes)
print(query)

Answer 2

Tired of using that regex in replacing the query, used this approach. Just put the filters with an option or 'ORM' or 'RAW'. If its a raw then it will construct the 'where' query. you can also add other filters in it.

    # For ORM
    GET_SETTLEMENT_PARAM_QUERY_FORMATTING = {
    "settlement_no": {"query_text": "settlement_no__in", "list": True},
    "start_date": {"query_text": "start_date__gte", "list": False},
    "end_date": {"query_text": "end_date__lte", "list": False}
    }

    # For RAW query
    BAGSETTLEMENT_DOWNLOAD_PARAM_QUERY_FORMATTING_RAW = {
    "start_date": {"query_text": "created_on >= '{}'", "list": True, "ignore_format": True},
    "end_date": {"query_text": "created_on <= '{}'", "list": True, "ignore_format": True},
    "store_id": {"query_text": "store_id IN {}", "list": True},
    "brand_id": {"query_text": "brand_id IN {}", "list": True},
    "company_id": {"query_text": "company_id = {}", "list": False},
    "bag_id": {"query_text": "bag_id = {}", "list": False},
    "awb_number": {"query_text": "awb_number = {}", "list": False},
    "settlement_no": {"query_text": "settlement_no IN {}", "list": True},
    "settled": {"query_text": "settled = {}", "list": False}

}


    @query_params(formatting=GET_BAGSETTLEMENT_PARAM_QUERY_FORMATTING, query_type='ORM')
        def get(self, request):

            filters = parse_url_params(params=self.request.query_params, formatting=BAGSETTLEMENT_DOWNLOAD_PARAM_QUERY_FORMATTING_RAW, query_type='RAW')

            try:
                link = get_download_link(filters=filters,
                                             store_ids=request.store_ids,
                                             end_file_name=settlement_no)
                return Response({"link": link})
            except Exception as e:
                logger.critical('Bag Settlement Download Link Generation Failed')
                return Response({'link': None, 'error': 'Error while fetching data'})


def query_params(formatting, query_type):
    def assign_query_params(f):
        @wraps(f)
        def decorated_function(req, *args, **kwargs):
            try:
                request = req.request
            except AttributeError:
                request = req

            # print(request.store_ids)
            data = dict(request.GET)
            if data.get('store_id'):
                data['store_id'] = list(set(data.get('store_id')).intersection(set(request.store_ids)))
            else:
                data['store_id'] = request.store_ids

            request.filters = parse_url_params(params=data,
                                               formatting=formatting,
                                               query_type=query_type)
            return f(req, *args, **kwargs)

        return decorated_function

    return assign_query_params

def parse_url_params(params, formatting, query_type):
    """
    :param params: dictionary
    :param formatting: Formatting Dictionary
    :param query_type: ORM else RAW
    :return: filters
    """
    # print(params)
    filters = dict() if query_type == "ORM" else ''

    for key, value in formatting.items():
        param_value = params.get(key)
        if not param_value:
            continue
        query_text = value['query_text']

        if query_type == "ORM":
            query_value = param_value[0] if not value['list'] else param_value

            filters[query_text] = query_value

        else:
            if not value['list']:
                query_value = param_value[0]
            else:

                if value.get('ignore_format'):
                    query_value = param_value
                else:
                    z = str(param_value)
                    query_value = z.replace(z[0], '(').replace(z[len(z)-1], ')')
            syntax = query_text.format(query_value)
            filters += syntax if not len(filters) else ' AND '+syntax

    return filters

Via this, you can get the ORM filters as well as RAW query filters. The problem which I was facing was that the datetime wasnt being inserted as string, so here I just made a string of all where conditions.

You would need to enter the query manually and store it in the database or in a constant like this -

select * from table where {filters};

You can also add the order by in it, but you would need to explicitly pass it into the query, didnt add that in filters, so its like this -

select * from table where {filters} order by {order_by}

If you need to add another explicit filters you can do it like this

select * from table where {filters} and store_id in {store_id};

Answer 3

At last after trying all the filters, solved it by replacing the query text by regex.

Answer 4

Django doesn't actually use the result of str(queryset.query) for querying the database. The reason for this is quite simple:

Databases take parameterized queries so Django never actually puts the parameters inside the query. It sends a query string and list of parameters separately to the database which figures out how to deal with it.

print(SomeModel.objects.filter(foo=1).query.sql_with_params())

('SELECT "app_model"."id", "app_model"."some_column", "app_model"."foo" FROM "app_model" WHERE "app_model"."foo" = %s',
 (1,))

Then for simplicity's sake, all it does is do plain string substitution when you call the __str__() on the query. It doesn't use this internally so it's purely for user convenience.

query, params = SomeModel.objects.filter(foo=1).query.sql_with_params()
print(query % params)

SELECT "app_model"."id", "app_model"."some_column", "app_model"."foo" FROM "app_model" WHERE "app_model"."foo" = 1

Naturally, when a date is inserted into a format string like that, there are no quotes surrounding it which leads to errors when you try copy-pasting the query as-is and using it in your database. They didn't bother implementing this because there are just too many types and too little benefit.

While you could make your own function to automatically quote the parameters, and even monkeypatch the queryset class so the __str__() method returned what you need, I feel that django-debug-toolbar is the way to go if you do this regularly.