On 6/21/07, SamDonaldson <[EMAIL PROTECTED]> wrote:
>
> Hello,
>
> I ran into a complex bug where I found the following: I wrote a query
> in my model.py where I return a list of tuples that sqlalchemy returns
> to me, and then, to convert those tuples into objects, I wrap my
> sqlalchemy class (representing the table) around the tuple to create a
> list of instances. I then return that back to the controller action.
> Now note, this was a read from the db of rows that I want to display.
> The problem occurrs when turbogears thinks these are new objects and
> tries to commit them or flush them at the time of returning from the
> controller action. Why does this happen? What if I don't want this
> behavior? This gets fixed if I do session.clear() but I dont 'want to
> have to do that. I want complete control of when things get commited
> by explicitly calling session.flush() and nothing automatic.
>
I'm not sure why you would do this. If you have objects with large
fields in the db then just set the fields that you don't want to see
as deferred columns[1].
> Now that I'm on this topic. I was wondering - what's the sqlalchemy
> function to convert a list of tuples that have been returned from the
> db back into instances? If there is one, can it handle the case where
> I select only a few columns and then conver that into an instance?
>
To do this I think you may need more than the tuple, but you can do a
standard select and then convert those results into objects. Here is
some fairly old code of mine that searched using MySQL's Full Text
Search capabilities and then coerced the results into Review objects:
search_count_query = select([func.count(reviews.c.id)],
"MATCH %s AGAINST (:q)" % search_fields)
search_query = select([reviews,
(func.sum(review_ratings.c.rating)/func.count(review_ratings.c.id).label("avg")),
"MATCH %s AGAINST (:q) AS score" %
search_fields ],
"MATCH %s AGAINST (:q)" % search_fields,
from_obj=[reviews.outerjoin(review_ratings,
reviews.c.id==review_ratings.c.review_id)],
order_by=[desc("avg"), desc("score")],
group_by=[reviews.c.id])
search_hits = search_count_query.scalar(q=q)
results = search_query.execute(q=q)
return session.query(Review).instances(results), search_hits
As you can see, the queries can be as complex as you like, but I've
retrieved every column from the review table. You may need to play
around with what data is required to be retrieved to be able to use
.instances().
Hope this helps.
Lee
[1]:
http://www.sqlalchemy.org/docs/adv_datamapping.html#advdatamapping_properties_deferred
--
Lee McFadden
blog: http://www.splee.co.uk
work: http://fireflisystems.com
skype: fireflisystems
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" group.
To post to this group, send email to turbogears@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
