CODEWITHSUNDEEP
pythonsqlalchemy
Graeme Stuart
Graeme Stuart

Reputation: 6053

SQLAlchemy modelling a complex relationship using reflection

I am querying a proprietary database which is maintained by a third party. The database has many tables each with large numbers of fields.

My problem refers to three tables of interest, Tree, Site and Meter.

The tree table describes nodes in a simple tree structure. Along with other data it has a foreign key referencing its own primary key. It also has an Object_Type field and an Object_ID field. The Site and Meter tables each have many fields.

A tree node has a one-to-one relationship with either be a meter or a site. If the Object_Type field is 1 then the Object_ID field refers to the primary key in the Site table. If it is 2 then it refers to the primary key in the Meter table.

following this example https://bitbucket.org/sqlalchemy/sqlalchemy/src/408388e5faf4/examples/declarative_reflection/declarative_reflection.py

I am using reflection to load the table structures like so

Base = declarative_base(cls=DeclarativeReflectedBase)

class Meter(Base):
    __tablename__ = 'Meter'

class Site(Base):
    __tablename__ = 'Site'

class Tree(Base):
    __tablename__ = 'Tree'
    Parent_Node_ID = Column(Integer, ForeignKey('Tree.Node_ID'))
    Node_ID = Column(Integer, primary_key=True)
    children = relationship("Tree", backref=backref('parent', remote_side=[Node_ID]))

Base.prepare(engine)

I have included the self-referential relationship and that works perfectly. How can I add the two relationships using Object_ID as the foreign key, with the appropriate check on the Object_Type field?

Upvotes: 0

Views: 488

Answers (2)

zzzeek
zzzeek

Reputation: 75127

for tree.object_id to be a foreign key that can refer either to Site or Meter, you can either have Site and Meter descend from a common base table, that is, joined table inheritance, or be mapped to the same table, that is, single table inheritance, or as someone said have Tree be mapped to two different tables as well as a common base table. This last suggestion goes well with the idea that TreeNode already has a "type" field.

The final alternative which might be easier is to use two foreign keys on TreeNode directly - site_id and meter_id, as well as two relationships, "meter" and "site"; then use a Python @property to return one or the other:

class TreeNode(Base):
   # ...

   @property
   def object(self):
       return self.meter or self.site

Upvotes: 1

Phil Cooper
Phil Cooper

Reputation: 5877

First a note on reflection. I've found myself much better off not relying on reflection.

  1. it does not require a valid database connection for you to load/work with your code
  2. it violates the python guide that explicit is better than implicit. If you look at you code you are better off seeing the elements (columns etc) rather than having them magically created outside your field of view.

This means more code but more maintainable.

The reason I suggested that is at least in part that I cannot see schema in your posting.

If you create the tables and classes in your code rather than relying on reflection, you can then have better control over mapping.

In this case you want to use polymorphic mapping

  1. create a TreeNode class as above.
  2. create SiteNode and MeterNode as subclasses

Your code would then include something like:

mapper(TreeNode,tree_table,polymorphic_on=tree_table.c.object_type)
mapper(SiteNode, site_table,inherits=TreeNode,
                 inherit_condition=site_table.c.node_id==tree_table.c.node_id,
                 polymorphic_identity=1)

Hope this helps.

Upvotes: 2

Related Questions