SQLAlchemy question for polymorphic reference /u/pleides101 Python Education

Hi. Started to learn python and am working on a small project to build a trading system. I took help from various coding AI assistants to build up the different pieces and got it to a working state. However I was using bare SQL queries for all my data management and although I tried to minimise possibilities of error and injections, it was still looking cumbersome. So I wanted to experiment with an ORM and tried sql alchemy. But due to how my table relationships are modelled Im having difficulty figuring out how to express them in a way sql alchemy can understand, none of the AI assistants have been able to help me.

Basically it is a hierarchy of Strategy -> StrategySet -> Leg. And the Strategy and Leg entities have indirect StopLoss and TrailingStopLoss relationships. I’m not using a dedicated column on the Strategy and Leg entities to reference the StopLoss and TrailingStopLoss as it wouldn’t work out. A Strategy or Leg can have multiple stop losses or Trailing Stop losses defined and hence it would be a one to many relationship. So I modelled the StopLoss and TrailingStopLoss to hold a polymorphic reference called entity type. Depending on the entity type(Strategy or Leg) the entity id could hold reference to a Strategy or Leg id. So now whenever a new StopLoss or TrailingStopLoss is to be created I want to ensure the entity type is validated to be Strategy or Leg and then ensure the corresponding entity id actually exists in Strategy or Leg. I was able to do all of this manually when I wrote the SQL queries myself but now having trouble modelling all of this in SQL alchemy. Any help or advice on what to do? Should I just create dedicated StopLoss and TrailingStopLoss entities for Strategy and Leg separately? I wanted to keep redundant columns or tables to a minimum.

Here’s the complete code(not working) for more details in case you need it – https://gist.github.com/karthik448/cd5bafb8e4cd5e37d5dfdffe26643d76

The only working code that any of the AIs could do was to add in a stop loss and trailing stop loss reference id into the strategy and leg entities and use the relationship and back-populates to add it in when a stop loss entry is created. This is obviously not going to work out for me as I want to have one to many relationship here.

Hi. Started to learn python and am working on a small project to build a trading system. I took help from various coding AI assistants to build up the different pieces and got it to a working state. However I was using bare SQL queries for all my data management and although I tried to minimise possibilities of error and injections, it was still looking cumbersome. So I wanted to experiment with an ORM and tried sql alchemy. But due to how my table relationships are modelled Im having difficulty figuring out how to express them in a way sql alchemy can understand, none of the AI assistants have been able to help me.

Basically it is a hierarchy of Strategy -> StrategySet -> Leg. And the Strategy and Leg entities have indirect StopLoss and TrailingStopLoss relationships. I’m not using a dedicated column on the Strategy and Leg entities to reference the StopLoss and TrailingStopLoss as it wouldn’t work out. A Strategy or Leg can have multiple stop losses or Trailing Stop losses defined and hence it would be a one to many relationship. So I modelled the StopLoss and TrailingStopLoss to hold a polymorphic reference called entity type. Depending on the entity type(Strategy or Leg) the entity id could hold reference to a Strategy or Leg id. So now whenever a new StopLoss or TrailingStopLoss is to be created I want to ensure the entity type is validated to be Strategy or Leg and then ensure the corresponding entity id actually exists in Strategy or Leg. I was able to do all of this manually when I wrote the SQL queries myself but now having trouble modelling all of this in SQL alchemy. Any help or advice on what to do? Should I just create dedicated StopLoss and TrailingStopLoss entities for Strategy and Leg separately? I wanted to keep redundant columns or tables to a minimum.

Here’s the complete code(not working) for more details in case you need it – https://gist.github.com/karthik448/cd5bafb8e4cd5e37d5dfdffe26643d76

The only working code that any of the AIs could do was to add in a stop loss and trailing stop loss reference id into the strategy and leg entities and use the relationship and back-populates to add it in when a stop loss entry is created. This is obviously not going to work out for me as I want to have one to many relationship here.