[sqlalchemy] Re: Multiple tables and foreignkey constraints
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(usqlite:///./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.
[sqlalchemy] Re: Multiple tables and foreignkey constraints
Thanks for the help Jonathan and Michael.
The below seems to work for what I'm doing. Let me know what normal forms
I'm violating with it :)
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(usqlite:///./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,
Re: [sqlalchemy] Multiple tables and foreignkey constraints
If I'm understanding correctly, something like the following: # Keep track of manufacturer names manufacturers_table = Table(u'manufacturer', metadata, Column(u'id', Integer, primary_key=True, Column(u'name', String(20)) ) # Keep track of model names models_table = Table(u'model', metadata, Column(u'id', Integer, primary_key=True, Column(u'name', String(20)) ) # Keep track of inputs available to models sources_table = Table(u'source', metadata, Column(u'id', Integer, primary_key=True, Column(u'pjlink', String(2)), Column(u'text', String(20)) ) # Foreign key table for mfgr/model/sources projector_table = Table(u'projector', metadata, Column(u'manufacturer_id', Integer, primary_key=True, ForeignKey(u'manufacturer.id')), Column(u'model_id', Integer, primary_key=True, ForeignKey(u'model.id')), Column(u'source_id', Integer, primary_key=True, ForeignKey(u'source.id')), ) # Persistent storage for installed projectors # model_id would be an index for model_table.id installed_table = Table(u'installed', metadata, Column(u'id', Integer, primary_key=True), Column(u'name', String(20)), Column(u'model_id', Integer) ) Or am I still off in left field? -- 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.
Re: [sqlalchemy] Multiple tables and foreignkey constraints
On Monday, July 21, 2014 8:38:54 AM UTC-7, Jonathan Vanasco wrote: I agree with what Mike said, but I would just suggest renaming projector_table to something like purchased_table or inventory_table. Everything in models is a different model of a projector, so the table names are a bit confusing. snip Short answer: manufacturer/model tables are projectors, and sources are the video inputs available for that particular model. The sources table is going to be used to keep track of what the manufacturer has listed in their documentation for selecting a particular video source. The projectors_table is persistent storage use for what projectors that the user can control via networking. Basic information is some text columns that the end-user can assign for their own (short) notes, but the model_id field is used so that the text can be matched with the projector that they have control over. Longer answer. Column 1 is PJLink code for selecting that input, the rest of the line is text. Example for Eiki model LC/XL200 projector: (Text is from Eiki webpage control) 11 RGB (pc analog) 12 RGB (Scart) 13 RGB (PC Digital) Example Hitachi CP-X2514: (Text is from PJLink user manual from Hitachi) 11 Computer IN 1 12 Computer IN 2 13 Component As noted, different manufacturers may have different text for the same inputs, so the sources table is just keeping track of the text for the input source - hopefully text that the end-user does not find too confusing :) This is not an inventory program. The part I'm looking to add is basic projector control to a program that will send some output via a second computer video output to a projector. One point is that there may be multiple computers connected to a single projector via multiple inputs (sources). I was thinking of having manufacturer/model/source tables so the end-user doesn't have to re-enter the information if they just happen to have multiple projectors with the same model - as a side possibility, also having an xml file with this information available that can be imported into those tables. When the end-user adds a projector to the program, they can select the projector by manufacturer (Eiki) - model (LC/XL200) - then the sources (video inputs) would be added to the projector class so they can then select the video source to display. Since using PJLink codes would be confusing (What does input 11 select?), the text pulled from the sources table would then let them use the familiar text (documented in their projector user manual - like RGB (pc analog) ) to select the source. An example xml file for importing would look something like: projector manufacturer='Eiki' model name='LC/XL200' source pjlink='11'RGB (PC analog)/source source pjlink='12'RGB (Scart)/source source pjlink='13'RGB (PC digital)/source /model model name=.'...' /model /projector With the importing in mind, there still has to be the option for the end-user to manually add an entry for projector manufacturer/model/sources (technical note, with PJLink, I can retrieve the manufacturer name, model name, and the available sources via the network, just not the text for the sources). With that, then Jonathan's suggestion of removing the foreign_key on the sources table and create a 4th table that keeps track of the model-sources constraints. As for the projectors_table, instead of a foreign_key just use an integer column as an index into the models_table would be the suggestion? projector_table = Table(u'projector', metadata, Column(u'id', Integer, primary_key=True), Column(u'model_2_source_id', Integer) ) The way things are looking, looks like I'm going to have multiple selects. Not an issue, since they will only be used on program startup, not during normal operations. -- 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.
Re: [sqlalchemy] Multiple tables and foreignkey constraints
Forgot to mention that during typical operation, the only time the database will be accessed would be during down time (add/delete) or program startup (retrieve list of projectors to control) - not during a presentation. -- 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.
