[sqlalchemy] now with code goodness: problems with single-table inheritance and column_properties?

[Zippy P]

I have the following code:

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()
)

 s = Switch(mac='s1')
 p = PC(mac='p1',switch_mac=s.mac, cablelength = 100)
 Session.add(p) # adds a PC with a cable length of 100 to switch s
 Session.add(s)
 Session.commit()
 s = Session.query(Switch).first()
 s.PC_cablelength_avg


Expected value for s.PC_cablelength_avg is 100.

The select statement (from first()) looks like this:

SELECT testdev.devtype AS testdev_devtype, testdev.mac AS testdev_mac,
testdev.switch_mac AS testdev_switch_mac, testdev.cablelength AS
testdev_cablelength, (SELECT avg(testdev.cablelength) AS avg_1
FROM testdev
WHERE testdev.switch_mac = testdev.mac) AS anon_1
FROM testdev
WHERE testdev.devtype IN (%s)
 LIMIT 0, 1
('S',)


The issue, I think, is that the WHERE testdev.devtype IN 'S' is causing
the results to not include objects of type PC, which is what's used in the
column_property.

How do I create column_properties for a subclass that reference another
subclass when using a single-table inheritance model?

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] inheritance and column_property() - subqueries are being limited

[Zippy P]

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] Returning an attribute from an object when using query_property()?

[Zippy P]

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.

[sqlalchemy] lazy = 'dynamic' and coercing AppenderQuery to a new object class

[Zippy P]

Hi all,

I have the following:

class Device(Base):
...
  source_id = Column(BigInteger, primary_key=True)
...

class EventQuery(Query):
  def custommethod():
...

class Event(Base):
  __tablename__ = 'events'
  query = Session.query.property(query_cls = EventQuery)
...
  device_source_id = Column(BigInteger, ForeignKey(Device.source_id))
  device = relationship(Device, backref=backref('events', lazy='dynamic'))
...

Now, Device.events returns a query of type AppenderQuery, which is fine for
some built-in methods like count() and all(). What I really need is for this
to be a query of type EventQuery, so I can call
Device.events.custommethod().

1) Is it possible to coerce AppenderQuery into a different Query class?
2) If not, is it appropriate to create a custom property on Device that will
return an EventQuery containing the equivalent query, or is there a more
proper 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.