SQLite数据库表结构对比与迁移脚本生成工具

🚀 实例简介

本工具用于对比两个SQLite数据库文件的表结构差异，自动生成ALTER TABLE迁移脚本。支持检测新增表、删除表、新增字段、删除字段、修改字段类型等5种变更类型，颜色编码直观展示差异，一键导出SQL迁移脚本。

💻 核心代码

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
SQLite数据库表结构对比与迁移脚本生成工具
功能：加载两个SQLite数据库文件，对比表结构差异，生成ALTER TABLE迁移脚本
"""
import tkinter as tk
from tkinter import ttk, filedialog, messagebox, scrolledtext
import sqlite3
import platform
import os

CHINESE_FONT = "WenQuanYi Zen Hei" if platform.system() == "Linux" else "Microsoft YaHei"

class SchemaComparator:
    def __init__(self, root):
        self.root = root
        self.root.title("SQLite数据库表结构对比与迁移脚本生成工具")
        self.root.geometry("900x650")
        self.db1_path = tk.StringVar()
        self.db2_path = tk.StringVar()
        self._build_ui()

    def _build_ui(self):
        # 标题
        tk.Label(self.root, text="SQLite 数据库表结构对比工具",
                 font=(CHINESE_FONT, 16, "bold"), fg="#2c3e50").pack(pady=10)
        # 文件选择区
        frame_files = ttk.LabelFrame(self.root, text="选择数据库文件", padding=10)
        frame_files.pack(fill="x", padx=15, pady=5)
        tk.Label(frame_files, text="数据库A(旧版):", font=(CHINESE_FONT, 11)).grid(row=0, column=0, sticky="w")
        tk.Entry(frame_files, textvariable=self.db1_path, width=55, font=(CHINESE_FONT, 10)).grid(row=0, column=1, padx=5)
        tk.Button(frame_files, text="浏览...", font=(CHINESE_FONT, 10),
                  command=lambda: self._browse(self.db1_path)).grid(row=0, column=2)
        tk.Label(frame_files, text="数据库B(新版):", font=(CHINESE_FONT, 11)).grid(row=1, column=0, sticky="w", pady=5)
        tk.Entry(frame_files, textvariable=self.db2_path, width=55, font=(CHINESE_FONT, 10)).grid(row=1, column=1, padx=5, pady=5)
        tk.Button(frame_files, text="浏览...", font=(CHINESE_FONT, 10),
                  command=lambda: self._browse(self.db2_path)).grid(row=1, column=2, pady=5)
        # 操作按钮
        frame_btn = tk.Frame(self.root)
        frame_btn.pack(pady=8)
        tk.Button(frame_btn, text="🔍 开始对比", font=(CHINESE_FONT, 12, "bold"),
                  bg="#3498db", fg="white", padx=20, command=self._compare).pack(side="left", padx=10)
        tk.Button(frame_btn, text="📝 生成迁移脚本", font=(CHINESE_FONT, 12, "bold"),
                  bg="#27ae60", fg="white", padx=20, command=self._generate_migration).pack(side="left", padx=10)
        tk.Button(frame_btn, text="💾 导出SQL", font=(CHINESE_FONT, 12, "bold"),
                  bg="#e67e22", fg="white", padx=20, command=self._export_sql).pack(side="left", padx=10)
        # 对比结果区
        frame_result = ttk.LabelFrame(self.root, text="对比结果", padding=5)
        frame_result.pack(fill="both", expand=True, padx=15, pady=5)
        cols = ("变更类型", "表名", "字段名", "详细信息")
        self.tree = ttk.Treeview(frame_result, columns=cols, show="headings", height=12)
        for c in cols:
            self.tree.heading(c, text=c)
        self.tree.column("变更类型", width=100)
        self.tree.column("表名", width=150)
        self.tree.column("字段名", width=150)
        self.tree.column("详细信息", width=400)
        self.tree.pack(fill="both", expand=True)
        # 统计标签
        self.lbl_stats = tk.Label(self.root, text="就绪 - 请选择两个数据库文件开始对比",
                                  font=(CHINESE_FONT, 10), fg="#7f8c8d", anchor="w")
        self.lbl_stats.pack(fill="x", padx=15, pady=3)
        self.diffs = []

    def _browse(self, var):
        path = filedialog.askopenfilename(filetypes=[("SQLite数据库", "*.db *.sqlite *.sqlite3"), ("所有文件", "*.*")])
        if path:
            var.set(path)

    def _get_schema(self, db_path):
        """获取数据库的完整表结构信息"""
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
        tables = {row[0]: {} for row in cursor.fetchall()}
        for table in tables:
            cursor.execute(f"PRAGMA table_info('{table}')")
            for col in cursor.fetchall():
                cid, name, col_type, notnull, default, pk = col

💡 适用场景

办公自动化、数据分析、快速原型开发