一、依赖安装
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