How do you mix raw SQL with ORM APIs when you use django.db?

Question

ORM tools are great when the queries we need are simple select or insert clauses.

But sometimes we may have to fall back to use raw SQL queries, because we may need to make queries so complex that simply using the ORM API can not give us an efficient and effective solution.

What do you do to deal with the difference between objects returned from raw queries and orm queries?

Answer 1

I personally strive to design my models so I don't have to deffer to writing raw SQL queries, or fallback to mixing in the ContentTypes framework for complex relationships, so I have no experience on the topic.

The documentation covers the topic of the APIs for performing raw SQL queries. You can either use the Manager.raw() on your models (MyModel.objects.raw()), for queries where you can map columns back to actual model fields, or user cursor to query raw rows on your database connection.

If your going to use Manager.raw(), you'll work with the RawQuerySet instead of the usual QuerySet. For all things concerned, when working with result objects the two emulate containers identically, but the QuerySet is a more feature-packed monad.

I can imagine that performing raw SQL queries in Django would be more rewarding than working with a framework with no ORM support—Django can manage your database schema and provide you with a database connection and you'll only have to manually create queries and position query arguments. The resulting rows can be accessed as lists or dictionaries, both of which make it suitable for displaying in templates or performing additional lifting.