I have used a lot of python pandas and MySQL. but in separated way. in order to achieve better automation task, I start getting to know pymysql.
For me, I found some of database action I can achieve only by pymysql+pandas action. however, some of the database action, I have to use sqlachemy to create engine. That make me confused. what is engine from sqlalechmy in there and difference/relationship between engine and connection
based on the official document pymysql: This package contains a pure-Python MySQL client library.
For my understanding, basically, it is the driver which connect between python to mysql. since pandas have read_sql method method,
so i should have achieved all database action by combining those two.
For example, based on the following
etl_conn = pymysql.connect(host = host, user = user,password = passwd,db = db_health_plan)
query = 'select * from HEALTH_PLAN.WD_PNL_MONTHLY'
pd.read_sql(query, etl_conn, index_col = 'index')
and I can create a table by using pandas.io and pymysql
from pandas.io import sql
create_table_query ='CREATE TABLE WD_PNL_MONTHLY_05052020 AS SELECT * FROM
HEALTH_PLAN.WD_PNL_MONTHLY';
sql.execute(create_table_query, etl_conn)
However, I failed when I tried to insert row into table.
total.to_sql('WD_PNL_MONTHLY', etl_conn,
schema= 'HEALTH_PLAN', if_exists='append',
index=True, index_label=None, chunksize=None, dtype=None)
in order me to achieve task, I have to create engine link sqlalchemy and pymysql
db_connection_str = 'mysql+pymysql://****'
from sqlalchemy import create_engine
sql_engine = create_engine(db_connection_str)
Then the following one works
total.to_sql('WD_PNL_MONTHLY', sql_engine,
schema= 'HEALTH_PLAN', if_exists='append',
index=True, index_label=None, chunksize=None, dtype=None)
