Pandas + Mysql

DATAFRAME写入MySQL

from sqlalchemy import create_engine engine=create_engine("mysql+pymysql://root:huojianpao949@localhost:3306/test")

import pandas as pd import numpy as np np.random.seed(24) m=np.random.randint(0,100,size=(100,5)) df=pd.DataFrame(m,columns=['departmen','date','proj_class','support_mode','content'])

sql_db=pd.read_sql('test',con=engine)

df.to_sql(name='test_df',con=engine)

从MYSQL表到DATAFRAME

方法一：

-- coding:utf-8 --

from sqlalchemy import create_engine

class mysql_engine(): user='**' passwd='*' host='' port = '' db_name='***' engine = create_engine('mysql://{0}:{1}@{2}:{3}/{4}?charset=utf8'.format(user,passwd,host,port,db_name))

def get_data(sql): pg_enine=mysql_engine() try: with pg_enine.engine.connect() as con, con.begin(): df=pd.read_sql(sql,con)# 获取数据 con.close() except: df=None return df

方法二：

conn = MySQLdb.connect(host="**",user="*",passwd="",db='',port = ,charset="utf8") sql = "select * from *** limit 3" df = pd.read_sql(sql,conn,index_col="id")