[sqlalchemy] Re: sqlalchemy and postgresql warning messages
Michael Bayer ha scritto: On Nov 16, 2007, at 1:54 PM, Manlio Perillo wrote: There is an error in the schema, b.id is of type String instead of type Integer. Unfortunately PostgreSQL does not raises an error, but just a warning. In fact I have found such a problem in one of my programs only after a pg_dump + pg_restore: WARNING: foreign key constraint b_id_fkey will require costly sequential scans DETAIL: Key columns id and id are of different types: text and integer. What's the best method to avoid these bugs? It would be nice to have something like `salint`, that can scan a metadata searching for problems. Also, it would help if PostgreSQL warnings messages can be reported by psycopg/SQLAlchemy. Better if warnings can be considered like errors, thus raising an exception (something like the Werror option in GCC). if the warning is issued by Psycopg2, you can turn warnings into exceptions using the warnings filter, http://www.python.org/doc/lib/warning-filter.html . If its not, then SA can't do anything about it, you'd have to post on the psycopg2 list for this behavior to be supported. I have asked on the psycopg2 list. psycopg2 connection has a notices attribute. try: conn = db.connect() metadata.create_all(bind=conn) print conn.connection.connection.notices finally: metadata.drop_all() ['NOTICE: CREATE TABLE will create implicit sequence a_id_seq for serial column a.id\n', 'NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index a_pkey for table a\n', 'NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index b_pkey for table b\n', 'WARNING: foreign key constraint b_id_fkey will require costly sequential scans\nDETAIL: Key columns id and id are of different types: text and integer.\n'] So SQLAlchemy can process it, if this is reasonable. Manlio Perillo --~--~-~--~~~---~--~~ 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] filter_by VS python properties/descriptors VS composite properties
Hi people, I have some classes with standard python properties which target another python object and also uses several columns in the database. I also got a global factory function to create an instance of that target object out of the value of the columns (the class of that target object can vary). Now, I'd like to use those properties in filter criteria, as so: session.query(MyClass).filter(MyClass.my_prop == value)... session.query(MyClass).filter_by(my_prop_name=value)... I've tried using composite properties for that (passing the factory function instead of a composite class), and it actually works, but I'm a little nervous about it: can I have some bad side effect provided that in *some* cases (but not always) the target object is loaded from the database. I also dislike the fact I have to provide a __composite_values__ on all the possible target classes, while in my case, I would prefer to put that logic on the property side. I'd prefer if I could provide a callable which'd take an instance and output the tuple of values, instead of the method. Would that be considered a valid use case for composite properties or am I abusing the system? I've also tried to simply change those properties to descriptors so that I can override __eq__ on the object returned by accessing the property on the class. This worked fine for filter. But I also want to be able to use filter_by. So, I'd wish that query(Class).filter_by(name=value) would be somehow equal to query(Class).filter(Class.name == value), but it's not. filter_by only accepts MapperProperties and not my custom property. So what do people think? -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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: filter_by VS python properties/descriptors VS composite properties
On Tuesday 20 November 2007 11:37:29 Gaetan de Menten wrote: Hi people, I have some classes with standard python properties which target another python object and also uses several columns in the database. I also got a global factory function to create an instance of that target object out of the value of the columns (the class of that target object can vary). Now, I'd like to use those properties in filter criteria, as so: session.query(MyClass).filter(MyClass.my_prop == value)... session.query(MyClass).filter_by(my_prop_name=value)... I've tried using composite properties for that (passing the factory function instead of a composite class), and it actually works, but I'm a little nervous about it: can I have some bad side effect provided that in *some* cases (but not always) the target object is loaded from the database. I also dislike the fact I have to provide a __composite_values__ on all the possible target classes, while in my case, I would prefer to put that logic on the property side. I'd prefer if I could provide a callable which'd take an instance and output the tuple of values, instead of the method. Would that be considered a valid use case for composite properties or am I abusing the system? I've also tried to simply change those properties to descriptors so that I can override __eq__ on the object returned by accessing the property on the class. This worked fine for filter. But I also want to be able to use filter_by. So, I'd wish that query(Class).filter_by(name=value) would be somehow equal to query(Class).filter(Class.name == value), but it's not. filter_by only accepts MapperProperties and not my custom property. what would query(Class).filter(Class.name == value) mean in the case of plain property? the Class.name == value would selfconvert to SA.expression? then what if the filter_by(**kargs) is simply doing _and( getattr( Class, k) == v for k,v in kargs,items() ) i dont understand the _composite bit, why u have to bother with that? --~--~-~--~~~---~--~~ 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] Merge Questions
I have some questions about pickling/merging objects. I have written example code to demonstrate: http://pastie.caboo.se/120146 Kindest regards, Koen Bok --~--~-~--~~~---~--~~ 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: filter_by VS python properties/descriptors VS composite properties
I think it's a good idea, if Mike agrees then I will submit a patch to do this later today. (Except MyClass.my_prop and my_prop_name won't be equiv, you'll have to have whatever your property returns support __eq__) On Nov 20, 4:37 am, Gaetan de Menten [EMAIL PROTECTED] wrote: Hi people, I have some classes with standard python properties which target another python object and also uses several columns in the database. I also got a global factory function to create an instance of that target object out of the value of the columns (the class of that target object can vary). Now, I'd like to use those properties in filter criteria, as so: session.query(MyClass).filter(MyClass.my_prop == value)... session.query(MyClass).filter_by(my_prop_name=value)... I've tried using composite properties for that (passing the factory function instead of a composite class), and it actually works, but I'm a little nervous about it: can I have some bad side effect provided that in *some* cases (but not always) the target object is loaded from the database. I also dislike the fact I have to provide a __composite_values__ on all the possible target classes, while in my case, I would prefer to put that logic on the property side. I'd prefer if I could provide a callable which'd take an instance and output the tuple of values, instead of the method. Would that be considered a valid use case for composite properties or am I abusing the system? I've also tried to simply change those properties to descriptors so that I can override __eq__ on the object returned by accessing the property on the class. This worked fine for filter. But I also want to be able to use filter_by. So, I'd wish that query(Class).filter_by(name=value) would be somehow equal to query(Class).filter(Class.name == value), but it's not. filter_by only accepts MapperProperties and not my custom property. So what do people think? -- Gaëtan de Mentenhttp://openhex.org --~--~-~--~~~---~--~~ 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: filter_by VS python properties/descriptors VS composite properties
On Nov 20, 2007 3:12 PM, Chris M [EMAIL PROTECTED] wrote: I think it's a good idea, if Mike agrees then I will submit a patch to do this later today. (Except MyClass.my_prop and my_prop_name won't be equiv, I never said that's what I wanted. Notice that in my example, i speak about filter and filter_by. you'll have to have whatever your property returns support __eq__) Of course. On Nov 20, 4:37 am, Gaetan de Menten [EMAIL PROTECTED] wrote: Hi people, I have some classes with standard python properties which target another python object and also uses several columns in the database. I also got a global factory function to create an instance of that target object out of the value of the columns (the class of that target object can vary). Now, I'd like to use those properties in filter criteria, as so: session.query(MyClass).filter(MyClass.my_prop == value)... session.query(MyClass).filter_by(my_prop_name=value)... I've tried using composite properties for that (passing the factory function instead of a composite class), and it actually works, but I'm a little nervous about it: can I have some bad side effect provided that in *some* cases (but not always) the target object is loaded from the database. I also dislike the fact I have to provide a __composite_values__ on all the possible target classes, while in my case, I would prefer to put that logic on the property side. I'd prefer if I could provide a callable which'd take an instance and output the tuple of values, instead of the method. Would that be considered a valid use case for composite properties or am I abusing the system? I've also tried to simply change those properties to descriptors so that I can override __eq__ on the object returned by accessing the property on the class. This worked fine for filter. But I also want to be able to use filter_by. So, I'd wish that query(Class).filter_by(name=value) would be somehow equal to query(Class).filter(Class.name == value), but it's not. filter_by only accepts MapperProperties and not my custom property. So what do people think? -- Gaëtan de Mentenhttp://openhex.org -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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: filter_by VS python properties/descriptors VS composite properties
On Nov 20, 2007 11:51 AM, svilen [EMAIL PROTECTED] wrote: On Tuesday 20 November 2007 11:37:29 Gaetan de Menten wrote: Hi people, I have some classes with standard python properties which target another python object and also uses several columns in the database. I also got a global factory function to create an instance of that target object out of the value of the columns (the class of that target object can vary). Now, I'd like to use those properties in filter criteria, as so: session.query(MyClass).filter(MyClass.my_prop == value)... session.query(MyClass).filter_by(my_prop_name=value)... I've tried using composite properties for that (passing the factory function instead of a composite class), and it actually works, but I'm a little nervous about it: can I have some bad side effect provided that in *some* cases (but not always) the target object is loaded from the database. I also dislike the fact I have to provide a __composite_values__ on all the possible target classes, while in my case, I would prefer to put that logic on the property side. I'd prefer if I could provide a callable which'd take an instance and output the tuple of values, instead of the method. Would that be considered a valid use case for composite properties or am I abusing the system? I've also tried to simply change those properties to descriptors so that I can override __eq__ on the object returned by accessing the property on the class. This worked fine for filter. But I also want to be able to use filter_by. So, I'd wish that query(Class).filter_by(name=value) would be somehow equal to query(Class).filter(Class.name == value), but it's not. filter_by only accepts MapperProperties and not my custom property. what would query(Class).filter(Class.name == value) mean in the case of plain property? Nothing. But in the case of a descriptor, you can do whatever you want with the class property (as opposed to with a standard property(fget, fset) where you are can only take care of instance values). the Class.name == value would selfconvert to SA.expression? Indeed. That's very easy to do though. then what if the filter_by(**kargs) is simply doing _and( getattr( Class, k) == v for k,v in kargs,items() ) That is exactly what I would need in this case BUT I'm not sure it's a good idea to do it, because I'm not sure it's possible to get that behavior alongside with the joinpoint thing (which is a good thing IMO). And it would certainly break other stuff too, so that's probably not worth the trouble if there is no way to get that behavior in addition to what already exist. i dont understand the _composite bit, why u have to bother with that? Because, that's another way to do what I want, which actually works (contrary to the python property solution which doesn't work in the filter_by scenario) but I'm not sure it's the right way to go. -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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: filter_by VS python properties/descriptors VS composite properties
I actually deleted my post on further consideration of this problem, I guess it lagged a bit so you got to it :) If this was implemented I'd rather it work with a new MapperProperty that takes the name of the attribute on the class to access instead of automatically detecting which attributes on the class were descriptors and which aren't (obviously, you don't want to be able to do stupid stuff like .filter_by(_private_variable_here=item). I don't currently have time to submit a patch for that kind of behavior. On Nov 20, 9:54 am, Gaetan de Menten [EMAIL PROTECTED] wrote: On Nov 20, 2007 3:12 PM, Chris M [EMAIL PROTECTED] wrote: I think it's a good idea, if Mike agrees then I will submit a patch to do this later today. (Except MyClass.my_prop and my_prop_name won't be equiv, I never said that's what I wanted. Notice that in my example, i speak about filter and filter_by. you'll have to have whatever your property returns support __eq__) Of course. On Nov 20, 4:37 am, Gaetan de Menten [EMAIL PROTECTED] wrote: Hi people, I have some classes with standard python properties which target another python object and also uses several columns in the database. I also got a global factory function to create an instance of that target object out of the value of the columns (the class of that target object can vary). Now, I'd like to use those properties in filter criteria, as so: session.query(MyClass).filter(MyClass.my_prop == value)... session.query(MyClass).filter_by(my_prop_name=value)... I've tried using composite properties for that (passing the factory function instead of a composite class), and it actually works, but I'm a little nervous about it: can I have some bad side effect provided that in *some* cases (but not always) the target object is loaded from the database. I also dislike the fact I have to provide a __composite_values__ on all the possible target classes, while in my case, I would prefer to put that logic on the property side. I'd prefer if I could provide a callable which'd take an instance and output the tuple of values, instead of the method. Would that be considered a valid use case for composite properties or am I abusing the system? I've also tried to simply change those properties to descriptors so that I can override __eq__ on the object returned by accessing the property on the class. This worked fine for filter. But I also want to be able to use filter_by. So, I'd wish that query(Class).filter_by(name=value) would be somehow equal to query(Class).filter(Class.name == value), but it's not. filter_by only accepts MapperProperties and not my custom property. So what do people think? -- Gaëtan de Mentenhttp://openhex.org -- Gaëtan de Mentenhttp://openhex.org --~--~-~--~~~---~--~~ 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 and postgresql warning messages
On Nov 20, 2007, at 4:35 AM, Manlio Perillo wrote: I have asked on the psycopg2 list. psycopg2 connection has a notices attribute. try: conn = db.connect() metadata.create_all(bind=conn) print conn.connection.connection.notices finally: metadata.drop_all() ['NOTICE: CREATE TABLE will create implicit sequence a_id_seq for serial column a.id\n', 'NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index a_pkey for table a\n', 'NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index b_pkey for table b\n', 'WARNING: foreign key constraint b_id_fkey will require costly sequential scans\nDETAIL: Key columns id and id are of different types: text and integer.\n'] So SQLAlchemy can process it, if this is reasonable. hmmm, thats interesting. it would have to be placed at the execute level, but of course we are only going to issue warnings, not exceptions. I think this might also be better as an optional flag for the PG dialect. create a ticket in trac else I'll lose track of this one --~--~-~--~~~---~--~~ 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: filter_by VS python properties/descriptors VS composite properties
On Nov 20, 2007, at 4:37 AM, Gaetan de Menten wrote: Hi people, I have some classes with standard python properties which target another python object and also uses several columns in the database. I also got a global factory function to create an instance of that target object out of the value of the columns (the class of that target object can vary). Now, I'd like to use those properties in filter criteria, as so: session.query(MyClass).filter(MyClass.my_prop == value)... session.query(MyClass).filter_by(my_prop_name=value)... I've tried using composite properties for that (passing the factory function instead of a composite class), and it actually works, but I'm a little nervous about it: can I have some bad side effect provided that in *some* cases (but not always) the target object is loaded from the database. I also dislike the fact I have to provide a __composite_values__ on all the possible target classes, while in my case, I would prefer to put that logic on the property side. I'd prefer if I could provide a callable which'd take an instance and output the tuple of values, instead of the method. Would that be considered a valid use case for composite properties or am I abusing the system? im not totally understanding the all possible target classes concept here, you're creating classes on the fly ?if you are in fact creating new classes on the fly, i dont see whats so hard about tacking on a __composite_values__ to each one as well (i.e. dont you have a single function that is doing the generation ?) I dont understand what put that logic on the property side means otherwise. If you can show me some example code that might help, but I think you probably are abusing the system here and the MapperProperty idea that Chris mentioned is probably the way to go. I've also tried to simply change those properties to descriptors so that I can override __eq__ on the object returned by accessing the property on the class. This worked fine for filter. But I also want to be able to use filter_by. So, I'd wish that query(Class).filter_by(name=value) would be somehow equal to query(Class).filter(Class.name == value), but it's not. filter_by only accepts MapperProperties and not my custom property. Well the general case of filter_by() and filter() awareness is accomplished via synonym(). the filter() part for synonym() doesnt work yet but we have ticket #801 to address that. this ticket will take the existing Python property and put an InstrumentedAttribute wrapper around it. But in this case you aren't making synonym(), you pretty much want a synonym() that doesn't target any particular column. Maybe we could expand ticket #801 to include that, or maybe make some new ticket for this. We'd just have another mapper function thats like synonym() but doesnt actually target a column, it just adds that name to the list of properties. But also, the target property, or something else, would have to provide the MapperProperty interface it as well, like Chris says. So a new kind of MapperPropery would be needed here. --~--~-~--~~~---~--~~ 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: filter_by VS python properties/descriptors VS composite properties
On Nov 20, 2007 5:23 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Nov 20, 2007, at 4:37 AM, Gaetan de Menten wrote: I have some classes with standard python properties which target another python object and also uses several columns in the database. I also got a global factory function to create an instance of that target object out of the value of the columns (the class of that target object can vary). Now, I'd like to use those properties in filter criteria, as so: session.query(MyClass).filter(MyClass.my_prop == value)... session.query(MyClass).filter_by(my_prop_name=value)... I've tried using composite properties for that (passing the factory function instead of a composite class), and it actually works, but I'm a little nervous about it: can I have some bad side effect provided that in *some* cases (but not always) the target object is loaded from the database. I also dislike the fact I have to provide a __composite_values__ on all the possible target classes, while in my case, I would prefer to put that logic on the property side. I'd prefer if I could provide a callable which'd take an instance and output the tuple of values, instead of the method. Would that be considered a valid use case for composite properties or am I abusing the system? im not totally understanding the all possible target classes concept here, you're creating classes on the fly ? No, but my property returns object of different classes. Let's call it a polymorphic property. if you are in fact creating new classes on the fly, i dont see whats so hard about tacking on a __composite_values__ to each one as well (i.e. dont you have a single function that is doing the generation ?) I dont understand what put that logic on the property side means otherwise. It means that my property is handled by a class and I'd rather put the __composite_values__ function in that class, than in my several target classes (some of them have nothing to do with the database, so it seems awkward to add a method for that). If you can show me some example code that might help, but I think you probably are abusing the system here and the MapperProperty idea that Chris mentioned is probably the way to go. Yeah, that seemed like an elegant way. I've also tried to simply change those properties to descriptors so that I can override __eq__ on the object returned by accessing the property on the class. This worked fine for filter. But I also want to be able to use filter_by. So, I'd wish that query(Class).filter_by(name=value) would be somehow equal to query(Class).filter(Class.name == value), but it's not. filter_by only accepts MapperProperties and not my custom property. Well the general case of filter_by() and filter() awareness is accomplished via synonym(). the filter() part for synonym() doesnt work yet but we have ticket #801 to address that. this ticket will take the existing Python property and put an InstrumentedAttribute wrapper around it. But in this case you aren't making synonym(), you pretty much want a synonym() that doesn't target any particular column. Indeed. Maybe we could expand ticket #801 to include that, or maybe make some new ticket for this. We'd just have another mapper function thats like synonym() but doesnt actually target a column, it just adds that name to the list of properties. But also, the target property, or something else, would have to provide the MapperProperty interface it as well, like Chris says. So a new kind of MapperPropery would be needed here. Done in ticket #875. Thanks for your reply. -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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: Merge Questions
Aight, thanks for the explanation! Koen On Nov 20, 5:02 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Nov 20, 2007, at 6:19 AM, Koen Bok wrote: I have some questions about pickling/merging objects. I have written example code to demonstrate:http://pastie.caboo.se/120146 Kindest regards, OK ive rewritten that error message in r3809 since it made no sense. now it will say: Could not update instance '[EMAIL PROTECTED]', identity key (class '__main__.User', (1,), None); a different instance with the same identity key already exists in this session. the error is that you need to use the return value of merge: user1 = Session.merge(user1, dont_load=True) as for the dirty list, i think we might need to put a more friendly dirty accessor on there (or a note in the docs)...the merge process sets attributes in the normal way so that things like backrefs fire off, but a side effect is that its flipping on the modified flag on every object. the modified flag is just a cue to add the object to the flush process, but if nothing actually changed on it then it wont be updated. A more expensive check, i.e. comparing the attribute values on the instances, would reveal that they arent dirty after all, and this is the check that happens during flush. we cant *really* preserve the modified flag here from merged to already-present, since if A1.foo == ed, and A2.foo == jack, both have no modified flag, but then you merge A2 on top of A1, A1 *is* actually modified. --~--~-~--~~~---~--~~ 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] Sum with Grouping
I'm very new to sqlalchemy and I'm still trying to wrap my head around how it works. I have a table with columns: type, amount. I want to sum the amounts grouped by type. In SQL I would write: SELECT sum(amount), type from purchases group by type; How do I do this with SQLAlchemy? This is what I have so far, but I don't really understand what's going on: pq = Session.query(Purchase).apply_sum(Purchase.amount) for x in pq: ret += 'br' + str(x.type) + str(x.amount) # This prints out every item in the db... the sum seems to not have done anything bytypes = pq.group_by(Purchase.type) for x in bytypes: ret += 'br' + str(x.type) + str(x.amount) # This prints out one of each type, but the amount is not the sum of all the types, it's just the last one of each type bytypes = bytypes.sum(Purchase.amount) # This is the sum of everything. --~--~-~--~~~---~--~~ 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: Sum with Grouping
the aggregate methods on Query, such as apply_sum(), apply_avg(), etc., are not in such great shape right now...they've been neglected and in fact aren't even working correctly with GROUP BY, etc...I've added trac ticket #876 for this. If you know the exact SQL and columns you'd like to get back, typically its best just to issue that query without using an ORM construct. your options for this are either just text: engine.execute(SELECT sum(amount), type from purchases group by type).fetchall() or the expression construct would look like: engine.execute(select([func.sum(Purchase.amount), Purchase.type]).group_by(Purchase.type)).fetchall() one important thing to be aware of is that SA has two distinct levels of SQL API; the ORM, which deals with Sessions and Query objects, and the SQL Expression Language, which deals with select(), update(), etc. the expression language provides more direct access to SQL constructs. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---