How To Perform Raw SQL Queries in Djangoby Applied Informatics
Django, a python based web framework allows us to build apps in a much more hassle free manner. Once we create a Django project we can have as many apps as we want depending on the need of the project. After we create an app for our project we have different files in our app folder such as models.py describing database structure of our app, views.py meant for model querying part, urls.py to describe url patterns for our app in addition to other files.
Once the django app is ready, it automatically provides us a database-abstraction API that lets us perform certain queries based on creating, updating, or deleting objects present in the model. However, sometimes it becomes quite difficult to perform queries using model query APIs if we have huge amount of data that needs to be executed based on certain get or filter queries.
In such cases we can write custom SQL Queries and execute them directly in Django. Django provides us two different approaches for executing sql queries, either we can use raw() manager method to execute queries and access model instances or we can skip model layer and execute custom sql queries directly.
Consider a django app, say for example, accounts which have a model structure for user profile with different fields.
GENDER_TYPES = ( (MALE, 'MALE'), (FEMALE, 'FEMALE') ) class Account(models.Model): first_name = models.Charfield(max_length=225) last_name = models.Charfield(max_length=225) created_on = models.DateTimeField(auto_now_add=True) modified_on = models.DateTimeField(auto_now=True) login_count = models.PositiveIntegerField(default=0) gender = models.CharField(max_length=1, choices=GENDER_TYPES)
Now, if we want to query Account Model to return all the objects, using Django results would be as below:
account_obj = Account.objects.all()
If we want to perform the same query using custom sql query we can use either raw() manager method or we can execute sql query directly. So using raw() manager method we get the result as,
account_obj = Account.objects.raw("SELECT * FROM accounts_account;")
Now both the account_obj will return same result. However, the only difference between these two would be that we cannot implement all methods in raw queryset which otherwise would have been possible with a simple queryset. The main reason for this cause is that all methods such as __len__ are not defined in rawqueryset and as such implementing them without internal caching would result in performance issues and this type of caching would be backward incompatible.
Using raw() method, we can automatically map fields in query to fields in the model as below:
Account.objects.raw('SELECT id, first_name, last_name, gender FROM accounts_account;')
It doesn’t matter in what order of fields it would map correctly to model fields as long as name is correct.
Now sometimes manager.raw() method is not enough if we want to access database directly without having to perform operations that don’t map cleanly to model fields. In these cases, we can can execute DB queries directly.
For this purpose we use django.db.connection object that represents default database connection. To use database connection, call connection.cursor() to get a cursor object. Then, call cursor.execute(sql, [params]) to execute SQL and cursor.fetchone() or cursor.fetchall() to return resulting rows.
An example to explain this is shown below:
from django.db import connection def my_custom_sql(self): cursor = connection.cursor() cursor.execute("SELECT * FROM accounts_account where id=%s",[self.id]) row = cursor.fetchall() return row
By default, Python DB API will return results without their field names, which means we end up with a list of values, rather than a dict. Depending on the need, we can get results as a list of values or simply a dict using dictfetchall() or collections.namedtuple() from python standard library.
Thus, custom sql queries is much more powerful than using normal queryset as it helps in optimization and improving performance when it comes to executing large datasets. But it overrides some of the features of Django ORM and if we can afford it, we can use custom sql queries to improve performance.