pandas读取mysql数据
def get_data():
conn = pymysql.connect(host=RSYS_HOST,
port=RSYS_PORT,
user=RSYS_USER,
passwd=RSYS_PASSWORDD,
db=RSYS_DB_NAME,
charset='utf8')
results = None
try:
read_result = pd.read_sql(sql, conn)
result_json = json.loads(read_result.reset_index().to_json(orient='records', force_ascii=False))
logger.info(sql)
return result_json
except Exception as e:
# 发生错误时回滚
logger.error("Error: unable to fecth data: %s,%s" % (repr(e), sql))
conn.rollback()
finally:
conn.close()
return results
pandas读取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")
print df
pd 1.9以后的版本,除了sqllite,均需要通过sqlalchemy来设置
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。