SQLAlchemy ORM how to join query with table

Question

I have an ORM model in SQLAlchemy that stores some prices (by product_id and date) and exchange rates (by currency_from, currency_to and date). I have a function that returns a query object. The query has id, date, currency and price columns. Optionally the prices can be converted to another currency. I would like to take this query object as an argument and join it with ExRates table, to convert to different currency.

A workaround I use is to read the parts that I need to pandas.DataFrame and merge. Another tricky part is that sometimes I have a price for Saturday, but no ex rate. In that case last available ex rate should be used. Again in pandas I can do it with .reindex and .fillna(method='ffill'), but I would like to implement it purely in SQL.

For reference below are bits from my code:

class Price(ReprMixin, GenFromDfMixin, ToDfMixin, Base):
    __tablename__ = 'prices'

    benchmark_id = Column(String, ForeignKey('benchmarks.benchmark_id'), index=True, primary_key=True)
    date = Column(Date, index=True, primary_key=True)
    price = Column(Float)
    return_ytd = Column(Float)

class Benchmark(ReprMixin, GenFromDfMixin, ToDfMixin, Base):
    __tablename__ = 'benchmarks'

    benchmark_id = Column(String, primary_key=True)
    index_name = Column(String)
    currency = Column(String)
    ...

class ExRate(ReprMixin, GenFromDfMixin, ToDfMixin, Base):
    __tablename__ = 'rates'

    cur_from = Column(String, primary_key=True)
    cur_to = Column(String, primary_key=True)
    date = Column(Date, primary_key=True, index=True)
    ex_rate = Column(Float)

def _query_by_id_and_date(benchmark_id, date_min=None, date_max=None):
    if date_min is None:
        date_min = datetime.date(1900, 1, 1)
    if date_max is None:
        date_max = datetime.date(2222, 1, 1)
    prices = dal.session.query(Price.benchmark_id, Price.date, Price.price, Benchmark.currency).join(Benchmark)\
        .filter(Price.benchmark_id == benchmark_id).filter(Price.date.between(date_min, date_max))
    return prices

def _currency_convert(q, to_curr):
    pass  
# I would like to call this function like this:
# _currency_convert(_query_by_id_and_date('some_id'), 'EUR')
# and get the same 4 column query, but with prices converted to a new currency

Create a subquery of ExRate table using the filter on currency and join your prices table with subquery. — mad_
– mad_, Commented Aug 24, 2018 at 16:26

mad_ · Accepted Answer · 2018-08-24 16:40:06Z

1

I am not sure if I have understood your problem correctly. I think you are trying to convert the prices based on different currencies. Give it try

from sqlalchemy import and_    
query=session.query(Price.benchmark_id,Price.date,Price.return_ytd,Price.price*ExRate.ex_rate)\
        .join(Benchmark)\
        .join(ExRate, ExRate.cur_from==Benchmark.currency)\
        .filter(and_(ExRate.cur_from==Benchmark.currency, ExRate.cur_to=='EUR')

answered Aug 24, 2018 at 16:40

mad_

8,2832 gold badges32 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MJB Over a year ago

Just came back from holiday, I will give it a try today. I can already see one problem - the ex rates are also per date and your query does not seem to take it into consideration. I will also experiment with the subquery you suggested in comment.

MJB Over a year ago

Is there any advantage of using and_ over chaining .filter(...).filter(...)?

Collectives™ on Stack Overflow

SQLAlchemy ORM how to join query with table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related