SQLite 資料庫 -6 多對多關聯
使用的資料表名稱稱：
學生資料表 (Student Table): 使用
student課程資料表 (Course Table): 使用
course2選課資料表 (Selection Table): 使用
CourseSelection
# many_to_many_integrated_v2.py
import tkinter as tk
from tkinter import messagebox, ttk
import sqlite3
import os
DB_NAME = "MySchoolDB.db"
STUDENT_TABLE = "student"
COURSE_TABLE = "course2"
SELECTION_TABLE = "CourseSelection"
# --- 資料庫操作類別 ---
class DatabaseManager:
"""管理 MySchoolDB.db 中 student, course2, CourseSelection 三個資料表的 CRUD 操作。"""
def __init__(self, db_name=DB_NAME):
self.db_name = db_name
def connect(self):
"""建立資料庫連線並啟用外來鍵約束"""
try:
conn = sqlite3.connect(self.db_name)
conn.execute("PRAGMA foreign_keys = ON;")
return conn
except sqlite3.Error as e:
messagebox.showerror("資料庫錯誤", f"無法連線到資料庫: {e}")
return None
# --- 1. 建立資料庫 ---
def create_tables(self):
"""建立 student, course2, CourseSelection 三個資料表"""
conn = self.connect()
if not conn: return False
try:
cursor = conn.cursor()
# 1. 建立 student (學生資料表)
# 欄位: *學號 (主鍵), 姓名, 系碼
cursor.execute(f'''
CREATE TABLE IF NOT EXISTS {STUDENT_TABLE} (
學號 TEXT PRIMARY KEY,
姓名 TEXT NOT NULL,
系碼 TEXT
)
''')
# 2. 建立 course2 (課程資料表)
# 欄位: *課程代號 (主鍵), 課程名稱, 學分數, #老師編號
cursor.execute(f'''
CREATE TABLE IF NOT EXISTS {COURSE_TABLE} (
課程代號 TEXT PRIMARY KEY,
課程名稱 TEXT NOT NULL,
學分數 INTEGER,
老師編號 TEXT
-- 假設老師編號的外來鍵約束由其他程式或資料庫結構處理
)
''')
# 3. 建立 CourseSelection (選課資料表 - 關聯表)
# 欄位: *學號 (複合主鍵), *課程代號 (複合主鍵), 成績
cursor.execute(f'''
CREATE TABLE IF NOT EXISTS {SELECTION_TABLE} (
學號 TEXT NOT NULL,
課程代號 TEXT NOT NULL,
成績 INTEGER,
PRIMARY KEY (學號, 課程代號),
-- 外來鍵約束
FOREIGN KEY (學號) REFERENCES {STUDENT_TABLE}(學號) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (課程代號) REFERENCES {COURSE_TABLE}(課程代號) ON DELETE CASCADE ON UPDATE CASCADE
)
''')
conn.commit()
return True
except sqlite3.Error as e:
messagebox.showerror("資料庫錯誤", f"建立資料表失敗: {e}")
return False
finally:
if conn: conn.close()
# --- 執行 CRUD 操作 (通用) ---
def execute_crud(self, query, params=None, action="select"):
conn = self.connect()
if not conn: return False if action != "select" else []
try:
cursor = conn.cursor()
if params:
cursor.execute(query, params)
else:
cursor.execute(query)
if action == "select":
return cursor.fetchall()
conn.commit()
return cursor.rowcount
except sqlite3.IntegrityError as e:
msg = "操作失敗："
if "UNIQUE constraint failed" in str(e):
msg += "主鍵重複或複合主鍵重複！"
elif "foreign key constraint failed" in str(e):
msg += "外來鍵無效，請確認學號或課程代號是否已存在於對應的主表中！"
else:
msg += str(e)
messagebox.showwarning("資料庫約束錯誤", msg)
conn.rollback()
return False
except sqlite3.Error as e:
messagebox.showerror("資料庫錯誤", f"{action} 操作失敗: {e}")
conn.rollback()
return False
finally:
if conn: conn.close()
# --- 6. 刪除資料庫 ---
def delete_database_file(self):
if os.path.exists(self.db_name):
try:
os.remove(self.db_name)
return True
except OSError as e:
messagebox.showerror("刪除失敗", f"無法刪除資料庫檔案 {self.db_name}: {e}\n請確認是否有其他程式正在使用此檔案。")
return False
else:
messagebox.showwarning("刪除失敗", f"資料庫檔案 {self.db_name} 不存在。")
return False
# --- Tkinter GUI 應用程式類別 ---
class ManyToManyManager(tk.Tk):
def __init__(self):
super().__init__()
self.title("多對多關聯管理系統 (學生/課程/選課)")
self.db = DatabaseManager()
self.geometry("900x600")
self.create_main_widgets()
# 初始載入資料
self.load_student_data()
self.load_course_data()
self.load_selection_data()
def create_main_widgets(self):
# 頂部功能按鈕
button_frame = tk.Frame(self, padx=10, pady=5)
button_frame.pack(fill='x')
tk.Button(button_frame, text="1. 建立資料庫", command=self.create_db_command).grid(row=0, column=0, padx=5, pady=5)
tk.Button(button_frame, text="6. 刪除資料庫", command=self.delete_db_command).grid(row=0, column=1, padx=5, pady=5)
# Notebook 分頁
self.notebook = ttk.Notebook(self)
self.notebook.pack(pady=10, padx=10, expand=True, fill="both")
# 建立三個分頁
self.student_frame = ttk.Frame(self.notebook); self.notebook.add(self.student_frame, text=f' 學生資料 ({STUDENT_TABLE}) ')
self.course_frame = ttk.Frame(self.notebook); self.notebook.add(self.course_frame, text=f' 課程資料 ({COURSE_TABLE}) ')
self.selection_frame = ttk.Frame(self.notebook); self.notebook.add(self.selection_frame, text=f' 選課紀錄 ({SELECTION_TABLE}) ')
self.create_student_widgets(self.student_frame)
self.create_course_widgets(self.course_frame)
self.create_selection_widgets(self.selection_frame)
# ==================================
# 學生資料 (student)
# ==================================
def create_student_widgets(self, parent):
input_frame = tk.LabelFrame(parent, text="輸入/查詢區", padx=10, pady=5); input_frame.pack(fill='x', padx=10, pady=5)
tk.Label(input_frame, text="學號:").grid(row=0, column=0, padx=5); self.s_entry_id = tk.Entry(input_frame); self.s_entry_id.grid(row=0, column=1, padx=5)
tk.Label(input_frame, text="姓名:").grid(row=0, column=2, padx=5); self.s_entry_name = tk.Entry(input_frame); self.s_entry_name.grid(row=0, column=3, padx=5)
tk.Label(input_frame, text="系碼:").grid(row=1, column=0, padx=5); self.s_entry_dept = tk.Entry(input_frame); self.s_entry_dept.grid(row=1, column=1, padx=5)
btn_frame = tk.Frame(parent, padx=10, pady=5); btn_frame.pack(fill='x')
tk.Button(btn_frame, text="2. 新增", command=self.add_student_data).grid(row=0, column=0, padx=5)
tk.Button(btn_frame, text="3. 更正", command=self.update_student_data).grid(row=0, column=1, padx=5)
tk.Button(btn_frame, text="4. 刪除", command=self.delete_student_data).grid(row=0, column=2, padx=5)
tk.Button(btn_frame, text="5. 顯示", command=self.load_student_data).grid(row=0, column=3, padx=5)
self.student_tree = ttk.Treeview(parent, columns=('學號', '姓名', '系碼'), show='headings'); self.student_tree.pack(fill='both', expand=True, padx=10, pady=10)
self.student_tree.heading('學號', text='學號'); self.student_tree.column('學號', width=120, anchor='center')
self.student_tree.heading('姓名', text='姓名'); self.student_tree.column('姓名', width=150)
self.student_tree.heading('系碼', text='系碼'); self.student_tree.column('系碼', width=100, anchor='center')
self.student_tree.bind('<<TreeviewSelect>>', self.on_student_select)
def on_student_select(self, event):
selected_item = self.student_tree.focus()
if selected_item:
values = self.student_tree.item(selected_item, 'values')
self.s_entry_id.delete(0, tk.END); self.s_entry_id.insert(0, values[0])
self.s_entry_name.delete(0, tk.END); self.s_entry_name.insert(0, values[1])
self.s_entry_dept.delete(0, tk.END); self.s_entry_dept.insert(0, values[2])
def load_student_data(self):
for item in self.student_tree.get_children(): self.student_tree.delete(item)
query = f"SELECT 學號, 姓名, 系碼 FROM {STUDENT_TABLE} ORDER BY 學號"
data = self.db.execute_crud(query, action="select")
for row in data: self.student_tree.insert('', tk.END, values=row)
def add_student_data(self):
query = f"INSERT INTO {STUDENT_TABLE} (學號, 姓名, 系碼) VALUES (?, ?, ?)"
if self.db.execute_crud(query, (self.s_entry_id.get().strip(), self.s_entry_name.get().strip(), self.s_entry_dept.get().strip()), action="insert"):
messagebox.showinfo("成功", "學生資料新增成功！"); self.load_student_data()
def update_student_data(self):
query = f"UPDATE {STUDENT_TABLE} SET 姓名=?, 系碼=? WHERE 學號=?"
if self.db.execute_crud(query, (self.s_entry_name.get().strip(), self.s_entry_dept.get().strip(), self.s_entry_id.get().strip()), action="update"):
messagebox.showinfo("成功", "學生資料更正成功！"); self.load_student_data(); self.load_selection_data()
def delete_student_data(self):
stu_id = self.s_entry_id.get().strip()
if messagebox.askyesno("確認刪除", f"**警告：** 刪除學號 {stu_id} 會同時刪除其選課紀錄 ({SELECTION_TABLE})。確定刪除嗎？"):
query = f"DELETE FROM {STUDENT_TABLE} WHERE 學號=?"
if self.db.execute_crud(query, (stu_id,), action="delete"):
messagebox.showinfo("成功", "學生資料刪除成功！"); self.load_student_data(); self.load_selection_data()
# ==================================
# 課程資料 (course2)
# ==================================
def create_course_widgets(self, parent):
input_frame = tk.LabelFrame(parent, text="輸入/查詢區", padx=10, pady=5); input_frame.pack(fill='x', padx=10, pady=5)
tk.Label(input_frame, text="課程代號:").grid(row=0, column=0, padx=5); self.c_entry_id = tk.Entry(input_frame); self.c_entry_id.grid(row=0, column=1, padx=5)
tk.Label(input_frame, text="課程名稱:").grid(row=0, column=2, padx=5); self.c_entry_name = tk.Entry(input_frame); self.c_entry_name.grid(row=0, column=3, padx=5)
tk.Label(input_frame, text="學分數:").grid(row=1, column=0, padx=5); self.c_entry_credits = tk.Entry(input_frame); self.c_entry_credits.grid(row=1, column=1, padx=5)
tk.Label(input_frame, text="老師編號:").grid(row=1, column=2, padx=5); self.c_entry_teacher = tk.Entry(input_frame); self.c_entry_teacher.grid(row=1, column=3, padx=5)
btn_frame = tk.Frame(parent, padx=10, pady=5); btn_frame.pack(fill='x')
tk.Button(btn_frame, text="2. 新增", command=self.add_course_data).grid(row=0, column=0, padx=5)
tk.Button(btn_frame, text="3. 更正", command=self.update_course_data).grid(row=0, column=1, padx=5)
tk.Button(btn_frame, text="4. 刪除", command=self.delete_course_data).grid(row=0, column=2, padx=5)
tk.Button(btn_frame, text="5. 顯示", command=self.load_course_data).grid(row=0, column=3, padx=5)
self.course_tree = ttk.Treeview(parent, columns=('代號', '名稱', '學分', '老師編號'), show='headings'); self.course_tree.pack(fill='both', expand=True, padx=10, pady=10)
self.course_tree.heading('代號', text='課程代號'); self.course_tree.column('代號', width=120, anchor='center')
self.course_tree.heading('名稱', text='課程名稱'); self.course_tree.column('名稱', width=180)
self.course_tree.heading('學分', text='學分數'); self.course_tree.column('學分', width=80, anchor='center')
self.course_tree.heading('老師編號', text='老師編號'); self.course_tree.column('老師編號', width=120, anchor='center')
self.course_tree.bind('<<TreeviewSelect>>', self.on_course_select)
def on_course_select(self, event):
selected_item = self.course_tree.focus()
if selected_item:
values = self.course_tree.item(selected_item, 'values')
self.c_entry_id.delete(0, tk.END); self.c_entry_id.insert(0, values[0])
self.c_entry_name.delete(0, tk.END); self.c_entry_name.insert(0, values[1])
self.c_entry_credits.delete(0, tk.END); self.c_entry_credits.insert(0, values[2])
self.c_entry_teacher.delete(0, tk.END); self.c_entry_teacher.insert(0, values[3])
def load_course_data(self):
for item in self.course_tree.get_children(): self.course_tree.delete(item)
query = f"SELECT 課程代號, 課程名稱, 學分數, 老師編號 FROM {COURSE_TABLE} ORDER BY 課程代號"
data = self.db.execute_crud(query, action="select")
for row in data: self.course_tree.insert('', tk.END, values=row)
def add_course_data(self):
query = f"INSERT INTO {COURSE_TABLE} (課程代號, 課程名稱, 學分數, 老師編號) VALUES (?, ?, ?, ?)"
if self.db.execute_crud(query, (self.c_entry_id.get().strip(), self.c_entry_name.get().strip(), self.c_entry_credits.get().strip(), self.c_entry_teacher.get().strip()), action="insert"):
messagebox.showinfo("成功", "課程資料新增成功！"); self.load_course_data()
def update_course_data(self):
query = f"UPDATE {COURSE_TABLE} SET 課程名稱=?, 學分數=?, 老師編號=? WHERE 課程代號=?"
if self.db.execute_crud(query, (self.c_entry_name.get().strip(), self.c_entry_credits.get().strip(), self.c_entry_teacher.get().strip(), self.c_entry_id.get().strip()), action="update"):
messagebox.showinfo("成功", "課程資料更正成功！"); self.load_course_data(); self.load_selection_data()
def delete_course_data(self):
course_id = self.c_entry_id.get().strip()
if messagebox.askyesno("確認刪除", f"**警告：** 刪除課程代號 {course_id} 會同時刪除相關選課紀錄 ({SELECTION_TABLE})。確定刪除嗎？"):
query = f"DELETE FROM {COURSE_TABLE} WHERE 課程代號=?"
if self.db.execute_crud(query, (course_id,), action="delete"):
messagebox.showinfo("成功", "課程資料刪除成功！"); self.load_course_data(); self.load_selection_data()
# ==================================
# 選課紀錄 (CourseSelection)
# ==================================
def create_selection_widgets(self, parent):
input_frame = tk.LabelFrame(parent, text="輸入/查詢區 (複合主鍵: 學號 + 課程代號)", padx=10, pady=5); input_frame.pack(fill='x', padx=10, pady=5)
tk.Label(input_frame, text="學號 (#外來鍵):").grid(row=0, column=0, padx=5); self.sel_entry_stu_id = tk.Entry(input_frame); self.sel_entry_stu_id.grid(row=0, column=1, padx=5)
tk.Label(input_frame, text="課程代號 (#外來鍵):").grid(row=0, column=2, padx=5); self.sel_entry_course_id = tk.Entry(input_frame); self.sel_entry_course_id.grid(row=0, column=3, padx=5)
tk.Label(input_frame, text="成績:").grid(row=1, column=0, padx=5); self.sel_entry_score = tk.Entry(input_frame); self.sel_entry_score.grid(row=1, column=1, padx=5)
btn_frame = tk.Frame(parent, padx=10, pady=5); btn_frame.pack(fill='x')
tk.Button(btn_frame, text="2. 新增", command=self.add_selection_data).grid(row=0, column=0, padx=5)
tk.Button(btn_frame, text="3. 更正", command=self.update_selection_data).grid(row=0, column=1, padx=5)
tk.Button(btn_frame, text="4. 刪除", command=self.delete_selection_data).grid(row=0, column=2, padx=5)
tk.Button(btn_frame, text="5. 顯示", command=self.load_selection_data).grid(row=0, column=3, padx=5)
self.selection_tree = ttk.Treeview(parent, columns=('學號', '課程代號', '成績', '姓名', '課程名稱'), show='headings'); self.selection_tree.pack(fill='both', expand=True, padx=10, pady=10)
self.selection_tree.heading('學號', text='學號'); self.selection_tree.column('學號', width=100, anchor='center')
self.selection_tree.heading('課程代號', text='課程代號'); self.selection_tree.column('課程代號', width=120, anchor='center')
self.selection_tree.heading('成績', text='成績'); self.selection_tree.column('成績', width=80, anchor='center')
self.selection_tree.heading('姓名', text='學生姓名'); self.selection_tree.column('姓名', width=150)
self.selection_tree.heading('課程名稱', text='課程名稱'); self.selection_tree.column('課程名稱', width=180)
self.selection_tree.bind('<<TreeviewSelect>>', self.on_selection_select)
def on_selection_select(self, event):
selected_item = self.selection_tree.focus()
if selected_item:
values = self.selection_tree.item(selected_item, 'values')
self.sel_entry_stu_id.delete(0, tk.END); self.sel_entry_stu_id.insert(0, values[0])
self.sel_entry_course_id.delete(0, tk.END); self.sel_entry_course_id.insert(0, values[1])
self.sel_entry_score.delete(0, tk.END); self.sel_entry_score.insert(0, values[2])
def load_selection_data(self):
for item in self.selection_tree.get_children(): self.selection_tree.delete(item)
query = f'''
SELECT
sel.學號, sel.課程代號, sel.成績, s.姓名, c.課程名稱
FROM
{SELECTION_TABLE} sel
LEFT JOIN {STUDENT_TABLE} s ON sel.學號 = s.學號
LEFT JOIN {COURSE_TABLE} c ON sel.課程代號 = c.課程代號
ORDER BY sel.學號, sel.課程代號
'''
data = self.db.execute_crud(query, action="select")
for row in data:
processed_row = tuple('' if x is None else x for x in row)
self.selection_tree.insert('', tk.END, values=processed_row)
def add_selection_data(self):
stu_id = self.sel_entry_stu_id.get().strip()
course_id = self.sel_entry_course_id.get().strip()
score_str = self.sel_entry_score.get().strip()
if not (stu_id and course_id and score_str):
messagebox.showwarning("警告", "所有欄位皆為必填！")
return
query = f"INSERT INTO {SELECTION_TABLE} (學號, 課程代號, 成績) VALUES (?, ?, ?)"
if self.db.execute_crud(query, (stu_id, course_id, score_str), action="insert"):
messagebox.showinfo("成功", "選課紀錄新增成功！"); self.load_selection_data()
def update_selection_data(self):
stu_id = self.sel_entry_stu_id.get().strip()
course_id = self.sel_entry_course_id.get().strip()
score_str = self.sel_entry_score.get().strip()
if not (stu_id and course_id and score_str):
messagebox.showwarning("警告", "請輸入學號、課程代號和新的成績！")
return
query = f"UPDATE {SELECTION_TABLE} SET 成績=? WHERE 學號=? AND 課程代號=?"
if self.db.execute_crud(query, (score_str, stu_id, course_id), action="update"):
messagebox.showinfo("成功", "選課紀錄更正成功！"); self.load_selection_data()
def delete_selection_data(self):
stu_id = self.sel_entry_stu_id.get().strip()
course_id = self.sel_entry_course_id.get().strip()
if not (stu_id and course_id):
messagebox.showwarning("警告", "請輸入學號和課程代號以指定刪除項目！")
return
if messagebox.askyesno("確認刪除", f"確定要刪除學號 {stu_id} 選課 {course_id} 的紀錄嗎？"):
query = f"DELETE FROM {SELECTION_TABLE} WHERE 學號=? AND 課程代號=?"
if self.db.execute_crud(query, (stu_id, course_id), action="delete"):
messagebox.showinfo("成功", "選課紀錄刪除成功！"); self.load_selection_data()
# ==================================
# 共同功能
# ==================================
def create_db_command(self):
if self.db.create_tables():
messagebox.showinfo("成功", f"資料庫 {DB_NAME} 及其所有資料表 ({STUDENT_TABLE}, {COURSE_TABLE}, {SELECTION_TABLE}) 已成功建立或已存在。")
self.load_student_data(); self.load_course_data(); self.load_selection_data()
def delete_db_command(self):
if messagebox.askyesno("確認刪除資料庫", f"**警告：** 確定要刪除整個資料庫檔案 {DB_NAME} 嗎？所有資料將會遺失！"):
if self.db.delete_database_file():
messagebox.showinfo("成功", "資料庫檔案已刪除。")
self.load_student_data(); self.load_course_data(); self.load_selection_data()
if __name__ == '__main__':
app = ManyToManyManager()
app.mainloop()
沒有留言:
張貼留言