db_gui.py

import tkinter as tk
from tkinter import ttk, messagebox
import pymysql
import datetime

#DB 연결 설정
db = pymysql.connect(
    host='ajsj123.iptime.org',
    port=3306,
    user='parksubin',
    password='qkr!tnqls',
    database='nfc_lock_db',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

def fetch_users():
    with db.cursor() as cursor:
        cursor.execute("SELECT * FROM users")
        return cursor.fetchall()

def refresh_table():
    for row in tree.get_children():
        tree.delete(row)
    for user in fetch_users():
        tree.insert('', 'end', values=(
            user['id'],
            user['name'],
            user['user_uid'],
            user['access_level'],
            user['registered_at']
        ))

def on_user_select(event):
    selected = tree.focus()
    if not selected:
        return
    values = tree.item(selected, 'values')

    entry_name.delete(0, tk.END)
    entry_name.insert(0, values[1])

    entry_uid.delete(0, tk.END)
    entry_uid.insert(0, values[2])

    entry_level.delete(0, tk.END)
    entry_level.insert(0, str(values[3]))

def update_user():
    selected = tree.focus()
    if not selected:
        messagebox.showwarning("경고", "수정할 사용자를 선택하세요.")
        return
    user_id = tree.item(selected, 'values')[0]

    name = entry_name.get()
    uid = entry_uid.get()
    access = entry_level.get()

    try:
        access = int(access)
    except ValueError:
        messagebox.showerror("오류", "권한 등급은 숫자여야 합니다.")
        return

    with db.cursor() as cursor:
        sql = "UPDATE users SET name=%s, user_uid=%s, access_level=%s WHERE id=%s"
        cursor.execute(sql, (name, uid, access, user_id))
    db.commit()
    refresh_table()
    messagebox.showinfo("성공", "사용자 정보가 수정되었습니다.")

def delete_user():
    selected = tree.focus()
    if not selected:
        messagebox.showwarning("경고", "삭제할 사용자를 선택하세요.")
        return
    user_id = tree.item(selected, 'values')[0]

    confirm = messagebox.askyesno("확인", "정말로 이 사용자를 삭제하시겠습니까?")
    if not confirm:
        return

    with db.cursor() as cursor:
        sql = "DELETE FROM users WHERE id=%s"
        cursor.execute(sql, (user_id,))
    db.commit()
    refresh_table()
    messagebox.showinfo("성공", "사용자가 삭제되었습니다.")

def add_user():
    name = entry_name.get().strip()
    uid = entry_uid.get().strip()
    level = entry_level.get().strip()

    if not name or not uid or not level:
        messagebox.showwarning("입력 오류", "모든 필드를 입력해주세요.")
        return

    try:
        level = int(level)
    except ValueError:
        messagebox.showwarning("입력 오류", "접근 권한은 숫자로 입력해주세요.")
        return

    try:
        with db.cursor() as cursor:
            sql = "INSERT INTO users (name, user_uid, access_level, registered_at) VALUES (%s, %s, %s, %s)"
            cursor.execute(sql, (name, uid, level, datetime.datetime.now()))
            db.commit()
            messagebox.showinfo("성공", "사용자 추가 완료!")
            entry_name.delete(0, tk.END)
            entry_uid.delete(0, tk.END)
            entry_level.delete(0, tk.END)
            refresh_table()
    except Exception as e:
        messagebox.showerror("DB 오류", str(e))

#GUI 구성
root = tk.Tk()
root.title("사용자 DB 관리 프로그램")

columns = ('id', 'name', 'uid', 'access_level', 'registered_at')
tree = ttk.Treeview(root, columns=columns, show='headings', height=10)
tree.pack(pady=10)

tree.heading('id', text='ID')
tree.heading('name', text='이름')
tree.heading('uid', text='UID')
tree.heading('access_level', text='권한')
tree.heading('registered_at', text='등록일')

tree.column('id', width=50, anchor='center')
tree.column('name', width=100, anchor='center')
tree.column('uid', width=150, anchor='center')
tree.column('access_level', width=60, anchor='center')
tree.column('registered_at', width=180, anchor='center')

tree.bind('<<TreeviewSelect>>', on_user_select)

#입력 폼
frame = tk.Frame(root)
frame.pack()

tk.Label(frame, text="이름").grid(row=0, column=0)
entry_name = tk.Entry(frame)
entry_name.grid(row=0, column=1)

tk.Label(frame, text="UID").grid(row=1, column=0)
entry_uid = tk.Entry(frame)
entry_uid.grid(row=1, column=1)

tk.Label(frame, text="접근 권한").grid(row=2, column=0)
entry_level = tk.Entry(frame)
entry_level.grid(row=2, column=1)

#버튼
tk.Button(root, text="사용자 추가", command=add_user).pack(pady=5)
tk.Button(root, text="사용자 수정", command=update_user).pack(pady=5)
tk.Button(root, text="사용자 삭제", command=delete_user).pack(pady=5)
tk.Button(root, text="새로고침", command=refresh_table).pack(pady=5)

refresh_table()

root.mainloop()