bulk insert rows with foreign key and automatically add row to the referenced table

Question

I have a one to many relationship between a stock prices table and a stock symbols (for example "AAPL" for apple) table.

I am receiving from a user a stock symbol and a date range to be retrieved. If the symbol is new I would like it to be added automatically to the symbols table.

Note that if the symbol does exist i need to somehow (automatically or manually) retrieve the symbol id.

The way that I am receiving the prices is a list of named tuples:

[Price(time=datetime.datetime(2019, 8, 16, 3, 0), open=117.82), ...]

What i am doing now is checking if the symbol exists in the table and then inserting it if it doesn't using this function. After getting the id I recreate the list but this time with stock symbol id.

Is there a better way?

All the answers I saw involved each price to have an ORM instance, and I don't want to do it as it would be a unnecessary waste of time because i simply want to bulk insert them.

the models I am using

class Asset(Base):

    id = Column(Integer, primary_key=True)
    asset_symbol = Column(String)


class RawBarsCollection(Base):
    info_datetime = Column(DateTime, primary_key=True)
    asset_id = Column(Integer, ForeignKey(Asset.id), primary_key=True)
    open_price = Column(Float)

    asset = relationship(Asset.__name__)

bulk insert rows with foreign key and automatically add row to the referenced table

0 Answers0