pandas如何读取mysql数据

目录

• pandas读取mysql数据
• pandas读取mysql数据到DataFrame
• 方法一
• 方法二
• 总结

pandas读取mysql数据

def get_data():
	conn = pymysql.connect(hos开发者_Hbaset=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)
        resandroidult_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 resultsdbYfHSxmb

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
  returhttp://www.devze.comn df

方法二

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

pd 1.9python以后的版本，除了sqllite，均需要通过sqlalchemy来设置

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。