How To Run / Show Raw SQL Quries In Django

Django model provide an easy way for ORM( object relational mapping) management, with it you even do not need to write sql command. But sometimes you realy need to write your own sql command to improve sql performance or special use case. This example will show you how to run raw sql or get queryset executed sql command in Django.

1. Django Run Raw Sql Example.

You can use Django model query set provided raw method to run your sql command directly.

>>> emp_list = Employee.objects.raw('select * from dept_emp_department')
>>> for emp in emp_list:
...     print(emp)
...     
jack,100000,13901234567
jerry,80000,1369090909
richard,100000,1369090908
jack,100000,13690909099
tom,10000,13901234568

You can also execute update or delete sql command with django.db.connection cursor object. Below example only execute the update sql command, you can execute delete as well.

>>> from django.db import connection

>>> cursor = connection.cursor()
>>> cursor.execute('update dept_emp_employee set emp_salary = 60000 where user_id = 15')
<django.db.backends.sqlite3.base.SQLiteCursorWrapper object at 0x7f31683f31f8>

>>> from dept_emp.models import Employee
>>> from django.contrib.auth.models import User

>>> user = User.objects.get(id=15)
>>> emp = Employee.objects.filter(user=user)
>>> emp
<QuerySet [<Employee: jack,60000,13901234567>, <Employee: jack,60000,13690909099>]>

2. Django Show Raw Sql Example.

When you use Django model field lookup to execute a sql query, you can display the low level raw sql command use the QuerySet’s query attribute like below.

>>> user = User.objects.get(id=15)

>>> emp = Employee.objects.filter(user=user)
>>> emp
<QuerySet [<Employee: jack,60000,13901234567>, <Employee: jack,60000,13690909099>]>

>>> print(emp.query)
SELECT "dept_emp_employee"."id", "dept_emp_employee"."user_id", "dept_emp_employee"."emp_mobile", "dept_emp_employee"."emp_salary", "dept_emp_employee"."emp_onboard_date", "dept_emp_employee"."sex" FROM "dept_emp_employee" WHERE "dept_emp_employee"."user_id" = 15

If you want to show all the raw sql for current database connection, you can execute connection.queries command in Django. This command will return a list, and each list element is a dictionary. The dictionary has two key value pairs, the raw sql and the sql execution time.

>>> connection.queries
[{'sql': 'update dept_emp_employee set emp_salary = 60000 where user_id = 15', 'time': '5.053'}, 
 
 {'sql': 'SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."id" = 15', 'time': '0.000'}, 
 
 {'sql': 'SELECT "dept_emp_employee"."id", "dept_emp_employee"."user_id", "dept_emp_employee"."emp_mobile", "dept_emp_employee"."emp_salary", "dept_emp_employee"."emp_onboard_date", "dept_emp_employee"."sex" FROM "dept_emp_employee" WHERE "dept_emp_employee"."user_id" = 15', 'time': '0.000'}]