执行.sql文件生成数据

处理.sql文件，起初以为pymysql库可以直接执行.sql文件的，踩坑后才发现，它是不支持直接执行sql文件。

解决方法，就是读取.sql文件中的数据，进行处理后提取sql语句，循环去执行并提交。

第一步：获取数据库是否存在

先进行数据库连接，查询数据库并获取数据库名称，根据名称是否存在来执行是否新建数据库，代码如下：

    def _show_databases_and_create(self):"""查询数据库是否存在，不存在则进行新建操作:return:"""connection = pymysql.connect(host=self._host, port=self._port, user=self._user, password=self._password,cursorclass=pymysql.cursors.DictCursor)with connection:with connection.cursor() as cursor:cursor.execute('show databases;')result = cursor.fetchall()results = self._database not in tuple(x["Database"] for x in result)if results:with connection.cursor() as cursor:cursor.execute(f'create database {self._database};')with connection.cursor() as cursor:cursor.execute('show databases;')result = cursor.fetchall()results = self._database in tuple(x["Database"] for x in result)return results if results else resultelse:return True

参数解释：

• cursorclass=pymysql.cursors.DictCursor ：指定返回的是字典类型数据
• results = self._database not in tuple(x["Database"] for x in result) ：生成器获取数据库名称，使用not in获取数据库名是否在元组中
• return results if results else result ：如果results为真则返回一个True，如果为假则返回result结果

---

第二步：获取sql文件并处理数据

处理完数据库创建后，开始读取.sql文件，部分.sql文件内容：

