pandas通过pymysql直接读取mysql库中的表,并写出到excel(会覆盖原有的excel!):
查询中含有中文时需加入use_unicode=True, charset="utf8"。
import pandas as pd
import pymysql
a=pymysql.connect(host='000.00.000.000',port=3306,user='0000',passwd='00000',db='acct',use_unicode=True, charset="utf8")
sql='select %s from %s where month(last_update)=%s limit 100'%('last_update','acct_info201609',10)
d=pd.read_sql(sql,con=a)
print(d)
a.close()
with pd.ExcelWriter(r'D:\baidu\Desktop\1.xls') as writer:
d.to_excel(writer,sheet_name ='2')
d.to_excel(writer, sheet_name='4')
pd.to_sql报错“mysql have go away”,可能是加载区过大,可将chunksize=10000改为chunksize=3000