Re: [sqlalchemy] Sequences, Primary Keys, Relations
On Thu, 2012-02-23 at 14:49 -0500, 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.
I solved my problem using an association proxy and the info entry is
maintained / created just as an attribute of the Project class. This
seems to work very well.
class Project(Base):
...
project = relation(Project,
uselist=False,
backref=backref(project, cascade=all,
delete-orphan),
primaryjoin=('ProjectInfo.project_id==Project.object_id'))
class ProjectInfo(Base):
...
info = relation(ProjectInfo,
uselist=False,
backref=backref('project_info'),
primaryjoin=('ProjectInfo.project_id==Project.object_id'))
comment = association_proxy('info', 'comment')
signature.asc
Description: This is a digitally signed message part
[sqlalchemy] Sequences, Primary Keys, Relations
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.
--
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.
Re: [sqlalchemy] Sequences, Primary Keys, Relations
On Thu, Feb 23, 2012 at 4:49 PM, Adam Tauno Williams awill...@whitemice.org wrote: 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? If you just use the relation (project.info = project_info), alchemy will do everything for you. -- 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.
Re: [sqlalchemy] Sequences, Primary Keys, Relations
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 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.
