SQLAlchemy related models demo

related_models.py

import sqlite3
import sqlalchemy
from sqlalchemy import *
from sqlalchemy import ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref

# Debug engine prints out all SQL activity to console; uncomment to use
# engine = create_engine('sqlite:///:memory:', echo=True)

# Production engine
engine = create_engine('sqlite:///:memory:')

# Bind engine to declarative base class
Base = declarative_base(bind=engine)

class StarSystem(Base):
  """Stores star system information"""
  
  __tablename__ = 'starSystems'
  
  id = Column(Integer, primary_key=True)
  name = Column(String)
  # FK relationship to StarAlliance model
  alliance_id = Column(Integer, ForeignKey('starAlliances.id'))
  
  def __init__(self, id, name):
    self.id = id
    self.name = name
    
  def __repr__(self):
    return "<StarSystem('%s')>" % (self.name)
    

class StarAlliance(Base):
  """Stores alliance information"""
  __tablename__ = 'starAlliances'
  
  id = Column(Integer, primary_key=True)
  name = Column(String)
  
  # establish relationship between StarAlliance and StarSystem
  # see: http://www.sqlalchemy.org/docs/orm/tutorial.html#building-a-relationship
  systems = relationship(StarSystem, order_by=StarSystem.id, backref='alliance')
  
  def __init__(self, id, name):
    self.id = id
    self.name = name
    
  def __repr__(self):
    return "<StarAlliance('%s')>" % (self.name)


if __name__ == "__main__":
  # create all tables and start a session
  Base.metadata.create_all()
  session = sessionmaker(bind=engine)()
  
  # create a test alliance and assign it new systems
  theEmpire = StarAlliance(666, 'Galatic Empire')
  theEmpire.systems = [StarSystem(1111, 'Coruscant'), StarSystem(1112, 'Geonosis'), StarSystem(1113, 'Endor')]
    
  # create a second alliance and assign it new systems
  rebelAlliance = StarAlliance(777,'Rebel Alliance')
  rebelAlliance.systems = [StarSystem(2222, 'Hoth'), StarSystem(2223, 'Yavin'), StarSystem(2224, 'Dantooine'), StarSystem(2225, 'Alderaan')]
  
  # create a third, empty alliance for systems with no soverign
  emptyAlliance = StarAlliance(0, 'UNCLAIMED')
  emptyAlliance.systems = [StarSystem(3333, 'Tatooine'), StarSystem(3334, 'Bespin')]
  
  # add alliances to the session
  # this adds any new systems associated with them
  session.add(theEmpire)
  session.add(rebelAlliance)
  session.add(emptyAlliance)
  
  session.commit()
  
  # The Rebels have abandoned their base on Dantooine
  dantooine = session.query(StarSystem).filter_by(name='Dantooine').first()
  dantooine.alliance = emptyAlliance
  
  # Alderaan has been destroyed by the Death Star!
  alderaan = session.query(StarSystem).filter_by(name='Alderaan').first()
  session.delete(alderaan) # :'(
  
  # The Imperials have taken Hoth and Yavin!
  hoth  = session.query(StarSystem).filter_by(name='Hoth').first()
  yavin = session.query(StarSystem).filter_by(name='Yavin').first()
  hoth.alliance  = theEmpire
  yavin.alliance = theEmpire
  
  # The Rebels have freed Endor!
  endor = session.query(StarSystem).filter_by(name='Endor').first()
  endor.alliance = rebelAlliance
  
  # retrieve alliances from database via query
  # this implicitly commits all the dirty data above
  alliances = session.query(StarAlliance).all()
  
  # display all alliances and their systems
  for alliance in alliances:
    print "\nSystems under %s sovereignity:" % alliance.name
  
    for system in alliance.systems:
      print system.name

This is hilarious.