Source Server         : docker-lms-mysql
Source Server Version : 50651
Source Host           : 192.168.123.113:3306
Source Database       : lms_v3Target Server Type    : MYSQL
Target Server Version : 50651
File Encoding         : 65001Date: 2022-01-05 15:31:16
*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for lms_activity
-- ----------------------------
DROP TABLE IF EXISTS `activity`;
CREATE TABLE `activity` (`activity_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '活动id',...
....
.....
......

因为.sql文件中有很多注释信息、符号及\r和\n字符，这种字符在执行sql语句时，是会报sql语法错误的，因此要替换掉或者去除掉。具体处理代码如下：

    def _export_databases_data(self):"""读取.sql文件，解析处理后，执行sql语句:return:"""if self._show_databases_and_create() is True:connection = pymysql.connect(host=self._host, port=self._port, user=self._user, password=self._password,database=self._database, charset='utf8')# 读取sql文件，并提取出sql语句results, results_list = "", []with open(self._file_path, mode="r+", encoding="utf-8") as r:for sql in r.readlines():# 去除数据中的“\n”和“\r”字符sql = sql.replace("\n", "").replace("\r", "")# 获取不是“--”开头且不是“--”结束的数据if not sql.startswith("--") and not sql.endswith("--"):# 获取不是“--”的数据if not sql.startswith("--"):results = results + sql# 根据“;”分割数据，处理后插入列表中for i in results.split(";"):if i.startswith("/*"):results_list.append(i.split("*/")[1] + ";")else:results_list.append(i + ";")# 执行sql语句with connection:with connection.cursor() as cursor:# 循环获取sql语句for x in results_list[:-1]:# 执行sql语句cursor.execute(x)# 提交事务connection.commit()else:return "sql全部语句执行成功 ！"

从示例代码中会发现，大部分都是字符串的处理方法，所以字符串的常用方法一定要牢记。

需要注意的是，这里的数据库连接使用的都是上下文管理，也就是说会自动打开连接，完成后自动关闭游标和关闭连接，因此关于close的方法就不需要出现了

---

第三步：执行方法

因为是内部处理，其他人使用只需要调用即可()，以上的两种方法全部写成了私有方法，也就是外部是访问不到的，所以增加一了个执行方法。代码如下：

    @propertydef sql_run(self):"""执行方法:return:"""return self._export_databases_data()

参数解释：、

• @property ：是把方法设置为属性调用，调用时不需要在方法名后面加()

---

第四步：测试功能

所写的方法，都要测试下，是否通过，是否能达到所需要的功能：

if __name__ == '__main__':res = ConnectMsql().sql_run

运行结果：

E:\venvPython\Scripts\python.exe E://pythonScript/connectMysql/connect_mysql.py
E:\venvPython\lib\site-packages\pymysql\cursors.py:170: Warning: (1831, "Duplicate index 'idx_user_id' defined on the table 'sonic3.lms_client_user_device_relations'. This is deprecated and will be disallowed in a future release.")result = self._query(query)
sql全部语句执行成功 ！Process finished with exit code 0

这里需要注意的是，运行完成后，控制台会出现几行Warning信息，因为我前面已经运行过一次，故只出现了一行警告信息，这里可以忽略不计。

产生警告的主要原因是：

mysql的warnings的级别为note，level是note级别。

这是属于正常范围内，如果你非要解决的话，可自行百度一下

---

第五步：封装

全部封装如下：

import pymysql
from pathlib import Pathclass ConnectMsql:def __init__(self, host='10.10.12.120', port=3307, user='root',password='123456', database="sonic4", filename: str = "lms_v3-v1.sql"):""":param host:        域名:param port:        端口:param user:        用户名:param password:    密码:param database:    数据库名:param filename:    文件名称"""self._host: str = hostself._port: int = portself._user: str = userself._password: str = passwordself._database: str = databaseself._file_path = Path(__file__).parent.joinpath(filename)def _show_databases_and_create(self):"""查询数据库是否存在，不存在则进行新建操作:return:"""connection = pymysql.connect(host=self._host, port=self._port, user=self._user, password=self._password,cursorclass=pymysql.cursors.DictCursor)with connection:with connection.cursor() as cursor:cursor.execute('show databases;')result = cursor.fetchall()results = self._database not in tuple(x["Database"] for x in result)if results:with connection.cursor() as cursor:cursor.execute(f'create database {self._database};')with connection.cursor() as cursor:cursor.execute('show databases;')result = cursor.fetchall()results = self._database in tuple(x["Database"] for x in result)return results if results else resultelse:return Truedef _export_databases_data(self):"""读取.sql文件，解析处理后，执行sql语句:return:"""if self._show_databases_and_create() is True:connection = pymysql.connect(host=self._host, port=self._port, user=self._user, password=self._password,database=self._database, charset='utf8')# 读取sql文件，并提取出sql语句results, results_list = "", []with open(self._file_path, mode="r+", encoding="utf-8") as r:for sql in r.readlines():# 去除数据中的“\n”和“\r”字符sql = sql.replace("\n", "").replace("\r", "")# 获取不是“--”开头且不是“--”结束的数据if not sql.startswith("--") and not sql.endswith("--"):# 获取不是“--”的数据if not sql.startswith("--"):results = results + sql# 根据“;”分割数据，处理后插入列表中for i in results.split(";"):if i.startswith("/*"):results_list.append(i.split("*/")[1] + ";")else:results_list.append(i + ";")# 执行sql语句with connection:with connection.cursor() as cursor:# 循环获取sql语句for x in results_list[:-1]:# 执行sql语句cursor.execute(x)# 提交事务connection.commit()else:return "sql全部语句执行成功 ！"@propertydef sql_run(self):"""执行方法:return:"""return self._export_databases_data()if __name__ == '__main__':res = ConnectMsql().sql_runprint(res)

至此，执行.sql文件完成，以上代码仅供参考，如果您也有好的思路或建议可私信。

---

今天先聊到这里吧，以上总结或许能帮助到你，或许帮助不到你，但还是希望能帮助到你，如有疑问、歧义，直接私信留言会及时修正发布；非常期待你的一键④连【 点赞、收藏、分享 】哟，谢谢！

未完成，待续……

一直在努力，希望你也是！

微信号：waiting940601
文章作者：李锋；编辑排版：梁莉莉