Looking for Best Practice for Raw SQL

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.

Thanks!!

UPDATE:

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[0] 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[0])
        except TrainingUser.DoesNotExist:
            row_dict['traininguser'] = None
        try:
            row_dict['trainingclass'] = TrainingClass.objects.get(id=row[1])
        except TrainingClass.DoesNotExist:
            row_dict['trainingclass'] = None
        try:
            row_dict['trainingschedule'] = TrainingSchedule.objects.get(id=row[2])
        except TrainingSchedule.DoesNotExist:
            row_dict['trainingschedule'] = None

        yield row_dict
    return

ANOTHER UPDATE:

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.