Raw SQL and model instances

[Georg Göttlich]

Hello everybody,

this question has probably been ask quite often already, but I
couldn't find anything proper on it. So here it is:

Is there a way to feed the result cursor of a custom sql query back
into the ORM, thus recieving model instances, as if you'd use a
queryset?

Thanks a lot,
Georg Göttlich

[Daniel Roseman]

The easiest way is to grab the PK values via the SQL, then do an ORM
query for x.objects.filter(pk__in=pklist)
--
DR.

[Russell Keith-Magee]

Building an queryset isn't really possible, but if all you want is
model instances that are instantiated with data from your cursor, you
can instantiate model instances directly from row data. For example,
if your have an Author model, you could do something like the
following:

>>> cursor.execute('SELECT ...')
>>> row = cursor.fetchone()
>>> a = Author(*row)

At this point, a will be a fully populated Author instance,
indistinguishable from one retrieved using a queryset.

The caveat on this technique is that the SELECT you execute using the
cursor to obtain the row must contain all the columns in your model,
specified in the same order that your model defines them. This
includes the auto-added id column, if appropriate. If you're uncertain
which fields will be required, and in what order, Author._meta.fields
contains the list that Django will expect.

If you need to return multiple rows and produce multiple instances,
iterate over the returned rows and construct an instance for each row,
storing the result. The end product won't be a queryset, but it will
be an iterable collection of Django model instances.

Yours,
Russ Magee %-)

[Georg Göttlich]

Hello Russ,

thank you very much. This is exactly what I wanted to know.

Best regards,
Georg Göttlich

[Malcolm Tredinnick]

On Fri, 2008-11-28 at 19:54 +0900, Russell Keith-Magee wrote:
[...]

> >>> cursor.execute('SELECT ...')
> >>> row = cursor.fetchone()
> >>> a = Author(*row)
>
> At this point, a will be a fully populated Author instance,
> indistinguishable from one retrieved using a queryset.
>
> The caveat on this technique is that the SELECT you execute using the
> cursor to obtain the row must contain all the columns in your model,
> specified in the same order that your model defines them. This
> includes the auto-added id column, if appropriate. If you're uncertain
> which fields will be required, and in what order, Author._meta.fields
> contains the list that Django will expect.

If you only have a subset of the fields, or if they aren't in the
expected order, you can also supply the values as keyword argument
parameters to the model initialisation function. Zip up the values with
the names of the parameters they represent and pass that in:

fields = ('name', 'age', 'data')
results = []
for row in cursor.fetchall():
params = zip(fields, row)
results.append(Author(**params)

Regards,
Malcolm

[alex....@gmail.com]

Malcolm, I believe that should be params = dict(zip(fields, row))

On Nov 28, 8:34 pm, Malcolm Tredinnick <malc...@pointy-stick.com>
wrote: