On Mon, Apr 15, 2013 at 3:39 AM, James Hartley <[email protected]> wrote:
> On Thu, Apr 4, 2013 at 1:39 PM, Michael Bayer <[email protected]>
> wrote:
>>
>> the requery is due to the default expire_on_commit of session.commit():
>> http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#committing. Feel
>> free to disable this feature if you don't need it.
>>
>> as far as one-to-many, I don't see the use of relationship() here, you'd
>> likely find it easier to use rather than assigning primary key identities to
>> foreign key attributes directly:
>>
>> http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#building-a-relationship
>>
>>
>>
>> On Apr 3, 2013, at 2:49 PM, James Hartley <[email protected]> wrote:
>>
>> I am finding it curious in the following output that once the subordinate
>> tuples are committed, SQLAlchemy is querying the database once again to
>> retrieve the primary keys of the second table. Am I performing too much
>> work in client code?
>>
> Thanks, Michael! expire_on_commit=False was exactly what I needed to stop
> redundant queries.
>
> I now need to go back one step, & ask about sqlalchemy.orm.relationship.
> Below is the table/class definitions I am using:
>
> =====8<---------------------------------------
> class Heartbeat(Base):
> __tablename__ = 'heartbeat'
>
> id = Column(Integer, primary_key=True)
> timestamp = Column(DateTime, unique=True, nullable=False)
> elapsed_time = Column(Float, CheckConstraint('elapsed_time > 0'),
> nullable=False)
>
> def __init__(self, elapsed_time):
> """Constructor."""
> self.timestamp = datetime.datetime.now()
> self.elapsed_time = elapsed_time
>
> def __repr__(self):
> """Overload."""
> return '<Heartbeat("{}","{}","{}")>'.format(self.id, self.timestamp,
> self.elapsed_time)
>
> class Platform(Base):
> __tablename__ = 'platforms'
>
> id = Column(Integer, primary_key=True)
> name = Column(String, nullable=False)
> #first_heartbeat_id = Column(Integer, ForeignKey('heartbeat.id'),
> nullable=False)
> first_heartbeat_id = Column(Integer, CheckConstraint('first_heartbeat_id
> != last_heartbeat_id'), ForeignKey('heartbeat.id'), nullable=False)
> last_heartbeat_id = Column(Integer, CheckConstraint('last_heartbeat_id
> != first_heartbeat_id'), ForeignKey('heartbeat.id'), nullable=True)
>
> UniqueConstraint('name', 'first_heartbeat_id')
>
> first_heartbeat = relationship('Heartbeat')
> #last_heartbeat = relationship('Heartbeat')
>
> def __init__(self, name):
> self.name = name
>
> def __repr__(self):
> return '<Platform("{}","{}","{}","{}")>'.format(self.id, self.name,
> self.first_heartbeat_id, self.last_heartbeat_id)
> =====8<---------------------------------------
>
> Numerous foreign keys in various tables refer back to the timestamp
> maintained in the "heartbeat" table, however in the case of the "platforms"
> table, timestamps are used to designate when the platform was originally
> detected & deactivated. If I don't specify a relationship() & work directly
> with the primary key ID values, my code works without specifying a
> relationship(). If I use the code above which abstracts away the direct use
> of assigning to "first_heartbeat_id" & "last_heartbeat_id" in favor of
> dealing with class instances, I get the following error upon creating any
> Heartbeat instance:
>
> ArgumentError: Could not determine join condition between parent/child
> tables on relationship Platform.first_heartbeat. Specify a 'primaryjoin'
> expression. If 'secondary' is present, 'secondaryjoin' is needed as well.
>
> I am guessing that this arises from relationship()'s focus on types. Since
> I have identical foreign keys when it comes to where the foreign keys point,
> I am surmising that the underlying code cannot determine that
> "first_heartbeat" actually maps to "first_heartbeat_id". Is this correct?
> If this is correct, I am not lobbying for SQLAlchemy to address this strange
> edge case. I'm only trying to understand how to leverage the mapping of
> Python objects to the underlying SQL tables better.
>
> Thanks for you insight.
>
Yes, SA doesn't know whether to use first_heartbeat_id or
last_heartbeat_id as the foreign key to the heartbeat table. As the
error message says, you can fix this by specifying the primaryjoin
keyword argument to "relationship", something like this:
first_heartbeat = relationship('Heartbeat',
primaryjoin=(first_heartbeat_id == Heartbeat.id))
last_heartbeat = relationship('Heartbeat',
primaryjoin=(last_heartbeat_id == Heartbeat.id))
According to
http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#configuring-how-relationship-joins,
SA 0.8 should be able to handle this just by specifying the
foreign_keys parameter:
first_heartbeat = relationship('Heartbeat', foreign_keys=[first_heartbeat_id])
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
