来存档一下搞 sql 的过程，方便以后查阅。
- 安装与配置 mysql server：https://blog.csdn.net/zhende_lan/article/details/129318514
- 在同一个网页下载 mysql workbench（数据库可视化）；
- 打开 workbench，新建一些表，用来测试：https://zhuanlan.zhihu.com/p/260139380
- python 连接 sql 的代码：
# pip install pymysql
import pymysql
import pandas as pd
def connect_db(host, user, password, database):
connect = pymysql.connect(host=host, user=user, password=password, database=database)
print("connect " + "success!" if connect else "failed!")
return connect
def get_all_data(cursor, table_name):
cursor.execute("select * from " + table_name)
results = cursor.fetchall()
description = cursor.description
# table head
df = pd.DataFrame(data=results, columns=[item[0] for item in description])
return df
if __name__ == '__main__':
# 连接数据库
conn = connect_db(host='localhost', user='root', password='123123', database='sys')
df = get_all_data(conn.cursor(), table_name='my_device')
# 保存 excel
df.to_excel('./get_db_data.xls', sheet_name='sheet1', index=False)
print('successfully save excel!')