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 > 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') > 2013-04-03 13:35:38,314 INFO sqlalchemy.engine.base.Engine INSERT INTO record > (subordinate_id, timestamp) VALUES (?, ?) > 2013-04-03 13:35:38,314 INFO sqlalchemy.engine.base.Engine (2, '2013-04-03 > 13:35:38.310071') > 2013-04-03 13:35:38,315 INFO sqlalchemy.engine.base.Engine INSERT INTO record > (subordinate_id, timestamp) VALUES (?, ?) > 2013-04-03 13:35:38,315 INFO sqlalchemy.engine.base.Engine (3, '2013-04-03 > 13:35:38.311947') > 2013-04-03 13:35:38,315 INFO sqlalchemy.engine.base.Engine COMMIT > ====8<----------------------- > > > -- > 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. > > -- 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.
