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 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.
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.
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.
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.
Here’s how I’m doing it now:
def available_classes_query(username=None): """Run a simple query and produce a generator that returns the results as a bunch of dictionaries with keys for the column values selected. """ from itertools import izip from django.db import connection query_string = """select tu.id as traininguser, cl.id as trainingclass, ts.id as trainingschedule -- big chunk of SQL cut for readability --""" cursor = connection.cursor() cursor.execute(query_string, [username]) ## parameter for query col_names = [desc for desc in cursor.description] while True: row = cursor.fetchone() if row is None: break row_dict = dict(izip(col_names, row)) try: row_dict['traininguser'] = TrainingUser.objects.get(id=row) except TrainingUser.DoesNotExist: row_dict['traininguser'] = None try: row_dict['trainingclass'] = TrainingClass.objects.get(id=row) except TrainingClass.DoesNotExist: row_dict['trainingclass'] = None try: row_dict['trainingschedule'] = TrainingSchedule.objects.get(id=row) except TrainingSchedule.DoesNotExist: row_dict['trainingschedule'] = None yield row_dict return
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. Â This was added in Django 1.2, and I remember reading about it. Â The RawQuerySet returned by this method will contain objects – even though the SQL provided will only return columns from the row. Â Pretty slick.
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. Â Yet one more reason to upgrade.