Mysql和Oracle按照指定顺序批量执行SQL脚本

博主个人博客
博主个人资源网站
博主个人资源网站备用

前言

每当进行环境搭建或者版本升级之际，往往需要执行数量众多的 SQL 脚本。目前，大多数情况下是通过 SQL 图形化工具或者命令行进行单个脚本的执行，然而这种方式效率较为低下。鉴于此，特地编写了一款适用于 Mysql 和 Oracle 的小工具，它能够按照指定顺序批量执行 SQL 脚本，极大地提高了工作效率，为数据库操作带来极大的便利。

界面展示

功能介绍

可按照文件创建时间升序,文件创建时间降序,文件名前数字升序,文件名前数字降序四种排序方式指定文件夹进行批量脚本执行，亦可以保存配置避免多次输入。

源码

主窗口 .py文件

import tkinter as tk
from tkinter import ttk, filedialog, messagebox
from datetime import datetime
import json
from jdbc_sql import *


class DatabaseConfigApp:
    def __init__(self, root):
        self.root = root
        self.root.title("数据库配置")

        # 按钮框架
        button_frame = tk.Frame(root)
        button_frame.pack(pady=10)

        # 增加行按钮
        self.add_row_button = tk.Button(button_frame, text="增加行", command=self.add_row)
        self.add_row_button.pack(side=tk.LEFT, padx=5)

        # 删除行按钮
        self.delete_row_button = tk.Button(button_frame, text="删除行", command=self.delete_row)
        self.delete_row_button.pack(side=tk.LEFT, padx=5)

        # 提交按钮
        self.submit_button = tk.Button(button_frame, text="提交", command=self.submit)
        self.submit_button.pack(side=tk.LEFT, padx=5)

        # 保存配置按钮
        self.save_button = tk.Button(button_frame, text="保存配置", command=self.save_config)
        self.save_button.pack(side=tk.LEFT, padx=5)

        # 加载配置按钮
        self.load_button = tk.Button(button_frame, text="加载配置", command=self.load_config)
        self.load_button.pack(side=tk.LEFT, padx=5)

        # 表格框架
        self.tree = ttk.Treeview(root,
                                  columns=("DB Type", "DB IP", "Port", "User", "Password", "DB Name", "SQL File", "Sort Rule"),
                                  show='headings')
        self.tree.heading("DB Type", text="数据库类型")
        self.tree.heading("DB IP", text="数据库 IP")
        self.tree.heading("Port", text="端口")
        self.tree.heading("User", text="用户")
        self.tree.heading("Password", text="密码")
        self.tree.heading("DB Name", text="数据库名")
        self.tree.heading("SQL File", text="SQL 文件目录")
        self.tree.heading("Sort Rule", text="排序规则")
        for col in self.tree["columns"]:
            # self.tree.heading(col, text=col)
            self.tree.column(col, width=160)
        self.tree.pack(pady=10)

        # 日志窗口
        self.log_window = None

        # 初始化输入框框架存储字典
        self.input_frames = {}

    def open_log_window(self):
        if self.log_window is None or not self.log_window.winfo_exists():
            self.log_window = tk.Toplevel(self.root)
            self.log_window.title("执行日志")
            self.log_text = tk.Text(self.log_window, height=30, width=150)
            self.log_text.pack(pady=10)

    def add_row(self):
        row_data = ["", "", "", "", "", "", "", ""]
        row_id = self.tree.insert("", "end", values=row_data)
        self.create_input_frame(row_id, row_data)  # 创建输入框和下拉框

    def create_input_frame(self, row_id, values):
        frame = tk.Frame(self.root)
        frame.pack()

        db_type_combobox = ttk.Combobox(frame, values=["MySQL", "Oracle"])
        db_type_combobox.grid(row=0, column=0)
        db_type_combobox.set(values[0])  # 设置默认值
        db_type_combobox.bind("<<ComboboxSelected>>",
                              lambda e, rid=row_id: self.update_tree_value(rid, 0, db_type_combobox.get()))

        for col_index in range(1, 6):  # 1到5列为输入框
            entry = tk.Entry(frame)
            entry.grid(row=0, column=col_index)
            entry.insert(0, values[col_index])  # 设置默认值
            entry.bind("<FocusOut>",
                       lambda e, col=col_index, rid=row_id: self.update_tree_value(rid, col, e.widget.get()))

        sql_file_entry = tk.Entry(frame)
        sql_file_entry.grid(row=0, column=6)
        sql_file_entry.insert(0, values[6])  # 设置默认值
        sql_button = tk.Button(frame, text="选择文件夹", command=lambda: self.browse_directory(sql_file_entry, row_id))
        sql_button.grid(row=0, column=7)

        sort_rule = ttk.Combobox(frame, values=["文件创建时间升序", "文件创建时间降序", "文件名前数字升序", "文件名前数字降序"])
        sort_rule.grid(row=0, column=8)
        sort_rule.set(values[7])  # 设置默认值
        sort_rule.bind("<<ComboboxSelected>>",
                       lambda e, rid=row_id: self.update_tree_value(rid, 7, sort_rule.get()))

        self.input_frames[row_id] = frame

    def browse_directory(self, entry, row_id):
        dir_path = filedialog.askdirectory()
        if dir_path:
            entry.delete(0, tk.END)
            entry.insert(0, dir_path)
            self.update_tree_value(row_id, 6, dir_path)

    def update_tree_value(self, row_id, column, value):
        current_values = list(self.tree.item(row_id)['values'])
        current_values[column] = value
        self.tree.item(row_id, values=current_values)
        self.check_validity()

    def check_validity(self):
        selected_items = self.tree.selection()
        valid = False
        for item in selected_items:
            row = self.tree.item(item)['values']
            is_valid, _ = self.validate_inputs(row)
            if is_valid:
                valid = True
                break
        self.submit_button.config(state=tk.NORMAL if valid else tk.DISABLED)

    def validate_inputs(self, row):
        db_ip = str(row[1])  # 确保 db_ip 为字符串
        port = str(row[2])  # 确保 port 为字符串
        user = str(row[3])
        password = str(row[4])
        db_name = str(row[5])

        if not db_ip or not self.is_valid_ip(db_ip):
            return False, "无效的IP地址。"
        if not port.isdigit() or not (0 < int(port) < 65536):
            return False, "端口必须在1到65535之间。"
        if not user:
            return False, "用户不能为空。"
        if not db_name:
            return False, "数据库名不能为空。"

        return True, ""

    def is_valid_ip(self, ip):
        parts = ip.split(".")
        if len(parts) != 4:
            return False
        for part in parts:
            if not part.isdigit() or not (0 <= int(part) <= 255):
                return False
        return True

    def delete_row(self):
        selected_item = self.tree.selection()
        if selected_item:
            for item in selected_item:
                self.tree.delete(item)
                if item in self.input_frames:
                    self.input_frames[item].destroy()
                    del self.input_frames[item]
        else:
            messagebox.showwarning("警告", "请选择要删除的行！")

    def submit(self):
        selected_items = self.tree.selection()
        if not selected_items:
            messagebox.showwarning("警告", "请选择要提交的数据！")
            return

        rows = []
        for item in selected_items:
            row = self.tree.item(item)['values']
            is_valid, message = self.validate_inputs(row)
            if not is_valid:
                messagebox.showerror("输入错误", message)
                return
            rows.append(row)

        self.open_log_window()

        for row in rows:
            try:
                self.log(f"正在执行：{row}")
                execute_scripts(row, self.log)
            except Exception as e:
                self.log(f"执行失败：{str(e)}")

        # messagebox.showinfo("信息", "数据提交成功！")

    def log(self, message):
        timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        log_message = f"[{timestamp}] {message}\n"
        self.log_text.insert(tk.END, f"[{timestamp}] {message}\n")
        self.log_text.see(tk.END)
        # 写入日志文件
        with open('sql_log.log', 'a',  encoding='utf-8') as log_file:  # 追加模式
            log_file.write(log_message)

    def save_config(self):
        config_data = []
        for item in self.tree.get_children():
            values = self.tree.item(item)['values']
            config_data.append(values)
        with open('config.json', 'w') as f:
            json.dump(config_data, f)
        # 提示保存成功
        messagebox.showinfo("信息", "配置已成功保存！")

    def load_config(self):
        try:
            with open('config.json', 'r') as f:
                config_data = json.load(f)
                for values in config_data:
                    row_id = self.tree.insert("", "end", values=values)
                    self.create_input_frame(row_id, values)  # 创建输入框和下拉框
        except FileNotFoundError:
            messagebox.showwarning("警告", "没有找到配置文件。")

    def on_closing(self):
        self.root.destroy()


