Python GUI 完整 NL2DSL 演示
做一个开箱即用的 GUI 桌面程序，完全复现 NL → DSL → SQL 流程：
- 自然语言提问 → 自动生成 DSL → 执行 SQL 查询
先安装依赖
pip install tkinter # 一般Python自带pip install sqlalchemy pandas
完整代码（直接复制运行）
import tkinter as tkfrom tkinter import ttk, scrolledtext, messageboximport pandas as pdimport sqlite3import jsonimport re# ====================== 1. 初始化测试数据库（3张表 + 测试数据）======================definit_test_db(): conn = sqlite3.connect("nl2dsl_demo.db") cursor = conn.cursor()# 订单表 cursor.execute(''' CREATE TABLE IF NOT EXISTS orders ( id INTEGER PRIMARY KEY, user_id INTEGER, product_id INTEGER, amount REAL, create_time TEXT, region TEXT )''')# 用户表 cursor.execute(''' CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER )''')# 商品表 cursor.execute(''' CREATE TABLE IF NOT EXISTS products ( id INTEGER PRIMARY KEY, name TEXT, category TEXT )''')# 插入测试数据 cursor.execute("DELETE FROM orders") cursor.execute("DELETE FROM users") cursor.execute("DELETE FROM products") cursor.execute("INSERT INTO users VALUES (1,'张三',28),(2,'李四',32),(3,'王五',25)") cursor.execute("INSERT INTO products VALUES (1,'手机','数码'),(2,'电脑','数码'),(3,'衬衫','服装')") cursor.execute("INSERT INTO orders VALUES (1,1,1,5999,'2025-01-10','华东'),""(2,2,2,7999,'2025-02-15','华北'),""(3,3,3,299,'2025-01-20','华东'),""(4,1,2,7999,'2025-03-05','华北')") conn.commit() conn.close()# ====================== 2. NL2DSL 核心逻辑（自然语言 → DSL → SQL）======================defnl_to_dsl(nl_text):"""自然语言 → DSL（JSON格式）""" dsl = {"intent": "query","metrics": [],"dimensions": [],"filters": [],"tables": [] }# 规则匹配（企业可用LLM替换，这里用规则做demo）if"销售额"in nl_text or"金额"in nl_text: dsl["metrics"].append("amount")if"订单"in nl_text: dsl["metrics"].append("id") dsl["tables"].append("orders")if"地区"in nl_text: dsl["dimensions"].append("region")if"商品"in nl_text or"品类"in nl_text: dsl["dimensions"].append("category") dsl["tables"].append("products")if"用户"in nl_text: dsl["tables"].append("users")# 过滤条件if"华东"in nl_text: dsl["filters"].append({"field": "region", "op": "=", "value": "'华东'"})if"华北"in nl_text: dsl["filters"].append({"field": "region", "op": "=", "value": "'华北'"})# 去重 dsl["tables"] = list(set(dsl["tables"]))return dsldefdsl_to_sql(dsl):"""DSL → 可执行SQL""" metrics = ", ".join([f"sum({m}) as {m}"if m == "amount"elsef"count({m}) as order_count"for m in dsl["metrics"]]) dimensions = ", ".join(dsl["dimensions"]) tables = ", ".join(dsl["tables"]) where_clause = ""if dsl["filters"]: conditions = [f"{f['field']}{f['op']}{f['value']}"for f in dsl["filters"]] where_clause = "WHERE " + " AND ".join(conditions) group_by = f"GROUP BY {dimensions}"if dimensions else"" sql = f"SELECT {metrics}{', ' + dimensions if dimensions else''} FROM {tables}{where_clause}{group_by}"return sql.strip()defexec_sql(sql):"""执行SQL并返回DataFrame"""try: conn = sqlite3.connect("nl2dsl_demo.db") df = pd.read_sql(sql, conn) conn.close()return dfexcept Exception as e:returnf"执行错误：{str(e)}"# ====================== 3. GUI 界面 ======================classNL2DSLGUI:def__init__(self, root): self.root = root self.root.title("NL2DSL 自然语言转SQL查询演示") self.root.geometry("900x700")# 标题 tk.Label(root, text="NL → DSL → SQL 演示", font=("微软雅黑",16,"bold")).pack(pady=10)# 输入框 tk.Label(root, text="请输入自然语言问题：", font=("微软雅黑",12)).pack() self.nl_input = ttk.Entry(root, width=80, font=("微软雅黑",12)) self.nl_input.pack(pady=5) self.nl_input.insert(0, "查询华东地区的销售额和订单数量")# 执行按钮 ttk.Button(root, text="开始查询", command=self.run_query).pack(pady=5)# 分隔 ttk.Separator(root, orient=tk.HORIZONTAL).pack(fill=tk.X, padx=20, pady=8)# DSL 展示 tk.Label(root, text="生成的DSL：", font=("微软雅黑",12)).pack() self.dsl_text = scrolledtext.ScrolledText(root, height=8, width=100, font=(" Consolas",11)) self.dsl_text.pack(pady=3)# SQL 展示 tk.Label(root, text="生成的SQL：", font=("微软雅黑",12)).pack() self.sql_text = scrolledtext.ScrolledText(root, height=4, width=100, font=(" Consolas",11)) self.sql_text.pack(pady=3)# 结果展示 tk.Label(root, text="查询结果：", font=("微软雅黑",12)).pack() self.result_text = scrolledtext.ScrolledText(root, height=12, width=100, font=(" Consolas",11)) self.result_text.pack(pady=3)defrun_query(self): nl_text = self.nl_input.get().strip()ifnot nl_text: messagebox.showwarning("提示","请输入问题")return# 1. NL → DSL dsl = nl_to_dsl(nl_text) self.dsl_text.delete(1.0, tk.END) self.dsl_text.insert(tk.END, json.dumps(dsl, ensure_ascii=False, indent=2))# 2. DSL → SQL sql = dsl_to_sql(dsl) self.sql_text.delete(1.0, tk.END) self.sql_text.insert(tk.END, sql)# 3. 执行SQL res = exec_sql(sql) self.result_text.delete(1.0, tk.END)if isinstance(res, pd.DataFrame): self.result_text.insert(tk.END, res.to_string(index=False))else: self.result_text.insert(tk.END, res)# ====================== 启动 ======================if __name__ == "__main__": init_test_db() root = tk.Tk() app = NL2DSLGUI(root) root.mainloop()
你可以直接测试这些问题
这个 Demo 完整实现了
✅ 3张业务表（订单/用户/商品）✅ 自然语言输入✅ 自动生成 DSL（结构化语义）✅ 自动生成可执行 SQL✅ 执行查询并展示结果✅ 完整 GUI 桌面程序✅ 企业级 NL2DSL 标准流程
