Python实现mysql数据库中的SQL文件生成和导入

发布时间：2023-11-11 15:30

1、将mysql数据导出到SQL文件中（数据库存在的情况）

主要需要修改数据库的相关信息，端口号、用户名、密码等

其中数据库得存在，不然会报错：

$\"Python实现mysql数据库中的SQL文件生成和导入_第1张图片\"$

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @description:导出数据库文件sql
import os
class InitSql(object):
    sql_file = \"A.sql\"
 
    def import_server_db(self):
        mysqldump_commad_dict = {\'dumpcommad\': \'mysqldump \', \'server\': \'localhost\', \'user\': \'root\',
                                 \'password\': \'root\', \'port\': 3306, \'db\': \'studentmanagersystem\'}
        # mysqldump 命令
        sqlfromat = \"mysqldump --column-statistics=0 -h%s -u%s -p%s -P%s %s > %s\"
        # 生成相应的sql语句
        sql = (sqlfromat % (mysqldump_commad_dict[\'server\'],
                            mysqldump_commad_dict[\'user\'],
                            mysqldump_commad_dict[\'password\'],
                            mysqldump_commad_dict[\'port\'],
                            mysqldump_commad_dict[\'db\'],
                            self.sql_file))
        print(\"执行的导出数据库的sql：\" + sql)
        result = os.system(sql)
        return result
if __name__ == \'__main__\':
    initSql = InitSql()
    initSql.import_server_db()

生成的sql文件如下所示：

-- MySQL dump 10.13  Distrib 8.0.29, for Win64 (x86_64)
--
-- Host: localhost    Database: studentmanagersystem
-- ------------------------------------------------------
-- Server version  8.0.29

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=\'+00:00\' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=\'NO_AUTO_VALUE_ON_ZERO\' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `studenttable`
--

DROP TABLE IF EXISTS `studenttable`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `studenttable` (
  `number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `classes` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `floor` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `room` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `money` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`number`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `studenttable`
--

LOCK TABLES `studenttable` WRITE;
/*!40000 ALTER TABLE `studenttable` DISABLE KEYS */;
INSERT INTO `studenttable` VALUES (\'2201\',\'刘同学\',\'男\',\'1班\',\'A1\',\'101\',\'1112\',\'30\'),(\'2202\',\'张同学\',\'男\',\'2班\',\'A2\',\'211\',\'1121\',\'13\'),(\'2203\',\'管同学\',\'女\',\'2班\',\'A3\',\'121\',\'1122\',\'11\'),(\'2204\',\'管同学\',\'女\',\'2班\',\'A3\',\'121\',\'1122\',\'11\'),(\'2205\',\'刘同学\',\'女\',\'2班\',\'A3\',\'121\',\'1122\',\'11\'),(\'2206\',\'张同学\',\'男\',\'2班\',\'A2\',\'211\',\'1121\',\'13\'),(\'2208\',\'杨同学\',\'男\',\'1班\',\'A1\',\'101\',\'1112\',\'30\'),(\'2209\',\'蔡同学\',\'男\',\'1班\',\'A1\',\'101\',\'1112\',\'30\');
/*!40000 ALTER TABLE `studenttable` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-06-05  0:30:03

2、将现有的sql文件数据导入到数据库中（前提数据库存在）

这里的话其实就是将SQL文件进行分割成一条条SQL语句，然后顺序执行即可

import pymysql
from pathlib import Path
class ConnectMsql:
    def __init__(self, host=\'localhost\', port=3306, user=\'root\',
                 password=\'root\', database=\"studentmanagersystem\", filename: str = \"studenttable.sql\"):
        \"\"\"
        :param host:        域名
        :param port:        端口
        :param user:        用户名
        :param password:    密码
        :param database:    数据库名
        :param filename:    文件名称
        \"\"\"
        self._host: str = host
        self._port: int = port
        self._user: str = user
        self._password: str = password
        self._database: str = database
        self._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 result
        else:
            return True
 
    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] + \";\")
                        # print(i.split(\"*/\")[1] + \";\")
                    else:
                        results_list.append(i + \";\")
                        # print(i + \";\")
            # 执行sql语句
            with connection:
                with connection.cursor() as cursor:
                    # 循环获取sql语句
                    for x in results_list[:-1]:
                        if x != \";\":
                            print(x)
                            # 执行sql语句
                            cursor.execute(x)
                            # 提交事务
                            connection.commit()
                    else:
                        return \"sql全部语句执行成功 ！\"
    @property
    def sql_run(self):
        \"\"\"
        执行方法
        :return:
        \"\"\"
        return self._export_databases_data()
if __name__ == \'__main__\':
    res = ConnectMsql().sql_run
    print(res)

利用Navicat软件可视化数据库，可以看到导入SQL文件成功

$\"Python实现mysql数据库中的SQL文件生成和导入_第2张图片\"$