if __name__ == "__main__":
    root = tk.Tk()
    app = DatabaseConfigApp(root)
    root.protocol("WM_DELETE_WINDOW", app.on_closing)
    app.load_config()  # 加载配置
    root.mainloop()

JDBC操作 .py文件

import mysql.connector
import cx_Oracle  # 确保安装了 cx_Oracle 库
import os
import psycopg2
import chardet


def execute_scripts(row, log):
    db_type = row[0]
    try:
        if db_type == "MySQL":
            execute_scripts_mysql(row, log)
        elif db_type == "Oracle":
            execute_scripts_oracle(row, log)
        # 可以扩展其他数据库类型的逻辑
    except Exception as e:
        log(f"数据库操作失败: {str(e)}")


def check_permissions_mysql(cursor):
    try:
        # 查询当前用户的权限信息
        cursor.execute(
            "SELECT * FROM information_schema.user_privileges WHERE grantee = CURRENT_USER() AND privilege_type = 'CREATE TABLE'")
        return cursor.fetchone() is not None
    except Exception as e:
        return False


def check_permissions_oracle(cursor):
    try:
        # 查询当前用户的系统权限
        cursor.execute("SELECT * FROM session_privs WHERE privilege = 'CREATE TABLE'")
        return cursor.fetchone() is not None
    except Exception as e:
        return False


