study_log blog

2022년 2학기 과제로 Embedded SQL을 수행한 결과물이다.

파이썬 및 웹에서 MySQL을 활용해 데이터베이스를 연동할 수 있도록 했다.

카페 운영을 위한 프로그램을 기획하여 제작하였고 table은 4개,

각각 데이터는 10개 이상으로 작성했다.

 

**3일만에 완성한 과제로 SQL에 대한 지식이 충분치 않아 결과물이 미숙할 수 있습니다**

 

ESQL-파이썬-웹-로고

 

 

Python ESQL CAFE.zip
0.02MB
make table and input data.sql
0.00MB

 

맨 위부터 Python, SQL(테이블 제작 및 데이터 입력 관련 파일)이다.

물론 글 아래에 코드도 삽입할 예정이다.

 

 

구현 고려사항과 관련된 이전 글은 여기를 클릭하면 된다.

2022.06.20 - [coding] - MySQL 웹 및 Python에서 사용하기(ESQL) 1

 

MySQL 웹 및 Python에서 사용하기(ESQL) 1 : 기획

2022년 2학기 과제로 Embedded SQL을 수행한 결과물이다. 파이썬 및 웹에서 MySQL을 활용해 데이터베이스를 연동할 수 있도록 했다. 카페 운영을 위한 프로그램을 기획하여 제작하였고 table은 4개, 각각

seeya-study.tistory.com


결과물

 

파이썬-ESQL-캡쳐본-1

 

위 사진에서 [원하는 관리 항목을 선택하세요]가 메인 창이며,

menu 버튼을 클릭할 경우 [메뉴 관리 시스템] 창이 뜬다.

그리고 UPDATE 버튼을 누를 경우 다음과 같은 창이 뜨며,

고유번호 - 변경하고 싶은 데이터 순으로 설명에 맞춰 입력한다.

 

파이썬-ESQL-캡쳐본-2

 

UPDATE 쿼리가 성공한 경우 우측 메세지 창이, 실패한 경우 좌측 메세지 창이 뜬다.

실패 메세지 창은 확인을 눌러도 UPDATE 창이 닫히지 않지만

성공 메세지 창은 확인을 누르면 UPDATE 창까지 닫힌다.

그리고 이전 창([메뉴 관리 시스템] 창)의 데이터가 갱신된다.

 

 

파이썬-ESQL-캡쳐본-3

 

예를 들어 위와 같이 UPDATE 창에 입력하게 되는 경우,

 

파이썬-ESQL-캡쳐본-4

 

위 창과 같이 이전 창의 MCOST값이 3000에서 4000으로 변경된다.

 

DELETE나 INSERT는 [메뉴 관리 시스템] 창 자체에서 입력이 가능하며,

어떤 입력 칸에 넣더라도 데이터 값이 중복되는 등의 오류가 아니라면

정상 쿼리 수행이 가능하다.

 

역시 성공 메세지 창을 닫게 되면 쿼리 수행 후의 테이블이 갱신된다.

 

QUIT버튼을 누를 경우 [메뉴 관리 시스템] 창이 닫히게 된다.

 

파이썬-ESQL-캡쳐본-5

 

메뉴 외에 재고(Stock), 주문(Order), 리뷰(Review)의 경우에도 동일한 구성이다.

다만 리뷰 테이블의 경우 속성 수가 6개이므로 창 크기를 키우고 배열을 조정했다.

 

파이썬-ESQL-캡쳐본-6

 

또한 메뉴 아래의 [examine review with join operation] 버튼을

누르게 되면 하단의 [리뷰 정렬해서 보기] 창이 뜬다.

 

JOIN 연산을 활용하여 리뷰 고유번호를 기준으로 정렬할 수 있도록 설정했다.


각각의 코드는 다음과 같다.

 

1. main.py : 메인 창을 띄우기 위한 파일

더보기
import tkinter as tk
from tkinter import * 
import stockpage as stp
import orderpage as odp
import menupage as mnp
import reviewpage as rvp
import joinPage as jp

MainWindow=tk.Tk()

MainWindow.title("원하는 관리 항목을 선택하세요")
MainWindow.geometry("800x280+1000+150") #가로,세로 가로위치,세로위치
MainWindow.resizable(False, False) #창 사이즈 변경 방지

#stock / menu / order / review

MainFrame = tk.Frame(MainWindow)
MainFrame.pack(pady=20)
SubFrame = tk.Frame(MainWindow)
SubFrame.pack(pady=10)

button1 =tk.Button(MainFrame, text="stock", width=20, height=10, fg="white", bg = "black", cursor="hand2", command = stp.stockFunction)
button1.pack(side = "left", padx=20)
button2 =tk.Button(MainFrame, text="menu", width=20, height=10, fg="white", bg = "black", cursor="hand2", command = mnp.menuFunction)
button2.pack(side = "left", padx=20)
button3 =tk.Button(MainFrame, text="order", width=20, height=10, fg="white", bg = "black", cursor="hand2", command = odp.orderFunction)
button3.pack(side = "left", padx=20)
button4 =tk.Button(MainFrame, text="review", width=20, height=10, fg="white", bg = "black", cursor="hand2", command = rvp.reviewFunction)
button4.pack(side = "left", padx=20)

button5 = tk.Button(SubFrame, text="examine review with join operation", width=100, height=2, fg="white", bg = "#2f3640", cursor="hand2", command = jp.joinFunction)
button5.pack()

MainWindow.mainloop()

2. joinPage.py : JOIN 연산 수행을 위한 창을 띄우기 위한 파일. 리뷰를 통해 정렬해 볼 수 있는 창

더보기
import tkinter as tk
from tkinter import * 
from tkinter import messagebox 
import tkinter.font as tkFont
import pymysql

