快捷導(dǎo)航

Python實(shí)現(xiàn)mysql數(shù)據(jù)庫(kù)中的SQL文件生成和導(dǎo)入

更新時(shí)間：2022年06月06日 14:33:40 作者：有情懷的機(jī)械男

這篇文章主要介紹了Python實(shí)現(xiàn)mysql數(shù)據(jù)庫(kù)中的SQL文件生成和導(dǎo)入，首先通過(guò)將mysql數(shù)據(jù)導(dǎo)出到SQL文件中展開(kāi)詳細(xì)內(nèi)容需要的小伙伴可以參考一下

1、將mysql數(shù)據(jù)導(dǎo)出到SQL文件中（數(shù)據(jù)庫(kù)存在的情況）

主要需要修改數(shù)據(jù)庫(kù)的相關(guān)信息，端口號(hào)、用戶(hù)名、密碼等

其中數(shù)據(jù)庫(kù)得存在，不然會(huì)報(bào)錯(cuò) ：

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @description:導(dǎo)出數(shù)據(jù)庫(kù)文件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"
?? ??? ?# 生成相應(yīng)的sql語(yǔ)句
?? ??? ?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("執(zhí)行的導(dǎo)出數(shù)據(jù)庫(kù)的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','劉同學(xué)','男','1班','A1','101','1112','30'),('2202','張同學(xué)','男','2班','A2','211','1121','13'),('2203','管同學(xué)','女','2班','A3','121','1122','11'),('2204','管同學(xué)','女','2班','A3','121','1122','11'),('2205','劉同學(xué)','女','2班','A3','121','1122','11'),('2206','張同學(xué)','男','2班','A2','211','1121','13'),('2208','楊同學(xué)','男','1班','A1','101','1112','30'),('2209','蔡同學(xué)','男','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、將現(xiàn)有的sql文件數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫(kù)中（前提數(shù)據(jù)庫(kù)存在）

這里的話(huà)其實(shí)就是將SQL文件進(jìn)行分割成一條條SQL語(yǔ)句，然后順序執(zhí)行即可

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: ? ? ? ?用戶(hù)名
? ? ? ? :param password: ? ?密碼
? ? ? ? :param database: ? ?數(shù)據(jù)庫(kù)名
? ? ? ? :param filename: ? ?文件名稱(chēng)
? ? ? ? """
?? ??? ?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):
?? ??? ?"""
? ? ? ? 查詢(xún)數(shù)據(jù)庫(kù)是否存在，不存在則進(jìn)行新建操作
? ? ? ? :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文件，解析處理后，執(zhí)行sql語(yǔ)句
? ? ? ? :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語(yǔ)句
?? ??? ??? ?results, results_list = "", []
?? ??? ??? ?with open(self._file_path, mode="r+", encoding="utf-8") as r:
?? ??? ??? ??? ?for sql in r.readlines():
?? ??? ??? ??? ??? ?# 去除數(shù)據(jù)中的“\n”和“\r”字符
?? ??? ??? ??? ??? ?sql = sql.replace("\n", "").replace("\r", "")
?? ??? ??? ??? ??? ?# 獲取不是“--”開(kāi)頭且不是“--”結(jié)束的數(shù)據(jù)
?? ??? ??? ??? ??? ?if not sql.startswith("--") and not sql.endswith("--"):
?? ??? ??? ??? ??? ??? ?# 獲取不是“--”的數(shù)據(jù)
?? ??? ??? ??? ??? ??? ?if not sql.startswith("--"):
?? ??? ??? ??? ??? ??? ??? ?results = results + sql
?
?? ??? ??? ??? ?# 根據(jù)“;”分割數(shù)據(jù)，處理后插入列表中
?? ??? ??? ??? ?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 + ";")
?? ??? ??? ?# 執(zhí)行sql語(yǔ)句
?? ??? ??? ?with connection:
?? ??? ??? ??? ?with connection.cursor() as cursor:
?? ??? ??? ??? ??? ?# 循環(huán)獲取sql語(yǔ)句
?? ??? ??? ??? ??? ?for x in results_list[:-1]:
?? ??? ??? ??? ??? ??? ?if x != ";":
?? ??? ??? ??? ??? ??? ??? ?print(x)
?? ??? ??? ??? ??? ??? ??? ?# 執(zhí)行sql語(yǔ)句
?? ??? ??? ??? ??? ??? ??? ?cursor.execute(x)
?? ??? ??? ??? ??? ??? ??? ?# 提交事務(wù)
?? ??? ??? ??? ??? ??? ??? ?connection.commit()
?? ??? ??? ??? ??? ?else:
?? ??? ??? ??? ??? ??? ?return "sql全部語(yǔ)句執(zhí)行成功 ！"
?? ?@property
?? ?def sql_run(self):
?? ??? ?"""
?? ??? ?執(zhí)行方法
?? ??? ?:return:
?? ??? ?"""
?? ??? ?return self._export_databases_data()
if __name__ == '__main__':
?? ?res = ConnectMsql().sql_run
?? ?print(res)

利用Navicat軟件可視化數(shù)據(jù)庫(kù)，可以看到導(dǎo)入SQL文件成功

3、利用Navicat導(dǎo)出SQL文件和導(dǎo)入SQL文件

1）從數(shù)據(jù)庫(kù)導(dǎo)出SQL文件

選擇需要導(dǎo)出的數(shù)據(jù)表——右鍵——轉(zhuǎn)儲(chǔ)為SQL文件——數(shù)據(jù)和結(jié)構(gòu)

這樣就可以將數(shù)據(jù)表的結(jié)構(gòu)和數(shù)據(jù)都一起保存在SQL文件中

2）導(dǎo)入SQL文件到數(shù)據(jù)庫(kù)

右鍵選中需要導(dǎo)入的數(shù)據(jù)庫(kù)——運(yùn)行SQL文件

點(diǎn)擊開(kāi)始后，即開(kāi)始運(yùn)行SQL文件，出現(xiàn)下述字樣表明導(dǎo)入SQL文件成功

到此這篇關(guān)于Python實(shí)現(xiàn)mysql數(shù)據(jù)庫(kù)中的SQL文件生成和導(dǎo)入的文章就介紹到這了,更多相關(guān)SQL文件生成內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: