SQLAlchemy Bulk Insert with ForeignKey

Question

I am new to sqlalchemy. I have the following model where securities and prices have 1 to many relationship:

class Security(Base):
        __tablename__ = "securities"
        id = sa.Column(sa.Integer, primary_key=True)
        symbol = sa.Column(sa.String)
        name = sa.Column(sa.String)

class Price(Base):
        __tablename__ = 'prices'
        id = sa.Column(sa.Integer, primary_key=True)
        security_id = sa.Column(sa.Integer, sa.ForeignKey('securities.id'))
        security = relationship("Security", backref = "securities")
        open_price = sa.Column(sa.Float)
        high_price = sa.Column(sa.Float)
        low_price = sa.Column(sa.Float)
        close_price = sa.Column(sa.Float)
        volume = sa.Column(sa.BigInteger)

I want to add prices for around 700 securities with each security has 500 prices data. The price data come from pandas dataframe (yfinance) which I convert to a list before inserting them to postgres database through sqlalchemy.

Following code works fine:

security =s.query(Security).filter(Security.symbol==symbol).first()
price_list = price_df.values.tolist()
for i in price_list:
    record = Price(
        open_price=i[0],
        high_price=i[1],
        low_price=i[2],
        close_price=i[3],
        volume=i[4],
        security=security
    )
    s.add(record)
s.commit()

However, it is not really fast. So, I tried using following code:

security =s.query(Security).filter(Security.symbol==symbol).first()
price_list = price_df.values.tolist()    
engine.execute(
        Price.__table__.insert(),
        [{"open_price": i[0],"high_price": i[1],"low_price": i[2],"close_price": i[3],"volume": i[4],"security":security} for i in price_list]
    )

The second method run 2x faster but the security (foreign key) is not stored (missing) from prices table. Is there any way to use bulk insert method to insert data with foreign key?

edit:

Tried bulk_save_objects() as suggested by Craicerjack in the comment

security =s.query(Security).filter(Security.symbol==symbol).first()
price_list = price_df.values.tolist()
objects = [
        Price(
            open_price=i[0],
            high_price=i[1],
            low_price=i[2],
            close_price=i[3],
            volume=i[4],
            security=security
        ) for i in price_list
    ]
s.bulk_save_objects(objects)

It run faster but create duplicate (one without foreignkey and the other with foreignkey) for each entry like following:

id |   security_id | open_price | high_price
----+--------------+-------------+------------
  1 |              |      10600 |      11000 |
  2 |              |      11100 |      11150 |
  3 |            1 |      10600 |      11000 |
  4 |            1 |      11100 |      11150 |

https://stackoverflow.com/questions/3659142/bulk-insert-with-sqlalchemy-orm?rq=1 — Craicerjack, Sep 17 '20 at 22:16
i tried to use above method but it creates double entry in prices table. First entry with missing foreign key followed by second entry containing the same data except it has foreign key. — amin, Sep 18 '20 at 22:23

SQLAlchemy Bulk Insert with ForeignKey

0 Answers0