Django ORM raw delete query not deleting records

Question

I am using raw_sql queries for my convenience for keeping my database minimal I am deleting extra records. By this query

#d is from a loop and has values
res=MyModel.objects.raw("DELETE FROM mydb_mymodel WHERE mydb_mymodel.s_type = '%s' and mydb_mymodel.barcode = '%s' and mydb_mymodel.shopcode = '%s' and mydb_mymodel.date = '%s'" ,[d.s_type,d.barcode,d.shopcode,d.date])

It is not deleting records in database but

when I do res.query and run it from postgres console it works!

Yes I can use

MyModel.objects.filter(s_type=d.s_type,barcode=d.barcode,
shopcode=d.shopcode,date=d.date).delete()

but what I am missing in raw_sql?

Answer 1

A .raw(..) is not executed eagerly, it is, just like most Django ORM queries performed lazily. It thus returns a RawQuerySet object with the query in the object. For example:

>>> User.objects.raw('BLA BLA BLA', [])
<RawQuerySet: BLA BLA BLA>

A query like BLA BLA BLA does not make any sense: a database will error on it, but still we retrieve a RawQuerySet.

You can force evaluation by for example iterating over it, and then we get:

>>> list(User.objects.raw('BLA BLA BLA', []))
Traceback (most recent call last):
  File "/djangotest/env/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/djangotest/env/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/djangotest/env/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/djangotest/env/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/djangotest/env/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/djangotest/env/lib/python3.6/site-packages/MySQLdb/cursors.py", line 412, in _query
    rowcount = self._do_query(q)
  File "/djangotest/env/lib/python3.6/site-packages/MySQLdb/cursors.py", line 375, in _do_query
    db.query(q)
  File "/djangotest/env/lib/python3.6/site-packages/MySQLdb/connections.py", line 276, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BLA BLA BLA' at line 1")

So the list(..) forces evaluation, and now the database of course produces an error. However even if it was a valid DELETE query, it would still raise an error, since such query does not return any record.

In order to make DELETE calls, the Django manual specifies that you should use a cursor [Django-doc]:

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute(
        "DELETE FROM mydb_mymodel WHERE s_type = '%s' AND barcode = '%s' AND shopcode = '%s' AND date = '%s'" ,
        [d.s_type,d.barcode,d.shopcode,d.date]
    )

But I think it is probably a lot simpler to specify it like:

MyModel.objects.filter(
    s_type=d.s_type,
    barcode=d.barcode,
    shopcode=d.shopcode,
    date=d.date
).delete()

This will construct a DELETE query, and serialize the parameters properly. A .delete() query is done eagerly, so the odds of making above discussed mistakes is a lot lower: if the ORM is implemented correctly, then we do not need to worry about that.