Re: [sqlalchemy] Merge support with ConcreteInheritedProperty
Excellent. The 'pass' ConcreteInheritedProperty.merge() method works fine. Thanks again. On 8/1/2010 2:24 PM, Michael Bayer wrote: On Jul 31, 2010, at 7:41 AM, Kent wrote: When I call merge() on an ArTran object, the merge() method of a ConcreteInheritedProperty 'artransarchiveid' that *exists only on ArTranArchive* is being called. The attribute artransarchiveid also exists on your base mapper, since the base mapper is mapped to a polymorphic union of all the subclasses. The ConcreteInheritedProperty is placed on subclasses which don't have any way to map this attribute, i.e. all your subclasses that are on tables which don't have that column. This is one of the awkwardnesses to the current system of a concrete map with no real superclass. If the polymorphic union were generated only against columns that were common to all subclasses, you wouldn't have such attributes in the end result, but then each subclass would invoke a second SELECT to load its remaining attributes (I just tried this to confirm it is the case).If the mapper knew that only a subset of columns are common to subclasses, and that another bunch of attributes were only for loading, that could make this cleaner still by not mapping those extra attributes on the base, but it hasn't been worked out how that would function or be configured. ( As an aside, instead of getting a 'NotImplementedError' when ConcreteInheritedProperty.merge() is called, I am getting TypeError: merge() takes exactly 6 arguments (8 given) anyway a do-nothing merge() has been added to ConcreteInheritedProperty in r3b1895a3b736 which allows merge() to work with a concrete mapping. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Declarative syntax with column_property (v0.6.3)
Hi, I'm having trouble understanding the correct syntax to be used with Declarative and a column property. The select statement I'm using is: select([func.substr(my_table.c.my_string, 2, 3)]).label(my_substr'), deferred=True And (as per the docs using the expanded syntax, this works as expected. With the Declarative syntax, I've tried: from sqlalchemy.util import classproperty from sqlalchemy.orm..., column_property class MySubstr(object): @classproperty def my_substr(cls): return column_property( select([func.substr(cls.my_str, 2, 3)]).label('my_substr') ) class MyTable(Base, MySubstr): . and then expect to be able to call a record in MyTable and return its my_substr, however, all I'm getting is a representation of the object ... eg sqlalchemy.orm.properties.ColumnProperty object at 0xa4951cc and no apparent way of seeing its value. Should the @classproperty sit within the MyTable(Base) itself (ie is there a need for the separate object -- and have I been confused by the section on 'mixins' )? I'd be really grateful for any clues! Many thanks, Rob -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Returning an attribute from an object when using query_property()?
Hi all: I have the following code: class EventQuery(Query): def histogram(self): ''' return a histogram of source / count(source) given an eventquery'' ... ... class Event(Base): __tablename__ = 'events' query = Session.query_property(query_cls = EventQuery) name = Column(Unicode(128)) source = Column(BigInteger) ... Now, Event has 54 additional attributes, some of which are quite large. In EventQuery.histogram(), all I really need is the Event.source attribute. 'events' has over 150 million rows, and it's likely that EventQuery.histogram() will be called for all events. So - is there any way to 'modify' the query (self) in histogram() so that it only returns (Event.source, func.count(Event.source)), or, for the general case, to modify the query to return an attribute of the object? I've tried from_self, but that seems highly inefficient (at least 20x slower than Session.query(...) ). I'd also really like to keep histogram() as a method of EventQuery. I can see reasons why this would be impossible, but perhaps there's a way to do it. Thanks, S. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Returning an attribute from an object when using query_property()?
On Aug 2, 2010, at 12:41 PM, Zippy P wrote: Hi all: I have the following code: class EventQuery(Query): def histogram(self): ''' return a histogram of source / count(source) given an eventquery'' ... ... class Event(Base): __tablename__ = 'events' query = Session.query_property(query_cls = EventQuery) name = Column(Unicode(128)) source = Column(BigInteger) ... Now, Event has 54 additional attributes, some of which are quite large. In EventQuery.histogram(), all I really need is the Event.source attribute. 'events' has over 150 million rows, and it's likely that EventQuery.histogram() will be called for all events. So - is there any way to 'modify' the query (self) in histogram() so that it only returns (Event.source, func.count(Event.source)), or, for the general case, to modify the query to return an attribute of the object? I've tried from_self, but that seems highly inefficient (at least 20x slower than Session.query(...) ). I'd also really like to keep histogram() as a method of EventQuery. I can see reasons why this would be impossible, but perhaps there's a way to do it. you can use values(Event.source, func.count(...)), or just put a method on Event called histogram that uses the standard object_session(self).query(...) approach, I'm not really sure what the using the old query_property accessor gets you here Thanks, S. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Declarative syntax with column_property (v0.6.3)
On Aug 2, 2010, at 12:19 PM, Robert Sudwarts wrote: Hi, I'm having trouble understanding the correct syntax to be used with Declarative and a column property. The select statement I'm using is: select([func.substr(my_table.c.my_string, 2, 3)]).label(my_substr'), deferred=True And (as per the docs using the expanded syntax, this works as expected. With the Declarative syntax, I've tried: from sqlalchemy.util import classproperty from sqlalchemy.orm..., column_property class MySubstr(object): @classproperty def my_substr(cls): return column_property( select([func.substr(cls.my_str, 2, 3)]).label('my_substr') ) class MyTable(Base, MySubstr): . and then expect to be able to call a record in MyTable and return its my_substr, however, all I'm getting is a representation of the object ... eg sqlalchemy.orm.properties.ColumnProperty object at 0xa4951cc and no apparent way of seeing its value. I see nothing wrong with the example, the only potential glitch is that my_str needs to have a name assigned up front. You didn't illustrate that part here, so FYI it would be extremely helpful if you could attach fully working examples. In this case the example is: from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.util import classproperty from sqlalchemy.orm import column_property, sessionmaker Base = declarative_base() engine = create_engine('sqlite://', echo=True) class MySubstr(object): @classproperty def my_substr(cls): return column_property( select([func.substr(cls.my_str, 2, 3)]).label('my_substr') ) class MyTable(Base, MySubstr): __tablename__ = 'foo' id = Column(Integer, primary_key=True) my_str = Column('my_str', String) Base.metadata.create_all(engine) sess = sessionmaker(engine)() m1 = MyTable(my_str='some string') sess.add(m1) sess.commit() print m1.my_substr and it works fine. Should the @classproperty sit within the MyTable(Base) itself (ie is there a need for the separate object -- @classproperty is typically specific to the mixin use case, which is why its only discussed in the mixins section of the declarative documentation. It's a useful construct in other situations but you certainly don't need it if you aren't using mixins. and have I been confused by the section on 'mixins' )? I'm not sure why you're choosing to use a mixin here, as your column_property appears to be pretty specific to the MyTable class. if the documentation led you to believe that you needed one, I am extremely curious why that is the case as mixins are absolutely optional and that would be a serious documentation bug. Without the mixin, it is: class MyTable(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) my_str = Column('my_str', String) my_substr = column_property( select([func.substr(my_str, 2, 3)]).label('my_substr') ) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Returning an attribute from an object when using query_property()?
I have the following code: class EventQuery(Query): def histogram(self): ''' return a histogram of source / count(source) given an eventquery'' ... ... class Event(Base): __tablename__ = 'events' query = Session.query_property(query_cls = EventQuery) name = Column(Unicode(128)) source = Column(BigInteger) ... Now, Event has 54 additional attributes, some of which are quite large. In EventQuery.histogram(), all I really need is the Event.source attribute. 'events' has over 150 million rows, and it's likely that EventQuery.histogram() will be called for all events. So - is there any way to 'modify' the query (self) in histogram() so that it only returns (Event.source, func.count(Event.source)), or, for the general case, to modify the query to return an attribute of the object? I've tried from_self, but that seems highly inefficient (at least 20x slower than Session.query(...) ). I'd also really like to keep histogram() as a method of EventQuery. I can see reasons why this would be impossible, but perhaps there's a way to do it. you can use values(Event.source, func.count(...)), or just put a method on Event called histogram that uses the standard object_session(self).query(...) approach, I'm not really sure what the using the old query_property accessor gets you here Thanks, Michael. Two followup questions: 1) The histogram() method is designed to provide statistics on a set of Events (defined by a query), so it made sense (to me) to make it a method of EventQuery. I don't know how I'd do this as a method of Event. Could you clarify? 2) When you say 'old query property' accessor, are you implying that query_property is deprecated? Is there an alternative? I really like the idea of subclassing Query and adding custom methods. Is this a misguided approach? BTW: the values() generator takes at least as long as the from_self() does, and uses a huge amount of memory since all().values() still passes the entire object list to python. from_self() took 1920 seconds to run against the entire 'events' table, where Session.query(func.distinct(Event.source)).all() took 0.02 seconds. I'll try experimenting with joins next. Thanks, S. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Returning an attribute from an object when using query_property()?
On Aug 2, 2010, at 1:58 PM, SQLAlchemy User wrote: I ha Thanks, Michael. Two followup questions: 1) The histogram() method is designed to provide statistics on a set of Events (defined by a query), so it made sense (to me) to make it a method of EventQuery. I don't know how I'd do this as a method of Event. Could you clarify? Well if you need to say Event.generate query.generate query.histogram(), then yeah I see what you are going for, though you might say Event.histogram.generate query.generate query, that's probably not as appealing. 2) When you say 'old query property' accessor, are you implying that query_property is deprecated? Is there an alternative? I really like the idea of subclassing Query and adding custom methods. Is this a misguided approach? subclassing Query and using query_property aren't necessarily connected things. The idea of MyClass.query(foo) isn't deprecated, though it is kind of an old pattern back when we were trying to hide the Session as much as possible (or when there wasn't really even a Session). I tend towards a more coarse grained interface, I guess, i.e. I'd just have a coarse grained Event.histogram(x, y, z) type of method that works only for the necessary use cases. I don't have a strong case for that approach (I have a weak one which is, hide complexity, sort of). BTW: the values() generator takes at least as long as the from_self() does, and uses a huge amount of memory since all().values() still passes the entire object list to python. from_self() took 1920 seconds to run against the entire 'events' table, where Session.query(func.distinct(Event.source)).all() took 0.02 seconds. I'll try experimenting with joins next. values() should be using the least amount of memory of any approach - it only buffers 10 rows in memory at a time, and doesn't deal with session or object accounting at all so is also much more performant than all(). I dont know what all().values() is or if that's a typo. Session.query(Event).values(func.distinct(Event.source)) should emit a brief SELECT DISTINCT query and whip through a single column for the result. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Declarative syntax with column_property (v0.6.3)
On Aug 2, 2010, at 1:40 PM, Michael Bayer wrote: On Aug 2, 2010, at 12:19 PM, Robert Sudwarts wrote: Hi, I'm having trouble understanding the correct syntax to be used with Declarative and a column property. The select statement I'm using is: select([func.substr(my_table.c.my_string, 2, 3)]).label(my_substr'), deferred=True And (as per the docs using the expanded syntax, this works as expected. With the Declarative syntax, I've tried: from sqlalchemy.util import classproperty from sqlalchemy.orm..., column_property class MySubstr(object): @classproperty def my_substr(cls): return column_property( select([func.substr(cls.my_str, 2, 3)]).label('my_substr') ) class MyTable(Base, MySubstr): . and then expect to be able to call a record in MyTable and return its my_substr, however, all I'm getting is a representation of the object ... eg sqlalchemy.orm.properties.ColumnProperty object at 0xa4951cc and no apparent way of seeing its value. oh and also, you don't need the select() here either: class MyTable(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) my_str = Column('my_str', String) my_substr = column_property(func.substr(cls.my_str, 2, 3)) I see nothing wrong with the example, the only potential glitch is that my_str needs to have a name assigned up front. You didn't illustrate that part here, so FYI it would be extremely helpful if you could attach fully working examples. In this case the example is: from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.util import classproperty from sqlalchemy.orm import column_property, sessionmaker Base = declarative_base() engine = create_engine('sqlite://', echo=True) class MySubstr(object): @classproperty def my_substr(cls): return column_property( select([func.substr(cls.my_str, 2, 3)]).label('my_substr') ) class MyTable(Base, MySubstr): __tablename__ = 'foo' id = Column(Integer, primary_key=True) my_str = Column('my_str', String) Base.metadata.create_all(engine) sess = sessionmaker(engine)() m1 = MyTable(my_str='some string') sess.add(m1) sess.commit() print m1.my_substr and it works fine. Should the @classproperty sit within the MyTable(Base) itself (ie is there a need for the separate object -- @classproperty is typically specific to the mixin use case, which is why its only discussed in the mixins section of the declarative documentation. It's a useful construct in other situations but you certainly don't need it if you aren't using mixins. and have I been confused by the section on 'mixins' )? I'm not sure why you're choosing to use a mixin here, as your column_property appears to be pretty specific to the MyTable class. if the documentation led you to believe that you needed one, I am extremely curious why that is the case as mixins are absolutely optional and that would be a serious documentation bug. Without the mixin, it is: class MyTable(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) my_str = Column('my_str', String) my_substr = column_property( select([func.substr(my_str, 2, 3)]).label('my_substr') ) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Returning an attribute from an object when using query_property()?
On Aug 2, 11:34 am, Michael Bayer mike...@zzzcomputing.com wrote: values() should be using the least amount of memory of any approach - it only buffers 10 rows in memory at a time, and doesn't deal with session or object accounting at all so is also much more performant than all(). I dont know what all().values() is or if that's a typo. Session.query(Event).values(func.distinct(Event.source)) should emit a brief SELECT DISTINCT query and whip through a single column for the result. It wasn't a typo; it was silliness on my part. The iterator approach is blazingly fast. Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] To select only some columns from some tables using session object, relation many-to-many
Hello, I have these classes where items (class Item) is related to channel object. Channel can contain many items: channel_items = Table( channel_items, metadata, Column(channel_id, Integer, ForeignKey(channels.id)), Column(item_id, Integer, ForeignKey(Item.id)) ) class Channel(rdb.Model): rdb.metadata(metadata) rdb.tablename(channels) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) items = relation(Item, secondary=channel_items, backref=channels) class Item(rdb.Model): rdb.metadata(metadata) rdb.tablename(items) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) I know how to get all the columns using something like: session = rdb.Session() channels = session.query(Channel).order_by(Channel.title) However, I'd like to select some columns from both tables with some conditions in Item. For example, select all the channels where item.type = 'jpg'. I'd like to get a channel object with items attributes with that condition for example. How can I do that? I've tried something like (no one worked out): result = session.query(Channel).filter(Item.typeItem != 'zeppelin/ channel').all() result = session.query(Channel, Item).filter(Item.typeItem != 'zeppelin/channel').all() Thanks in advance! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: To select only some columns from some tables using session object, relation many-to-many
On Aug 3, 8:43 am, Alvaro Reinoso alvrein...@gmail.com wrote: Hello, I have these classes where items (class Item) is related to channel object. Channel can contain many items: channel_items = Table( channel_items, metadata, Column(channel_id, Integer, ForeignKey(channels.id)), Column(item_id, Integer, ForeignKey(Item.id)) ) class Channel(rdb.Model): rdb.metadata(metadata) rdb.tablename(channels) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) items = relation(Item, secondary=channel_items, backref=channels) class Item(rdb.Model): rdb.metadata(metadata) rdb.tablename(items) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) I know how to get all the columns using something like: session = rdb.Session() channels = session.query(Channel).order_by(Channel.title) However, I'd like to select some columns from both tables with some conditions in Item. For example, select all the channels where item.type = 'jpg'. I'd like to get a channel object with items attributes with that condition for example. How can I do that? I've tried something like (no one worked out): result = session.query(Channel).filter(Item.typeItem != 'zeppelin/ channel').all() result = session.query(Channel, Item).filter(Item.typeItem != 'zeppelin/channel').all() Thanks in advance! Try something like session.query(Channel).join('items').filter(Item.typeItem != 'whatever').all() -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.