|
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 |
