在python环境下搭建MySQL存取dataframe数据
使用MySQL数据库的理由
一直懒得学数据库，平时都用excel存取数据，直到最近处理了一个单表就五六十万条20多个表的大数据，数据量一大excel表的存取慢到怀疑人生，于是不得不来学习一下用数据库来存取。
下载MySQL
我的环境是windows+anaconda+pycharm
此次安装MySQL版本是mysql-installer-community-8.0.35.0
打开MySQL官方网站，点击DOWNLOADS跳转
下翻找到MySQL Community (GPL) Downloads »点击跳转
找到MySQL Installer for Windows点击跳转
默认为最新版本，选择安装包更大的点击Download跳转
不用管那两个登录和注册的大按钮，点击下面No thanks, just start my download.直接下载
安装MySQL
找到下载下来的安装包点击安装
选择最后一个自定义选项之后点击Next
层层展开找到MySQL服务器版本，点击右箭头添加到右侧，然后在右侧再点击一下添加过来的版本包，下面才会出现Advanced Options（这里我就半天没找到），点击Advanced Options设置MySQL安装路径(Install Directory)和数据保存路径(Data Directory)，将两个路径记下来后面要用，设置完后点击OK再点击Next
点击Execute
等待安装完成点击Next
点击Next
点击Next
默认使用强密码身份验证（下面是使用旧式身份验证），点击Next
输入并确认密码点击Next
点击Next
点击Next
点击Execute
点击Finish
点击Next
点击Finish
安装完毕，打开MySQL验证一下是否正确安装
在Windows开始搜索输入MySQL，并选择第一个打开
输入安装时的密码
回车出现如下图说明正确安装
配置环境变量
打开MySQL安装路径下的bin文件夹，复制其路径栏中的路径
右键“此电脑”，左键属性->高级系统设置->环境变量，在系统变量中选中Path，点击下方编辑
点击“新建”，将复制的路径粘贴上去，点击“确定”
一路确定关闭窗口
配置完毕，验证是否正确配置环境变量
“win+R”键弹出运行框，输入“cmd”回车进入windows命令提示符，输入
mysql -u root -p
回车，再输入密码回车，出现下图则正确配置
至此，电脑上就安装好了MySQL并为其配置好了环境变量
我们可以像验证正确安装或验证正确配置环境变量那样时在命令行中用MySQL语句来操作使用MySQL数据库，只是没有可视化不够直观；所以也可以用一些数据可视化软件；但这都不是我这次讨论的重点，我们讨论是在python中调用MySQL去存取dataframe数据
使用MySQL
你去找连接MySQL的python代码时，会发现有几个必须提供的信息，这些连接MySQL数据库所需的信息的含义如下：
- host：这是你的MySQL服务器的主机名或IP地址。例如，我的数据库就在本地机器上，可以直接使用localhost
- username：这是你用来连接MySQL数据库的用户名
- password：这是你用来连接MySQL数据库的用户密码
- database：这是你想要连接的MySQL数据库的名称
这几个概念花了一点时间理解，host不用管直接使用localhost，因为我就是在本地机器上使用数据库；那么这个username用户名和password用户密码提到的用户是指什么呢，打个比方，它就像是excel的账户，我们可以给各个用户分配各个数据库的不同的增删改查权限，不同的用户具有对各数据库不同的权限；database数据库名就像是excel文件，其中可以有多个数据表sheet，数据表中存放我们的数据
新建和删除用户
先看看有哪些用户， 打开MySQL命令行工具（看上文验证正确安装的操作）
输入安装时的密码回车
再输入命令如下
SELECT User, Host FROM mysql.user;
回车，出现下图，即为所有的用户及其主机
发现已有4个用户，它们都是特殊用户
- mysql.infoschema是一个特殊用户。它是用于管理information_schema数据库的用户
- mysql.session是一个特殊用户。这个用户主要用于内部系统操作，比如执行系统任务和管理内部事件
- mysql.sys是一个特殊用户。这个用户主要用于运行系统级别的存储过程和视图，这些存储过程和视图通常用于性能诊断和其他管理任务
- root是一个特殊用户。这个用户通常在MySQL数据库安装时创建，拥有对数据库的完全控制权，包括创建、删除数据库，创建、删除表，创建、删除用户等所有权限。 root用户的权限非常大，因此在日常操作中，我们通常不直接使用root用户，而是创建具有特定权限的其他用户来进行操作，以防止误操作导致的问题。同时，root用户的密码也需要妥善保管，防止被他人获取
这些都是特殊用户，其中root用户的密码就是我们安装MySQL时创建的密码，现在我们需要创建特定权限的其它用户来使用数据库
使用CREATE USER语句来创建一个新的数据库用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
其中，newuser是新用户的用户名，localhost是用户的主机名，password是用户密码
再次输入查看用户命令（上下键可回退）回车查看
发现多了一个newuser用户，说明成功创建新用户
如果要删除用户则使用命令
DROP USER 'newuser'@'localhost';
其中，newuser为要删除的用户的用户名，localhost为该用户的主机名
新建和删除数据库
先看看有哪些数据库，输入命令
SHOW DATABASES;
回车如图
发现已有4个数据库，它们都是特殊数据库
- information_schema是一个特殊数据库，它提供了关于数据库服务器的元数据，包括数据库的信息，表，列，索引等。这个数据库是只读的，你不能在其中进行插入，更新或删除操作
- performance_schema是一个特殊数据库，它用于存储数据库服务器的性能参数和统计信息。这个数据库是只读的，你不能在其中进行插入，更新或删除操作
- sys是一个特殊数据库，它提供了一组视图，函数和过程，用于帮助数据库管理员更好地理解和优化MySQL的性能。sys数据库中的对象是基于performance_schema和information_schema数据库中的数据构建的
- mysql是一个特殊数据库。它包含了一些重要的系统表，用于存储用户账户信息、权限信息、系统配置等
接着我们创建自己的新数据库
输入命令并回车
CREATE DATABASE newdatabase;
其中，newdatabase是新数据库的名称
再次输入查看数据库命令（上下键可回退）回车查看
发现多了一个newdatabase数据库，说明成功创建新数据库
如果要删除数据库则使用命令
DROP DATABASE newdatabase;
其中，newdatabase为要删除的数据库的名称
成功创建数据库后，数据保存路径下的Data文件夹中会创建和数据库同名的文件夹，之后保存到此数据库的数据表会放在其中
授予所有权限
至此我们分别新建了用户和数据库，但是新用户还没有操作任何数据库的权限，我们需要给用户newuser授予对newdatabase数据库的所有权限
首先查看用户newuser的权限，输入命令
SHOW GRANTS FOR 'newuser'@'localhost';
回车如图
提示如下，newuser用户具有USAGE权限。USAGE权限实际上并不授予任何特定的权限，它只是允许用户登录到MySQL服务器
GRANT USAGE ON . TO newuser@localhost
因此我们给newuser用户授予对newdatabase数据库的所有权限
输入命令
GRANT ALL PRIVILEGES ON newdatabase.* TO 'newuser'@'localhost';
其中，newdatabase是数据库的名称，newuser是用户名，localhost是用户的主机名
然后使更改生效
FLUSH PRIVILEGES;
再次输入查看权限命令回车
增加的这一条说明更改权限成功
用python代码连接数据库存取dataframe数据
至此，我们下载安装好了MySQL，配置了其环境变量，然后在MySQL中新建了用户newuser并为其配置了密码password，也新建了数据库newdatabase，最后给用户newuser授予了对newdatabase数据库的所有权限，准备工作都做好了，现在就用python代码将dataframe数据存取到数据库里
首先需要确保安装了pymysql和sqlalchemy模块，如果没有则使用pip安装
pip install pymysql
pip install sqlalchemy
然后编写代码如下
#import pymysql
import pandas as pd
from sqlalchemy import create_engine
# 创建一个连接对象，这将连接到MySQL服务器，并返回一个Engine对象。
engine = create_engine("mysql+pymysql://newuser:password@localhost/newdatabase")
# localhost是MySQL主机名，newuser是用户名，password是密码，newdatabase是数据库名
# 从excel读取dataframe表格
df = pd.read_excel('demo.xlsx')
# 使用to_sql方法将df存入数据库
df.to_sql('newtable', con=engine, index=False, if_exists='replace')
# 'newtable'是保存的数据表名，con是连接对象，if_exists='replace'表示如果表已经存在则替换表
# 使用read_sql方法将数据库表读取到DataFrame
df = pd.read_sql('SELECT * FROM newtable', con=engine)
# 'newtable'是读取的数据表名，con是连接对象
代码中并未调用pymysql模块但它是必须要安装的。使用pymysql模块是为了通过sqlalchemy的create_engine函数创建一个数据库连接，这个函数需要一个数据库URL，其中包含了数据库类型和连接信息。在这个URL中，我们使用mysql+pymysql来指定我们要连接的是MySQL数据库，并且使用pymysql作为驱动程序
运行代码即可将dataframe数据存取到我们创建的newdatabase数据库，存取的数据表名就是代码中指定的newtable，数据保存路径中的\Data\newdatabase文件夹下就会生成一个newtable.ibd，即是我们保存的数据表
如果要删除newdatabase数据库中的newtable数据表则使用SQL命令
DROP TABLE newdatabase.newtable;
服务器失去连接的情况
如果数据库服务器因为各种原因失去连接，运行代码就会报错如“Lost connection to MySQL server during query”，此时需要重新启动数据库服务器
- 首先检查你的MySQL服务器名称
- 打开命令提示符（按Win+R，然后输入cmd并按Enter）
- 输入services.msc并按Enter打开服务管理器
- 找到MySQL服务，可能是MySQL，MySQL57，MySQL80或类似的名称，这取决于你的MySQL版本和安装配置
- 以管理员身份启动服务
- 在Windows搜索栏中输入cmd
- 在搜索结果中找到命令提示符，然后右键点击它
- 在弹出的菜单中选择以管理员身份运行
- 使用net start命令来启动服务
net start MySQL80
MySQL密码过期策略的问题
当你过了一阵子运行python代码可能报错提示“密码过期”之类的问题，这是因为MySQL具有密码过期策略导致的，你可以选择更改密码过期策略或者更改账户密码来解决
MySQL的密码过期策略由default_password_lifetime系统变量控制，如果这个值设置为0密码将永不过期；如果这个值设置为N（N>0）密码将在N天后过期
通过以下SQL命令查看当前的密码过期策略
SHOW VARIABLES LIKE 'default_password_lifetime';
命令将返回default_password_lifetime的当前值
设置你的用户密码永不过期
ALTER USER 'newuser'@'localhost' PASSWORD EXPIRE NEVER;
其中的newuser为你的用户名
或者也可以更改账户密码
SET PASSWORD FOR 'newuser'@'localhost' = 'newpassword';
其中的newuser和newpassword替换为你的用户名和新密码
可视化软件MySQL Workbench的安装和简单汉化
使用中发现没有可视化还是不太方便，于是还是把MySQL官方Workbench安装了上来，只是Workbench并不支持中文，我也只是找到了简单的菜单汉化方法
由于已经安装好了MySQL，此时电脑上会有一个MySQL Installer程序，在开始菜单中找或是直接在搜索中搜
打开它，点击Add
层层展开找到Workbench版本包，点击右箭头添加到右侧，右侧点击一下添加过来的版本包，下面出现Advanced Options，点击它设置安装位置
点击OK，点击Next
点击Execute
安装完毕点击Next
点击Finish，Workbench会自行打开，即可关闭MySQL Installer程序
Workbench安装完成，可在开始菜单中找到它
可以看到软件是全英文的，接着对其菜单进行一点简单汉化
找到Workbench安装路径下的data文件夹，打开它找到main_menu.xml文件，在其他位置新建一个txt文件，将这篇博客的替换代码粘贴到txt文件中，将txt文件重命名为main_menu.xml，如果看不到扩展名则在上方点击查看勾选文件扩展名，再将这个重命名的main_menu.xml文件替换掉data文件夹中的main_menu.xml文件
重新启动MySQL Workbench即可看到菜单已汉化
引用链接
Mysql的安装配置教程（非常详细）从零基础入门到精通，看完这一篇就够了
【Python】在python中使用MySQL
MySQL更改安装路径和Data位置
MySQL Workbench的安装
MySQL WorkBench 8.0 菜单汉化
334
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
