Re: [sqlalchemy] implementing one-to-many relationship?
On Mon, Apr 15, 2013 at 3:39 AM, James Hartley jjhart...@gmail.com wrote:
On Thu, Apr 4, 2013 at 1:39 PM, Michael Bayer mike...@zzzcomputing.com
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 jjhart...@gmail.com 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to
Re: [sqlalchemy] implementing one-to-many relationship?
On Thu, Apr 4, 2013 at 1:39 PM, Michael Bayer mike...@zzzcomputing.comwrote:
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 jjhart...@gmail.com 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.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] implementing one-to-many relationship?
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 jjhart...@gmail.com wrote:
I have implemented a (simplified) one-to-many relationship which works, but I
suspect I am reimplementing functionality in a suboptimal fashion which is
already done by SQLAlchemy. The following short example:
8---
#!/usr/bin/env python
import datetime
from sqlalchemy import create_engine, Column, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.schema import UniqueConstraint
from sqlalchemy.dialects.sqlite import INTEGER, TEXT, DATETIME
Base = declarative_base()
Session = sessionmaker()
engine = create_engine('sqlite:///test.db', echo=True)
class Subordinate(Base):
__tablename__ = 'subordinate'
id = Column(INTEGER, primary_key=True)
name = Column(TEXT, unique=True, nullable=False)
discovered = Column(DATETIME, nullable=False)
discontinued = Column(DATETIME, nullable=True)
def __init__(self, name):
constructor
self.name = name
self.discovered = datetime.datetime.now()
def __repr__(self):
string representation overload
return 'subordinate(%d,%s,%s)' % (self.id, self.discovered,
self.discontinued)
class Record(Base):
__tablename__ = 'record'
id = Column(INTEGER, primary_key=True)
subordinate_id = Column(INTEGER, ForeignKey('subordinate.id'),
nullable=False)
timestamp = Column(DATETIME, nullable=False)
UniqueConstraint('subordinate_id', 'timestamp', name='occurrence')
def __init__(self, subordinate):
constructor
self.subordinate_id = subordinate.id
self.timestamp = datetime.datetime.now()
def __repr__(self):
string representation overload
return 'Snapshot(%s,%s,%s)' % (self.id, self.subordinate_id,
self.timestamp)
if __name__ == '__main__':
Session.configure(bind=engine)
session = Session()
Base.metadata.create_all(engine)
d = {'subordinates':{}, 'records':{}}
lst = []
for p in ('abc', 'ijk', 'xyz'):
d['subordinates'][p] = Subordinate(p)
lst.append(d['subordinates'][p])
session.add_all(lst)
session.commit()
lst = []
for p in ('abc', 'ijk', 'xyz'):
d['records'][p] = Record(d['subordinates'][p])
lst.append(d['records'][p])
session.add_all(lst)
session.commit()
8---
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?
Any insight shared would be appreciated.
8---
2013-04-03 13:35:38,291 INFO sqlalchemy.engine.base.Engine ()
2013-04-03 13:35:38,293 INFO sqlalchemy.engine.base.Engine COMMIT
2013-04-03 13:35:38,297 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-04-03 13:35:38,299 INFO sqlalchemy.engine.base.Engine INSERT INTO
subordinate (name, discovered, discontinued) VALUES (?, ?, ?)
2013-04-03 13:35:38,300 INFO sqlalchemy.engine.base.Engine ('abc',
'2013-04-03 13:35:38.296111', None)
2013-04-03 13:35:38,301 INFO sqlalchemy.engine.base.Engine INSERT INTO
subordinate (name, discovered, discontinued) VALUES (?, ?, ?)
2013-04-03 13:35:38,301 INFO sqlalchemy.engine.base.Engine ('ijk',
'2013-04-03 13:35:38.296223', None)
2013-04-03 13:35:38,302 INFO sqlalchemy.engine.base.Engine INSERT INTO
subordinate (name, discovered, discontinued) VALUES (?, ?, ?)
2013-04-03 13:35:38,302 INFO sqlalchemy.engine.base.Engine ('xyz',
'2013-04-03 13:35:38.296309', None)
2013-04-03 13:35:38,303 INFO sqlalchemy.engine.base.Engine COMMIT
2013-04-03 13:35:38,305 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-04-03 13:35:38,306 INFO sqlalchemy.engine.base.Engine SELECT
subordinate.id AS subordinate_id, subordinate.name AS subordinate_name,
subordinate.discovered AS subordinate_discovered, subordinate.discontinued AS
subordinate_discontinued
FROM subordinate
WHERE subordinate.id = ?
2013-04-03 13:35:38,306 INFO sqlalchemy.engine.base.Engine (1,)
2013-04-03 13:35:38,309 INFO sqlalchemy.engine.base.Engine SELECT
subordinate.id AS subordinate_id, subordinate.name AS subordinate_name,
subordinate.discovered AS subordinate_discovered, subordinate.discontinued AS
[sqlalchemy] implementing one-to-many relationship?
I have implemented a (simplified) one-to-many relationship which works, but
I suspect I am reimplementing functionality in a suboptimal fashion which
is already done by SQLAlchemy. The following short example:
8---
#!/usr/bin/env python
import datetime
from sqlalchemy import create_engine, Column, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.schema import UniqueConstraint
from sqlalchemy.dialects.sqlite import INTEGER, TEXT, DATETIME
Base = declarative_base()
Session = sessionmaker()
engine = create_engine('sqlite:///test.db', echo=True)
class Subordinate(Base):
__tablename__ = 'subordinate'
id = Column(INTEGER, primary_key=True)
name = Column(TEXT, unique=True, nullable=False)
discovered = Column(DATETIME, nullable=False)
discontinued = Column(DATETIME, nullable=True)
def __init__(self, name):
constructor
self.name = name
self.discovered = datetime.datetime.now()
def __repr__(self):
string representation overload
return 'subordinate(%d,%s,%s)' % (self.id, self.discovered,
self.discontinued)
class Record(Base):
__tablename__ = 'record'
id = Column(INTEGER, primary_key=True)
subordinate_id = Column(INTEGER, ForeignKey('subordinate.id'),
nullable=False)
timestamp = Column(DATETIME, nullable=False)
UniqueConstraint('subordinate_id', 'timestamp', name='occurrence')
def __init__(self, subordinate):
constructor
self.subordinate_id = subordinate.id
self.timestamp = datetime.datetime.now()
def __repr__(self):
string representation overload
return 'Snapshot(%s,%s,%s)' % (self.id,
self.subordinate_id, self.timestamp)
if __name__ == '__main__':
Session.configure(bind=engine)
session = Session()
Base.metadata.create_all(engine)
d = {'subordinates':{}, 'records':{}}
lst = []
for p in ('abc', 'ijk', 'xyz'):
d['subordinates'][p] = Subordinate(p)
lst.append(d['subordinates'][p])
session.add_all(lst)
session.commit()
lst = []
for p in ('abc', 'ijk', 'xyz'):
d['records'][p] = Record(d['subordinates'][p])
lst.append(d['records'][p])
session.add_all(lst)
session.commit()
8---
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?
Any insight shared would be appreciated.
8---
2013-04-03 13:35:38,291 INFO sqlalchemy.engine.base.Engine ()
2013-04-03 13:35:38,293 INFO sqlalchemy.engine.base.Engine COMMIT
2013-04-03 13:35:38,297 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-04-03 13:35:38,299 INFO sqlalchemy.engine.base.Engine INSERT INTO
subordinate (name, discovered, discontinued) VALUES (?, ?, ?)
2013-04-03 13:35:38,300 INFO sqlalchemy.engine.base.Engine ('abc',
'2013-04-03 13:35:38.296111', None)
2013-04-03 13:35:38,301 INFO sqlalchemy.engine.base.Engine INSERT INTO
subordinate (name, discovered, discontinued) VALUES (?, ?, ?)
2013-04-03 13:35:38,301 INFO sqlalchemy.engine.base.Engine ('ijk',
'2013-04-03 13:35:38.296223', None)
2013-04-03 13:35:38,302 INFO sqlalchemy.engine.base.Engine INSERT INTO
subordinate (name, discovered, discontinued) VALUES (?, ?, ?)
2013-04-03 13:35:38,302 INFO sqlalchemy.engine.base.Engine ('xyz',
'2013-04-03 13:35:38.296309', None)
2013-04-03 13:35:38,303 INFO sqlalchemy.engine.base.Engine COMMIT
2013-04-03 13:35:38,305 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-04-03 13:35:38,306 INFO sqlalchemy.engine.base.Engine SELECT
subordinate.id AS subordinate_id, subordinate.name AS subordinate_name,
subordinate.discovered AS subordinate_discovered, subordinate.discontinued
AS subordinate_discontinued
FROM subordinate
WHERE subordinate.id = ?
2013-04-03 13:35:38,306 INFO sqlalchemy.engine.base.Engine (1,)
2013-04-03 13:35:38,309 INFO sqlalchemy.engine.base.Engine SELECT
subordinate.id AS subordinate_id, subordinate.name AS subordinate_name,
subordinate.discovered AS subordinate_discovered, subordinate.discontinued
AS subordinate_discontinued
FROM subordinate
WHERE subordinate.id = ?
2013-04-03 13:35:38,309 INFO sqlalchemy.engine.base.Engine (2,)
2013-04-03 13:35:38,311 INFO sqlalchemy.engine.base.Engine SELECT
subordinate.id AS subordinate_id, subordinate.name AS subordinate_name,
subordinate.discovered AS subordinate_discovered, subordinate.discontinued
AS subordinate_discontinued
FROM subordinate
WHERE subordinate.id = ?
2013-04-03 13:35:38,311 INFO sqlalchemy.engine.base.Engine (3,)
2013-04-03 13:35:38,313 INFO sqlalchemy.engine.base.Engine INSERT INTO
record (subordinate_id, timestamp) VALUES (?, ?)
2013-04-03 13:35:38,313 INFO sqlalchemy.engine.base.Engine (1, '2013-04-03
13:35:38.308225')
