How to make Pandas Medium DataFrame Data import to MySQL In the database ?
So how to achieve it ?
To prevent code redundancy, I wrote a class directly
When using, you only need to modify the passed parameters
import pymysql
from sqlalchemy import create_engine
import pandas as pd
""" Paste directly into brackets host='127.0.0.1', port='3306', user="root", pasword="123456", db=" Database name ", tb=" Table name ", df="dataframe data " """
#pandas dataframe Data is loaded directly into mysql in
class PDTOMYSQL:
def __init__(self,host,user,pasword,db,tb,df,port='3306'):
self.host = host
self.user = user
self.port = port
self.db = db
self.password = pasword
self.tb = tb
self.df = df
sql = 'select * from '+self.tb
conn = create_engine('mysql+pymysql://'+self.user+':'+self.password+'@'+self.host+':'+self.port+'/'+self.db)
df.to_sql(self.tb, con=conn, if_exists='replace')
self.pdata = pd.read_sql(sql,conn)
def show(self):# Show datasets
return print(self.pdata)
Test it :
#data It's test data
data = {
'state':['Ohio','Ohio','Ohio','Nevada','Nevada'],
'year':[2000,2001,2002,2001,2002],
'pop':[1.5,1.7,3.6,2.4,2.9]
}
# hold data convert to dataframe data
frame = pd.DataFrame(data)
t = PDTOMYSQL(
host='127.0.0.1',
port='3306',
user="root",
pasword="123456",
db="data_base",
tb="t_demo",
df=frame)
t.show()