On Feb 23, 2012, at 2:49 PM, Adam Tauno Williams wrote:
> I have a database where multiple objects use the same sequence to
> generate primary keys -
>
> class ProjectInfo(Base, KVC):
> __tablename__ = 'project_info'
> object_id = Column("project_info_id", Integer,
> Sequence('key_generator'),
> primary_key=True)
> project_id = Column("project_id",
> ForeignKey('project.project_id'), )
> ...
>
> class Project(Base, KVC):
> """ An OpenGroupware Project object """
> __tablename__ = 'project'
> object_id = Column("project_id",
> Sequence('key_generator'),
> ForeignKey('project_info.project_id'),
> ...
>
> Project.info = relation("ProjectInfo", uselist=False,
> back_populates="project",
> primaryjoin=(ProjectInfo.project_id==Project.object_id))
>
> ProjectInfo.project = relation("Project", uselist=False, backref="info",
> primaryjoin=(ProjectInfo.project_id==Project.object_id))
>
> This works fine. But if I create a Project object I can't relate it to
> a ProjectInfo object within the same transaction without calling flush()
> first. Is there some way to encourage SQLalchemy to allocate a value
> from the sequence when the object is created?
>
> Basically a ProjectInfo should be created for every Project that is
> created; this relationship is one-to-one.
like someone else said, relationship() allows this to all be worked out for you
- if you associate a Project and ProjectInfo together using Project.info =
ProjectInfo(), the flush process inserts the row for the parent first, then
applies the new primary key value to the child. The application code deals
only with the collections and object references, whereas details about primary
and foreign key columns are handled transparently.
The above mapping looks a little off, I see ProjectInfo.project_id refers to
Project.project_id but that column isn't pictured on Project. Also Project has
its own object_id, which is fine, but I see that it has both a Sequence as well
as a ForeignKey together. If that's the actual code, one or the other
shouldn't be there. A ForeignKey indicates this Column is always referring to
a value that was created elsewhere, and stored in the referenced column - so it
would never have a Sequence of it's own.
If in fact Project.object_id is a foreign key to ProjectInfo, and
ProjectInfo.project_id is a foreign key to Project, then this is the "mutual
reference" pattern. For this pattern there's a relationship() flag called
"post_update" that handles this, emitting an UPDATE statement for one of those
foreign keys:
http://docs.sqlalchemy.org/en/latest/orm/relationships.html#rows-that-point-to-themselves-mutually-dependent-rows
Finally, f you want to invoke the sequence manually, you can say:
seq = Sequence('key_generator')
nextvalue = Session.execute(seq.next_value())
--
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.
