Python實現mysql資料庫中的SQL檔案生成和匯入

2022-06-06 18:00:17

1、將mysql資料匯出到SQL檔案中（資料庫存在的情況）

主要需要修改資料庫的相關資訊，埠號、使用者名稱、密碼等

其中資料庫得存在，不然會報錯：

#!/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檔案成功