Each of the four tables for the networknode subclasses have hundreds of
columns, but each has an mid column and a <type>name column. Here's the
definition of connecttb, thanks to sqlautocode:
connecttb = Table('connecttb', metadata,
Column(u'deleteflag', Numeric(precision=10, scale=2, asdecimal=True),
primary_key=False),
Column(u'backgroundtaskflag', Numeric(precision=10, scale=2,
asdecimal=True), primary_key=False),
Column(u'datetimestamp', DateTime(timezone=False),
primary_key=False),
Column(u'userdatestamp', DateTime(timezone=False),
primary_key=False),
Column(u'usertimestamp', String(length=8,
convert_unicode=False, assert_unicode=None), primary_key=False),
Column(u'userid', Numeric(precision=10, scale=2,
asdecimal=True), primary_key=False),
Column(u'upstreamtype', Numeric(precision=10, scale=2,
asdecimal=True), primary_key=True, nullable=False),
Column(u'upstreamid', Numeric(precision=10, scale=2,
asdecimal=True), primary_key=True, nullable=False),
Column(u'downstreamtype', Numeric(precision=10, scale=2,
asdecimal=True), primary_key=True, nullable=False),
Column(u'downstreamid', Numeric(precision=10, scale=2,
asdecimal=True), primary_key=True, nullable=False),
Column(u'startdate', DateTime(timezone=False),
primary_key=True, nullable=False),
Column(u'enddate', DateTime(timezone=False),
primary_key=False),
Column(u'description', String(length=100,
convert_unicode=False, assert_unicode=None), primary_key=False),
Column(u'startmonth', DateTime(timezone=False),
primary_key=False),
Column(u'endmonth', DateTime(timezone=False),
primary_key=False),
Column(u'splitconnect', Numeric(precision=10, scale=2,
asdecimal=True), primary_key=False),
Column(u'allocationfactor', Numeric(precision=10, scale=2,
asdecimal=True), primary_key=False),
Column(u'allocationmethod', Numeric(precision=10, scale=2,
asdecimal=True), primary_key=False)
)
Adding concrete=True got rid of the specific error listed. The problem I
face now is the relation between connecttb and productionentities. How
can I specify this relationship? I tried again and specified the
relationships on the association object, Connection. This method throws
the following error.
connmapper = orm.mapper(Connection,connecttb,properties=dict(
upstreamnodes=orm.relation(NetworkNode,primaryjoin=
and_(connecttb.c.downstreamid==
s.c.mobjectid,
connecttb.c.downstreamtype==
s.c.mobjecttype),
foreign_keys=[
s.c.mobjectid,
s.c.mobjecttype]),
downstreamnodes=orm.relation(NetworkNode,primaryjoin=
and_(connecttb.c.upstreamid==
s.c.mobjectid,
connecttb.c.upstreamtype==
s.c.mobjecttype)),
foreign_keys=[
s.c.mobjectid,
s.c.mobjecttype]))
ArgumentError: Column 'productionentities.merrickobjectid' is not
represented in mapper's table. Use the `column_property()` function to
force this column to be mapped as a read-only attribute.
I played around with mobjectid = column_property(...) on the NetworkNode
object, but I don't think that is the right direction.
Is there a way to do this with polymorphic union? I don't see how to
specify the relation on each type. The relation direction keeps tripping
me up when the relation properties are not the association object, as the
foreign key would be defined in connecttb, if that were possible.. By the
way, this is all read-only.
Wes Dyk
Re: [sqlalchemy] Concrete table inheritance without a master table
Michael Bayer
to:
sqlalchemy
04/14/2010 06:07 PM
Sent by:
[email protected]
Please respond to sqlalchemy
On Apr 14, 2010, at 7:51 PM, [email protected] wrote:
I've got a legacy database that I need to work through the following
scenario with.
There are four types of objects, completions, meters, tanks and equipment.
Each has its own table. There is no "master" table representing all the
entities. There is a connection table that describes the relationship
between the entities. Each has a numeric identifier and the four types
are distinguished in the connection table by another numeric id, between 1
and 4.
In the implementation presented, I am trying to create a network node
abstract object with each of the four types inheriting from this class
(NetworkNode). What I really want is to be able to query from an apex
node to its upstream objects and get a class of the specific type. So if
what I'm doing is going about it in the wrong way, please set me straight!
The problem I've run into is the following error. Since there is no base
table to define foreign keys on, and I'm not sure if I should define
properties of each object as relations to the connection table, I'm not
sure where to go with this. Any ideas?
Can't find any foreign key relationships between 'productionentities' and
'completiontb'
would need to see the strucure of completiontb, connecttb, etc. to work
out the above error as well as what triggers that error. You do need to
say "concrete=True" in an inheriting mapper which you'd like to be
concrete versus its parent.
Here's the code (with some names changed and anything pertaining only to
tanks, meters or equipment left out). I apologize for the lengthy
message. I'm not sure that I can condense this issue any further.
from sqlalchemy import *
from sqlalchemy import sql
from sqlalchemy import orm
from pc.orm.tables import (tanktb, metertb, completiontb, equipmenttb,
connecttb)
from pc.orm.tables import metadata
# add the object types to this selectable to define an abstract master
table
# there's not much to know about the underlying tables except the columns
# listed here
cs = select([literal(1).label('mobjecttype'),
completiontb.c.mid.label('mobjectid'),
completiontb.c.wellpluscompletionname.label('objectname')])
ms = select([literal(2),metertb.c.mid,metertb.c.metername])
ts = select([literal(3),tanktb.c.mid,tanktb.c.tankname])
es = select([literal(4),equipmenttb.c.mid,equipmenttb.c.equipmentname])
s = union_all(cs,ms,ts,es).alias('productionentities')
# We'll call the abstract object a network node, since we are dealing with
# a network of objects
class NetworkNode(object):
pass
class Completion(NetworkNode):
pass
# Use an association object to represent the relationship between nodes
class Connection(object):
pass
connmapper = orm.mapper(Connection,connecttb)
# Define the upstream and downstream relationships on the abstract node
nnmapper = orm.mapper(NetworkNode,s,primary_key=[s.c.mobjecttype,
s.c.mobjectid],
polymorphic_on=s.c.mobjecttype,
properties=dict(
upstreamobjects=orm.relation(Connection,primaryjoin=
and_(connecttb.c.downstreamid==s.c.mobjectid,
connecttb.c.downstreamtype==s.c.mobjecttype)),
downstreamobjects=orm.relation(Connection,primaryjoin=
and_(connecttb.c.upstreamid==s.c.mobjectid,
connecttb.c.upstreamtype==s.c.mobjecttype))))
# the concrete types
cmapper = orm.mapper(Completion,completiontb,inherits=NetworkNode,
polymorphic_identity=1)
Thank you for the help!
Wes Dyk
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
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.
