[sqlalchemy] Re: Aggregate function of connected items as a property?
My problem is: I want to be able to select from Thread, ordering it by descending order of the maximum tn_ctime for each thread, to find the most recently referenced threads. Which is to say, I want to do something like select t.*, coalesce(c.most_recent_child, t.tn_ctime) as last_upd from tnode t left join (select tn_parent as node_id, max(tn_ctime) as most_recent_child from tnode group by tn_parent) c on c.node_id==t.tn_id group by t.tn_id order by last_upd desc; Is it possible to add a property to Thread holding the maximum child node timestamp if any (or the thread node's timestamp, if none), so that I can do something like Thread.select(Thread.c.tn_parent==forum_id, order_by=desc(Thread.c.last_upd)) ? i dont think i got what u want from these examples, but adding a property that always fires a select is easy: class Thread: ... @property def mymaxprop( self): return self.select(...) or whatever eventualy u can put some cache, but you're responsible for keeping it uptodate Another way would be to play with relation, maybe it can do something like that too; And another way is to look here: http://www.mr-pc.kiev.ua/en/projects/SQLAlchemyAggregator/ or another ~copy (with v0.3 support) here https://dbcook.svn.sourceforge.net/svnroot/dbcook/trunk/dbcook/misc/aggregator/ now after rereading your stuff i'm even less sure if what i say is relevant. svilen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] _state and related
before v3463, _state was a property and was setup on the fly whenever used. now its being setup into the __init__-replacement of the object. Thus, with the property it was possible as side-effect to have an object instance _before_ having any sqlalchemy around, then declare/build mappers/ whatever, and then save /use that instance as db-persistent. Now this wont work, as the instance has no _state attribute, and noone to set it up. i guess this usage case - of instances having wider lifetime than orm-mapping itself - is rare. i use it for tests, running many db- tests over same instances. So i'll probably put a check in my save() method to setup that missing ._state. Not sure about the mapext.init_instance(), and why's that is called before the original oldinit, and is given that oldinit as argument. Anyway it would be nice if these lifetime-related expectations/limitations are documented somewhere. Another one is the ._instance_key that stays on the instance after orm is gone (the ._state will also stay). ciao svilen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy with turbogears and assign_mapper: group_by
On 9/8/07, Lukasz Szybalski [EMAIL PROTECTED] wrote: On 9/7/07, Paul Johnston [EMAIL PROTECTED] wrote: Hi, And if I wanted to select a year and group by year? select User.Year from User group by User.Year db.execute(select([User.Year]) ??? Have a look at http://www.sqlalchemy.org/docs/04/sqlexpression.html Ok. Based on documentation. I do: import sqlalchemy s2=sqlalchemy.select([User.c.YEAR]) s3=s2.execute() Got all year fields. 1995,1995,1995,1996,1996.. Now I want to group so I get just one. s2=sqlalchemy.select([User.c.YEAR]).group_by(User.c.YEAR) But when I execute, I get: s3=s2.execute() Traceback (most recent call last): File console, line 1, in ? AttributeError: 'NoneType' object has no attribute 'execute' In docs they use conn.execute(s2) Is this a different execute that is being called from somewhere else? I didn't read this thread from the beginning, but if you are using TG, probably you are using SA = 0.3.10, which means you don't have generative select() constructs... So you'll need to modify your code to (not tested): s2=sqlalchemy.select([User.c.YEAR], group_by=[User.c.YEAR]) s3=s2.execute() Cheers, Roger --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: add condition over a classmethod
somethings' missing here.. whats the link classmethod - select - etc? do explain again/more... u mean the classmethod generates the filter-expression? whats the difference classmethod vs plainmethod here? all the same, just call it: self.myclassmethod(..) On Monday 10 September 2007 14:40:57 Glauco wrote: Yes this is strange.. but i want (if is possible) to implement in select process, a condition that is evaluated as a result of classmethod. In my case i must select all person from my database that are inside a geografical circle with coordinate = x,y... and radius = z. i must use a callable (wich is called for each item) because the inside or outside is calculated for each item. i cannot iterate over result of the qry, because after this particular condition i must add more other filter condition . My workflow data is something like my_base_qry = select . if condition1: my_base_qry = my_base_qry.filter( ...) if coordinate: my_base_qry = my_base_qry.filter( my_method_inside_circle ( latitude, longitude,radius) ) if condition3: my_base_qry = my_base_qry.filter( ...) if condition_n: my_base_qry = my_base_qry.filter( ...) sorry for my poor english any idea? Glauco --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Changeset 2642's fix is in the wrong place [firebird]
Michael, On 9/10/07, Michael Bayer [EMAIL PROTECTED] wrote: hey Roger - can you reopen ticket #570 and attach your patch there ? FTR, the 0.4 codebase does this differently and is probably correct over there (but also, not tested since I dont have FB). I'll do that tonight... and will test it against 0.4 too... []s Roger --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: backref relation is None instead of list?
On 9/9/07, Michael Bayer [EMAIL PROTECTED] wrote: I cant reproduce this, although the error to me seems like you are actually saying c._parent = [] (some list object). If thats not it, send along a reproducing test script. I tried to recreate it in the shell and failed. I then loaded my application, changed _parents to parent and it just worked. I don't think the change did it, I think it just disappeared (maybe the reboot?) If it happens again I'll post it on this mailing list. Thanks for the tip about one-to-many relations, I was hoping they worked like that, but when I got the errors I assumed it must not. Sorry for the false alarm, -Dan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Changeset 2642's fix is in the wrong place [firebird] [PATCHES]
Michael
On 9/10/07, Michael Bayer [EMAIL PROTECTED] wrote:
hey Roger -
can you reopen ticket #570 and attach your patch there ? FTR, the
0.4 codebase does this differently and is probably correct over
there (but also, not tested since I dont have FB).
I guess you meant #370.. :)
Well, it is still opened... I tried to upload the patches, but found
no option to do this operation on an existing ticket (only for new
tickets)... I logged as guest/guest, since there's also no option to
register myself as a new user.
So, here are the 2 patches and a test script... (hope the list accept
attachments).
Cheers,
Roger
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
from sqlalchemy import *
from sqlalchemy.orm import *
dburi = firebird://SYSDBA:[EMAIL PROTECTED]//tmp/test.fdb
engine = create_engine(dburi)
metadata = MetaData()
conn = engine.connect()
users_table = Table('T_USER', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(20)))
prefs_table = Table('T_PREFS', metadata,
Column('id', Integer, primary_key=True),
Column('user_id', Integer, ForeignKey('T_USER.id')),
Column('name', String(20)))
class User(object): pass
class UserPref(object): pass
mapper(User, users_table, properties = dict(
preferences = relation(UserPref, cascade=all, delete-orphan),
))
mapper(UserPref, prefs_table)
metadata.bind = engine
session = create_session(bind=engine)
users_table.create(checkfirst=True)
prefs_table.create(checkfirst=True)
# delete existing data
prefs_table.delete().execute()
users_table.delete().execute()
# populate initial data
users_table.insert().execute(dict(id=1, name='Smith'), dict(id=2, name='Mark'))
prefs_table.insert().execute(
dict(id=1, user_id=1, name='Smith-1'),
dict(id=2, user_id=1, name='Smith-2'),
dict(id=3, user_id=1, name='Smith-3'),
dict(id=4, user_id=2, name='Mark-1'),
dict(id=5, user_id=2, name='Mark-2')
)
assert select([func.count(users_table.c.id)]).scalar() == 2
assert select([func.count(prefs_table.c.id)]).scalar() == 5
u = session.query(User).get(1)
session.delete(u)
session.flush()
assert select([func.count(users_table.c.id)]).scalar() == 1
assert select([func.count(prefs_table.c.id)]).scalar() == 2
u = session.query(User).get(2)
u.preferences = []
session.flush()
assert select([func.count(users_table.c.id)]).scalar() == 1
assert select([func.count(prefs_table.c.id)]).scalar() == 0
370_branch_0.3.patch
Description: Binary data
370_branch_0.4.patch
Description: Binary data
