This is a sort of response to EjbTernaryRelationshipExample and OrTernaryRelationshipExample.
#!/usr/bin/env python2
import sqlalchemy as sa;
de = sa.create_engine("postgres:///3way")
md = sa.BoundMetaData(de)
#md.engine.echo = True
# inspect the table metadata
for t in ['suppliers', 'parts', 'projects',
'r_projectsparts', 'r_suppliersparts', 'r_supplierspartsprojects']:
globals()[t] = sa.Table(t, md, autoload=True)
# ORM session
sn = sa.create_session(bind_to=de)#, echo_uow=True)
# Full ORM, although it's not needed for this problem
class Supplier(object):
def __init__(self, sname):
self.supplier_name = sname # missing Ruby's Struct here
class Project(object):
def __init__(self, pname):
self.project_name = pname
class Part(object):
def __init__(self, pcode):
self.part_code = pcode
class SuppliedPart(object):
def __init__(self, supp, part):
self.part = part
self.supplier = supp
class RequiredPart(object):
def __init__(self, proj, part):
self.part = part
self.project = proj
class PotentialPart(object):
def __init__(self, reqp, supp):
# assuming you want this caught before the db sees it
if reqp.part != supp.part:
raise Exception("Parts mismatch")
self.reqp = reqp
self.supp = supp
class AcceptedPart(PotentialPart):
def __init__(self, reqp, supp):
PotentialPart.__init__(self, reqp, supp)
sa.mapper(Part, parts)
sa.mapper(Supplier, suppliers)
sa.mapper(Project, projects)
sa.mapper(SuppliedPart, r_suppliersparts, properties = {
'part': sa.relation(Part, backref='splist', lazy=False),
'supplier': sa.relation(Supplier, backref='splist', lazy=False)},)
sa.mapper(RequiredPart, r_projectsparts, properties = {
'part': sa.relation(Part, backref='rplist', lazy=False),
'project': sa.relation(Project, backref='rplist', lazy=False)},)
sa.mapper(AcceptedPart, r_supplierspartsprojects, properties = {
'reqp': sa.relation(RequiredPart, backref=sa.backref('ap', uselist=False),
lazy=False), # 1:1, but may be None
'supp': sa.relation(SuppliedPart, backref=sa.backref('ap', uselist=False),
lazy=False)},)
# this is more or less a client-side view
big_join = sa.join(projects, r_projectsparts).join(parts). join(r_suppliersparts).join(suppliers) # problem with col names
named_join = sa.select([projects.c.project_name, parts.c.part_code,
suppliers.c.supplier_name], from_obj=[big_join]). alias('potparts')
# the fields are only needed if you want traversals or insertions
sa.mapper(PotentialPart, named_join, properties = {
'reqp': sa.relation(RequiredPart, backref='pplist',
primaryjoin=sa.and_(named_join.c.part_code==
r_projectsparts.c.part_code,
named_join.c.project_name==
r_projectsparts.c.project_name),
lazy=False),
'supp': sa.relation(SuppliedPart, backref='pplist',
primaryjoin=sa.and_(named_join.c.part_code==
r_suppliersparts.c.part_code,
named_join.c.supplier_name==
r_suppliersparts.c.supplier_name),
lazy=False)},)
# ORM ends here, insert some data now to show that it works as expected:
# all these assignments can be avoided with metaclasses, but KISS for now
# SA only checks shallow identity, so you'd need a weakref hash, yuck...
hammer = Part('hammer')
nails = Part('nails')
wood = Part('wood')
game = Part('game')
spice_rack = Project('spice rack')
nice_meal = Project('nice meal')
acme = Supplier('ACME')
forrest = Supplier('Forrest')
acme_hammer = SuppliedPart(acme, hammer)
acme_nails = SuppliedPart(acme, nails)
forrest_wood = SuppliedPart(forrest, wood)
forrest_game = SuppliedPart(forrest, game)
sr_hammer = RequiredPart(spice_rack, hammer)
sr_wood = RequiredPart(spice_rack, wood)
sr_nails = RequiredPart(spice_rack, nails)
nm_game = RequiredPart(nice_meal, game)
sn.save(AcceptedPart(sr_hammer, acme_hammer))
# ACME nails are substandard, so they're not accepted
## sn.save(AcceptedPart(sr_nails, acme_nails))
sn.save(AcceptedPart(sr_wood, forrest_wood))
sn.save(AcceptedPart(nm_game, forrest_game))
sn.flush() # commit
def parts_that_cannot(pname):
"""
List all parts that suppliers cannot provide for the project
and that the project actually requires, and only for those
providers that provide at least one part; also count how many
parts they *can* supply, and the total number of parts needed
for the project: (total, [(supplier, [count, missing]), ...])
"""
proj = sn.query(Project).selectone_by(project_name=pname)
requires = frozenset((rp.part_code for rp in proj.rplist))
hres = {},
for part in sn.query(PotentialPart).select_by(project_name=pname):
he = hres.setdefault(part.supplier_name, [0, []])
he[0] = he[0] + 1
he[1].append(part.part_code)
hres[part.supplier_name] = he
# do the set complement w/o SQL, probably more efficient
# if most projects use only a small subset of the parts
for hk, hv in hres.iteritems():
hres[hk][1] = requires - frozenset(hv[1])
res = hres.items()
res.sort(key=lambda x: x[1][0], reverse=True)
return (len(requires), res)
def parts_that_cannot_meth(self):
return parts_that_cannot(self.project_name)
Project.suppliers_with_parts_they_cannot_provide = parts_that_cannot_meth
print "---spice rack---", parts_that_cannot('spice rack')
print "---nice meal---", nice_meal.suppliers_with_parts_they_cannot_provide() # OO way