如何把Pandas中的Dataframe数据导入到MySQL数据库中？
那么如何实现呢?
实现代码：
为了防止代码冗余我就直接写了个类
使用的时候只需要修改传递的参数就好了
import pymysql
from sqlalchemy import create_engine
import pandas as pd
""" 直接粘贴到括号中中
host='127.0.0.1',
port='3306',
user="root",
pasword="123456",
db="数据库名",
tb="表名",
df="dataframe数据"
"""
#pandas dataframe数据直接加载到mysql中
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):#显示数据集
return print(self.pdata)
测试一下:
#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]
}
#把data转换成dataframe数据
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()
