On Aug 15, 2012, at 4:00 AM, Richard Rosenberg wrote:
> I think at this point, the docs are simply making it worse for me. Is there
> an example out there that is declarative and concise?
>
> This is a really simple scenario involving a single "header" table, and
> multiple (identical) "detail" tables, as in:
>
> headertable
> id int
> namekey varchar
>
> detail1
> id integer
> headerid integer, fk headertable.id
> groupid integer
> somevalue varchar
>
> And so on, ad nauseum, detail2. . .detailN
>
> Employees, engineers, and managers. . .Is.Not.Working for me. Is there
> something better out there. . ? I can make it AbstractConcreteBase or
> ConcreteBase, or whatever at this point, any direction in the way of best
> practice or gotchas is appreciated too.
First note that the main inheritance docs for "latest" have been updated to be
fully declarative for "joined" and "single" inheritance:
http://docs.sqlalchemy.org/en/latest/orm/inheritance.html . Concrete will
follow.
The next step is identifying one of three patterns: single, joined, or concrete
table inheritance. If our docs aren't enough, here are Fowler's description
of each : http://martinfowler.com/eaaCatalog/singleTableInheritance.html
http://martinfowler.com/eaaCatalog/classTableInheritance.html (this is what we
call "joined"),
http://martinfowler.com/eaaCatalog/concreteTableInheritance.html. Here's
another extremely in-depth description of the various inheritance patterns,
including ones we don't support:
http://www.agiledata.org/essays/mappingObjects.html#MappingInheritance . I
should actually consider adding Fowler's links to our own docs, that's a good
idea.
So at this point, you'd have decided which of those three patterns you're
using. I can already tell you that its "joined", because you have two
separate tables (so not single) and your "base" table has a column that is not
in the "sub" table (so not concrete).
From there, you can pretty much swap out the names in our Employee example at
http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#joined-table-inheritance
to get at the initial "headertable" and "detail1".
I've attached that, laying out Header and Detail1 explicitly. Then, since you
said you have "detail2, 3, 4,...N" I illustrate a function to create more
Detail classes dynamically. You can run this example as is, as it creates its
own SQLite database. I hope it helps!
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Header(Base):
__tablename__ = 'headertable'
id = Column(Integer, primary_key=True)
namekey = Column(String(50))
type = Column(String(50))
__mapper_args__ = {
'polymorphic_identity': 'header',
'polymorphic_on': type
}
class Detail1(Header):
__tablename__ = 'detail1'
id = Column(Integer, primary_key=True)
headerid = Column(Integer, ForeignKey('headertable.id'))
groupid = Column(Integer)
somevalue = Column(String(30))
__mapper_args__ = {
'polymorphic_identity': 'detail1',
}
def make_detail(id):
# to get "N" Detail classes, this creates new Detail classes
# dynamically
return type(
"Detail%d" % id,
(Header,),
dict(
__tablename__='detail%d' % id,
id=Column(Integer, primary_key=True),
headerid=Column(Integer, ForeignKey('headertable.id')),
groupid=Column(Integer),
somevalue=Column(String(30)),
__mapper_args__={
'polymorphic_identity': 'detail%d' % id,
}
)
)
# lets make N detail classes
N = 10
for i in xrange(2, N):
new_detail_cls = make_detail(i)
locals()[new_detail_cls.__name__] = new_detail_cls
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
Detail5(somevalue="d5", groupid=6),
Detail2(somevalue="d2", groupid=7),
Detail9(somevalue="d9", groupid=8),
Detail5(somevalue="d5", groupid=9),
Detail8(somevalue="d8", groupid=4),
Detail1(somevalue="d1", groupid=5),
])
s.commit()
print s.query(Header).all()
