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]))
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?
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.
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'))
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).
So far Example 3 wins, but if altering columns instead of replacing is
possible, Example 4 might be better.
Any answers to questions in example 2 or 4?
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
