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

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 b开发者_如何学Goetween objects returned from raw queries and orm queries?

---

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.

---

SQLAlchemy allows a fair bit of complexity in formulating queries so you can usually get away without raw sql. If you do need to dip down to raw sql, you can use connection.execute. But there are helpers like the text and select functions to make this easier when programming. As far as dealing with the returned objects, you get a list of tuples which are simple to deal with in a pythonic way.

In general, if you need to treat rows (list of tuples, etc) as what your ORM returns, one approach would be to write an adapter class which mimics a queryset interface. This could be initialized with the "schema" of the returned tuples, and then iterate and return objects with properties instead of tuples. I haven't really needed this, but I can see how it might be useful if, for example, you have a framework which relies on querysets being passed around.