[sqlalchemy] Re: inheritance and column_property() - subqueries are being limited
...and we're back to the same issue with another column_property: that
is,
How do you create column_properties with subqueries on child objects
where the subquery references another child object? This:
PC_avgdist_subq = select([func.avg(func.distance(PC.pt.RAW,
Switch.pt.RAW))]).where(PC.switch_mac == Switch.mac).as_scalar()
Switch.PC_avgdist = column_property(PC_avgdist_subq)
no longer works. The workaround worked for the last case but doesn't
work when I need to do a func.average(func.distance(..)) to get the
average physical distance between a switch and the PCs that are
attached to it.
As before, the generated sql is appending WHERE 'devtype' in
'Switch' to the end of the switch = Session.query(Switch)...
statement, which I think is throwing off the subquery results (but I
could be completely wrong).
Thanks,
S.
On Aug 4, 5:11 pm, SQLAlchemy User sqlalchemyu...@gmail.com wrote:
...and I've figured out a workaround:
in Switch(Device):
pcs = relationship('PC', primaryjoin = 'Switch.mac ==
PC.switch_mac', lazy='dynamic')
Now, switch.pcs.count() works :)
S.
On Aug 4, 5:00 pm, Zippy P sqlalchemyu...@gmail.com wrote:
I have a single-table inheritance setup:
class Device(Base):
__tablename__ = 'devices'
devtype = Column(Unicode(20), nullable = False)
mac = Column(Unicode(128), primary_key = True)
...
class PC(Device):
__mapper_args_ = {'polymorphic_identity':u'PC'}
switch_mac = Column(Unicode(128), ForeignKey(mac), nullable = True) #
ignore any typos here - this is munged code
class Switch(Device):
__mapper_args_ = {'polymorphic_identity':u'Switch'}
...
pc_ct_subq = select([func.count(PC.mac)]).where(PC.switch_mac ==
Switch.mac).as_scalar()
Switch.pc_ct = column_property(pc_ct_subq)
When I didn't have the inheritance set up (when Switch and PC were separate
tables and separate objects), pc_ct worked fine. Now, however, it's failing
- I think because the inheritance is appending AND devices.devtype IN 'AP'
to the initial load query since it's trying to bring up just devices of type
'Switch'.
How do I get around this? I just want Switch to have an attribute that
represents the number of PCs that are associated to it (where the PC's
switch_mac equals the Switch's mac).
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.
[sqlalchemy] Re: now with code goodness: problems with single-table inheritance and column_properties?
Trying again without tab indents. Hopefully the formatting works this
time.
from objects import *
class Device(Base):
__tablename__=testdev
devtype = Column(Unicode(20), primary_key = True)
__mapper_args__ = {'polymorphic_on': devtype}
mac = Column(Unicode(128), primary_key = True)
switch_mac = Column(Unicode(128), ForeignKey(mac))
switch = relationship('Device', primaryjoin = 'Device.switch_mac
== Device.mac', backref=backref('connections'), remote_side =
[mac])
cablelength = Column(Float, nullable = True)
class PC(Device):
__mapper_args__ = {'polymorphic_identity': u'P'}
class Switch(Device):
__mapper_args__ = {'polymorphic_identity': u'S'}
Switch.PC_cablelength_avg = column_property (
select([func.avg(PC.cablelength)]).where(PC.switch_mac ==
Switch.mac).as_scalar()
)
--
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: now with code goodness: problems with single-table inheritance and column_properties?
On Aug 5, 3:11 pm, Michael Bayer mike...@zzzcomputing.com wrote: Ultimately you're looking for a self-referential correlation here. In SQL, that always implies using aliases. Your column_property needs to be against aliased(PC) and not PC itself. Thanks - that did the trick. I can't believe it was such a simple thing. -- 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: inheritance and column_property() - subqueries are being limited
...and I've figured out a workaround:
in Switch(Device):
pcs = relationship('PC', primaryjoin = 'Switch.mac ==
PC.switch_mac', lazy='dynamic')
Now, switch.pcs.count() works :)
S.
On Aug 4, 5:00 pm, Zippy P sqlalchemyu...@gmail.com wrote:
I have a single-table inheritance setup:
class Device(Base):
__tablename__ = 'devices'
devtype = Column(Unicode(20), nullable = False)
mac = Column(Unicode(128), primary_key = True)
...
class PC(Device):
__mapper_args_ = {'polymorphic_identity':u'PC'}
switch_mac = Column(Unicode(128), ForeignKey(mac), nullable = True) #
ignore any typos here - this is munged code
class Switch(Device):
__mapper_args_ = {'polymorphic_identity':u'Switch'}
...
pc_ct_subq = select([func.count(PC.mac)]).where(PC.switch_mac ==
Switch.mac).as_scalar()
Switch.pc_ct = column_property(pc_ct_subq)
When I didn't have the inheritance set up (when Switch and PC were separate
tables and separate objects), pc_ct worked fine. Now, however, it's failing
- I think because the inheritance is appending AND devices.devtype IN 'AP'
to the initial load query since it's trying to bring up just devices of type
'Switch'.
How do I get around this? I just want Switch to have an attribute that
represents the number of PCs that are associated to it (where the PC's
switch_mac equals the Switch's mac).
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.
[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.
[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] Re: multi-table comparators / properties - how?
On Jun 2, 6:43 am, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 2, 2010, at 12:48 AM, SQLAlchemy User wrote: OK, I get the need for column_property, but the select is fairly complex and involves unions of the containing class (and it hasn't been defined yet). How do I get around this, and other questions below I think I got the SQL right (for a device with id = foo): SELECT v1.adminstate FROM ( (SELECT devices.adminstate, devices.processdate as insert_ts, devices.deviceid FROM devices WHERE devices.deviceid = foo) UNION (SELECT devicestates.adminstate, devicestates.insert_ts, devicestates.deviceid FROM devicestates WHERE devicestates.deviceid = foo)) AS v1 ORDER BY v1.insert_ts DESC LIMIT 1; You dont need to get into UNION here. My suggestion would be to avoid UNION at all costs, in fact, they perform poorly and are very cumbersome to work with. A correlated subquery here should do what you need: (i.e., when you say query(Device).filter(Device.curradminstate=='FOO'): select device.* from device where coalesce( (select devicestate.adminstate from devicestate where devicestate.insert_ts device.import_ts order by devicestate.insert_ts limit 1), device.adminstate ) = 'FOO' you'd need to verify the above works first (particularly the LIMIT inside the subquery, I know mysql can do it, not sure about PG, don't know your backend). property is: class Device(Base): ... device_subq = select([DeviceState.adminstate]).where(DeviceState.insert_ts device.import_ts).order_by(DeviceState.insert_ts).limit(1).as_scalar() Device.curradminstate = column_property( func.coalesce(device_subq, Device.adminstate) ) Thanks so much - this worked perfectly. -- 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] multi-table comparators / properties - how?
Hi all: Hoping some kind soul on the list will be able to advise on this use/ implementation problem I'm having: I have two tables (both declarative representations): the first, Device, describes devices (device ID, import time, other details), and the second, DeviceState, lists state changes for those devices (device ID, old state, new state, timestamp). The device table is imported daily and contains state information at the time the export is performed. The state changes come in to DeviceState as the devices change state. I have a property on Device as follows: @property def curradminstate(self): return Session.query(DeviceState.adminstate).filter(DeviceState.deviceid == self.deviceid).filter(DeviceState.insert_ts self.import_ts).order_by(desc(DeviceState.insert_ts)).limit(1).scalar() or self.adminstate ... and this works as expected: that is, I can query device.curradminstate as a property instead of a method. I'd like to create a Comparator so I can do something like the following: Session.Query(Device).filter(Device.curradminstate == ACTIVE) but I'm completely lost as to how to do this. I've tried creating a class that returns a PropComparator but it hasn't worked (I get a variety of errors; here's my latest attempt which has at least one error in doit()): class AdminStateComparator(PropComparator): ''' Compares admin states (case-insensitive) ''' def doit(self): device = self.prop.parent.class_ return Session.query(DeviceState.adminstate).filter(DeviceState.deviceid == device.deviceid).filter(DeviceState.insert_ts self.import_ts).order_by(desc(DeviceState.insert_ts)).limit(1).scalar() or device.adminstate sprop = property(doit) def operate(self,op,other,**kwargs): return op(self.sprop,other,**kwargs) (hope the formatting came through - if not, assume appropriate indentation.) I have read the docs (several times!) but don't quite understand how PropComparator is supposed to work, especially with multiple tables. I got it working with transparent encryption/decryption (using func.aes_encrypt) for another table, but that was within a single table. In addition, I'm enough of a novice with SQL that I can't even describe what I want to do that way - I'm hoping someone on the list will be able to help regardless. 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.
[sqlalchemy] Re: multi-table comparators / properties - how?
On Jun 1, 3:31 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 1, 2010, at 1:06 PM, SQLAlchemy User wrote: I have two tables (both declarative representations): the first, Device, describes devices (device ID, import time, other details), and the second, DeviceState, lists state changes for those devices (device ID, old state, new state, timestamp). The device table is imported daily and contains state information at the time the export is performed. The state changes come in to DeviceState as the devices change state. I have a property on Device as follows: @property def curradminstate(self): return Session.query(DeviceState.adminstate).filter(DeviceState.deviceid == self.deviceid).filter(DeviceState.insert_ts self.import_ts).order_by(desc(DeviceState.insert_ts)).limit(1).scalar() or self.adminstate ... and this works as expected: that is, I can query device.curradminstate as a property instead of a method. I'd like to create a Comparator so I can do something like the following: Session.Query(Device).filter(Device.curradminstate == ACTIVE) you don't need a custom comparator here, you just want to map a column attribute to a correlated subquery, such as that illustrated inhttp://www.sqlalchemy.org/docs/mappers.html#sql-expressions-as-mapped you have an I want the max(related X) type of pattern which is fairly common, I should get around to adding several examples for this to the wiki. OK, I get the need for column_property, but the select is fairly complex and involves unions of the containing class (and it hasn't been defined yet). How do I get around this, and other questions below I think I got the SQL right (for a device with id = foo): SELECT v1.adminstate FROM ( (SELECT devices.adminstate, devices.processdate as insert_ts, devices.deviceid FROM devices WHERE devices.deviceid = foo) UNION (SELECT devicestates.adminstate, devicestates.insert_ts, devicestates.deviceid FROM devicestates WHERE devicestates.deviceid = foo)) AS v1 ORDER BY v1.insert_ts DESC LIMIT 1; So, three questions come to mind: 1) how do I create a select statement that references a label (I did it using quotes - that is, select(['v1.adminstate'],...) but not sure whether that's the most correct way, and I'm running into difficulties later on in the order_by); 2) how do I reference Device from within the select statement that's a column_property for Device; and 3) am I on the right track here or (more likely) am I off-base and missing something simple (like func.max())? Thanks again - 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.
