【python】pandas读取mysql数据

 一、依赖安装

pip install pymsql
pip install pandas -i https://pypi.tuna.tsinghua.edu.cn/simple

二、mysql中test库下的walk_info表信息

mysql> select * from walk_info;
+-----------+---------------------+-----------------+------------+
| user      | counttime           | walkplace       | newwalknum |
+-----------+---------------------+-----------------+------------+
| zhangSan  | 2020-07-02 13:44:43 | 操场西南门      |          1 |
| liSi      | 2020-07-02 13:44:45 | 操场西南门      |          3 |
| wangWu    | 2020-07-02 13:44:47 | 操场东北门      |          3 |
| xiaoQiang | 2020-07-02 13:44:49 | 操场西北门      |          2 |
| zhangSan  | 2020-07-02 13:44:51 | 操场西门        |          3 |
| liSi      | 2020-07-02 13:44:53 | 操场东南门      |          1 |
| wangWu    | 2020-07-02 13:44:55 | 操场东南门      |          3 |
| xiaoQiang | 2020-07-02 13:44:57 | 操场西门        |          1 |
| zhangSan  | 2020-07-02 13:44:59 | 操场北门        |          1 |
+-----------+---------------------+-----------------+------------+
9 rows in set (0.00 sec)

三、python代码实现

import pymysql
import pandas as pd
'''
pandas直接读取mysql
'''

# 打开数据库连接
conn = pymysql.connect(host="192.168.xxx.xx",
                       port=3306,
                       user="root",
                       passwd="xxx",
                       db="test")
sql = "select * from walk_info"
df = pd.read_sql_query(sql, conn)
# 关闭连接
conn.close()
print(df)

运行结果： 

        user            counttime walkplace  newwalknum
0   zhangSan  2020-07-02 13:44:43     操场西南门           1
1       liSi  2020-07-02 13:44:45     操场西南门           3
2     wangWu  2020-07-02 13:44:47     操场东北门           3
3  xiaoQiang  2020-07-02 13:44:49     操场西北门           2
4   zhangSan  2020-07-02 13:44:51      操场西门           3
5       liSi  2020-07-02 13:44:53     操场东南门           1
6     wangWu  2020-07-02 13:44:55     操场东南门           3
7  xiaoQiang  2020-07-02 13:44:57      操场西门           1
8   zhangSan  2020-07-02 13:44:59      操场北门           1

Process finished with exit code 0