from openpyxl import Workbook
from en_lemma import lemmas  # 導(dǎo)入字典數(shù)據(jù)
 
# 創(chuàng)建新工作簿并獲取活動工作表
wb = Workbook()
ws = wb.active 
 
# 遍歷字典的鍵值對
for key, values in lemmas.items(): 
    # 構(gòu)造行數(shù)據(jù)：鍵作為第一列，后面接列表元素
    row_data = [key] + values
    # 將數(shù)據(jù)寫入工作表
    ws.append(row_data) 
 
# 保存Excel文件
wb.save("lemmas_data.xlsx") 

import pandas as pd
import sqlite3
 
# 1. 讀取 Excel 文件
excel_file = "en_lemmas.xlsx"  # Excel 文件路徑
sheet_name = "Sheet1"  # Excel 工作表名稱
df = pd.read_excel(excel_file, sheet_name=sheet_name)
 
# 2. 創(chuàng)建 SQLite 數(shù)據(jù)庫
sqlite_db = "verb_forms.db"  # SQLite 數(shù)據(jù)庫文件路徑
conn = sqlite3.connect(sqlite_db)
cursor = conn.cursor()
 
# 3. 創(chuàng)建表
create_table_query = """
CREATE TABLE IF NOT EXISTS verb_forms (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    base_form TEXT NOT NULL,
    variant TEXT NOT NULL
);
"""
cursor.execute(create_table_query)
 
# 4. 將數(shù)據(jù)插入 SQLite 表
for index, row in df.iterrows():
    base_form = row["BaseForm"]  # 假設(shè) A 列是 BaseForm
    
    # 遍歷 B、C、D 列（Variant1, Variant2, Variant3）
    for col in ["Variant1", "Variant2", "Variant3", "Variant4", "Variant5", "Variant6"]:
        variant = row[col]
        
        # 檢查是否為有效值（非空）
        if pd.notna(variant) and variant.strip() != "":
            # 使用參數(shù)化查詢避免 SQL 注入和特殊字符問題
            insert_query = """
            INSERT INTO verb_forms (base_form, variant)
            VALUES (?, ?);
            """
            cursor.execute(insert_query, (base_form, variant.strip()))
 
# 5. 提交更改并關(guān)閉連接
conn.commit()
conn.close()
 
print(f"數(shù)據(jù)已成功導(dǎo)入 SQLite 數(shù)據(jù)庫: {sqlite_db}")

import sqlite3
 
def get_base_form(word_to_find: str) -> str:
    try:
        # 連接數(shù)據(jù)庫（自動處理相對路徑）
        with sqlite3.connect("verb_forms.db")  as conn:
            cursor = conn.cursor() 
            # 參數(shù)化查詢防止 SQL 注入 
            cursor.execute( 
                "SELECT base_form FROM verb_forms WHERE variant = ?",
                (word_to_find,)
            )
            result = cursor.fetchone() 
            return result[0] if result else ""  # 關(guān)鍵修復(fù)：去掉括號 
    except sqlite3.Error as e:
        print(f"數(shù)據(jù)庫錯誤：{e}")
        return ""
 
# 測試代碼 
if __name__ == "__main__":
    test_word = "thought"
    base_form = get_base_form(test_word)
    if base_form:
        print(f"單詞 '{test_word}' 的基本形式是：{base_form}")
    else:
        print(f"未找到 '{test_word}' 的基本形式")

import sqlite3
import tkinter as tk
from tkinter import ttk
from tkinter import messagebox
 
 
def get_base_form(word_to_find: str) -> str:
    try:
        # 連接數(shù)據(jù)庫（自動處理相對路徑）
        with sqlite3.connect("verb_forms.db")  as conn:
            cursor = conn.cursor() 
            # 參數(shù)化查詢防止 SQL 注入
            cursor.execute( 
                "SELECT base_form FROM verb_forms WHERE variant = ?",
                (word_to_find,)
            )
            result = cursor.fetchone() 
            return result[0] if result else ""
    except sqlite3.Error as e:
        messagebox.showerror("  數(shù)據(jù)庫錯誤", f"數(shù)據(jù)庫錯誤：{e}")
        return ""
 
 
def get_variants(word_to_find: str) -> list:
    try:
        with sqlite3.connect("verb_forms.db")  as conn:
            cursor = conn.cursor() 
 
            # 先驗證輸入的是否為有效原形
            cursor.execute("SELECT  base_form FROM verb_forms WHERE base_form = ?", (word_to_find,))
            if not cursor.fetchone(): 
                return []  # 不是有效原形則直接返回空
 
            # 查詢變形
            cursor.execute("SELECT  variant FROM verb_forms WHERE base_form = ?", (word_to_find,))
            return [row[0] for row in cursor.fetchall()] 
 
    except sqlite3.Error as e:
        messagebox.showerror("  數(shù)據(jù)庫錯誤", f"數(shù)據(jù)庫錯誤：{e}")
        return []
 
 
