How insert the dataframe output to mysql

Question

import pymysql
import pandas as pd
db = pymysql.connect('localhost', 'testuser', 'test123', 'world')
df1 = pd.read_sql('select * from country limit 5', db)
df1

I need to create a table name with country2 and update the df1 out to country2

Answer 1

Definitely check out SqlAlchemy. Use SqlAlchemy to write a Mysql interation class. SqlAlchemy enables using python to connect database. Encoding your dataframe into a upsert sql string. And then use cursor.execute(query_string) to do the upsert.

Answer 2

engine = sqlalchemy.create_engine(
    'mysql+mysqlconnector://user:pwd@hostname/db_name',
    connect_args={'auth_plugin': 'mysql_native_password'})

sample_sql_database = df.to_sql('table_name', con=engine)

There is an option to "append" the contends from data frame or "replace" also

sample_sql_database =  df.to_sql('table_name', engine, if_exists='replace')
sample_sql_database =  df.to_sql('table_name', engine, if_exists='append')

Reference :

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html