This is probably violating some normal form, but after playing with it
(Thanks Michael and Jonathan), this seems to work for what I'm using.
Suggestions welcome on improving it.
#!/usr/bin/env python
from sqlalchemy import Column, ForeignKey, Integer, MetaData, Sequence,
String, \
create_engine
from sqlalchemy.orm import backref, joinedload, relationship, \
scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.pool import NullPool
engine = create_engine(u"sqlite:///./test.sql", poolclass=NullPool)
metadata = MetaData(bind=engine)
session = scoped_session(sessionmaker(autoflush=True,
autocommit=False,
bind=engine)
)
Base = declarative_base(metadata=metadata)
# Table base class to assign table name based on class name and
# add id column
class CommonBase(object):
@declared_attr
def __tablename__(cls):
return cls.__name__.lower()
id = Column(Integer, primary_key=True)
# Table class to keep track of manufacturers
class Manufacturer(CommonBase, Base):
def __repr__(self):
return "<Manufacturer(name='%s')>" % self.name
name = Column(String(30))
models = relationship("Model",
order_by="Model.id",
backref="manufacturer",
cascade="all, delete-orphan",
primaryjoin="Manufacturer.id==Model.manufacturer_id",
lazy='joined')
# table class to keep track of models related to Manufacturer.
class Model(CommonBase, Base):
def __repr__(self):
return "<Model(name=%s')>" % self.name
manufacturer_id = Column(Integer, ForeignKey("manufacturer.id"))
name = Column(String(20))
sources = relationship("Source",
order_by="Source.id",
backref="model",
cascade="all, delete-orphan",
primaryjoin="Model.id==Source.model_id",
lazy='joined')
# Table class to keep track of sources related to Model.
class Source(CommonBase, Base):
def __repr__(self):
return "<Source(pjlink_name='%s', pjlink_code='%s', text='%s')>" % \
(self.pjlink_name, self.pjlink_code, self.text)
model_id = Column(Integer, ForeignKey('model.id'))
pjlink_name = Column(String(15))
pjlink_code = Column(String(2))
text = Column(String(30))
# Table class to keep track of installed projectors.
# Use a separate query to get manufacturer/model/sources rather than
# using a relationship (one-time at program start so minimal impact).
class Installed(CommonBase, Base):
name = Column(String(20))
location = Column(String(30))
ip = Column(String(50))
# Class for projector instances.
class Projector(object):
def __init__(name=None, location=None, ip=None):
self.name = name
self.location = location
self.ip = ip
# Following variables will be filled-in after projector connected.
self.make = None
self.model = None
# Following variable will be filled in after projector connected
and db queried.
# List of pjlink dictionary items: [ {name='', code='', text=''},
...]
self.sources = None
metadata.create_all()
m = Manufacturer(name='eiki')
m.models = [ Model(name='lc/xl200') ]
m.models[0].sources = [
Source(pjlink_name='RGB1', pjlink_code='11', text='HDMI RGB'),
Source(pjlink_name='RGB2', pjlink_code='12', text='VGA RGB')
]
session.add(m)
session.commit()
p = session.query(Manufacturer, Model).\
options(joinedload(Manufacturer.models), joinedload(Model.sources)).\
filter(Manufacturer.name=='eiki', Model.name=='lc/xl200').\
all()
if len(p) == 0:
m = Manufacturer(name='eiki')
m.models = [ Model(name='lc/xl200') ]
m.models[0].sources = [
Source(pjlink_name='RGB1', pjlink_code='11', text='HDMI RGB'),
Source(pjlink_name='RGB2', pjlink_code='12', text='VGA RGB')
]
session.add(m)
session.commit()
p = session.query(Manufacturer, Model).\
options(joinedload(Manufacturer.models), joinedload(Model.sources)).\
filter(Manufacturer.name=='eiki', Model.name=='lc/xl200').\
all()
m=p[0][0]
print m.name
print m.models[0].name
for i in m.models[0].sources:
print " PJLink name: %s code: %s Text: %s" % \
(i.pjlink_name, i.pjlink_code, i.text)
--
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/d/optout.
