Need To Edit Subset Of Rows From Mysql Table Using Pandas Dataframe

I'm in the process of trying to alter a table in my database. However I am finding it difficult using the to_sql method provided by Pandas. My price_data Dataframe looks something

Solution 1:

Consider a temp table with exact structure as final table but regularly replaced and will then be used to update existing final table. Try using sqlalchemy engine for both operations.

Specifically, for the latter SQL you would use an UPDATE JOIN query between temp and final table. Below assumes you use pymysql module (adjust as needed):

import pymysql
from sqlalchemy import create_engine
...

engine = create_engine("mysql+pymysql://user:password@hostname:port/database")

# PANDAS UPLOAD
price_data.to_sql(name='clean_prices_temp', con=engine, if_exists='replace', index=False)

# SQL UPDATE (USING TRANSACTION)with engine.begin() as conn:     
    conn.execute("UPDATE clean_prices_final f" +
                 " INNER JOIN clean_prices_temp t" +
                 " ON f.symbol_id = t.symbol_id" +
                 " AND f.price_date = t.price_date" +
                 " SET f.open_price = t.open_price," +
                 "     f.high_price = t.high_price," +
                 "     f.low_price = t.low_price," +
                 "     f.close_price = t.close_price," +
                 "     f.adj_close_price = t.adj_close_price;")

engine.dispose()