使用方法：导出数据库结构sql，然后开始转换即可
[Asm] 纯文本查看 复制代码import re
from docx import Document
from docx.shared import Inches
from docx.enum.text import WD_ALIGN_PARAGRAPH
import tkinter as tk
from tkinter import filedialog, messagebox, scrolledtext, ttk
import threading
import os
import sys
from datetime import datetime
class SQLToWordConverter:
def __init__(self, root):
self.root = root
self.root.title("SQL到Word文档转换器")
self.root.geometry("700x550")
self.root.resizable(False, False)
self.root.configure(bg="#f0f0f0")
# 设置图标（如果有）
try:
self.root.iconbitmap("database.ico") # 如果有图标文件的话
except:
pass
# 文件路径变量
self.sql_file_path = tk.StringVar()
self.output_file_path = tk.StringVar()
self.create_widgets()
def create_widgets(self):
# 标题框架
title_frame = tk.Frame(self.root, bg="#2c3e50", height=80)
title_frame.pack(fill=tk.X)
title_frame.pack_propagate(False)
title_label = tk.Label(title_frame, text="SQL到Word文档转换器",
font=("Arial", 16, "bold"), fg="white", bg="#2c3e50")
title_label.pack(pady=20)
# 主内容框架
main_frame = tk.Frame(self.root, bg="#f0f0f0", padx=20, pady=20)
main_frame.pack(fill=tk.BOTH, expand=True)
# SQL文件选择
sql_frame = tk.Frame(main_frame, bg="#f0f0f0")
sql_frame.pack(fill=tk.X, pady=10)
tk.Label(sql_frame, text="SQL文件:", font=("Arial", 10),
bg="#f0f0f0").grid(row=0, column=0, sticky=tk.W, padx=(0, 10))
sql_entry = tk.Entry(sql_frame, textvariable=self.sql_file_path,
width=50, font=("Arial", 10))
sql_entry.grid(row=0, column=1, padx=5, sticky=tk.EW)
sql_btn = tk.Button(sql_frame, text="浏览", command=self.browse_sql_file,
bg="#3498db", fg="white", font=("Arial", 10), relief=tk.FLAT)
sql_btn.grid(row=0, column=2, padx=(5, 0))
sql_frame.columnconfigure(1, weight=1)
# 输出文件选择
output_frame = tk.Frame(main_frame, bg="#f0f0f0")
output_frame.pack(fill=tk.X, pady=10)
tk.Label(output_frame, text="输出文件:", font=("Arial", 10),
bg="#f0f0f0").grid(row=0, column=0, sticky=tk.W, padx=(0, 10))
output_entry = tk.Entry(output_frame, textvariable=self.output_file_path,
width=50, font=("Arial", 10))
output_entry.grid(row=0, column=1, padx=5, sticky=tk.EW)
output_btn = tk.Button(output_frame, text="浏览", command=self.browse_output_file,
bg="#3498db", fg="white", font=("Arial", 10), relief=tk.FLAT)
output_btn.grid(row=0, column=2, padx=(5, 0))
output_frame.columnconfigure(1, weight=1)
# 按钮框架
button_frame = tk.Frame(main_frame, bg="#f0f0f0")
button_frame.pack(pady=20)
convert_btn = tk.Button(button_frame, text="开始转换", command=self.start_conversion,
bg="#2ecc71", fg="white", font=("Arial", 12, "bold"),
width=15, height=2, relief=tk.FLAT)
convert_btn.pack(pady=5)
# 日志区域
log_frame = tk.LabelFrame(main_frame, text="日志输出", font=("Arial", 10, "bold"),
bg="#f0f0f0", fg="#2c3e50")
log_frame.pack(fill=tk.BOTH, expand=True, pady=10)
self.log_text = scrolledtext.ScrolledText(log_frame, height=12, wrap=tk.WORD,
font=("Consolas", 9))
self.log_text.pack(fill=tk.BOTH, expand=True, padx=5, pady=5)
# 状态栏
status_frame = tk.Frame(self.root, bg="#ecf0f1", height=30)
status_frame.pack(fill=tk.X)
status_frame.pack_propagate(False)
self.status_label = tk.Label(status_frame, text="就绪", font=("Arial", 9),
bg="#ecf0f1", fg="#7f8c8d")
self.status_label.pack(side=tk.LEFT, padx=10)
# 版权信息
copyright_label = tk.Label(status_frame,
text="© 2023 SQL转换工具",
font=("Arial", 9), bg="#ecf0f1", fg="#7f8c8d")
copyright_label.pack(side=tk.RIGHT, padx=10)
def browse_sql_file(self):
file_path = filedialog.askopenfilename(
title="选择SQL文件",
filetypes=[("SQL文件", "*.sql"), ("所有文件", "*.*")]
)
if file_path:
self.sql_file_path.set(file_path)
# 自动生成输出文件路径
if not self.output_file_path.get():
base_name = os.path.splitext(os.path.basename(file_path))[0]
output_path = os.path.join(os.path.dirname(file_path), f"{base_name}_文档.docx")
self.output_file_path.set(output_path)
def browse_output_file(self):
file_path = filedialog.asksaveasfilename(
title="保存Word文档",
defaultextension=".docx",
filetypes=[("Word文档", "*.docx"), ("所有文件", "*.*")]
)
if file_path:
self.output_file_path.set(file_path)
def log_message(self, message):
timestamp = datetime.now().strftime("%H:%M:%S")
self.log_text.insert(tk.END, f"[{timestamp}] {message}\n")
self.log_text.see(tk.END)
self.root.update_idletasks()
def start_conversion(self):
sql_path = self.sql_file_path.get()
output_path = self.output_file_path.get()
if not sql_path:
messagebox.showerror("错误", "请选择SQL文件")
return
if not output_path:
messagebox.showerror("错误", "请选择输出文件路径")
return
# 清空日志
self.log_text.delete(1.0, tk.END)
self.status_label.config(text="正在处理...")
# 在新线程中执行转换，避免界面冻结
thread = threading.Thread(target=self.convert, args=(sql_path, output_path))
thread.daemon = True
thread.start()
def convert(self, sql_path, output_path):
try:
self.log_message("正在解析SQL文件...")
tables = parse_sql_file(sql_path)
self.log_message(f"成功解析 {len(tables)} 个表")
self.log_message("正在生成Word文档...")
create_word_document(tables, output_path)
self.log_message(f"文档已保存到: {output_path}")
self.log_message("处理完成!")
self.status_label.config(text="转换完成")
messagebox.showinfo("成功", "转换完成!")
except Exception as e:
error_msg = f"处理过程中出现错误: {str(e)}"
self.log_message(error_msg)
self.status_label.config(text="转换失败")
messagebox.showerror("错误", error_msg)
def parse_sql_file(file_path):
"""
解析SQL文件，提取表结构和字段信息
"""
with open(file_path, 'r', encoding='utf-8') as f:
content = f.read()
# 移除注释和多余空格，便于处理
content = re.sub(r'--.*?$', '', content, flags=re.MULTILINE) # 移除行注释
content = re.sub(r'/\*.*?\*/', '', content, flags=re.DOTALL) # 移除块注释
content = re.sub(r'\n+', '\n', content) # 移除多余空行
# 查找所有CREATE TABLE语句
table_pattern = r'CREATE TABLE (?:IF NOT EXISTS )?`?(.*?)`?\s*\((.*?)\)\s*[^)]*?;'
table_blocks = re.findall(table_pattern, content, re.DOTALL | re.IGNORECASE)
tables = []
for table_name, table_body in table_blocks:
# 处理表名
table_name = table_name.strip().replace('`', '')
# 解析表体内的字段定义
lines = [line.strip() for line in table_body.split('\n') if line.strip()]
fields = []
field_index = 1
for line in lines:
# 跳过约束和索引行
if (line.startswith('PRIMARY KEY') or line.startswith('KEY') or
line.startswith('UNIQUE KEY') or line.startswith('CONSTRAINT') or
line.startswith('FOREIGN KEY')):
continue
# 提取字段基本信息
field_match = re.match(r'`?([^`\s]+)`?\s+([a-zA-Z]+)(?:\(([^)]+)\))?', line)
if field_match:
field_name = field_match.group(1)
field_type = field_match.group(2).upper()
field_domain = field_match.group(3) if field_match.group(3) else ''
# 专门提取注释内容 - 位于COMMENT之后，单引号包裹，以逗号结束
comment_match = re.search(r"COMMENT\s+'([^']+)',?", line, re.IGNORECASE)
field_comment = comment_match.group(1) if comment_match else ''
fields.append({
'index': field_index,
'name': field_name,
'type': field_type,
'domain': field_domain,
'comment': field_comment
})
field_index += 1
tables.append({
'name': table_name,
'fields': fields
})
return tables
def create_word_document(tables, output_path):
"""
创建Word文档并填充表结构信息
"""
doc = Document()
# 设置文档标题
title = doc.add_heading('数据库表结构文档', 0)
title.alignment = WD_ALIGN_PARAGRAPH.CENTER
# 添加每个表的信息
for table in tables:
# 添加表名标题
heading = doc.add_heading(f'表: {table["name"]}', level=1)
# 创建表格
table_grid = doc.add_table(rows=1, cols=5)
table_grid.style = 'Table Grid'
# 设置表格列宽
col_widths = [0.5, 1.5, 1.5, 1.5, 3.0] # 单位英寸
for i, width in enumerate(col_widths):
for cell in table_grid.columns.cells:
cell.width = Inches(width)
# 设置表头
hdr_cells = table_grid.rows[0].cells
headers = ['序号', '字段代码', '字段类型', '值域', '注释']
for i, header in enumerate(headers):
hdr_cells.text = header
hdr_cells.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
hdr_cells.paragraphs[0].runs[0].bold = True
# 填充数据行
for field in table['fields']:
row_cells = table_grid.add_row().cells
row_cells[0].text = str(field['index'])
row_cells[1].text = field['name']
row_cells[2].text = field['type']
row_cells[3].text = field['domain']
row_cells[4].text = field['comment']
# 设置单元格对齐方式
row_cells[0].paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
for i in range(1, 5):
row_cells.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.LEFT
# 在表之间添加空行
doc.add_paragraph()
# 保存文档
doc.save(output_path)
def main():
root = tk.Tk()
app = SQLToWordConverter(root)
root.mainloop()
if __name__ == "__main__":
main()
sql语句如下所示：
[Asm] 纯文本查看 复制代码-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS example_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE example_db;
-- 用户表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY COMMENT '用户ID',
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
password_hash VARCHAR(255) NOT NULL COMMENT '加密密码',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) COMMENT='用户表';
-- 商品表
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',
name VARCHAR(100) NOT NULL COMMENT '商品名称',
description TEXT COMMENT '商品描述',
price DECIMAL(10, 2) NOT NULL COMMENT '商品价格',
stock INT DEFAULT 0 COMMENT '库存数量',
status ENUM('AVAILABLE', 'DISCONTINUED') DEFAULT 'AVAILABLE' COMMENT '商品状态',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) COMMENT='商品表';
-- 订单表
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
user_id INT NOT NULL COMMENT '用户ID',
total_amount DECIMAL(10, 2) NOT NULL COMMENT '订单总金额',
status ENUM('PENDING', 'PAID', 'SHIPPED', 'COMPLETED', 'CANCELLED') DEFAULT 'PENDING' COMMENT '订单状态',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) COMMENT='订单表';
-- 订单明细表
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '明细ID',
order_id INT NOT NULL COMMENT '订单ID',
product_id INT NOT NULL COMMENT '商品ID',
quantity INT NOT NULL COMMENT '购买数量',
unit_price DECIMAL(10, 2) NOT NULL COMMENT '商品单价',
subtotal DECIMAL(10, 2) NOT NULL COMMENT '小计金额',
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) COMMENT='订单明细表';
生成结果文件如下：
