On Mar 7, 2009, at 7:30 PM, MikeCo wrote:
>
> Sometimes we autoload tables that do not have foreign keys specified
> in the DDL, but need the relations in SQLA.
>
> Here are some techniques that seem to work along with my observations.
> I'm interested finding best approach.
>
> We have a parent-child one-to-many relation. The relation will be
> defined in the child class with a backref to the parent.
>
> Example 1 - Using mappers
> table_child = Table('child', meta, autoload=True)
> class Child(object): pass
> mapper(C, table_child, properties={
> 'parent' : relation(Parent,
> primaryjoin=(table_parent.c.id==table_child.c.id_parent),
> foreign_keys=[table_child.c.id_parent],
> backref=backref('children',
> foreign_keys=[table_child.c.id_parent])),
> })
>
> Wow, sure is a lot of typing for a simple concept. Wonder if there is
> something easier.
>
> Example 2 - Same syntax with declarative (I prefer declarative
> approach)
> class Child(Base):
> __tablename__ = 'child'
> __table_args__ = {'autoload' : True}
> Child.parent = relation(Parent, primaryjoin=
> (Parent.id==Child.id_parent),
> foreign_keys=[Child.id_parent],
> backref=backref('children',foreign_keys=[Child.id_parent]))
we should probably make backref take on the foreign_keys argument of
the forward reference the way it does for primaryjoin.
> Still pretty verbose. Curious, I have only been successful defining
> the "parent" relation after fully defining the Child class. I have not
> found if it is possible to define "parent" inside the Child class. Any
> method I use of quoting various parts of the relation give me either
> Python or SQLA errors. Any pointers anyone?
there should be no issue. as always the actual error message,
unspecified here, would describe what the issue is. the only
complexity is if Child or Parent is not yet defined you need to use
strings to define relation() attributes, which are evaulated when the
classes are available. this is documented in the declarative docs.
> Example 3 - redefine the foreign key column
> class Child(Base):
> __tablename__ = 'child'
> __table_args__ = {'autoload' : True}
> id_parent = Column(Integer, ForeignKey('parent.id'))
> parent = relation(P, backref=('children'))
>
> Good, this is getting close. The code is simpler and I don't have to
> use a verbose syntax to tell SQLA about the columns in the relation
> and foreign key.
this is the easiest way to go, having fully defined table metadata.
> Example 4 - add foreign key to column
> I can't do this one yet. Haven't figured it out how.
> class Child(Base):
> __tablename__ = 'child'
> __table_args__ = {'autoload' : True}
> -- do something to add ForeignKey clause to id_parent --
> -- conceptually id_parent += ForeignKey('parent.id') --
> parent = relation(P, backref=('children'))
Column provides an append_foreign_key() method.
> Big question, is it possible to alter definition the of autoloaded
> columns? If so, that could be useful for other attributes clauses also
> (thinking of onupdate or initial values).
you're doing it in #3. this is also documented in the "table
metadata" chapter and is the primary method to override a reflected
column definition.
> So far Example 3 wins, but if altering columns instead of replacing is
> possible, Example 4 might be better.
#4's method is awkward since it's non-declarative. Column's public
API does not have methods for adding defaults as of yet.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
