After resisting ORM and database abstraction packages for a long time in favour of plain sqlite3, I decided to give SQLAlchemy's ORM features a try.
I've managed to specify a schema and get objects in and out of tables. I'm not sure if I'm "doing it right" though. I feel like I have a fairly typical use case, but that I might be writing more / uglier code than I need to. Given the schema below, is my insert_into_sql() function idiomatic? The objects are passed into this function as nested dictionaries/lists after being parsed from the attached YAML file. In particular: 1) Is this way of constructing the schema objects from dictionaries idiomatic / clear (using the built-in constructor in Base which takes fields as **kwargs)? 2) Do I need to be doing the manual "flush() + get ID" fiddling to create the linkage between objects? Or is there a better way to do this, eg by linking the objects together in code and making a single session call at the end to insert all the rows in one hit? 3) What's 'best practice' for transaction management with SQLAlchemy ORM? The documentation provides a snippet for a context manager called session_scope(); is there anything like that built-in that I could/should be using? http://docs.sqlalchemy.org/en/latest/orm/session.html 4) Any other comments or suggestions? Sorry for the fairly open-ended question, but I think a few tips / pointers in the right direction will go a long way. def import_tags(tags): engine = create_engine('sqlite://') Base.metadata.create_all(engine) Base.metadata.bind = engine session = sessionmaker(bind=engine)() for t in tags: tag_obj = Tag(**{k: t[k] for k in ['order_code', 'version', 'status', 'comments']}) session.add(tag_obj) session.flush() for hw in t['hardware']: hw_dict = {k: hw[k] for k in ['product_id', 'version']} hw_dict['tag_id'] = tag_obj.tag_id hw_obj = Hardware(**hw_dict) session.add(hw_obj) session.flush() # so that we can access hardware id for m in hw['mods']: m_dict = {'mod_number': m} m_dict ['hardware_id'] = hw_obj.hardware_id session.add(Mod(**m_dict)) for sw in t['software']: sw_dict = {k: sw[k] for k in ['artefact', 'version']} sw_dict['tag_id'] = tag_obj.tag_id session.add(Software(**sw_dict)) session.commit() ''' SQLAlchemy schema ''' Base = declarative_base() class Tag(Base): __tablename__ = 'tag' tag_id = Column(Integer, primary_key=True) order_code = Column(String, nullable=False) version = Column(String, nullable=False) status = Column(String, nullable=False) comments = Column(String) software = relationship("Software") hardware = relationship("Hardware") __table_args__ = ( UniqueConstraint('tag_id', 'order_code', 'version'), ) class Software(Base): __tablename__ = 'software' software_id = Column(Integer, primary_key=True) tag_id = Column(String, ForeignKey('tag.tag_id')) artefact = Column(String, nullable=False) version = Column(String, nullable=False) __table_args__ = ( UniqueConstraint('tag_id', 'artefact'), ) class Hardware(Base): __tablename__ = 'hardware' hardware_id = Column(Integer, primary_key=True) tag_id = Column(String, ForeignKey('tag.tag_id')) tag = relationship(Tag) product_id = Column(String, nullable=False) version = Column(String, nullable=False) mods = relationship("Mod") __table_args__ = ( UniqueConstraint('tag_id', 'product_id'), ) class Mod(Base): __tablename__ = 'mod' hardware_id = Column(String, ForeignKey('hardware.hardware_id'), primary_key=True) mod_number = Column('mod_number', Integer, primary_key=True, nullable=False) __table_args__ = ( UniqueConstraint('hardware_id', 'mod_number'), ) -- 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. For more options, visit https://groups.google.com/groups/opt_out.
tags.yaml
Description: Binary data
