Hi ,
Did you try python psycopg2.
A simple deomostration.
Ex:-
import psycopg2
conn=psycopg2.connect("dbname='xxx' user='xxx' host='localhost'
password='xxx'")
cur = conn.cursor()
query="""SELECT * FROM bookmarkmanager_bookmark b '
'LEFT JOIN bookmarkmanager_bookmark_tags bt ON (b.id =
bt.bookmark_id) '
'LEFT JOIN bookmarkmanager_tag t ON (bt.tag_id = t.id
);"""
cur.execute(query)
result=cur.fetchall()
you can iterate over result list for data-processing and getting the
requires resultset.
-Siddharth
On Sun, Apr 28, 2013 at 4:06 AM, Marc R <[email protected]> wrote:
> did you use Bookmark.objetcs.all().select_related() ?
>
> As this will create a join query... at least for your model you show in
> the message.... I have found that for very complex joins, you need to use
> raw query and not a Model.
>
> On Thursday, April 25, 2013 10:44:28 AM UTC-4, Matthieu Bouron wrote:
>>
>> On Thursday, April 11, 2013 5:39:57 PM UTC+2, Tom Evans wrote:
>>>
>>> On Thu, Apr 11, 2013 at 3:42 PM, Matthieu Bouron
>>> <[email protected]> wrote:
>>> > Hello,
>>> >
>>> > Is there a way to handle many-to-many relationship with raw sql
>>> queries ?
>>> > I have the following model:
>>> >
>>> > from django.db import models
>>> >
>>> > class Tag(models.Model):
>>> > name = models.CharField(max_length=**512, unique=True)
>>> >
>>> > class Bookmark(models.Model):
>>> > link = models.CharField(max_length=**512)
>>> > title = models.CharField(max_length=**512)
>>> > tags = models.ManyToManyField(Tag)
>>> > added_at = models.DateField()
>>> >
>>> > Using Bookmark.objetcs.all() will result in a subquery for each row of
>>> the
>>> > bookmark table which is not acceptable for performance reasons.
>>> > First question is there a way to fetch all the content with a single
>>> query ?
>>> > Second question is it possible to use raw sql queries. I tried the
>>> > following:
>>> >
>>> > bookmarks = Bookmark.objects.raw(
>>> > 'SELECT * FROM bookmarkmanager_bookmark b '
>>> > 'LEFT JOIN bookmarkmanager_bookmark_tags bt ON (
>>> b.id =
>>> > bt.bookmark_id) '
>>> > 'LEFT JOIN bookmarkmanager_tag t ON (bt.tag_id =
>>> t.id)'
>>> > )
>>> >
>>> > But when i tried to access the tags attribute on a bookmark i get the
>>> > following exception:
>>> >
>>> > ValueError: "<Bookmark: Bookmark object>" needs to have a value for
>>> field
>>> > "bookmark" before this many-to-many relationship can be used.
>>> >
>>> > Thanks in advance,
>>> > Matthieu
>>> >
>>>
>>> Are you tied to raw SQL queries? Django itself supports traversing M2M
>>> relationships in a sane manner if you tell it to do so:
>>>
>>> https://docs.djangoproject.**com/en/1.5/ref/models/**
>>> querysets/#prefetch-related<https://docs.djangoproject.com/en/1.5/ref/models/querysets/#prefetch-related>
>>>
>>
>> Thanks.
>> Another problem remains ... performances are horrible even if i use
>> values().
>> I have 1000 rows in base, executing from a script (db sqlite):
>> - 800ms to fetch all the objects (traversing m2m with prefetch_related).
>> - 200ms to fetch all the row from bookmark table without traversing the
>> m2m relationship).
>>
>> Are my results expected ?
>> Are there any plan on improving django orm performance ?
>>
>> Matthieu
>>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/django-users?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
