Load dataframe to database table but keeping existing table structure

user_1866833 0
Viewed 156

I have dataframe which I want to load into existing mysql database table. Dataframe column names matches names in database table. Loading data works but table structure (column datatypes) are changed. Also I have in same database table additinal field TIMESTAMP type which is automticallly updated when data is inserted but somehow this field does not exist anymore after dataframe is loaded.

Here is the code how I'm connecting to mysql:

import pandas as pd
import pymysql
import pyodbc
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.ext.declarative import declarative_base

 engine = create_engine('mysql+pymysql://{0}:{1}@{2}:{3}/{4}'.format(mysql["user"], mysql["pw"], mysql["host"], mysql["port"], mysql["database"]))
 Base = declarative_base()
 Base.metadata.reflect(engine)
    
 df.to_sql('mytable', con = engine, if_exists = 'replace', chunksize=1000, index = False)
pymysql dataframe python-3.x python
1 Answers

Since you use argument, if_exists='replace', you essentially drop the previous same named table and create a new one. Hence, why the timestamp field disappears. See to_sql pandas docs:

replace: Drop the table before inserting new values.

To maintain original table structure, consider deleting all rows prior to DataFrame.to_sql with if_exists='append'.

# RUN ACTION QUERY IN TRANSACTION
with engine.begin() as cn:
   cn.execute("DELETE FROM myTable")

# APPEND TO EMPTY TABLE
df.to_sql('mytable', con=engine, if_exists='append', chunksize=1000, index=False)

Note: Be ready to handle any errors if DataFrame data types and values do not align to database tables.

user_14224510
Related