def query():
    word = entry.get() 
    if choice.get()  == 1:
        base_form = get_base_form(word)
 
        if base_form:
            result_text.delete(1.0,  tk.END)
            result_text.insert(tk.END,  f"單詞【{word}】的基本形式是：{base_form}")
        else:
            result_text.delete(1.0,  tk.END)
            result_text.insert(tk.END,  f"未找到【{word}】的基本形式")
    elif choice.get()  == 2:
        variants = get_variants(word)
        result_text.delete(1.0,  tk.END)
 
        if variants:
            # 顯示變形列表
            result_text.insert(tk.END,  f"原形【{word}】的變形：\n" + ", ".join(variants))
        else:
            # 分情況提示
            try:
                with sqlite3.connect("verb_forms.db")  as conn:
                    cursor = conn.cursor() 
                    cursor.execute("SELECT  base_form FROM verb_forms WHERE variant = ?", (word,))
                    if cursor.fetchone(): 
                        result_text.insert(tk.END,  f"注意：您輸入的是變形單詞，請切換至「原形」模式查詢")
                    else:
                        result_text.insert(tk.END,  f"數(shù)據(jù)庫未收錄【{word}】的相關(guān)變形")
            except sqlite3.Error as e:
                messagebox.showerror("  數(shù)據(jù)庫錯誤", f"數(shù)據(jù)庫錯誤：{e}")
 
 
def copy_text():
    result_text.clipboard_clear() 
    result_text.clipboard_append(result_text.selection_get()) 
 
 
def cut_text():
    copy_text()
    result_text.delete(tk.SEL_FIRST,  tk.SEL_LAST)
 
 
def paste_text():
    result_text.insert(tk.INSERT,  result_text.clipboard_get())
    
def entry_copy():
    try:
        # 獲取 Entry 選中內(nèi)容并復(fù)制 
        entry.clipboard_clear() 
        entry.clipboard_append(entry.selection_get()) 
    except tk.TclError:
        pass  # 無選中內(nèi)容時不操作 
 
def entry_cut():
    entry_copy()  # 先復(fù)制 
    try:
        entry.delete(tk.SEL_FIRST,  tk.SEL_LAST)  # 再刪除選中內(nèi)容 
    except tk.TclError:
        pass 
 
def entry_paste():
    entry.insert(tk.INSERT,  entry.clipboard_get())   # 插入剪貼板內(nèi)容 
 
# 創(chuàng)建主窗口
root = tk.Tk()
root.title("  單詞查詢")
# 設(shè)置所有標(biāo)準(zhǔn)控件的默認(rèn)字體
root.option_add("*Font",  ("Times New Roman", 14))  # 影響 Entry、Button 等非 ttk 控件
 
# 設(shè)置 ttk 控件的主題字體
style = ttk.Style()
style.configure(".",  font=("Times New Roman", 14))  # 通配符 . 表示所有 ttk 控件
 
# 創(chuàng)建輸入框、單選按鈕和查詢按鈕
frame_top = ttk.Frame(root)
frame_top.pack(pady=10) 
 
entry = ttk.Entry(frame_top, width=20)
entry.pack(side=tk.LEFT,  padx=5)
entry.bind("<Return>",  lambda event: query())  # 按回車觸發(fā)查詢
entry.focus_set()
 
# 創(chuàng)建 Entry 的右鍵菜單 
entry_menu = tk.Menu(entry, tearoff=0)
entry_menu.add_command(label="  剪切", command=entry_cut)
entry_menu.add_command(label="  復(fù)制", command=entry_copy)
entry_menu.add_command(label="  粘貼", command=entry_paste)
 
def show_entry_menu(event):
    entry_menu.post(event.x_root,  event.y_root)  # 顯示菜單 
 
entry.bind("<Button-3>",  show_entry_menu)  # 綁定右鍵事件 
 
 
choice = tk.IntVar()
choice.set(2) 
radio1 = ttk.Radiobutton(frame_top, text="原形", variable=choice, value=1)
radio1.pack(side=tk.LEFT,  padx=5)
 
radio2 = ttk.Radiobutton(frame_top, text="變形", variable=choice, value=2)
radio2.pack(side=tk.LEFT,  padx=5)
 
query_button = ttk.Button(frame_top, text="查詢", command=query)
query_button.pack(side=tk.LEFT,  padx=5)
 
# 創(chuàng)建結(jié)果顯示文本框
result_text = tk.Text(root, height=10, width=60)
result_text.pack(pady=10) 
 
# 創(chuàng)建右鍵菜單
menu = tk.Menu(result_text, tearoff=0)
menu.add_command(label="  復(fù)制", command=copy_text)
menu.add_command(label="  剪切", command=cut_text)
menu.add_command(label="  粘貼", command=paste_text)
 
def show_menu(event):
    menu.post(event.x_root,  event.y_root)
    
result_text.bind("<Button-3>",  show_menu)
 
# 運行主循環(huán)
root.mainloop()