python - REPLACE rows in mysql database table with pandas DataFrame -

python version - 2.7.6

pandas version - 0.17.1

mysqldb version - 1.2.5

in database ( product ) , have table ( xml_feed ). table xml_feed huge ( millions of record ) have pandas.dataframe() ( processed_df ). dataframe has thousands of rows.

now need run

replace table product.xml_feed (col1, col2, col3, col4, col5), values (processed_df.values) 

question:-

is there way run replace table in pandas? checked pandas.dataframe.to_sql() not need. not prefer read xml_feed table in pandas because huge.

till version (0.17.1) unable find direct way in pandas. reported feature request same. did in project executing queries using mysqldb , using dataframe.to_sql(if_exists='append')

suppose

1) product_id primary key in table product

2) feed_id primary key in table xml_feed.

simple version

import mysqldb import sqlalchemy import pandas  con = mysqldb.connect('localhost','root','my_password', 'database_name') con_str = 'mysql+mysqldb://root:my_password@localhost/database_name' engine = sqlalchemy.create_engine(con_str) #because using mysql df = pandas.read_sql('select * product', con=engine) df_product_id = df['product_id'] product_id_str = (str(list(df_product_id.values))).strip('[]') delete_str = 'delete xml_feed feed_id in ({0})'.format(product_id_str) cur = con.cursor() cur.execute(delete_str) con.commit() df.to_sql('xml_feed', if_exists='append', con=engine)# can use flavor='mysql' if not want create sqlalchemy engine depreciated 

please note:- replace [into] syntax allows insert row table, except if unique key (including primary key) violation occurs, old row deleted prior new insert, hence no violation.