def check_permissions_dm(cursor):
    # 达梦数据库的权限检查逻辑
    try:
        # 查询用户是否具有执行特定操作的权限，这里只是示例，实际情况需根据需求调整
        cursor.execute("SELECT COUNT(*) FROM SYSDBA.V$USER_PRIVS WHERE PRIVILEGE='CREATE TABLE'")
        count = cursor.fetchone()[0]
        return count > 0
    except Exception as e:
        return False


def check_permissions_mogdb(cursor):
    # MogDB的权限检查逻辑
    try:
        # 查询当前用户
        cursor.execute("SELECT current_user;")
        current_user = cursor.fetchone()[0]

        # 假设要检查是否有创建表的权限，可以查询系统表 pg_catalog.pg_authid 和 pg_catalog.pg_namespace
        cursor.execute(f"""
                SELECT 1 
                FROM pg_catalog.pg_authid a 
                JOIN pg_catalog.pg_namespace n ON a.oid = n.nspowner
                WHERE a.rolname = '{current_user}' AND n.nspname = 'public' AND
                      a.rolcreatetable = true;
            """)
        return cursor.fetchone() is not None
    except Exception as e:
        return False


def execute_scripts_mysql(row, log):
    db_type, db_ip, port, user, password, db_name, sql_folder, sort_rule = row
    try:
        connection = mysql.connector.connect(
            host=db_ip,
            user=user,
            password=password,
            database=db_name
        )
        cursor = connection.cursor()

        # 列出并排序 SQL 文件
        files = os.listdir(sql_folder)
        log(f"找到的 SQL 文件: {files}")

        # 根据排序规则对文件进行排序
        sort_files(files, sql_folder, sort_rule, log)

        for file_name in files:
            if file_name.endswith('.sql'):
                log(f"正在执行文件: {file_name}")
                file_path = os.path.join(sql_folder, file_name)
                with open(file_path, 'r', encoding='utf-8') as sql_file:
                    sql_script = sql_file.read()
                    statements = [s.strip() for s in sql_script.split(';') if s.strip()]

                    for statement in statements:
                        # log(f"正在执行语句: {statement}")
                        try:
                            cursor.execute(statement)
                            if statement.upper().startswith(('SELECT', 'SHOW', 'DESCRIBE')):
                                cursor.fetchall()
                            else:
                                cursor.nextset()  # 清除未处理的结果集
                        except mysql.connector.Error as e:
                            log(f"数据库错误: {str(e)}，语句: {statement}，文件: {file_name}")
                            return
                        except Exception as e:
                            log(f"其他错误: {str(e)}，语句: {statement}，文件: {file_name}")
                            return

        connection.commit()

    except Exception as e:
        log(f"数据库操作失败: {str(e)}")
    finally:
        cursor.close()
        connection.close()
        log(f"数据库操作成功！")


