Best Practice for Raw SQL

[Dan Gentry]

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 members of the group 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.

Thanks!!

[Jacob Kaplan-Moss]

On Mon, Apr 11, 2011 at 7:53 AM, Dan Gentry <d...@gentryville.net> wrote:
> 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.

You're looking for the `raw()` method: http://django.me/raw. It takes
a raw SQL query and yields models for you.

Jacob

[Sells, Fred]

In my case I had to read some legacy data from a different schema on the
same MySQL server and it was easy (but perhaps not elegant) to just
establish a separate connection using MySQLdb module.

Jacob

--
You received this message because you are subscribed to the Google
Groups "Django users" group.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to
django-users...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.

[Nick Arnett]

On Wed, Apr 13, 2011 at 1:38 PM, Sells, Fred <fred....@adventistcare.org> wrote:

In my case I had to read some legacy data from a different schema on the
same MySQL server and it was easy (but perhaps not elegant) to just
establish a separate connection using MySQLdb module.

You shouldn't have to do that.  Use this:

from django.db import connection, transaction

cursor = connection.cursor()

Then you can do anything you would with a MySQLdb cursor, e.g.:

cursor.execute("UPDATE foo SET bar = 3")

After any operation that changes data, apparently you should also call this:

transaction.commit_unless_managed()

I'm doing a lot of that right now to vastly speed up a huge data import.

Nick

[Sells, Fred]

Xcellent improvement; that’s (my code) what happens when you’re too busy fixing the problem to learn the tool ;)

 

From: django...@googlegroups.com [mailto:django...@googlegroups.com] On Behalf Of Nick Arnett
Sent: Wednesday, April 13, 2011 4:44 PM
To: django...@googlegroups.com
Subject: Re: Best Practice for Raw SQL

 

 

On Wed, Apr 13, 2011 at 1:38 PM, Sells, Fred <fred....@adventistcare.org> wrote:

--

[Dan Gentry]

Thanks. I'll give raw() a try.