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.