def execute_scripts_oracle(row, log):
    db_type, db_ip, port, user, password, db_name, sql_folder, sort_rule = row
    try:
        dsn = cx_Oracle.makedsn(db_ip, port, service_name=db_name)
        connection = cx_Oracle.connect(user, password, dsn)
        cursor = connection.cursor()

        # 检查用户权限
        if not check_permissions_oracle(cursor):
            log("用户没有足够的权限执行 SQL 脚本。")
            return

        # 列出并排序 SQL 文件
        files = os.listdir(sql_folder)
        log(f"找到的 SQL 文件: {files}")

        # 根据排序规则对文件进行排序
        sort_files(files, sql_folder, sort_rule, log)

        for file_name in files:
            if file_name.endswith('.sql'):
                log(f"正在执行文件: {file_name}")
                file_path = os.path.join(sql_folder, file_name)

                with open(file_path, 'rb') as f:
                    raw_data = f.read()
                    result = chardet.detect(raw_data)
                    encoding = result['encoding']
                    log(f"检测到的文件编码: {encoding}")

                with open(file_path, 'r', encoding=encoding) as sql_file:
                    sql_script = sql_file.read()
                    sql_statements = sql_script.split(';')

                    for idx, statement in enumerate(sql_statements):
                        cleaned_statement = statement.strip()  # 去除首尾空格
                        if cleaned_statement:  # 跳过空语句
                            log(f"准备执行第 {idx + 1} 条 SQL 语句: {cleaned_statement}")

                            # 打印调试信息，确认当前 SQL 语句
                            log(f"当前 SQL 语句内容: {cleaned_statement}")

                            try:
                                # 判断语句类型
                                cleaned_statement_lower = cleaned_statement.lower()  # 转为小写以便比较

                                if cleaned_statement_lower.startswith(
                                        ('insert', 'update', 'delete', 'merge', 'replace')):
                                    cursor.execute(cleaned_statement)
                                    connection.commit()  # 提交事务
                                    log(f"成功执行 DML 语句: {cleaned_statement}")
                                elif cleaned_statement_lower.startswith(('create', 'alter', 'drop', 'truncate')):
                                    cursor.execute(cleaned_statement)
                                    log(f"成功执行 DDL 语句: {cleaned_statement}")  # DDL 语句通常会自动提交
                                elif cleaned_statement_lower.startswith('select'):
                                    cursor.execute(cleaned_statement)
                                    results = cursor.fetchall()  # 获取查询结果
                                    log(f"成功执行查询语句: {cleaned_statement}, 返回结果: {results}")
                                else:
                                    log(f"未识别的 SQL 语句类型: {cleaned_statement}")  # 处理未识别的语句
                            except Exception as exec_error:
                                log(f"执行 SQL 语句失败: {cleaned_statement}, 错误: {str(exec_error)}")
                                # 仅在 DML 语句中回滚事务
                                if cleaned_statement_lower.startswith(
                                        ('insert', 'update', 'delete', 'merge', 'replace')):
                                    connection.rollback()
                        else:
                            log(f"跳过空语句")

    except Exception as e:
        log(f"数据库操作失败: {str(e)}")
    finally:
        cursor.close()
        connection.close()
        log(f"数据库操作成功！")



def sort_files(files, sql_folder, sort_rule, log):
    # 根据排序规则对文件进行排序
    if sort_rule == "文件创建时间升序":
        files.sort(key=lambda x: os.path.getctime(os.path.join(sql_folder, x)))
    elif sort_rule == "文件创建时间降序":
        files.sort(key=lambda x: os.path.getctime(os.path.join(sql_folder, x)), reverse=True)
    elif sort_rule == "文件名前数字升序":
        files.sort(key=lambda x: int(''.join(filter(str.isdigit, x.split('.')[0]))))
    elif sort_rule == "文件名前数字降序":
        files.sort(key=lambda x: int(''.join(filter(str.isdigit, x.split('.')[0]))), reverse=True)