CODEWITHSUNDEEP
mysqlsqldjangotuples
Gereltod
Gereltod

Reputation: 2234

Django get raw query result as objects

I recently started learn Django. I've 2 case. 1 of them works as I wanted. Other one is not.

Working case:

cursor = connections['ticket'].cursor()
        raw_query = "SELECT * FROM ticket WHERE customer_code = '%s'" % customer.custid
        cursor.execute(raw_query)
        tickets = cursor.fetchall()

And when I use tickets in template:

{{ ticket.name }}

It prints its value.

Now other not working case:

cursor = connections['ubds'].cursor()
        raw_query = """SELECT  * FROM IncomeTransactions WHERE CustID = '%s' AND TranType = '17'"""\
                    """ORDER BY TranDate ASC""" % (request.session['member_id'])
        cursor.execute(raw_query)
        invoices = cursor.fetchall()

And when I print its value in print:

{{ invoice.name }} #blank result

Its not printing anything. Just blank. But when I access into index and print it works

{{ invoice.2 }} #it printing 3rd column from table

I suspect first one return object, but second one returning array (tuple). But they both used same functions. What could be reason? How can I access via column name on 2nd case?

Edit: Print result on both cases

print tickets

[(917235, 162, None, datetime.datetime(2014, 1, 17, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 8199484.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.101', u'mijiddorj', 29522, 19, 3, None, None, None), (920746, 681, None, datetime.datetime(2014, 2, 17, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 5237942.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.141', u'mijiddorj', 29522, 19, 3, None, None, None), (924625, 1148, None, datetime.datetime(2014, 3, 17, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 6574669.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.141', u'mijiddorj', 29522, 19, 3, None, None, None), (928477, 1690, None, datetime.datetime(2014, 4, 17, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 4269964.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.31', u'mijiddorj', 29522, 19, 3, None, None, None), (932106, 1103, None, datetime.datetime(2014, 5, 19, 0, 0, tzinfo=), u'0001-04', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 2408917.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.121', u'mijiddorj', 29522, 19, 3, None, None, None), (934461, 173, None, datetime.datetime(2014, 6, 20, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 12024.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.109', u'mijiddorj', 29522, 19, 3, None, None, None), (936002, 8, None, datetime.datetime(2014, 7, 17, 0, 0, tzinfo=), u'0001-04', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 52355.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.75', u'mijiddorj', 29522, 19, 3, None, None, None), (938032, 16, None, datetime.datetime(2014, 8, 20, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 40833.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.78', u'mijiddorj', 29522, 19, 3, None, None, None), (940140, 53, None, datetime.datetime(2014, 9, 10, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 1399746.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.143', u'mijiddorj', 29522, 19, 3, None, None, None), (942116, 179, None, datetime.datetime(2014, 9, 17, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 40833.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.54', u'mijiddorj', 29522, 19, 3, None, None, None), (946440, 96, None, datetime.datetime(2014, 10, 17, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 270453.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.196', u'mijiddorj', 29522, 19, 3, None, None, None), (949055, 105, None, datetime.datetime(2014, 11, 13, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 2869320.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.196', u'mijiddorj', 29522, 19, 3, None, None, None), (952901, 169, None, datetime.datetime(2014, 12, 17, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 7107883.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.196', u'mijiddorj', 29522, 19, 3, None, None, None), (1118349, 177, None, datetime.datetime(2015, 1, 19, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 7546811.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.63', u'Handaa', 29522, 19, 3, None, None, None), (1122726, 273, None, datetime.datetime(2015, 2, 17, 0, 0, tzinfo=), u'0001-08', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'0002', None, None, 8005173.0, u'\u0414\u0443\u043b\u0430\u0430\u043d\u044b \u04af\u043d\u044d ', 17, None, None, None, None, u'192.168.0.75', u'Handaa', 29522, 19, 3, None, None, None)]

print invoices

((1L, u'T15021715135295', 2L, u'call', u'\u0428\u0443\u0443\u0434 \u0445\u0430\u0440\u0438\u0443\u043b\u0441\u0430\u043d. test122123', u'closed', u'99881122', 2L, u'\u0414\u04af\u0433\u044d\u0440\u0445\u04af\u04af \u042d\u043d\u0445\u0447\u0438\u043c\u044d\u0433', 4L, u'\u0414\u0430\u0432\u0430\u0430\u0445\u04af\u04af \u0410\u043b\u0442\u0430\u043d\u043d\u0430\u0432\u0447', 10L, u'\u0410\u0432\u0442\u043e \u0430\u0436 \u0430\u0445\u0443\u0439\u043d \u0430\u043b\u0431\u0430', datetime.datetime(2015, 2, 21, 15, 13, 52, tzinfo=), datetime.datetime(2015, 2, 26, 18, 53, 36, tzinfo=), datetime.datetime(2015, 2, 26, 17, 20, tzinfo=), datetime.datetime(2015, 2, 17, 15, 13, 52, tzinfo=), None, None, None, u'agent-101-1424234875-330.wav', u'0002', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'', None, u'', u'', u'', u'', u'', '\x01', '\x00', '\x01', '\x01'), (2L, u'T15030212560836', 2L, u'call', u'test test', u'open', u'99081133', 2L, u'\u0414\u04af\u0433\u044d\u0440\u0445\u04af\u04af \u042d\u043d\u0445\u0447\u0438\u043c\u044d\u0433', None, None, None, None, datetime.datetime(2015, 3, 2, 12, 56, 8, tzinfo=), datetime.datetime(2015, 3, 2, 12, 56, 8, tzinfo=), None, None, u'', None, None, None, u'0002', u'\u0410\u0437\u0438\u044f \u043f\u0440\u043e\u043f\u0435\u0440\u0442\u0438\u0441 \u0425\u0425\u041a', u'\u0411\u043e\u043b\u0434', 14000L, u'3', u'\u0410\u043c\u0430\u0440', u'55', u'55', u'[email protected]', '\x00', '\x00', '\x00', '\x00'))

Difference is 1st one (working one) have [( )], but 2nd one (not working one) have (( )).

Upvotes: 1

Views: 2763

Answers (2)

knbk
knbk

Reputation: 53649

To fetch raw results as model instances, use the raw() queryset method:

raw_query = "SELECT * FROM ticket WHERE customer_code = '%s'"
tickets = Ticket.objects.raw(raw_query, params=[customer.custid])

Always (yes, always) pass your parameters with the params parameter, this will protect you against SQL injection attacks. You should do the same when passing parameters to cursor.execute.

Upvotes: 1

catavaran
catavaran

Reputation: 45565

Hm, I don't understand why the first example works. It shouldn't - cursor.fetchall() always returns list of tuples.

But if you want to get the list of dicts instead of tuples then use the recipe from the django documentation:

def dictfetchall(cursor):
    desc = cursor.description
    return [dict(zip([col[0] for col in desc], row))
              for row in cursor.fetchall()]
...
cursor.execute(raw_query)
invoices = dictfetchall(cursor)

Upvotes: 1

Related Questions