# 修復列出的表不全以及表內容（列）不完整問題
import tkinter as tk
from tkinter import ttk, filedialog, messagebox, scrolledtext
import sqlite3
import os
from datetime import datetime
 
class SQLiteViewer:
    def __init__(self, root):
        self.root = root
        self.root.title("SQLite可視化工具 V2.01")
        self.root.geometry("1100x750")
        self.root.minsize(800, 600)
         
        # 數(shù)據(jù)庫連接狀態(tài)
        self.db_connection = None
        self.db_path = None
         
        # 初始化UI
        self.setup_ui()
         
    def setup_ui(self):
        """設置界面布局"""
        # 頂部控制面板
        control_frame = ttk.Frame(self.root, padding="10")
        control_frame.pack(fill=tk.X, anchor=tk.W)
         
        # 打開數(shù)據(jù)庫按鈕
        self.open_btn = ttk.Button(control_frame, text="選擇SQLite數(shù)據(jù)庫", command=self.open_database)
        self.open_btn.pack(side=tk.LEFT, padx=5)
         
        # 數(shù)據(jù)庫路徑顯示
        self.db_label = ttk.Label(control_frame, text="未選擇數(shù)據(jù)庫", foreground="gray")
        self.db_label.pack(side=tk.LEFT, padx=10, fill=tk.X, expand=True)
         
        # 導出按鈕（默認禁用）
        self.export_btn = ttk.Button(control_frame, text="導出數(shù)據(jù)", command=self.export_data, state=tk.DISABLED)
        self.export_btn.pack(side=tk.RIGHT, padx=5)
         
        # 中間選擇區(qū)域
        select_frame = ttk.Frame(self.root, padding="10")
        select_frame.pack(fill=tk.X, anchor=tk.W)
         
        ttk.Label(select_frame, text="數(shù)據(jù)表列表：").pack(side=tk.LEFT, padx=5)
         
        # 表選擇下拉框
        self.table_var = tk.StringVar()
        self.table_combobox = ttk.Combobox(select_frame, textvariable=self.table_var, state="readonly", width=30)
        self.table_combobox.pack(side=tk.LEFT, padx=5, fill=tk.X, expand=True)
        self.table_combobox.bind("<<ComboboxSelected>>", self.on_table_selected)
         
        # 底部顯示區(qū)域（分為結構和數(shù)據(jù)兩個標簽頁）
        tab_control = ttk.Notebook(self.root)
        tab_control.pack(fill=tk.BOTH, expand=True, padx=10, pady=5)
         
        # 表結構標簽頁
        self.structure_tab = ttk.Frame(tab_control)
        tab_control.add(self.structure_tab, text="表結構")
         
        # 數(shù)據(jù)標簽頁
        self.data_tab = ttk.Frame(tab_control)
        tab_control.add(self.data_tab, text="表數(shù)據(jù)")
         
        # 初始化表結構顯示區(qū)域
        self.init_structure_display()
         
        # 初始化數(shù)據(jù)顯示區(qū)域
        self.init_data_display()
         
    def init_structure_display(self):
        """初始化表結構顯示區(qū)域"""
        # 創(chuàng)建滾動文本框顯示表結構
        self.structure_text = scrolledtext.ScrolledText(self.structure_tab, wrap=tk.WORD, font=("Consolas", 10))
        self.structure_text.pack(fill=tk.BOTH, expand=True, padx=5, pady=5)
        self.structure_text.insert(tk.END, "請先選擇數(shù)據(jù)庫文件，然后選擇數(shù)據(jù)表查看結構...")
        self.structure_text.config(state=tk.DISABLED)
         
    def init_data_display(self):
        """初始化數(shù)據(jù)顯示區(qū)域"""
        # 創(chuàng)建表格
        self.data_tree = ttk.Treeview(self.data_tab, show="headings")
        self.data_tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True, padx=5, pady=5)
         
        # 添加滾動條
        scrollbar = ttk.Scrollbar(self.data_tab, orient=tk.VERTICAL, command=self.data_tree.yview)
        self.data_tree.configure(yscrollcommand=scrollbar.set)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y, padx=(0,5), pady=5)
         
        # 初始提示
        self.data_tree["columns"] = ("tip",)
        self.data_tree.heading("tip", text="提示信息")
        self.data_tree.insert("", tk.END, values=("請先選擇數(shù)據(jù)庫文件和數(shù)據(jù)表查看數(shù)據(jù)...",))
         
    def open_database(self):
        """打開SQLite數(shù)據(jù)庫文件"""
        file_path = filedialog.askopenfilename(
            title="選擇SQLite數(shù)據(jù)庫文件",
            filetypes=[("SQLite Files", "*.db *.sqlite *.sqlite3"), ("All Files", "*.*")]
        )
         
        if not file_path:
            return
         
        try:
            # 關閉之前的連接
            if self.db_connection:
                self.db_connection.close()
             
            # 建立新連接
            self.db_connection = sqlite3.connect(file_path)
            self.db_path = file_path
             
            # 更新界面顯示
            self.db_label.config(text=f"當前數(shù)據(jù)庫：{os.path.basename(file_path)}", foreground="black")
            self.export_btn.config(state=tk.NORMAL)
             
            # 獲取所有表名并更新下拉框
            tables = self.get_all_tables()
            self.table_combobox["values"] = tables
             
            # 清空之前的顯示
            self.clear_structure_display()
            self.clear_data_display()
             
            if tables:
                messagebox.showinfo("成功", f"成功連接數(shù)據(jù)庫！共發(fā)現(xiàn) {len(tables)} 個數(shù)據(jù)表")
            else:
                messagebox.showwarning("警告", "數(shù)據(jù)庫連接成功，但未發(fā)現(xiàn)任何數(shù)據(jù)表")
                 
        except sqlite3.Error as e:
            messagebox.showerror("錯誤", f"數(shù)據(jù)庫連接失敗：{str(e)}")
            self.db_connection = None
            self.db_path = None
            self.db_label.config(text="連接失敗", foreground="red")
            self.export_btn.config(state=tk.DISABLED)
     
    def get_all_tables(self):
        """獲取數(shù)據(jù)庫中所有表名和視圖名"""
        if not self.db_connection:
            return []
         
        cursor = self.db_connection.cursor()
        try:
            # 兼容性最好、最穩(wěn)妥的查詢方式：同時獲取表和視圖
            cursor.execute("""
                SELECT name FROM sqlite_master 
                WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%'
                ORDER BY name
            """)
            tables = [row[0] for row in cursor.fetchall()]
        except sqlite3.Error as e:
            print(f"獲取表列表失敗: {e}")
            tables = []
        finally:
            cursor.close()
            
        return tables

     
    def on_table_selected(self, event):
        """當選擇不同表時的處理"""
        selected_table = self.table_var.get()
        if not selected_table or not self.db_connection:
            return
         
        # 顯示表結構
        self.show_table_structure(selected_table)
         
        # 顯示表數(shù)據(jù)
        self.show_table_data(selected_table)
     
    def show_table_structure(self, table_name):
        """顯示表結構信息"""
        cursor = self.db_connection.cursor()
         
        # 核心防錯：必須處理表名中的雙引號并用雙引號包裹，否則"Order Details"這種帶空格的表會報錯
        safe_table = table_name.replace('"', '""')
         
        # 獲取表結構
        cursor.execute(f'PRAGMA table_info("{safe_table}")')
        structure = cursor.fetchall()
         
        # 獲取表的創(chuàng)建語句 (使用 ? 占位符最安全)
        cursor.execute("""
            SELECT sql FROM sqlite_master 
            WHERE name=? AND type IN ('table', 'view')
        """, (table_name,))
        create_sql = cursor.fetchone()
        create_sql = create_sql[0] if create_sql and create_sql[0] else "未找到創(chuàng)建語句(可能是視圖或系統(tǒng)表)"
         
        cursor.close()
         
        # 更新顯示
        self.clear_structure_display()
        self.structure_text.config(state=tk.NORMAL)
         
        # 寫入表基本信息
        self.structure_text.insert(tk.END, f"=== 結構：{table_name} ===\n\n")
        self.structure_text.insert(tk.END, f"創(chuàng)建語句：\n{create_sql}\n\n")
        self.structure_text.insert(tk.END, "字段信息：\n")
        self.structure_text.insert(tk.END, f"{'序號':<5} {'字段名':<20} {'類型':<15} {'是否主鍵':<10} {'是否允許空':<10} {'默認值':<15}\n")
        self.structure_text.insert(tk.END, "-" * 80 + "\n")
         
        for field in structure:
            cid, name, type_, notnull, dflt_value, pk = field
            is_pk = "是" if pk else "否"
            is_nullable = "否" if notnull else "是"
            default = dflt_value if dflt_value is not None else "無"
             
            self.structure_text.insert(tk.END, f"{cid:<5} {name:<20} {type_:<15} {is_pk:<10} {is_nullable:<10} {default:<15}\n")
         
        self.structure_text.config(state=tk.DISABLED)

     
    def show_table_data(self, table_name):
        """顯示表數(shù)據(jù)"""
        cursor = self.db_connection.cursor()
        safe_table = table_name.replace('"', '""')
         
        try:
            # 獲取所有數(shù)據(jù) (使用安全表名包裹)
            cursor.execute(f'SELECT * FROM "{safe_table}"')
            data = cursor.fetchall()
             
            # 【核心修復】必須使用 cursor.description 獲取實際的列名，
            # 絕對不要用 PRAGMA 去匹配，以防止列數(shù)量錯位或視圖引發(fā)錯誤
            if cursor.description:
                columns = [desc[0] for desc in cursor.description]
            else:
                columns = []
             
            cursor.close()
             
            # 清空之前的數(shù)據(jù)
            self.clear_data_display()
             
            # 設置表格列
            if columns:
                self.data_tree["columns"] = columns
                for col in columns:
                    self.data_tree.heading(col, text=col)
                    self.data_tree.column(col, width=120, anchor=tk.CENTER)
            else:
                # 極端情況：表存在但連列名都沒有定義
                self.data_tree["columns"] = ("tip",)
                self.data_tree.heading("tip", text="提示")
             
            # 插入數(shù)據(jù)
            if data:
                for i, row in enumerate(data):
                    # 處理None值
                    formatted_row = [str(val) if val is not None else "NULL" for val in row]
                    self.data_tree.insert("", tk.END, iid=i, values=formatted_row)
            else:
                # 沒有數(shù)據(jù)時顯示提示
                col_tuple = ("該表暫無數(shù)據(jù)",) + ("",) * (len(columns) - 1) if columns else ("該表沒有定義列",)
                self.data_tree.insert("", tk.END, values=col_tuple)
                 
        except sqlite3.Error as e:
            self.clear_data_display()
            self.data_tree["columns"] = ("error",)
            self.data_tree.heading("error", text="錯誤信息")
            self.data_tree.column("error", anchor=tk.W)
            self.data_tree.insert("", tk.END, values=(f"查詢失?。簕str(e)}",))

     
    def clear_structure_display(self):
        """清空表結構顯示"""
        self.structure_text.config(state=tk.NORMAL)
        self.structure_text.delete(1.0, tk.END)
        self.structure_text.config(state=tk.DISABLED)
     
    def clear_data_display(self):
        """清空數(shù)據(jù)表格"""
        # 刪除所有列
        for col in self.data_tree["columns"]:
            self.data_tree.heading(col, text="")
        self.data_tree["columns"] = ()
         
        # 刪除所有行
        for item in self.data_tree.get_children():
            self.data_tree.delete(item)
     
    def export_data(self):
        """導出當前表的數(shù)據(jù)"""
        selected_table = self.table_var.get()
        if not selected_table or not self.db_connection:
            messagebox.showwarning("警告", "請先選擇數(shù)據(jù)庫和數(shù)據(jù)表")
            return
         
        # 選擇保存路徑
        file_path = filedialog.asksaveasfilename(
            title="導出數(shù)據(jù)",
            defaultextension=".csv",
            filetypes=[("CSV文件", "*.csv"), ("文本文件", "*.txt"), ("所有文件", "*.*")]
        )
         
        if not file_path:
            return
         
        cursor = self.db_connection.cursor()
        safe_table = selected_table.replace('"', '""')
         
        try:
            # 統(tǒng)一修改使用游標去獲取列名確保絕對匹配
            cursor.execute(f'SELECT * FROM "{safe_table}"')
            data = cursor.fetchall()
            columns = [desc[0] for desc in cursor.description] if cursor.description else []
             
            cursor.close()
             
            # 寫入文件
            with open(file_path, "w", encoding="utf-8-sig", newline="") as f:
                # 寫入表頭
                if columns:
                    f.write(",".join(columns) + "\n")
                 
                # 寫入數(shù)據(jù)
                if data:
                    for row in data:
                        formatted_row = []
                        for val in row:
                            if val is None:
                                formatted_val = "NULL"
                            elif isinstance(val, str):
                                if "," in val or "\n" in val or '"' in val:
                                    formatted_val = '"' + val.replace('"', '""') + '"'
                                else:
                                    formatted_val = val
                            else:
                                formatted_val = str(val)
                            formatted_row.append(formatted_val)
                         
                        f.write(",".join(formatted_row) + "\n")
             
            messagebox.showinfo("成功", f"數(shù)據(jù)已成功導出到：\n{file_path}")
             
        except Exception as e:
            messagebox.showerror("錯誤", f"導出數(shù)據(jù)失?。簕str(e)}")

     
    def __del__(self):
        """程序退出時關閉數(shù)據(jù)庫連接"""
        if self.db_connection:
            self.db_connection.close()
 
if __name__ == "__main__":
    root = tk.Tk()
    app = SQLiteViewer(root)
    root.mainloop()