{"id":199,"date":"2011-04-11T10:50:24","date_gmt":"2011-04-11T14:50:24","guid":{"rendered":"http:\/\/dashdrum.com\/blog\/?p=199"},"modified":"2012-01-29T11:17:41","modified_gmt":"2012-01-29T15:17:41","slug":"looking-for-best-practice-for-raw-sql","status":"publish","type":"post","link":"https:\/\/dashdrum.com\/blog\/2011\/04\/looking-for-best-practice-for-raw-sql\/","title":{"rendered":"Looking for Best Practice for Raw SQL"},"content":{"rendered":"
\"DVD<\/a>
Not Quite This Raw<\/figcaption><\/figure>\n

My application includes some existing tables from another, non-Django application. One of them does not have an atomic primary key, and I’m not in a position to change that. Therefore, I’m using one raw SQL to access the data.<\/p>\n

 <\/p>\n

Where I run into trouble is that the query returns data in columns, but not objects. That means that I can’t reference an object attribute using dot notation in my templates or views. Instead, I have to include each attribute that will be needed as a query column. This will limit what I or someone else can do in templates later without a mod to the query.<\/p>\n

In an earlier application with a similar situation, I took the results from the query and built a dictionary of objects. Clumsy, hard-coded, and likely slow, but it did give me what I needed.<\/p>\n

I’ve looked around the web, but haven’t found any practices to follow, so I hope my many readers (smirk) can offer some help.<\/p>\n

Have you tackled this dilemma in the past? Or maybe read where someone else has discussed a possible solution? (I’ll also take wild guesses). Please leave a comment below or drop me a line. I’ll summarize the responses and my solution(s) in a future post.<\/p>\n

Thanks!!<\/p>\n

UPDATE:<\/h3>\n

Here’s how I’m doing it now:<\/p>\n

def available_classes_query(username=None):\r\n    \"\"\"Run a simple query and produce a generator\r\n    that returns the results as a bunch of dictionaries\r\n    with keys for the column values selected.\r\n    \"\"\"\r\n    from itertools import izip\r\n    from django.db import connection\r\n\r\n    query_string = \"\"\"select tu.id as traininguser, cl.id as trainingclass, ts.id as trainingschedule\r\n                                 -- big chunk of SQL cut for readability --\"\"\"\r\n\r\n    cursor = connection.cursor()\r\n    cursor.execute(query_string, [username])  ## parameter for query\r\n    col_names = [desc[0] for desc in cursor.description]\r\n    while True:\r\n        row = cursor.fetchone()\r\n        if row is None:\r\n            break\r\n        row_dict = dict(izip(col_names, row))\r\n\r\n        try:\r\n            row_dict['traininguser'] = TrainingUser.objects.get(id=row[0])\r\n        except TrainingUser.DoesNotExist:\r\n            row_dict['traininguser'] = None\r\n        try:\r\n            row_dict['trainingclass'] = TrainingClass.objects.get(id=row[1])\r\n        except TrainingClass.DoesNotExist:\r\n            row_dict['trainingclass'] = None\r\n        try:\r\n            row_dict['trainingschedule'] = TrainingSchedule.objects.get(id=row[2])\r\n        except TrainingSchedule.DoesNotExist:\r\n            row_dict['trainingschedule'] = None\r\n\r\n        yield row_dict\r\n    return<\/pre>\n
ANOTHER UPDATE:<\/h3>\n
I posted this question on the Django Users group of Google Groups, and the very obvious answer was suggested – use the .raw() method of the manager<\/a>. \u00c2\u00a0This was added in Django 1.2, and I remember reading about it. \u00c2\u00a0The RawQuerySet returned by this method will contain objects – even though the SQL provided will only return columns from the row. \u00c2\u00a0Pretty slick.<\/p>\n
The reason I didn’t find this is because I’m still using version 1.0.4, so I limited my searches to the docs from that version. \u00c2\u00a0Yet one more reason to upgrade.<\/p>\n","protected":false},"excerpt":{"rendered":"
My application includes some existing tables from another, non-Django application. One of them does not have an atomic primary key, and I’m not in a position to change that. Therefore, I’m using one raw SQL to access the data.   Where I run into trouble is that the query returns data in columns, but not … <\/p>\n
Continue reading “Looking for Best Practice for Raw SQL”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-199","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/dashdrum.com\/blog\/wp-json\/wp\/v2\/posts\/199","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dashdrum.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dashdrum.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dashdrum.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dashdrum.com\/blog\/wp-json\/wp\/v2\/comments?post=199"}],"version-history":[{"count":11,"href":"https:\/\/dashdrum.com\/blog\/wp-json\/wp\/v2\/posts\/199\/revisions"}],"predecessor-version":[{"id":344,"href":"https:\/\/dashdrum.com\/blog\/wp-json\/wp\/v2\/posts\/199\/revisions\/344"}],"wp:attachment":[{"href":"https:\/\/dashdrum.com\/blog\/wp-json\/wp\/v2\/media?parent=199"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dashdrum.com\/blog\/wp-json\/wp\/v2\/categories?post=199"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dashdrum.com\/blog\/wp-json\/wp\/v2\/tags?post=199"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}