def getData() :
    #데이터 읽기(mysql에서 가져오기)
    strData1 = []; strData2 = []; strData3 = []; strData4 = []; strData5 = []; strData6 = [] 

    conn = pymysql.connect(host='localhost', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 
    cur = conn.cursor() 
    cur.execute("select Review.RNUM, Review.RDATE, stock.SNAME, Menu.MNAME, `order`.OTAKE, `order`.OHOTCOLD from review inner join stock on review.SNUM = stock.SNUM inner join `order` on review.ONUM = `order`.ONUM inner join menu on review.MNUM = menu.MNUM order by RDATE") 

    while (True) : 
        row = cur.fetchone() 
        if row == None : 
            break 
        strData1.append(row[0]); strData2.append(row[1]); strData3.append(row[2]); strData4.append(row[3]); strData5.append(row[4]); strData6.append(row[5])
    
    #데이터 갱신해서 불러오기 전 초기화(먼저 있던 데이터 삭제하고 가져오기)
    listData1.delete(0, listData1.size() - 1) 
    listData2.delete(0, listData2.size() - 1) 
    listData3.delete(0, listData3.size() - 1) 
    listData4.delete(0, listData4.size() - 1) 
    listData5.delete(0, listData5.size() - 1) 
    listData6.delete(0, listData6.size() - 1) 


    for item1, item2, item3, item4, item5, item6 in zip(strData1, strData2, strData3, strData4, strData5, strData6) : 
        listData1.insert(END, item1) 
        listData2.insert(END, item2) 
        listData3.insert(END, item3) 
        listData4.insert(END, item4) 
        listData5.insert(END, item5) 
        listData6.insert(END, item6) 
    conn.close() 

def joinFunction():
    SubWindow = tk.Tk()
    SubWindow.title("리뷰 정렬해서 보기")
    SubWindow.geometry("900x400+600+150")
    SubWindow.resizable(False, False)

    voidFrame = tk.Frame(SubWindow, height="20")
    voidFrame.pack()
    textFrame1 = tk.Frame(SubWindow)
    textFrame1.pack()
    labelFrame1 = tk.Frame(SubWindow)
    labelFrame1.pack()
    listFrame = tk.Frame(SubWindow) 
    listFrame.pack(side = "bottom", fill="both", expand=1) 
    
    #설명
    textLabel1 = tk.Label(textFrame1, text="리뷰를 기준으로 날짜, 주재료명, 주문한 메뉴, 테이크아웃 여부, 메뉴 온도를 확인할 수 있는 페이지입니다")
    textLabel1.pack(side=BOTTOM, pady=10)

    #각 데이터 이름 담은 라벨 제작
    dataLabel1 = tk.Label(labelFrame1, text="RNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel1.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel2 = tk.Label(labelFrame1, text="RDATE", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel2.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel3 = tk.Label(labelFrame1, text="SNAME", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel3.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel4 = tk.Label(labelFrame1, text="MNAME", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel4.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel5 = tk.Label(labelFrame1, text="OTAKE", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel5.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel6 = tk.Label(labelFrame1, text="OHOTCOLD", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel6.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)

    #각 데이터를 담은 리스트 제작
    global listData1; global listData2; global listData3; global listData4; global listData5; global listData6
    listData1 = tk.Listbox(listFrame, bg = "white", width=18) 
    listData1.pack(side="left", fill="both", expand=1, padx=5, pady=10) 
    listData2 = tk.Listbox(listFrame, bg = "white", width=18) 
    listData2.pack(side="left", fill="both", expand=1, padx=5, pady=10) 
    listData3 = tk.Listbox(listFrame, bg = "white", width=18) 
    listData3.pack(side="left", fill="both", expand=1, padx=5, pady=10) 
    listData4 = tk.Listbox(listFrame, bg = "white", width=18) 
    listData4.pack(side="left", fill="both", expand=1, padx=5, pady=10) 
    listData5 = tk.Listbox(listFrame, bg = "white", width=18) 
    listData5.pack(side="left", fill="both", expand=1, padx=5, pady=10) 
    listData6 = tk.Listbox(listFrame, bg = "white", width=18) 
    listData6.pack(side="left", fill="both", expand=1, padx=5, pady=10) 

    getData()

    SubWindow.mainloop()

3. menupage.py : 메뉴 테이블 관리를 위한 창

더보기
import tkinter as tk
from tkinter import * 
from tkinter import messagebox 
import tkinter.font as tkFont
import pymysql
import updateMenuPage as upp

def getData() :
    #데이터 읽기(mysql에서 가져오기)
    strData1 = []; strData2 = []; strData3 = []; strData4 = [] 
    conn = pymysql.connect(host='localhost', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 
    cur = conn.cursor() 
    cur.execute("SELECT * FROM menu ORDER BY length(MNUM) desc,MNUM desc;") #오름차순 정렬에 계속 오류가 생겨서 내림차순으로 정렬함

    while (True) : 
        row = cur.fetchone() 
        if row == None : 
            break 
        strData1.append(row[0]); strData2.append(row[1]); strData3.append(row[2]); strData4.append(row[3]) 
    
    #데이터 갱신해서 불러오기 전 초기화(먼저 있던 데이터 삭제하고 가져오기)
    listData1.delete(0, listData1.size() - 1) 
    listData2.delete(0, listData2.size() - 1) 
    listData3.delete(0, listData3.size() - 1) 
    listData4.delete(0, listData4.size() - 1) 

    for item1, item2, item3, item4 in zip(strData1, strData2, strData3, strData4) : 
        listData1.insert(END, item1) 
        listData2.insert(END, item2) 
        listData3.insert(END, item3) 
        listData4.insert(END, item4) 
    
    conn.close() 

def insertData() : 
    conn, cur = None, None 
    data = [""] * 4 
    sql = "" 

    # connect database
    conn = pymysql.connect(host='127.0.0.1', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 

    # create cursor 
    cur = conn.cursor() 
    data[0] = edit1.get(); data[1] = edit2.get(); data[2] = edit3.get(); data[3] = edit4.get();
    
    try : 
        # sql script (DML, Data Manipulation Language) : INSERT 
        sql = "INSERT INTO menu VALUES('" + data[0] + "', '" + data[1] + "', '" + data[2] + "', " + data[3] + ")" 
        print(sql) 
        cur.execute(sql) 
    
    except : 
        tk.messagebox.showerror('오류', '데이터 입력 오류') 
    
    else: tk.messagebox.showinfo('성공', '데이터 입력 성공')
    
    conn.commit() 
    conn.close()

    #입력 칸 리셋
    edit1.delete(0, listData1.size() - 1) 
    edit2.delete(0, listData2.size() - 1) 
    edit3.delete(0, listData3.size() - 1) 
    edit4.delete(0, listData4.size() - 1) 

    getData()

def deleteData() : 
    conn, cur = None, None 
    data = [""] * 4 
    sql = "" 

    # connect database
    conn = pymysql.connect(host='127.0.0.1', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 

    # create cursor 
    cur = conn.cursor() 
    data[0] = edit1.get(); data[1] = edit2.get(); data[2] = edit3.get(); data[3] = edit4.get();
    
    try : 
        #MNUM O -> MNUM은 primary key이므로 단일 검색 가능, 예외 설정 X
        if (data[0] != '' and data[1] == '' and data[2] == '' and data[3] ==''):
            sql = "DELETE FROM menu WHERE MNUM = '" + data[0] + "'"
        elif (data[0] == '' and data[1] == '' and data[2] == '' and data[3] ==''):
            sql = ""

        #MNUM X, MNAME O
        elif(data[1] != ''):
            #MNUM X, MNAME O, SNUM O
            if (data[2] != ''):
                if (data[3] != ''):
                    #MNUM X, MNAME O, SNUM O, MCOST O
                    sql = "DELETE FROM menu WHERE MNAME = '" + data[1] + "' AND SNUM = '" + data[2] + "' AND MCOST = " + data[3] + ""
                else:
                    #MNUM X, MNAME O, SNUM O, MCOST X
                    sql = "DELETE FROM menu WHERE MNAME = '" + data[1] + "' AND SNUM = '" + data[2] + "'"
            #MNUM X, MNAME O, SNUM X
            else:
                if (data[3] != ''):
                    #MNUM X, MNAME O, SNUM X, MCOST O
                    sql = "DELETE FROM menu WHERE MNAME = '" + data[1] + "' AND MCOST = " + data[3] + ""
                else:
                    #MNUM X, MNAMEO, SNUM X, MCOST X
                    sql = "DELETE FROM menu WHERE MNAME = '" + data[1] + "'"
        
        #MNUM X, MNAME X, SNUM O
        elif (data[2] != ''):
            if (data[3] != ''):
                #MNUM X, MNAME X, SNUM O, MCOST O
                sql = "DELETE FROM menu WHERE SNUM = '" + data[2] + "' AND MCOST = " + data[3] + ""
            else:
                #MNUM X, MNAME X, SNUM O, MCOST X
                sql = "DELETE FROM menu WHERE SNUM = '" + data[2] + "'"
        
        #MNUM X, MNAME X, SNUM X, MCOST O
        elif (data[3] != ''):
            sql = "DELETE FROM menu WHERE MCOST = " + data[3] + ""
    
        print(sql)
        cur.execute(sql) 
    
    except : 
        tk.messagebox.showerror('오류', '데이터 삭제 오류') 
    
    else: tk.messagebox.showinfo('성공', '데이터 삭제 성공')
    
    conn.commit() 
    conn.close()

    #입력 칸 리셋
    edit1.delete(0, listData1.size() - 1) 
    edit2.delete(0, listData2.size() - 1) 
    edit3.delete(0, listData3.size() - 1) 
    edit4.delete(0, listData4.size() - 1) 

    getData()

def updateData() :
    #새 창 열고 검색해서 update
    upp.updateFunction()

    
def menuFunction():
    SubWindow = tk.Tk()
    SubWindow.title("메뉴 관리 시스템")
    SubWindow.geometry("600x700+400+150")
    SubWindow.resizable(False, False)

    voidFrame = tk.Frame(SubWindow, height="10")
    voidFrame.pack()
    labelFrame1 = tk.Frame(SubWindow)
    labelFrame1.pack()
    editFrame = tk.Frame(SubWindow) 
    editFrame.pack()
    btnFrame = tk.Frame(SubWindow)
    btnFrame.pack()
    voidFrame = tk.Frame(SubWindow, height="40")
    voidFrame.pack()

    labelFrame2 = tk.Frame(SubWindow)
    labelFrame2.pack()
    listFrame = tk.Frame(SubWindow) 
    listFrame.pack(side = "bottom", fill="both", expand=1) 


    #입력용 칸 제작
    global edit1; global edit2; global edit3; global edit4
    edit1 = Entry(editFrame, width=13); edit1.pack(side=LEFT, padx=27, pady=2) 
    edit2 = Entry(editFrame, width=13); edit2.pack(side=LEFT, padx=27, pady=2) 
    edit3 = Entry(editFrame, width=13); edit3.pack(side=LEFT, padx=27, pady=2) 
    edit4 = Entry(editFrame, width=13); edit4.pack(side=LEFT, padx=27, pady=2) 

    #삽입, 삭제, 수정 기능이 들어간 버튼 제작
    btnInsert = Button(btnFrame, text="INSERT", width=13, height="3", command = insertData, bg="#2f3640", fg="white", cursor="hand2") 
    btnInsert.pack(side=LEFT, padx=24, pady=15) 
    btnDelete = Button(btnFrame, text="DELETE", width=13, height="3", command = deleteData, bg="#2f3640", fg="white", cursor="hand2") 
    btnDelete.pack(side=LEFT, padx=24, pady=15) 
    btnUpdate = Button(btnFrame, text="UPDATE", width=13, height="3", command = updateData, bg="#2f3640", fg="white", cursor="hand2") 
    btnUpdate.pack(side=LEFT, padx=24, pady=15) 
    btnQuit = Button(btnFrame, text="QUIT", width=13, height="3", command = SubWindow.destroy, bg="#2f3640", fg="white", cursor="hand2") 
    btnQuit.pack(side=LEFT, padx=24, pady=15) 
    
    #각 데이터 이름 담은 라벨 제작
    dataLabel1 = tk.Label(labelFrame1, text="MNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel1.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel2 = tk.Label(labelFrame1, text="MNAME", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel2.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel3 = tk.Label(labelFrame1, text="SNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel3.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel4 = tk.Label(labelFrame1, text="MCOST", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel4.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)

    dataLabel5 = tk.Label(labelFrame2, text="MNUM", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel5.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel6 = tk.Label(labelFrame2, text="MNAME", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel6.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel7 = tk.Label(labelFrame2, text="SNUM", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel7.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel8 = tk.Label(labelFrame2, text="MCOST", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel8.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)

    #각 데이터를 담은 리스트 제작
    global listData1; global listData2; global listData3; global listData4
    listData1 = tk.Listbox(listFrame, bg = "white", width=13) 
    listData1.pack(side="left", fill="both", expand=1, padx=10, pady=10) 
    listData2 = tk.Listbox(listFrame, bg = "white", width=13) 
    listData2.pack(side="left", fill="both", expand=1, padx=10, pady=10) 
    listData3 = tk.Listbox(listFrame, bg = "white", width=13) 
    listData3.pack(side="left", fill="both", expand=1, padx=10, pady=10) 
    listData4 = tk.Listbox(listFrame, bg = "white", width=13) 
    listData4.pack(side="left", fill="both", expand=1, padx=10, pady=10) 
    
    getData()

    SubWindow.mainloop()

4. orderpage.py : 주문 테이블 관리를 위한 창

더보기
import tkinter as tk
from tkinter import * 
from tkinter import messagebox 
import tkinter.font as tkFont
import pymysql
import updateOrderPage as upp

def getData() :
    #데이터 읽기(mysql에서 가져오기)
    strData1 = []; strData2 = []; strData3 = []; strData4 = [] 
    conn = pymysql.connect(host='localhost', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 
    cur = conn.cursor() 
    cur.execute("SELECT * FROM `order` ORDER BY length(ONUM) desc,ONUM desc;") #오름차순 정렬에 계속 오류가 생겨서 내림차순으로 정렬함

    while (True) : 
        row = cur.fetchone() 
        if row == None : 
            break 
        strData1.append(row[0]); strData2.append(row[1]); strData3.append(row[2]); strData4.append(row[3]) 
    
    #데이터 갱신해서 불러오기 전 초기화(먼저 있던 데이터 삭제하고 가져오기)
    listData1.delete(0, listData1.size() - 1) 
    listData2.delete(0, listData2.size() - 1) 
    listData3.delete(0, listData3.size() - 1) 
    listData4.delete(0, listData4.size() - 1) 

    for item1, item2, item3, item4 in zip(strData1, strData2, strData3, strData4) : 
        listData1.insert(END, item1) 
        listData2.insert(END, item2) 
        listData3.insert(END, item3) 
        listData4.insert(END, item4) 
    
    conn.close() 

def insertData() : 
    conn, cur = None, None 
    data = [""] * 4 
    sql = "" 

    # connect database
    conn = pymysql.connect(host='127.0.0.1', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 

    # create cursor 
    cur = conn.cursor() 
    data[0] = edit1.get(); data[1] = edit2.get(); data[2] = edit3.get(); data[3] = edit4.get();
    
    try : 
        # sql script (DML, Data Manipulation Language) : INSERT 
        sql = "INSERT INTO `order` VALUES('" + data[0] + "', '" + data[1] + "', '" + data[2] + "', '" + data[3] + "')" 
        print(sql) 
        cur.execute(sql) 
    
    except : 
        tk.messagebox.showerror('오류', '데이터 입력 오류') 
    
    else: tk.messagebox.showinfo('성공', '데이터 입력 성공')
    
    conn.commit() 
    conn.close()

    #입력 칸 리셋
    edit1.delete(0, listData1.size() - 1) 
    edit2.delete(0, listData2.size() - 1) 
    edit3.delete(0, listData3.size() - 1) 
    edit4.delete(0, listData4.size() - 1) 

    getData()

def deleteData() : 
    conn, cur = None, None 
    data = [""] * 4 
    sql = "" 

    # connect database
    conn = pymysql.connect(host='127.0.0.1', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 

    # create cursor 
    cur = conn.cursor() 
    data[0] = edit1.get(); data[1] = edit2.get(); data[2] = edit3.get(); data[3] = edit4.get();
    
    try : 
        #ONUM O -> ONUM은 primary key이므로 단일 검색 가능, 예외 설정 X
        if (data[0] != '' and data[1] == '' and data[2] == '' and data[3] ==''):
            sql = "DELETE FROM `order` WHERE ONUM = '" + data[0] + "'"
        elif (data[0] == '' and data[1] == '' and data[2] == '' and data[3] ==''):
            sql = ""

        #ONUM X, MNUM O
        elif(data[1] != ''):
            #ONUM X, MNUM O, OTAKE O
            if (data[2] != ''):
                if (data[3] != ''):
                    #ONUM X, MNUM O, OTAKE O, OHOTCOLD O
                    sql = "DELETE FROM `order` WHERE MNUM = '" + data[1] + "' AND OTAKE = '" + data[2] + "' AND OHOTCOLD = '" + data[3] + "'"
                else:
                    #ONUM X, MNUM O, OTAKE O, OHOTCOLD X
                    sql = "DELETE FROM `order` WHERE MNUM = '" + data[1] + "' AND OTAKE = '" + data[2] + "'"
            #ONUM X, MNUM O, OTAKE X
            else:
                if (data[3] != ''):
                    #ONUM X, MNUM O, OTAKE X, OHOTCOLD O
                    sql = "DELETE FROM `order` WHERE MNUM = '" + data[1] + "' AND OHOTCOLD = '" + data[3] + "'"
                else:
                    #ONUM X, MNUMO, OTAKE X, OHOTCOLD X
                    sql = "DELETE FROM `order` WHERE MNUM = '" + data[1] + "'"
        
        #ONUM X, MNUM X, OTAKE O
        elif (data[2] != ''):
            if (data[3] != ''):
                #ONUM X, MNUM X, OTAKE O, OHOTCOLD O
                sql = "DELETE FROM `order` WHERE OTAKE = '" + data[2] + "' AND OHOTCOLD = '" + data[3] + "'"
            else:
                #ONUM X, MNUM X, OTAKE O, OHOTCOLD X
                sql = "DELETE FROM `order` WHERE OTAKE = '" + data[2] + "'"
        
        #ONUM X, MNUM X, OTAKE X, OHOTCOLD O
        elif (data[3] != ''):
            sql = "DELETE FROM `order` WHERE OHOTCOLD = '" + data[3] + "'"
    
        print(sql)
        cur.execute(sql) 
    
    except : 
        tk.messagebox.showerror('오류', '데이터 삭제 오류') 
    
    else: tk.messagebox.showinfo('성공', '데이터 삭제 성공')
    
    conn.commit() 
    conn.close()

    #입력 칸 리셋
    edit1.delete(0, listData1.size() - 1) 
    edit2.delete(0, listData2.size() - 1) 
    edit3.delete(0, listData3.size() - 1) 
    edit4.delete(0, listData4.size() - 1) 

    getData()

def updateData() :
    #새 창 열고 검색해서 update
    upp.updateFunction()

    
def orderFunction():
    SubWindow = tk.Tk()
    SubWindow.title("주문 관리 시스템")
    SubWindow.geometry("600x700+500+150")
    SubWindow.resizable(False, False)

    voidFrame = tk.Frame(SubWindow, height="10")
    voidFrame.pack()
    labelFrame1 = tk.Frame(SubWindow)
    labelFrame1.pack()
    editFrame = tk.Frame(SubWindow) 
    editFrame.pack()
    btnFrame = tk.Frame(SubWindow)
    btnFrame.pack()
    voidFrame = tk.Frame(SubWindow, height="40")
    voidFrame.pack()

    labelFrame2 = tk.Frame(SubWindow)
    labelFrame2.pack()
    listFrame = tk.Frame(SubWindow) 
    listFrame.pack(side = "bottom", fill="both", expand=1) 


    #입력용 칸 제작
    global edit1; global edit2; global edit3; global edit4
    edit1 = Entry(editFrame, width=13); edit1.pack(side=LEFT, padx=27, pady=2) 
    edit2 = Entry(editFrame, width=13); edit2.pack(side=LEFT, padx=27, pady=2) 
    edit3 = Entry(editFrame, width=13); edit3.pack(side=LEFT, padx=27, pady=2) 
    edit4 = Entry(editFrame, width=13); edit4.pack(side=LEFT, padx=27, pady=2) 

    #삽입, 삭제, 수정 기능이 들어간 버튼 제작
    btnInsert = Button(btnFrame, text="INSERT", width=13, height="3", command = insertData, bg="#2f3640", fg="white", cursor="hand2") 
    btnInsert.pack(side=LEFT, padx=24, pady=15) 
    btnDelete = Button(btnFrame, text="DELETE", width=13, height="3", command = deleteData, bg="#2f3640", fg="white", cursor="hand2") 
    btnDelete.pack(side=LEFT, padx=24, pady=15) 
    btnUpdate = Button(btnFrame, text="UPDATE", width=13, height="3", command = updateData, bg="#2f3640", fg="white", cursor="hand2") 
    btnUpdate.pack(side=LEFT, padx=24, pady=15) 
    btnQuit = Button(btnFrame, text="QUIT", width=13, height="3", command = SubWindow.destroy, bg="#2f3640", fg="white", cursor="hand2") 
    btnQuit.pack(side=LEFT, padx=24, pady=15) 
    
    #각 데이터 이름 담은 라벨 제작
    dataLabel1 = tk.Label(labelFrame1, text="ONUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel1.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel2 = tk.Label(labelFrame1, text="MNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel2.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel3 = tk.Label(labelFrame1, text="OTAKE", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel3.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel4 = tk.Label(labelFrame1, text="OHOTCOLD", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel4.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)

    dataLabel5 = tk.Label(labelFrame2, text="ONUM", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel5.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel6 = tk.Label(labelFrame2, text="MNUM", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel6.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel7 = tk.Label(labelFrame2, text="OTAKE", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel7.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel8 = tk.Label(labelFrame2, text="OHOTCOLD", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel8.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)

    #각 데이터를 담은 리스트 제작
    global listData1; global listData2; global listData3; global listData4
    listData1 = tk.Listbox(listFrame, bg = "white", width=13) 
    listData1.pack(side="left", fill="both", expand=1, padx=10, pady=10) 
    listData2 = tk.Listbox(listFrame, bg = "white", width=13) 
    listData2.pack(side="left", fill="both", expand=1, padx=10, pady=10) 
    listData3 = tk.Listbox(listFrame, bg = "white", width=13) 
    listData3.pack(side="left", fill="both", expand=1, padx=10, pady=10) 
    listData4 = tk.Listbox(listFrame, bg = "white", width=13) 
    listData4.pack(side="left", fill="both", expand=1, padx=10, pady=10) 
    
    getData()

    SubWindow.mainloop()

5. reviewpage.py : 고객 후기 테이블 관리를 위한 창

더보기
import tkinter as tk
from tkinter import * 
from tkinter import messagebox 
import tkinter.font as tkFont
import pymysql
import updateReviewPage as urp

def getData() :
    #데이터 읽기(mysql에서 가져오기)
    strData1 = []; strData2 = []; strData3 = []; strData4 = []; strData5 = []; strData6 = [] 

    conn = pymysql.connect(host='localhost', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 
    cur = conn.cursor() 
    cur.execute("SELECT * FROM review ORDER BY length(RNUM) desc,RNUM desc;") 

    while (True) : 
        row = cur.fetchone() 
        if row == None : 
            break 
        strData1.append(row[0]); strData2.append(row[1]); strData3.append(row[2]); strData4.append(row[3]); strData5.append(row[4]); strData6.append(row[5])
    
    #데이터 갱신해서 불러오기 전 초기화(먼저 있던 데이터 삭제하고 가져오기)
    listData1.delete(0, listData1.size() - 1) 
    listData2.delete(0, listData2.size() - 1) 
    listData3.delete(0, listData3.size() - 1) 
    listData4.delete(0, listData4.size() - 1) 
    listData5.delete(0, listData5.size() - 1) 
    listData6.delete(0, listData6.size() - 1) 


    for item1, item2, item3, item4, item5, item6 in zip(strData1, strData2, strData3, strData4, strData5, strData6) : 
        listData1.insert(END, item1) 
        listData2.insert(END, item2) 
        listData3.insert(END, item3) 
        listData4.insert(END, item4) 
        listData5.insert(END, item5) 
        listData6.insert(END, item6) 
    conn.close() 

def insertData() : 
    conn, cur = None, None 
    data = [""] * 6
    sql = "" 

    # connect database
    conn = pymysql.connect(host='127.0.0.1', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 

    # create cursor 
    cur = conn.cursor() 
    data[0] = edit1.get(); data[1] = edit2.get(); data[2] = edit3.get(); data[3] = edit4.get(); data[4] = edit5.get(); data[5] = edit6.get();
    
    try : 
        # sql script (DML, Data Manipulation Language) : INSERT 
        sql = "INSERT INTO review VALUES('" + data[0] + "', " + data[1] + ", '" + data[2] + "', '" + data[3] + "', '" + data[4] + "', '" + data[5] + "')" 
        print(sql) 
        cur.execute(sql) 
    
    except : 
        tk.messagebox.showerror('오류', '데이터 입력 오류') 
    
    else: tk.messagebox.showinfo('성공', '데이터 입력 성공')
    
    conn.commit() 
    conn.close()

    #입력 칸 리셋
    edit1.delete(0, listData1.size() - 1) 
    edit2.delete(0, listData2.size() - 1) 
    edit3.delete(0, listData3.size() - 1) 
    edit4.delete(0, listData4.size() - 1) 
    edit5.delete(0, listData5.size() - 1) 
    edit6.delete(0, listData6.size() - 1) 

    getData()

def deleteData() : 
    conn, cur = None, None 
    data = [""] * 6
    sql = "" 

    # connect database
    conn = pymysql.connect(host='127.0.0.1', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 

    # create cursor 
    cur = conn.cursor() 
    data[0] = edit1.get(); data[1] = edit2.get(); data[2] = edit3.get(); data[3] = edit4.get(); data[4] = edit5.get(); data[5] = edit6.get()
    
    try : 
        #RNUM O -> RNUM은 primary key이므로 단일 검색 가능, 예외 설정 X
        if (data[0] != '' and data[1] == '' and data[2] == '' and data[3] =='' and data[4] =='' and data[5] ==''):
            sql = "DELETE FROM review WHERE RNUM = '" + data[0] + "'"
        elif (data[0] == '' and data[1] == '' and data[2] == '' and data[3] =='' and data[4] =='' and data[5] ==''):
            sql = ""

        #RNUM X, RSCORE O
        elif(data[1] != ''):
            #RNUM X, RSCORE O, RDATE O
            if (data[2] != ''):
                #RNUM X, RSCORE O, RDATE O, ONUM O
                if (data[3] != ''):
                    #RNUM X, RSCORE O, RDATE O, ONUM O, MNUM O
                    if (data[4] != ''):
                        #RNUM X, RSCORE O, RDATE O, ONUM O, MNUM O, SNUM O
                        if (data[5] != ''):
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND RDATE = '" + data[2] + "' AND ONUM = '" + data[3] + "' AND MNUM = '" + data[4] + "' AND SNUM = '" + data[5] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE O, ONUM O, MNUM O, SNUM X
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND RDATE = '" + data[2] + "' AND ONUM = '" + data[3] + "' AND MNUM = '" + data[4] + "'"
                    else:
                        if (data[5] != ''):
                            #RNUM X, RSCORE O, RDATE O, ONUM O, MNUM X, SNUM O
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND RDATE = '" + data[2] + "' AND ONUM = '" + data[3] + "' AND SNUM = '" + data[5] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE O, ONUM O, MNUM X, SNUM X
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND RDATE = '" + data[2] + "' AND ONUM = '" + data[3] + "'"
                else:
                    if (data[4] != ''):
                        if (data[5] != ''):
                            #RNUM X, RSCORE O, RDATE O, ONUM X, MNUM O, SNUM O
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND RDATE = '" + data[2] + "' AND MNUM = '" + data[4] + "' AND SNUM = '" + data[5] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE O, ONUM X, MNUM O, SNUM X
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND RDATE = '" + data[2] + "' AND MNUM = '" + data[4] + "'"
                    else:
                        if (data[5] != ''):
                            #RNUM X, RSCORE O, RDATE O, ONUM X, MNUM X, SNUM O
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND RDATE = '" + data[2] + "' AND SNUM = '" + data[5] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE O, ONUM X, MNUM X, SNUM X
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND RDATE = '" + data[2] + "'"
            #RNUM X, RSCORE O, RDATE X
            else:
                #RNUM X, RSCORE O, RDATE X, ONUM O
                if (data[3] != ''):
                    #RNUM X, RSCORE O, RDATE X, ONUM O, MNUM O
                    if (data[4] != ''):
                        #RNUM X, RSCORE O, RDATE X, ONUM O, MNUM O, SNUM O
                        if (data[5] != ''):
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND ONUM = '" + data[3] + "' AND MNUM = '" + data[4] + "' AND SNUM = '" + data[5] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE X, ONUM O, MNUM O, SNUM X
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND ONUM = '" + data[3] + "' AND MNUM = '" + data[4] + "'"
                    else:
                        if (data[5] != ''):
                            #RNUM X, RSCORE O, RDATE X, ONUM O, MNUM X, SNUM O
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND ONUM = '" + data[3] + "' AND SNUM = '" + data[5] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE X, ONUM O, MNUM X, SNUM X
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND ONUM = '" + data[3] + "'"
                else:
                    if (data[4] != ''):
                        if (data[5] != ''):
                            #RNUM X, RSCORE O, RDATE X, ONUM X, MNUM O, SNUM O
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + "  AND MNUM = '" + data[4] + "' AND SNUM = '" + data[5] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE X, ONUM X, MNUM O, SNUM X
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND MNUM = '" + data[4] + "'"
                    else:
                        if (data[5] != ''):
                            #RNUM X, RSCORE O, RDATE X, ONUM X, MNUM X, SNUM O
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + " AND SNUM = '" + data[5] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE X, ONUM X, MNUM X, SNUM X
                            sql = "DELETE FROM review WHERE RSCORE = " + data[1] + ""
        #RNUM X, RSCORE X
        elif (data[2] != ''):
                #RNUM X, RSCORE X, RDATE O, ONUM O
            if (data[3] != ''):
                #RNUM X, RSCORE X, RDATE O, ONUM O, MNUM O
                if (data[4] != ''):
                    #RNUM X, RSCORE X, RDATE O, ONUM O, MNUM O, SNUM O
                    if (data[5] != ''):
                        sql = "DELETE FROM review WHERE RDATE = '" + data[2] + "' AND ONUM = '" + data[3] + "' AND MNUM = '" + data[4] + "' AND SNUM = '" + data[5] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE O, ONUM O, MNUM O, SNUM X
                        sql = "DELETE FROM review WHERE RDATE = '" + data[2] + "' AND ONUM = '" + data[3] + "' AND MNUM = '" + data[4] + "'"
                else:
                    if (data[5] != ''):
                        #RNUM X, RSCORE X, RDATE O, ONUM O, MNUM X, SNUM O
                        sql = "DELETE FROM review WHERE RDATE = '" + data[2] + "' AND ONUM = '" + data[3] + "' AND SNUM = '" + data[5] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE O, ONUM O, MNUM X, SNUM X
                        sql = "DELETE FROM review WHERE RDATE = '" + data[2] + "' AND ONUM = '" + data[3] + "'"
            else:
                if (data[4] != ''):
                    if (data[5] != ''):
                        #RNUM X, RSCORE X, RDATE O, ONUM X, MNUM O, SNUM O
                        sql = "DELETE FROM review WHERE RDATE = '" + data[2] + "' AND MNUM = '" + data[4] + "' AND SNUM = '" + data[5] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE O, ONUM X, MNUM O, SNUM X
                        sql = "DELETE FROM review WHERE RDATE = '" + data[2] + "' AND MNUM = '" + data[4] + "'"
                else:
                    if (data[5] != ''):
                        #RNUM X, RSCORE X, RDATE O, ONUM X, MNUM X, SNUM O
                        sql = "DELETE FROM review WHERE RDATE = '" + data[2] + "' AND SNUM = '" + data[5] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE O, ONUM X, MNUM X, SNUM X
                        sql = "DELETE FROM review WHERE RDATE = '" + data[2] + "'"
            #RNUM X, RSCORE X, RDATE X
        else:
            #RNUM X, RSCORE X, RDATE X, ONUM O
            if (data[3] != ''):
                #RNUM X, RSCORE X, RDATE X, ONUM O, MNUM O
                if (data[4] != ''):
                    #RNUM X, RSCORE X, RDATE X, ONUM O, MNUM O, SNUM O
                    if (data[5] != ''):
                        sql = "DELETE FROM review WHERE ONUM = '" + data[3] + "' AND MNUM = '" + data[4] + "' AND SNUM = '" + data[5] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE X, ONUM O, MNUM O, SNUM X
                        sql = "DELETE FROM review WHERE ONUM = '" + data[3] + "' AND MNUM = '" + data[4] + "'"
                else:
                    if (data[5] != ''):
                        #RNUM X, RSCORE X, RDATE X, ONUM O, MNUM X, SNUM O
                        sql = "DELETE FROM review WHERE ONUM = '" + data[3] + "' AND SNUM = '" + data[5] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE X, ONUM O, MNUM X, SNUM X
                        sql = "DELETE FROM review WHERE ONUM = '" + data[3] + "'"
            else:
                if (data[4] != ''):
                    if (data[5] != ''):
                        #RNUM X, RSCORE X, RDATE X, ONUM X, MNUM O, SNUM O
                        sql = "DELETE FROM review WHERE RSCORE = " + data[1] + "  AND MNUM = '" + data[4] + "' AND SNUM = '" + data[5] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE X, ONUM X, MNUM O, SNUM X
                        sql = "DELETE FROM review WHERE MNUM = '" + data[4] + "'"
                else:
                    if (data[5] != ''):
                        #RNUM X, RSCORE X, RDATE X, ONUM X, MNUM X, SNUM O
                        sql = "DELETE FROM review WHERE SNUM = '" + data[5] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE X, ONUM X, MNUM X, SNUM X -> 오류
                        sql = ""
    
        print(sql)
        cur.execute(sql) 
    
    except : 
        tk.messagebox.showerror('오류', '데이터 삭제 오류') 
    
    else: tk.messagebox.showinfo('성공', '데이터 삭제 성공')
    
    conn.commit() 
    conn.close()

    #입력 칸 리셋
    edit1.delete(0, listData1.size() - 1) 
    edit2.delete(0, listData2.size() - 1) 
    edit3.delete(0, listData3.size() - 1) 
    edit4.delete(0, listData4.size() - 1) 
    edit5.delete(0, listData5.size() - 1) 
    edit6.delete(0, listData6.size() - 1) 

    getData()

def updateData() :
    #새 창 열고 검색해서 update
    urp.updateFunction()
    
def reviewFunction():
    SubWindow = tk.Tk()
    SubWindow.title("리뷰 관리 시스템")
    SubWindow.geometry("900x700+600+150")
    SubWindow.resizable(False, False)

    voidFrame = tk.Frame(SubWindow, height="10")
    voidFrame.pack()
    labelFrame1 = tk.Frame(SubWindow)
    labelFrame1.pack()
    editFrame = tk.Frame(SubWindow) 
    editFrame.pack()
    btnFrame = tk.Frame(SubWindow)
    btnFrame.pack()
    voidFrame = tk.Frame(SubWindow, height="40")
    voidFrame.pack()

    labelFrame2 = tk.Frame(SubWindow)
    labelFrame2.pack()
    listFrame = tk.Frame(SubWindow) 
    listFrame.pack(side = "bottom", fill="both", expand=1) 

    #입력용 칸 제작
    global edit1; global edit2; global edit3; global edit4; global edit5; global edit6
    edit1 = Entry(editFrame, width=13); edit1.pack(side=LEFT, padx=27, pady=2) 
    edit2 = Entry(editFrame, width=13); edit2.pack(side=LEFT, padx=27, pady=2) 
    edit3 = Entry(editFrame, width=13); edit3.pack(side=LEFT, padx=27, pady=2) 
    edit4 = Entry(editFrame, width=13); edit4.pack(side=LEFT, padx=27, pady=2) 
    edit5 = Entry(editFrame, width=13); edit5.pack(side=LEFT, padx=27, pady=2) 
    edit6 = Entry(editFrame, width=13); edit6.pack(side=LEFT, padx=27, pady=2) 

    #삽입, 삭제, 수정 기능이 들어간 버튼 제작
    btnInsert = Button(btnFrame, text="INSERT", width=13, height="3", command = insertData, bg="#2f3640", fg="white", cursor="hand2") 
    btnInsert.pack(side=LEFT, padx=24, pady=15) 
    btnDelete = Button(btnFrame, text="DELETE", width=13, height="3", command = deleteData, bg="#2f3640", fg="white", cursor="hand2") 
    btnDelete.pack(side=LEFT, padx=24, pady=15) 
    btnUpdate = Button(btnFrame, text="UPDATE", width=13, height="3", command = updateData, bg="#2f3640", fg="white", cursor="hand2") 
    btnUpdate.pack(side=LEFT, padx=24, pady=15) 
    btnQuit = Button(btnFrame, text="QUIT", width=13, height="3", command = SubWindow.destroy, bg="#2f3640", fg="white", cursor="hand2") 
    btnQuit.pack(side=LEFT, padx=24, pady=15) 
    
    #각 데이터 이름 담은 라벨 제작
    dataLabel1 = tk.Label(labelFrame1, text="RNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel1.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel2 = tk.Label(labelFrame1, text="RSCORE", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel2.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel3 = tk.Label(labelFrame1, text="RDATE", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel3.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel4 = tk.Label(labelFrame1, text="ONUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel4.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel5 = tk.Label(labelFrame1, text="MNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel5.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel6 = tk.Label(labelFrame1, text="SNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel6.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)

    dataLabel7 = tk.Label(labelFrame2, text="RNUM", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel7.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel8 = tk.Label(labelFrame2, text="RSCORE", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel8.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel9 = tk.Label(labelFrame2, text="RDATE", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel9.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel10 = tk.Label(labelFrame2, text="ONUM", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel10.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel11 = tk.Label(labelFrame2, text="MNUM", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel11.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel12 = tk.Label(labelFrame2, text="SNUM", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel12.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)

    #각 데이터를 담은 리스트 제작
    global listData1; global listData2; global listData3; global listData4; global listData5; global listData6
    listData1 = tk.Listbox(listFrame, bg = "white", width=18) 
    listData1.pack(side="left", fill="both", expand=1, padx=5, pady=10) 
    listData2 = tk.Listbox(listFrame, bg = "white", width=18) 
    listData2.pack(side="left", fill="both", expand=1, padx=5, pady=10) 
    listData3 = tk.Listbox(listFrame, bg = "white", width=18) 
    listData3.pack(side="left", fill="both", expand=1, padx=5, pady=10) 
    listData4 = tk.Listbox(listFrame, bg = "white", width=18) 
    listData4.pack(side="left", fill="both", expand=1, padx=5, pady=10) 
    listData5 = tk.Listbox(listFrame, bg = "white", width=18) 
    listData5.pack(side="left", fill="both", expand=1, padx=5, pady=10) 
    listData6 = tk.Listbox(listFrame, bg = "white", width=18) 
    listData6.pack(side="left", fill="both", expand=1, padx=5, pady=10) 

    getData()

    SubWindow.mainloop()

6. stockpage.py : 재고 테이블 관리를 위한 창

더보기
import tkinter as tk
from tkinter import * 
from tkinter import messagebox 
import tkinter.font as tkFont
import pymysql
import updateStockPage as upp

def getData() :
    #데이터 읽기(mysql에서 가져오기)
    strData1 = []; strData2 = []; strData3 = []; strData4 = [] 
    conn = pymysql.connect(host='localhost', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 
    cur = conn.cursor() 
    cur.execute("SELECT * FROM stock ORDER BY length(SNUM) desc,SNUM desc;") #오름차순 정렬에 계속 오류가 생겨서 내림차순으로 정렬함

    while (True) : 
        row = cur.fetchone() 
        if row == None : 
            break 
        strData1.append(row[0]); strData2.append(row[1]); strData3.append(row[2]); strData4.append(row[3]) 
    
    #데이터 갱신해서 불러오기 전 초기화(먼저 있던 데이터 삭제하고 가져오기)
    listData1.delete(0, listData1.size() - 1) 
    listData2.delete(0, listData2.size() - 1) 
    listData3.delete(0, listData3.size() - 1) 
    listData4.delete(0, listData4.size() - 1) 

    for item1, item2, item3, item4 in zip(strData1, strData2, strData3, strData4) : 
        listData1.insert(END, item1) 
        listData2.insert(END, item2) 
        listData3.insert(END, item3) 
        listData4.insert(END, item4) 
    
    conn.close() 

def insertData() : 
    conn, cur = None, None 
    data = [""] * 4 
    sql = "" 

    # connect database
    conn = pymysql.connect(host='127.0.0.1', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 

    # create cursor 
    cur = conn.cursor() 
    data[0] = edit1.get(); data[1] = edit2.get(); data[2] = edit3.get(); data[3] = edit4.get();
    
    try : 
        # sql script (DML, Data Manipulation Language) : INSERT 
        sql = "INSERT INTO stock VALUES('" + data[0] + "', '" + data[1] + "', " + data[2] + ", " + data[3] + ")" 
        print(sql) 
        cur.execute(sql) 
    
    except : 
        tk.messagebox.showerror('오류', '데이터 입력 오류') 
    
    else: tk.messagebox.showinfo('성공', '데이터 입력 성공')
    
    conn.commit() 
    conn.close()

    #입력 칸 리셋
    edit1.delete(0, listData1.size() - 1) 
    edit2.delete(0, listData2.size() - 1) 
    edit3.delete(0, listData3.size() - 1) 
    edit4.delete(0, listData4.size() - 1) 

    getData()

def deleteData() : 
    conn, cur = None, None 
    data = [""] * 4 
    sql = "" 

    # connect database
    conn = pymysql.connect(host='127.0.0.1', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 

    # create cursor 
    cur = conn.cursor() 
    data[0] = edit1.get(); data[1] = edit2.get(); data[2] = edit3.get(); data[3] = edit4.get();
    
    try : 
        #snum O -> snum은 primary key이므로 단일 검색 가능, 예외 설정 X
        if (data[0] != '' and data[1] == '' and data[2] == '' and data[3] ==''):
            sql = "DELETE FROM stock WHERE SNUM = '" + data[0] + "'"
        elif (data[0] == '' and data[1] == '' and data[2] == '' and data[3] ==''):
            sql = ""

        #snum X, sname O
        elif(data[1] != ''):
            #snum X, sname O, scost O
            if (data[2] != ''):
                if (data[3] != ''):
                    #snum X, sname O, scost O, sqty O
                    sql = "DELETE FROM stock WHERE SNAME = '" + data[1] + "' AND SCOST = " + data[2] + " AND SQTY = " + data[3] + ""
                else:
                    #snum X, sname O, scost O, sqty X
                    sql = "DELETE FROM stock WHERE SNAME = '" + data[1] + "' AND SCOST = " + data[2] + ""
            #snum X, sname O, scost X
            else:
                if (data[3] != ''):
                    #snum X, sname O, scost X, sqty O
                    sql = "DELETE FROM stock WHERE SNAME = '" + data[1] + "' AND SQTY = " + data[3] + ""
                else:
                    #snum X, snameO, scost X, sqty X
                    sql = "DELETE FROM stock WHERE SNAME = '" + data[1] + "'"
        
        #snum X, sname X, scost O
        elif (data[2] != ''):
            if (data[3] != ''):
                #snum X, sname X, scost O, sqty O
                sql = "DELETE FROM stock WHERE SCOST = " + data[2] + " AND SQTY = " + data[3] + ""
            else:
                #snum X, sname X, scost O, sqty X
                sql = "DELETE FROM stock WHERE SCOST = " + data[2] + ""
        
        #snum X, sname X, scost X, sqty O
        elif (data[3] != ''):
            sql = "DELETE FROM stock WHERE SQTY = " + data[3] + ""
    
        print(sql)
        cur.execute(sql) 
    
    except : 
        tk.messagebox.showerror('오류', '데이터 삭제 오류') 
    
    else: tk.messagebox.showinfo('성공', '데이터 삭제 성공')
    
    conn.commit() 
    conn.close()

    #입력 칸 리셋
    edit1.delete(0, listData1.size() - 1) 
    edit2.delete(0, listData2.size() - 1) 
    edit3.delete(0, listData3.size() - 1) 
    edit4.delete(0, listData4.size() - 1) 

    getData()

def updateData() :
    #새 창 열고 검색해서 update
    upp.updateFunction()

    
def stockFunction():
    SubWindow = tk.Tk()
    SubWindow.title("재고 관리 시스템")
    SubWindow.geometry("600x700+300+150")
    SubWindow.resizable(False, False)

    voidFrame = tk.Frame(SubWindow, height="10")
    voidFrame.pack()
    labelFrame1 = tk.Frame(SubWindow)
    labelFrame1.pack()
    editFrame = tk.Frame(SubWindow) 
    editFrame.pack()
    btnFrame = tk.Frame(SubWindow)
    btnFrame.pack()
    voidFrame = tk.Frame(SubWindow, height="40")
    voidFrame.pack()

    labelFrame2 = tk.Frame(SubWindow)
    labelFrame2.pack()
    listFrame = tk.Frame(SubWindow) 
    listFrame.pack(side = "bottom", fill="both", expand=1) 


    #입력용 칸 제작
    global edit1; global edit2; global edit3; global edit4
    edit1 = Entry(editFrame, width=13); edit1.pack(side=LEFT, padx=27, pady=2) 
    edit2 = Entry(editFrame, width=13); edit2.pack(side=LEFT, padx=27, pady=2) 
    edit3 = Entry(editFrame, width=13); edit3.pack(side=LEFT, padx=27, pady=2) 
    edit4 = Entry(editFrame, width=13); edit4.pack(side=LEFT, padx=27, pady=2) 

    #삽입, 삭제, 수정 기능이 들어간 버튼 제작
    btnInsert = Button(btnFrame, text="INSERT", width=13, height="3", command = insertData, bg="#2f3640", fg="white", cursor="hand2") 
    btnInsert.pack(side=LEFT, padx=24, pady=15) 
    btnDelete = Button(btnFrame, text="DELETE", width=13, height="3", command = deleteData, bg="#2f3640", fg="white", cursor="hand2") 
    btnDelete.pack(side=LEFT, padx=24, pady=15) 
    btnUpdate = Button(btnFrame, text="UPDATE", width=13, height="3", command = updateData, bg="#2f3640", fg="white", cursor="hand2") 
    btnUpdate.pack(side=LEFT, padx=24, pady=15) 
    btnQuit = Button(btnFrame, text="QUIT", width=13, height="3", command = SubWindow.destroy, bg="#2f3640", fg="white", cursor="hand2") 
    btnQuit.pack(side=LEFT, padx=24, pady=15) 
    
    #각 데이터 이름 담은 라벨 제작
    dataLabel1 = tk.Label(labelFrame1, text="SNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel1.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel2 = tk.Label(labelFrame1, text="SNAME", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel2.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel3 = tk.Label(labelFrame1, text="SCOST", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel3.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)
    dataLabel4 = tk.Label(labelFrame1, text="SQTY", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel4.pack(side=LEFT, fill="both", expand=1, padx=27, pady=10)

    dataLabel5 = tk.Label(labelFrame2, text="SNUM", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel5.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel6 = tk.Label(labelFrame2, text="SNAME", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel6.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel7 = tk.Label(labelFrame2, text="SCOST", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel7.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)
    dataLabel8 = tk.Label(labelFrame2, text="SQTY", bg = "white", width=17, font=("Arial", "8", "bold"))
    dataLabel8.pack(side=LEFT, fill="both", expand=1, padx=13, pady=10)

    #각 데이터를 담은 리스트 제작
    global listData1; global listData2; global listData3; global listData4
    listData1 = tk.Listbox(listFrame, bg = "white", width=13) 
    listData1.pack(side="left", fill="both", expand=1, padx=10, pady=10) 
    listData2 = tk.Listbox(listFrame, bg = "white", width=13) 
    listData2.pack(side="left", fill="both", expand=1, padx=10, pady=10) 
    listData3 = tk.Listbox(listFrame, bg = "white", width=13) 
    listData3.pack(side="left", fill="both", expand=1, padx=10, pady=10) 
    listData4 = tk.Listbox(listFrame, bg = "white", width=13) 
    listData4.pack(side="left", fill="both", expand=1, padx=10, pady=10) 
    
    getData()

    SubWindow.mainloop()

7. updateMenuPage.py : 메뉴 테이블 UPDATE 쿼리를 위한 창

더보기
import tkinter as tk
from tkinter import * 
from tkinter import messagebox 
import menupage as mnp
import pymysql

def updateFunction() :
    global UpWindow
    UpWindow = tk.Tk()
    UpWindow.title("데이터 업데이트")
    UpWindow.geometry("400x300+1000+500")
    UpWindow.resizable(False, False)

    textFrame1 = tk.Frame(UpWindow)
    textFrame1.pack()
    inputFrame1 = tk.Frame(UpWindow) 
    inputFrame1.pack()
    voidFrame = tk.Frame(UpWindow, height="20")
    voidFrame.pack()

    textFrame2 = tk.Frame(UpWindow)
    textFrame2.pack()
    labelFrame = tk.Frame(UpWindow)
    labelFrame.pack()
    inputFrame2 = tk.Frame(UpWindow) 
    inputFrame2.pack()
    btnFrame = tk.Frame(UpWindow)
    btnFrame.pack()

    global edit1; global edit2; global edit3; global edit4

    textLabel1 = tk.Label(textFrame1, text="업데이트하고 싶은 데이터의 고유번호를 하단에 입력하십시오")
    textLabel1.pack(side=BOTTOM, pady=10)

    dataLabel1 = tk.Label(inputFrame1, text="MNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel1.pack(side=LEFT, fill="both", expand=1, padx=10)
    edit1 = Entry(inputFrame1, width=13); edit1.pack(side=LEFT, padx=10) 

    textLabel2 = tk.Label(textFrame2, text="업데이트하고 싶은 데이터를 하단에 입력하십시오")
    textLabel2.pack(side=BOTTOM, pady=10)
    
    dataLabel2 = tk.Label(labelFrame, text="MNAME", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel2.pack(side=LEFT, fill="both", expand=1, padx=15, pady=5)
    dataLabel3 = tk.Label(labelFrame, text="SNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel3.pack(side=LEFT, fill="both", expand=1, padx=15, pady=5)
    dataLabel4 = tk.Label(labelFrame, text="MCOST", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel4.pack(side=LEFT, fill="both", expand=1, padx=15, pady=5)

    edit2 = Entry(inputFrame2, width=13); edit2.pack(side=LEFT, padx=15)
    edit3 = Entry(inputFrame2, width=13); edit3.pack(side=LEFT, padx=15) 
    edit4 = Entry(inputFrame2, width=13); edit4.pack(side=LEFT, padx=15)

    btnUpdate = Button(btnFrame, text="UPDATE", width=13, height="3", command = connectForUpdate, bg="#2f3640", fg="white", cursor="hand2") 
    btnUpdate.pack(pady="35")

    UpWindow.mainloop()

    
def connectForUpdate():
    conn, cur = None, None 
    data = [""] * 4 
    sql = "" 

    # connect database
    conn = pymysql.connect(host='127.0.0.1', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 

    # create cursor 
    cur = conn.cursor() 
    data[0] = edit1.get(); data[1] = edit2.get(); data[2] = edit3.get(); data[3] = edit4.get();
    
    try :
        #MNAME O
        if(data[1] != ''):
            #MNAME O, SNUM O
            if (data[2] != ''):
                if (data[3] != ''):
                    #MNAME O, SNUM O, MCOST O
                    sql = "UPDATE menu SET MNAME = '" + data[1] + "', SNUM = '" + data[2] + "', MCOST = " + data[3] + " WHERE MNUM = '" + data[0] + "'"
                else:
                    #MNAME O, SNUM O, MCOST X
                    sql = "UPDATE menu SET MNAME = '" + data[1] + "', SNUM = '" + data[2] + "' WHERE MNUM = '" + data[0] + "'"
            #MNAME O, SNUM X
            else:
                if (data[3] != ''):
                    #MNAME O, SNUM X, MCOST O
                    sql = "UPDATE menu SET MNAME = '" + data[1] + "', MCOST = " + data[3] + " WHERE MNUM = '" + data[0] + "'"
                else:
                    #MNAMEO, SNUM X, MCOST X
                    sql = "UPDATE menu SET MNAME = '" + data[1] + "' WHERE MNUM = '" + data[0] + "'"
        
        #MNAME X, SNUM O
        elif (data[2] != ''):
            if (data[3] != ''):
                #MNAME X, SNUM O, MCOST O
                sql = "UPDATE menu SET SNUM = '" + data[2] + "', MCOST = " + data[3] + " WHERE MNUM = '" + data[0] + "'"
            else:
                #MNAME X, SNUM O, MCOST X
                sql = "UPDATE menu SET SNUM = '" + data[2] + "' WHERE MNUM = '" + data[0] + "'"
        
        #MNAME X, SNUM X, MCOST O
        elif (data[3] != ''):
            sql = "UPDATE menu SET MCOST = " + data[3] + " WHERE MNUM = '" + data[0] + "'"
        
        elif (data[0] != '' and data[1] == '' or data[2] == '' or data[3] ==''):
            sql = ""

        print(sql)
        cur.execute(sql) 
    
    
    except : 
        tk.messagebox.showerror('오류', '업데이트 오류가 발생함')
    
    else: 
        tk.messagebox.showinfo('성공', '데이터 업데이트 성공')
        conn.commit() 
        conn.close()
        #메인페이지 데이터 업데이트
        mnp.getData() 
        UpWindow.destroy()

8. updateOrderPage.py : 주문 테이블 UPDATE 쿼리를 위한 창

더보기
import tkinter as tk
from tkinter import * 
from tkinter import messagebox 
import orderpage as odp
import pymysql

def updateFunction() :
    global UpWindow
    UpWindow = tk.Tk()
    UpWindow.title("데이터 업데이트")
    UpWindow.geometry("400x300+1000+500")
    UpWindow.resizable(False, False)

    textFrame1 = tk.Frame(UpWindow)
    textFrame1.pack()
    inputFrame1 = tk.Frame(UpWindow) 
    inputFrame1.pack()
    voidFrame = tk.Frame(UpWindow, height="20")
    voidFrame.pack()

    textFrame2 = tk.Frame(UpWindow)
    textFrame2.pack()
    labelFrame = tk.Frame(UpWindow)
    labelFrame.pack()
    inputFrame2 = tk.Frame(UpWindow) 
    inputFrame2.pack()
    btnFrame = tk.Frame(UpWindow)
    btnFrame.pack()

    global edit1; global edit2; global edit3; global edit4

    textLabel1 = tk.Label(textFrame1, text="업데이트하고 싶은 데이터의 고유번호를 하단에 입력하십시오")
    textLabel1.pack(side=BOTTOM, pady=10)

    dataLabel1 = tk.Label(inputFrame1, text="ONUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel1.pack(side=LEFT, fill="both", expand=1, padx=10)
    edit1 = Entry(inputFrame1, width=13); edit1.pack(side=LEFT, padx=10) 

    textLabel2 = tk.Label(textFrame2, text="업데이트하고 싶은 데이터를 하단에 입력하십시오")
    textLabel2.pack(side=BOTTOM, pady=10)
    
    dataLabel2 = tk.Label(labelFrame, text="MNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel2.pack(side=LEFT, fill="both", expand=1, padx=15, pady=5)
    dataLabel3 = tk.Label(labelFrame, text="OTAKE", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel3.pack(side=LEFT, fill="both", expand=1, padx=15, pady=5)
    dataLabel4 = tk.Label(labelFrame, text="OHOTCOLD", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel4.pack(side=LEFT, fill="both", expand=1, padx=15, pady=5)

    edit2 = Entry(inputFrame2, width=13); edit2.pack(side=LEFT, padx=15)
    edit3 = Entry(inputFrame2, width=13); edit3.pack(side=LEFT, padx=15) 
    edit4 = Entry(inputFrame2, width=13); edit4.pack(side=LEFT, padx=15)

    btnUpdate = Button(btnFrame, text="UPDATE", width=13, height="3", command = connectForUpdate, bg="#2f3640", fg="white", cursor="hand2") 
    btnUpdate.pack(pady="35")

    UpWindow.mainloop()

    
def connectForUpdate():
    conn, cur = None, None 
    data = [""] * 4 
    sql = "" 

    # connect database
    conn = pymysql.connect(host='127.0.0.1', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 

    # create cursor 
    cur = conn.cursor() 
    data[0] = edit1.get(); data[1] = edit2.get(); data[2] = edit3.get(); data[3] = edit4.get();
    
    try :
        #MNUM O
        if(data[1] != ''):
            #MNUM O, OTAKE O
            if (data[2] != ''):
                if (data[3] != ''):
                    #MNUM O, OTAKE O, OHOTCOLD O
                    sql = "UPDATE `order` SET MNUM = '" + data[1] + "', OTAKE = '" + data[2] + "', OHOTCOLD = '" + data[3] + "' WHERE ONUM = '" + data[0] + "'"
                else:
                    #MNUM O, OTAKE O, OHOTCOLD X
                    sql = "UPDATE `order` SET MNUM = '" + data[1] + "', OTAKE = '" + data[2] + "' WHERE ONUM = '" + data[0] + "'"
            #MNUM O, OTAKE X
            else:
                if (data[3] != ''):
                    #MNUM O, OTAKE X, OHOTCOLD O
                    sql = "UPDATE `order` SET MNUM = '" + data[1] + "', OHOTCOLD = '" + data[3] + "' WHERE ONUM = '" + data[0] + "'"
                else:
                    #MNUMO, OTAKE X, OHOTCOLD X
                    sql = "UPDATE `order` SET MNUM = '" + data[1] + "' WHERE ONUM = '" + data[0] + "'"
        
        #MNUM X, OTAKE O
        elif (data[2] != ''):
            if (data[3] != ''):
                #MNUM X, OTAKE O, OHOTCOLD O
                sql = "UPDATE `order` SET OTAKE = '" + data[2] + "', OHOTCOLD = '" + data[3] + "' WHERE ONUM = '" + data[0] + "'"
            else:
                #MNUM X, OTAKE O, OHOTCOLD X
                sql = "UPDATE `order` SET OTAKE = '" + data[2] + "' WHERE ONUM = '" + data[0] + "'"
        
        #MNUM X, OTAKE X, OHOTCOLD O
        elif (data[3] != ''):
            sql = "UPDATE `order` SET OHOTCOLD = '" + data[3] + "' WHERE ONUM = '" + data[0] + "'"
        
        elif (data[0] != '' and data[1] == '' or data[2] == '' or data[3] ==''):
            sql = ""

        print(sql)
        cur.execute(sql) 
    
    
    except : 
        tk.messagebox.showerror('오류', '업데이트 오류가 발생함')
    
    else: 
        tk.messagebox.showinfo('성공', '데이터 업데이트 성공')
        conn.commit() 
        conn.close()
        #메인페이지 데이터 업데이트
        odp.getData() 
        UpWindow.destroy()

9. updateReviewPage.py : 리뷰 테이블 UPDATE 쿼리를 위한 창

더보기
import tkinter as tk
from tkinter import * 
from tkinter import messagebox 
import reviewpage as rvp
import pymysql

def updateFunction() :
    global UpWindow
    UpWindow = tk.Tk()
    UpWindow.title("데이터 업데이트")
    UpWindow.geometry("650x300+1000+500")
    UpWindow.resizable(False, False)

    textFrame1 = tk.Frame(UpWindow)
    textFrame1.pack()
    inputFrame1 = tk.Frame(UpWindow) 
    inputFrame1.pack()
    voidFrame = tk.Frame(UpWindow, height="20")
    voidFrame.pack()

    textFrame2 = tk.Frame(UpWindow)
    textFrame2.pack()
    labelFrame = tk.Frame(UpWindow)
    labelFrame.pack()
    inputFrame2 = tk.Frame(UpWindow) 
    inputFrame2.pack()
    btnFrame = tk.Frame(UpWindow)
    btnFrame.pack()

    global edit1; global edit2; global edit3; global edit4; global edit5; global edit6

    textLabel1 = tk.Label(textFrame1, text="업데이트하고 싶은 데이터의 고유번호를 하단에 입력하십시오")
    textLabel1.pack(side=BOTTOM, pady=10)

    dataLabel1 = tk.Label(inputFrame1, text="RNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel1.pack(side=LEFT, fill="both", expand=1, padx=10)
    edit1 = Entry(inputFrame1, width=13); edit1.pack(side=LEFT, padx=10) 

    textLabel2 = tk.Label(textFrame2, text="업데이트하고 싶은 데이터를 하단에 입력하십시오")
    textLabel2.pack(side=BOTTOM, pady=10)
    
    dataLabel2 = tk.Label(labelFrame, text="RSCORE", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel2.pack(side=LEFT, fill="both", expand=1, padx=13, pady=5)
    dataLabel3 = tk.Label(labelFrame, text="RDATE", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel3.pack(side=LEFT, fill="both", expand=1, padx=13, pady=5)
    dataLabel4 = tk.Label(labelFrame, text="ONUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel4.pack(side=LEFT, fill="both", expand=1, padx=13, pady=5)
    dataLabel5 = tk.Label(labelFrame, text="MNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel5.pack(side=LEFT, fill="both", expand=1, padx=13, pady=5)
    dataLabel6 = tk.Label(labelFrame, text="SNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel6.pack(side=LEFT, fill="both", expand=1, padx=13, pady=5)

    edit2 = Entry(inputFrame2, width=13); edit2.pack(side=LEFT, padx=13)
    edit3 = Entry(inputFrame2, width=13); edit3.pack(side=LEFT, padx=13) 
    edit4 = Entry(inputFrame2, width=13); edit4.pack(side=LEFT, padx=13)
    edit5 = Entry(inputFrame2, width=13); edit5.pack(side=LEFT, padx=13) 
    edit6 = Entry(inputFrame2, width=13); edit6.pack(side=LEFT, padx=13)

    btnUpdate = Button(btnFrame, text="UPDATE", width=13, height="3", command = connectForUpdate, bg="#2f3640", fg="white", cursor="hand2") 
    btnUpdate.pack(pady="35")

    UpWindow.mainloop()

    
def connectForUpdate():
    conn, cur = None, None 
    data = [""] * 6
    sql = "" 

    # connect database
    conn = pymysql.connect(host='127.0.0.1', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 

    # create cursor 
    cur = conn.cursor() 
    data[0] = edit1.get(); data[1] = edit2.get(); data[2] = edit3.get(); data[3] = edit4.get(); data[4] = edit5.get(); data[5] = edit6.get()
    
    try :
        #RNUM X, RSCORE O
        if(data[1] != ''):
            #RNUM X, RSCORE O, RDATE O
            if (data[2] != ''):
                #RNUM X, RSCORE O, RDATE O, ONUM O
                if (data[3] != ''):
                    #RNUM X, RSCORE O, RDATE O, ONUM O, MNUM O
                    if (data[4] != ''):
                        #RNUM X, RSCORE O, RDATE O, ONUM O, MNUM O, SNUM O
                        if (data[5] != ''):
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", RDATE = '" + data[2] + "', ONUM = '" + data[3] + "', MNUM = '" + data[4] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE O, ONUM O, MNUM O, SNUM X
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", RDATE = '" + data[2] + "', ONUM = '" + data[3] + "', MNUM = '" + data[4] + "' WHERE RNUM = '" + data[0] + "'"
                    else:
                        if (data[5] != ''):
                            #RNUM X, RSCORE O, RDATE O, ONUM O, MNUM X, SNUM O
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", RDATE = '" + data[2] + "', ONUM = '" + data[3] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE O, ONUM O, MNUM X, SNUM X
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", RDATE = '" + data[2] + "', ONUM = '" + data[3] + "' WHERE RNUM = '" + data[0] + "'"
                else:
                    if (data[4] != ''):
                        if (data[5] != ''):
                            #RNUM X, RSCORE O, RDATE O, ONUM X, MNUM O, SNUM O
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", RDATE = '" + data[2] + "', MNUM = '" + data[4] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE O, ONUM X, MNUM O, SNUM X
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", RDATE = '" + data[2] + "', MNUM = '" + data[4] + "' WHERE RNUM = '" + data[0] + "'"
                    else:
                        if (data[5] != ''):
                            #RNUM X, RSCORE O, RDATE O, ONUM X, MNUM X, SNUM O
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", RDATE = '" + data[2] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE O, ONUM X, MNUM X, SNUM X
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", RDATE = '" + data[2] + "' WHERE RNUM = '" + data[0] + "'"
            #RNUM X, RSCORE O, RDATE X
            else:
                #RNUM X, RSCORE O, RDATE X, ONUM O
                if (data[3] != ''):
                    #RNUM X, RSCORE O, RDATE X, ONUM O, MNUM O
                    if (data[4] != ''):
                        #RNUM X, RSCORE O, RDATE X, ONUM O, MNUM O, SNUM O
                        if (data[5] != ''):
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", ONUM = '" + data[3] + "', MNUM = '" + data[4] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE X, ONUM O, MNUM O, SNUM X
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", ONUM = '" + data[3] + "', MNUM = '" + data[4] + "' WHERE RNUM = '" + data[0] + "'"
                    else:
                        if (data[5] != ''):
                            #RNUM X, RSCORE O, RDATE X, ONUM O, MNUM X, SNUM O
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", ONUM = '" + data[3] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE X, ONUM O, MNUM X, SNUM X
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", ONUM = '" + data[3] + "' WHERE RNUM = '" + data[0] + "'"
                else:
                    if (data[4] != ''):
                        if (data[5] != ''):
                            #RNUM X, RSCORE O, RDATE X, ONUM X, MNUM O, SNUM O
                            sql = "UPDATE review SET RSCORE = " + data[1] + " , MNUM = '" + data[4] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE X, ONUM X, MNUM O, SNUM X
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", MNUM = '" + data[4] + "' WHERE RNUM = '" + data[0] + "'"
                    else:
                        if (data[5] != ''):
                            #RNUM X, RSCORE O, RDATE X, ONUM X, MNUM X, SNUM O
                            sql = "UPDATE review SET RSCORE = " + data[1] + ", SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                        else:
                            #RNUM X, RSCORE O, RDATE X, ONUM X, MNUM X, SNUM X
                            sql = "UPDATE review SET RSCORE = " + data[1] + " WHERE RNUM = '" + data[0] + "'"
        #RNUM X, RSCORE X
        elif (data[2] != ''):
                #RNUM X, RSCORE X, RDATE O, ONUM O
            if (data[3] != ''):
                #RNUM X, RSCORE X, RDATE O, ONUM O, MNUM O
                if (data[4] != ''):
                    #RNUM X, RSCORE X, RDATE O, ONUM O, MNUM O, SNUM O
                    if (data[5] != ''):
                        sql = "UPDATE review SET RDATE = '" + data[2] + "', ONUM = '" + data[3] + "', MNUM = '" + data[4] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE O, ONUM O, MNUM O, SNUM X
                        sql = "UPDATE review SET RDATE = '" + data[2] + "', ONUM = '" + data[3] + "', MNUM = '" + data[4] + "' WHERE RNUM = '" + data[0] + "'"
                else:
                    if (data[5] != ''):
                        #RNUM X, RSCORE X, RDATE O, ONUM O, MNUM X, SNUM O
                        sql = "UPDATE review SET RDATE = '" + data[2] + "', ONUM = '" + data[3] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE O, ONUM O, MNUM X, SNUM X
                        sql = "UPDATE review SET RDATE = '" + data[2] + "', ONUM = '" + data[3] + "' WHERE RNUM = '" + data[0] + "'"
            else:
                if (data[4] != ''):
                    if (data[5] != ''):
                        #RNUM X, RSCORE X, RDATE O, ONUM X, MNUM O, SNUM O
                        sql = "UPDATE review SET RDATE = '" + data[2] + "', MNUM = '" + data[4] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE O, ONUM X, MNUM O, SNUM X
                        sql = "UPDATE review SET RDATE = '" + data[2] + "', MNUM = '" + data[4] + "' WHERE RNUM = '" + data[0] + "'"
                else:
                    if (data[5] != ''):
                        #RNUM X, RSCORE X, RDATE O, ONUM X, MNUM X, SNUM O
                        sql = "UPDATE review SET RDATE = '" + data[2] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE O, ONUM X, MNUM X, SNUM X
                        sql = "UPDATE review SET RDATE = '" + data[2] + "' WHERE RNUM = '" + data[0] + "'"
            #RNUM X, RSCORE X, RDATE X
        else:
            #RNUM X, RSCORE X, RDATE X, ONUM O
            if (data[3] != ''):
                #RNUM X, RSCORE X, RDATE X, ONUM O, MNUM O
                if (data[4] != ''):
                    #RNUM X, RSCORE X, RDATE X, ONUM O, MNUM O, SNUM O
                    if (data[5] != ''):
                        sql = "UPDATE review SET ONUM = '" + data[3] + "', MNUM = '" + data[4] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE X, ONUM O, MNUM O, SNUM X
                        sql = "UPDATE review SET ONUM = '" + data[3] + "', MNUM = '" + data[4] + "' WHERE RNUM = '" + data[0] + "'"
                else:
                    if (data[5] != ''):
                        #RNUM X, RSCORE X, RDATE X, ONUM O, MNUM X, SNUM O
                        sql = "UPDATE review SET ONUM = '" + data[3] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE X, ONUM O, MNUM X, SNUM X
                        sql = "UPDATE review SET ONUM = '" + data[3] + "' WHERE RNUM = '" + data[0] + "'"
            else:
                if (data[4] != ''):
                    if (data[5] != ''):
                        #RNUM X, RSCORE X, RDATE X, ONUM X, MNUM O, SNUM O
                        sql = "UPDATE review SET RSCORE = " + data[1] + " , MNUM = '" + data[4] + "', SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE X, ONUM X, MNUM O, SNUM X
                        sql = "UPDATE review SET MNUM = '" + data[4] + "' WHERE RNUM = '" + data[0] + "'"
                else:
                    if (data[5] != ''):
                        #RNUM X, RSCORE X, RDATE X, ONUM X, MNUM X, SNUM O
                        sql = "UPDATE review SET SNUM = '" + data[5] + "' WHERE RNUM = '" + data[0] + "'"
                    else:
                        #RNUM X, RSCORE X, RDATE X, ONUM X, MNUM X, SNUM X -> 오류
                        sql = ""

        print(sql)
        cur.execute(sql) 
    
    
    except : 
        tk.messagebox.showerror('오류', '업데이트 오류가 발생함')
    
    else: 
        tk.messagebox.showinfo('성공', '데이터 업데이트 성공')
        conn.commit() 
        conn.close()
        #메인페이지 데이터 업데이트
        rvp.getData() 
        UpWindow.destroy()

10. updateStockPage.py : 재고 테이블 UPDATE 쿼리를 위한 창

더보기
import tkinter as tk
from tkinter import * 
from tkinter import messagebox 
import stockpage as stp
import pymysql

def updateFunction() :
    global UpWindow
    UpWindow = tk.Tk()
    UpWindow.title("데이터 업데이트")
    UpWindow.geometry("400x300+1000+500")
    UpWindow.resizable(False, False)

    textFrame1 = tk.Frame(UpWindow)
    textFrame1.pack()
    inputFrame1 = tk.Frame(UpWindow) 
    inputFrame1.pack()
    voidFrame = tk.Frame(UpWindow, height="20")
    voidFrame.pack()

    textFrame2 = tk.Frame(UpWindow)
    textFrame2.pack()
    labelFrame = tk.Frame(UpWindow)
    labelFrame.pack()
    inputFrame2 = tk.Frame(UpWindow) 
    inputFrame2.pack()
    btnFrame = tk.Frame(UpWindow)
    btnFrame.pack()

    global edit1; global edit2; global edit3; global edit4

    textLabel1 = tk.Label(textFrame1, text="업데이트하고 싶은 데이터의 고유번호를 하단에 입력하십시오")
    textLabel1.pack(side=BOTTOM, pady=10)

    dataLabel1 = tk.Label(inputFrame1, text="SNUM", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel1.pack(side=LEFT, fill="both", expand=1, padx=10)
    edit1 = Entry(inputFrame1, width=13); edit1.pack(side=LEFT, padx=10) 

    textLabel2 = tk.Label(textFrame2, text="업데이트하고 싶은 데이터를 하단에 입력하십시오")
    textLabel2.pack(side=BOTTOM, pady=10)
    
    dataLabel2 = tk.Label(labelFrame, text="SNAME", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel2.pack(side=LEFT, fill="both", expand=1, padx=15, pady=5)
    dataLabel3 = tk.Label(labelFrame, text="SCOST", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel3.pack(side=LEFT, fill="both", expand=1, padx=15, pady=5)
    dataLabel4 = tk.Label(labelFrame, text="SQTY", bg = "white", width=13, font=("Arial", "8", "bold"))
    dataLabel4.pack(side=LEFT, fill="both", expand=1, padx=15, pady=5)

    edit2 = Entry(inputFrame2, width=13); edit2.pack(side=LEFT, padx=15)
    edit3 = Entry(inputFrame2, width=13); edit3.pack(side=LEFT, padx=15) 
    edit4 = Entry(inputFrame2, width=13); edit4.pack(side=LEFT, padx=15)

    btnUpdate = Button(btnFrame, text="UPDATE", width=13, height="3", command = connectForUpdate, bg="#2f3640", fg="white", cursor="hand2") 
    btnUpdate.pack(pady="35")

    UpWindow.mainloop()

    
def connectForUpdate():
    conn, cur = None, None 
    data = [""] * 4 
    sql = "" 

    # connect database
    conn = pymysql.connect(host='127.0.0.1', user='root', password='#비밀번호', db='cafe_management', charset='utf8') 

    # create cursor 
    cur = conn.cursor() 
    data[0] = edit1.get(); data[1] = edit2.get(); data[2] = edit3.get(); data[3] = edit4.get();
    
    try :
        #sname O
        if(data[1] != ''):
            #sname O, scost O
            if (data[2] != ''):
                if (data[3] != ''):
                    #sname O, scost O, sqty O
                    sql = "UPDATE stock SET SNAME = '" + data[1] + "', SCOST = " + data[2] + ", SQTY = " + data[3] + " WHERE SNUM = '" + data[0] + "'"
                else:
                    #sname O, scost O, sqty X
                    sql = "UPDATE stock SET SNAME = '" + data[1] + "', SCOST = " + data[2] + " WHERE SNUM = '" + data[0] + "'"
            #sname O, scost X
            else:
                if (data[3] != ''):
                    #sname O, scost X, sqty O
                    sql = "UPDATE stock SET SNAME = '" + data[1] + "', SQTY = " + data[3] + " WHERE SNUM = '" + data[0] + "'"
                else:
                    #snameO, scost X, sqty X
                    sql = "UPDATE stock SET SNAME = '" + data[1] + "' WHERE SNUM = '" + data[0] + "'"
        
        #sname X, scost O
        elif (data[2] != ''):
            if (data[3] != ''):
                #sname X, scost O, sqty O
                sql = "UPDATE stock SET SCOST = " + data[2] + ", SQTY = " + data[3] + " WHERE SNUM = '" + data[0] + "'"
            else:
                #sname X, scost O, sqty X
                sql = "UPDATE stock SET SCOST = " + data[2] + " WHERE SNUM = '" + data[0] + "'"
        
        #sname X, scost X, sqty O
        elif (data[3] != ''):
            sql = "UPDATE stock SET SQTY = " + data[3] + " WHERE SNUM = '" + data[0] + "'"
        
        elif (data[0] != '' and data[1] == '' or data[2] == '' or data[3] ==''):
            sql = ""

        print(sql)
        cur.execute(sql) 
    
    
    except : 
        tk.messagebox.showerror('오류', '업데이트 오류가 발생함')
    
    else: 
        tk.messagebox.showinfo('성공', '데이터 업데이트 성공')
        conn.commit() 
        conn.close()
        #메인페이지 데이터 업데이트
        stp.getData() 
        UpWindow.destroy()

 

 

사실 알고리즘을 제대로 배우면 예외 처리도 훨씬 깔끔하게 할 수 있을 것 같은데,

급하게 코드를 짜다 보니 너무 길어지고 이상해진 것 같아서 속상했다.

또, UI도 더 예쁘고 화려하게 할 수 있을 것 같은데 너무 단순하게 만들어진 건 아닌가 생각했다.

내 코드가 다른 개발자분들의 짜집기인 느낌이 강해서 더 발전해야겠다고 생각하기도 했다.

 

프로젝트를 더 많이 하다 보면 괜찮아질 수 있겠지? 🤔

 

 

 

코드블럭 입력이 한 글에 하나의 언어씩만 적용이 되어, 다음 글에서 php 결과물을 확인할 수 있다.

2022.06.22 - [coding] - MySQL 웹 및 Python에서 사용하기(ESQL) 3 : Php

 

MySQL 웹 및 Python에서 사용하기(ESQL) 3 : Php

2022년 2학기 과제로 Embedded SQL을 수행한 결과물이다. **3일만에 완성한 과제로 SQL에 대한 지식이 충분치 않아 결과물이 미숙할 수 있습니다** 맨 위부터 Php, SQL(테이블 제작 및 데이터 입력 관련 파

seeya-study.tistory.com