Tuesday 31 August 2021

SQLAlchemy: how to create a relationship programmatically

I'd like to create a 1:n relationship between two tables dynamically. My DB model is mapped via SQLAlchemy but due to some special features of my application I can not use the default declarative way.

E.g.

class Foo(Base):
    id = Column(Integer, autoincrement=True, primary_key=True)
    flag = Column(Boolean)

class Bar(Base):
    id = Column(Integer, autoincrement=True, primary_key=True)
    foo_id = Column(Integer, ForeignKey('foo.id'))
    # declarative version:
    # foo = relationship(Foo)

So I want to add relationship named "foo" to the mapped class "Bar" after Bar was defined and SQLAlchemy did its job of defining a mapper etc.

Update 2017-09-05: Why is this necessary for me? (I thought I could omit this because I think it mostly distracts from the actual problem to solve but since there were comments abouts it...)

First of all I don't have a single database but hundreds/thousands. Data in old databases must not be altered in any way but I want a single source code to access old data (even though data structure and calculation rules change significantly).

Currently we use multiple model definitions. Later definitions extend/modify previous ones. Often we manipulate SQLAlchemy models dynamically. We try not to have code in mapped classes because we think it will be much harder ensuring correctness of that code after changing a table many times (code must work in every intermediate step).

In many cases we extend tables (mapped classes) programatically in model X after it was initially defined in model X-1. Adding columns to an existing SQLAlchemy ORM class is manageable. Now we are adding a new reference column an existing table and a relationship() provides a nicer Python API.



from SQLAlchemy: how to create a relationship programmatically

No comments:

Post a Comment