python - Best way to save Raw SQL queries in Django

Question

What is the best way to make raw SQL queries in django? I have to search a table for the mode of another table. I could not find a way to solve this in django's ORM so I turned to raw SQL queries. Yet creating all these very long queries in python is very unreadable and does not feel like a proper way to do this. Is there a way to save these queries in a neat format perhaps in the database.

I have to join three separate tables and compute the mode of a few columns on the last table. The length of the queries is getting very big and the code to make these queries becomes very unreadable. An example query would be

SELECT * FROM "core_assembly" INNER JOIN (SELECT * FROM "core_taxonomy" INNER JOIN(SELECT "core_phenotypic"."taxonomy_id"  , 
array_agg("core_phenotypic"."isolation_host_NCBI_tax_id") FILTER (WHERE "core_phenotypic"."isolation_host_NCBI_tax_id" IS NOT NULL) 
AS super_set_isolation_host_NCBI_tax_ids FROM core_phenotypic GROUP BY "core_phenotypic"."taxonomy_id") "mode_table" ON 
"core_taxonomy"."id"="mode_table"."taxonomy_id") "tax_mode" ON "core_assembly"."taxonomy_id"="tax_mode"."id" WHERE  ( 404=ANY(super_set_isolation_host_NCBI_tax_ids));

Where I would have a very big parse function to make all the WHERE clauses based on user input.

Answer 1

You can try this:

from django.db import connection

cursor = connection.cursor()

raw_query = "write your query here"
cursor.execute(raw_query)

You can also run raw queries for models. eg. MyModel.objects.raw('my query').

Read Performing raw SQL queries | Django documentation | Django for more.