Django/python: Raw Sql With Multiple Tables

I need to perform a raw sql on multiple tables. I then render the result set. For one table I would do: sql = 'select * from my_table' results = my_table.objects.raw(sql) For mult

Solution 1:

1. This works. Don't know why it didn't before :( From Dennis Baker's comment:

You do NOT need to have a model with all the fields in it, you just need the first model and fields from that. You do need to have the fields with unique names and as far as I know you should use "tablename.field as fieldname" to make sure you have all unique fields. I've done some fairly complex queries with 5+ tables this way and always tie them back to a single model. –

2 . Another solution is to use a cursor. However, a cursor has to be changed from a list of tuples to a list of dictionaries. I'm sure there are cleaner ways using iterators, but this function works. It takes a string, which is the raw sql query, and returns a list which can be rendered and used in a template.

from django.db import connection, transaction

defsql_select(sql):
    cursor = connection.cursor()
    cursor.execute(sql)
    results = cursor.fetchall()
    list = []
    i = 0for row in results:
        dict = {} 
        field = 0whileTrue:
           try:
                dict[cursor.description[field][0]] = str(results[i][field])
                field = field +1except IndexError as e:
                break
        i = i + 1list.append(dict) 
    returnlist

Solution 2:

you do not need a model that includes the fields that you want to return from your raw sql. If you happen to have a model that actually has the fields that you want to return from your raw sql then you can map your raw sql output to this model, otherwise you can use cursors to go around models altogether.