Dynamically assigning multiple relationship on a model in SQLAlchemy

Question

I am trying to generate dynamic sql queries based on certain conditions in SQLAlchemy. I have a model that is gets queried constantly based on different conditions

The code goes like this:

Models:

from sqlalchemy import Column, ForeignKey, String, create_engine, INTEGER
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from database import db_url

engine = create_engine(db_url, echo=True)
session_maker = sessionmaker(bind=engine)

session = session_maker()

Base = declarative_base()


class Fund(Base):
    __tablename__ = 'fund'

    port_id = Column(String(4), primary_key=True, nullable=False)

    @property
    def serialize(self):
        attrs = list(map(lambda x: x.serialize, self.attributes))

        return {
            'portId': self.port_id,
            'attributes': attrs
        }


class FundAttribute(Base):
    __tablename__ = 'attribute'

    id = Column(INTEGER, primary_key=True)
    fund_port_id = Column(String(4), ForeignKey('fund.port_id'))
    attribute_name = Column(String)

    @property
    def serialize(self):
        return {
            'attributeName': self.attribute_name
        }

main.py

from flask import Flask, jsonify
from sqlalchemy.orm import relationship
from models import Fund, session


app = Flask(__name__)

funds = ['0001', '0002', '0003']
attributes_1 = [
    'Short Name',
    'Full Name'
]


attributes_2 = [
    'Retirement Year',
    'Currency'
]

flag = False


@app.route('/')
def get_funds():
    global flag

    flag = not flag

    print('flag===', flag)
    join_condition = ''
    if flag:
        join_condition = f'and_(Fund.port_id==FundAttribute.fund_port_id, FundAttribute.attribute_name.in_({attributes_1}))'
        Fund.attributes = relationship('FundAttribute', primaryjoin=join_condition, lazy='selectin')
    else:
        join_condition = f'and_(Fund.port_id==FundAttribute.fund_port_id, FundAttribute.attribute_name.in_({attributes_2}))'
        Fund.attributes = relationship('FundAttribute', primaryjoin=join_condition, lazy='selectin')

    print('join_condition', join_condition)
    res = session.query(Fund).filter(Fund.port_id.in_(funds)).all()
    res = list(map(lambda x: x.serialize, res))

    print(res)
    return jsonify(res)


if __name__ == '__main__':
    app.run(port=8081)

I have setup the code in such a way that it runs both conditions alternately. When I run the code for the first time, it creates a join condition for attributes_1 Here's the log:

flag=== True

join_condition == and_(Fund.port_id==FundAttribute.fund_port_id, FundAttribute.attribute_name.in_(['Short Name', 'Full Name']))

SELECT mv_funds_common.port_id AS mv_funds_common_port_id FROM mv_funds_common WHERE mv_funds_common.port_id IN (%(port_id_1_1)s, %(port_id_1_2)s, %(port_id_1_3)s)

[generated in 0.00045s] {'port_id_1_1': '0001', 'port_id_1_2': '0002', 'port_id_1_3': '0003'}

SELECT mv_funds_common_1.port_id AS mv_funds_common_1_port_id, mv_fund_attribute.id AS mv_fund_attribute_id, mv_fund_attribute.fund_port_id AS mv_fund_attribute_fund_port_id, mv_fund_attribute.attribute_name AS mv_fund_attribute_attribute_name FROM mv_funds_common AS mv_funds_common_1 JOIN mv_fund_attribute ON mv_funds_common_1.port_id = mv_fund_attribute.fund_port_id AND mv_fund_attribute.attribute_name IN (%(attribute_name_1_1)s, %(attribute_name_1_2)s) WHERE mv_funds_common_1.port_id IN (%(primary_keys_1)s, %(primary_keys_2)s)

[generated in 0.00022s] {'attribute_name_1_1': 'Short Name', 'attribute_name_1_2': 'Full Name', 'primary_keys_1': '0001', 'primary_keys_2': '0002'}

In theory. when I run the code again, it should run the else part, i.e for attributes_2. Code enters the else part, and creates the join_condition with attributes_2:

I verified that through the print statements:

flag=== False

join_condition == and_(Fund.port_id==FundAttribute.fund_port_id, FundAttribute.attribute_name.in_(['Retirement Year', 'Currency']))

However, that doesn't get assigned as relationship. It only prints attributes_1 in the query when SQLAlchemy creates the relationship.

What caught my attention was [cached since 6.906s ago] (Check printed logs and generated sql statements below). Does SQLAlchemy caches the query, if yes, is there a way to disable this cache. May be the relationship is not getting generated dynamically.

SELECT mv_funds_common.port_id AS mv_funds_common_port_id FROM mv_funds_common WHERE mv_funds_common.port_id IN (%(port_id_1_1)s, %(port_id_1_2)s, %(port_id_1_3)s)

[cached since 6.917s ago] {'port_id_1_1': '0001', 'port_id_1_2': '0002', 'port_id_1_3': '0003'} main.py:37: SAWarning: Property Fund.attributes on mapped class Fund->mv_funds_common being replaced with new property Fund.attributes; the old property will be discarded Fund.attributes = relationship('FundAttribute', primaryjoin=join_condition, lazy='selectin')

SELECT mv_funds_common_1.port_id AS mv_funds_common_1_port_id, mv_fund_attribute.id AS mv_fund_attribute_id, mv_fund_attribute.fund_port_id AS mv_fund_attribute_fund_port_id, mv_fund_attribute.attribute_name AS mv_fund_attribute_attribute_name FROM mv_funds_common AS mv_funds_common_1 JOIN mv_fund_attribute ON mv_funds_common_1.port_id = mv_fund_attribute.fund_port_id AND mv_fund_attribute.attribute_name IN (%(attribute_name_1_1)s, %(attribute_name_1_2)s) WHERE mv_funds_common_1.port_id IN (%(primary_keys_1)s, %(primary_keys_2)s)

[cached since 6.906s ago] {'attribute_name_1_1': 'Short Name', 'attribute_name_1_2': 'Full Name', 'primary_keys_1': '0001', 'primary_keys_2': '0002'} [{'portId': '0001', 'attributes': []}, {'portId': '0002', 'attributes': []}]