파이썬 + MySQL

주피터 노트북 설치

pip install notebook

 

 

 

윈도우 프로그래밍

기본 윈도 창의 구성

from tkinter import *

window = Tk()

#이부분에서 화면을 구성하고 처리

window.mainloop()

from tkinter import *
window = Tk()
window.title('윈도창 연습')
window.geometry('400x100')
window.resizable(width = False, height = False)
window.mainloop()

• 크기 변경 안됨

 

from tkinter import *
window = Tk()

label1 = Label(window, text = 'This is MySQL을')
label2 = Label(window, text = '열심히', font = ('궁서체', 30), fg = 'blue')
label3 = Label(window, text = '공부 중입니다.', bg = 'magenta', width = 20, height = 5,
              anchor = SE) #(SouthEast)anchor은 위젯이 어느 위치에 자리 잡을지 정한다.

label1.pack()
label2.pack()
label3.pack()

window.mainloop()

 

버튼 활용

from tkinter import *
from tkinter import messagebox

def clickButton():
    messagebox.showinfo('버튼 클릭', '버튼을 클릭했습니다.')

window = Tk()
window.geometry('200x200')

button1 = Button(window, text = '요기 눌러요', fg = 'red', bg= 'yellow',
                 command = clickButton)
button1.pack(expand = 1)

window.mainloop()

 

from tkinter import *
from tkinter import messagebox

def clickButton():
    messagebox.showinfo('버튼 클릭', '버튼을 클릭했습니다.')

window = Tk()
window.geometry('200x200')

button1 = Button(window, text = '버튼1', fg = 'red', bg= 'yellow',
                 command = clickButton)
button2 = Button(window, text = '버튼2', fg = 'red', bg= 'yellow',
                 command = clickButton)
button3 = Button(window, text = '버튼3', fg = 'red', bg= 'yellow',
                 command = clickButton)

button1.pack(side = RIGHT)
button2.pack(side = RIGHT)
button3.pack(side = RIGHT)

btnList = [None]*3
for i in range(0,3):
    btnList[i] = Button(window, text = 'FOR 버튼'+str(i+1))

for btn in btnList:
    btn.pack(side = BOTTOM)

window.mainloop()

 

프레임, 엔트리, 리스트 박스

from tkinter import *
window = Tk()
window.geometry('200x200')
upFrame = Frame(window)
upFrame.pack()
downFrame = Frame(window)
downFrame.pack()

editBox = Entry(upFrame, width = 10, bg = 'green')
editBox.pack(padx = 20, pady = 20)

listbox = Listbox(downFrame, bg = 'yellow')
listbox.pack()

listbox.insert(END, '하나')
listbox.insert(END, '둘')

window.mainloop()

 

메뉴

from tkinter import *
window = Tk()

mainMenu = Menu(window)
window.config(menu = mainMenu)

fileMenu = Menu(mainMenu)
mainMenu.add_cascade(label = '파일', menu = fileMenu)
fileMenu.add_command(label = '열기')
fileMenu.add_separator()
fileMenu.add_command(label = '종료')

window.mainloop()

 

 

from tkinter import *
from tkinter import messagebox

def func_open():
    messagebox.showinfo('메뉴선택', '열기메뉴 선택함')

def func_exit():
    window.quit()
    window.destroy()

window = Tk()

mainMenu = Menu(window)
window.config(menu=mainMenu)

fileMenu = Menu(mainMenu)
mainMenu.add_cascade(label = '파일', menu = fileMenu)
fileMenu.add_command(label = '열기', command = func_open)
fileMenu.add_separator()
fileMenu.add_command(label = '종료', command = func_exit)

window.mainloop()

 

대화 상자

from tkinter import *
from tkinter.simpledialog import *

window = Tk()
window.geometry('400x100')

label1 = Label(window, text = '입력된 값')
label1.pack()

value = askinteger('확대배수', '주사위 숫자(1~6)를 입력하세요.', minvalue = 1, maxvalue = 6)
label1.configure(text=str(value))

window.mainloop()

 

캔버스

from tkinter import *
from tkinter.simpledialog import *

window = Tk()

canvas = Canvas(window, height = 300, width = 300)
canvas.pack()

canvas.create_line([[0,0], [70, 70], [30, 170]], fill = 'blue', width = 3)
canvas.create_polygon([[100,100], [100,150], [150,150], [150,100]], fill = 'red')
canvas.create_text([200,200], text = '이것이 MySQL이다.', font = ('굴림', 15))

window.mainloop()

 

 

 

파이썬과 MySQL 연동

• cmd 에서 pip install pymysql 라이브러리 설치
• MySQL 에서 DB를 만들어 준다.

-- 주피터 노트북
import pymysql

conn = pymysql.connect(
    host = '127.0.0.1', user = 'root', password = '1234', db = 'hanbitDB', charset = 'utf8')

-- pymysql.connect로 로컬 sql서버와 연결

cur = conn.cursor()

cur.execute("CREATE TABLE IF NOT EXISTS userTable (id char(4), userName char(15), email char(20), birthYear int)")

cur.execute("INSERT INTO userTable VALUES('john','John Bann', 'john@naver.com', 1990)")
cur.execute("INSERT INTO userTable VALUES('kim','Kim Chi', 'kim@naver.com', 1992)")
cur.execute("INSERT INTO userTable VALUES('lee','Lee Bann', 'lee@naver.com', 1988)")
cur.execute("INSERT INTO userTable VALUES('park','Park Bann', 'park@naver.com', 1980)")

conn.commit()

• commit 을 먼저 해줘야 그 다음 데이터가 들어감
• MySQL 에서 SELECT

 

import pymysql

# 전역변수 선언부
conn, cur = None, None
data1, data2, data3, data4 = "", "", "", ""
sql=""

# 메인 코드
conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='hanbitDB', charset='utf8')
cur = conn.cursor()

while (True) :
    data1 = input("사용자 ID ==> ")
    if data1 == "" :
        break;
    data2 = input("사용자 이름 ==> ")
    data3 = input("사용자 이메일 ==> ")
    data4 = input("사용자 출생연도 ==> ")
    sql = "INSERT INTO userTable VALUES('" + data1 + "','" + data2 + "','" + data3 + "'," + data4 + ")"
    cur.execute(sql)

conn.commit()
conn.close()

데이터 조회

import pymysql

# 전역변수 선언부
con, cur = None, None
data1, data2, data3, data4 = "", "", "", ""
row=None

# 메인 코드
conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='hanbitDB', charset='utf8')
cur = conn.cursor()

cur.execute("SELECT * FROM userTable")

print("사용자ID    사용자이름    이메일        출생연도")
print("----------------------------------------------------")

while (True) :
    row = cur.fetchone()
    if row== None :
        break
    data1 = row[0]
    data2 = row[1]
    data3 = row[2]
    data4 = row[3]
    print("%5s   %15s   %15s   %d" % (data1, data2, data3, data4))

conn.close()

사용자ID 사용자이름 이메일 출생연도

----------------------------------------------------

john John Bann john@naver.com 1990

kim Kim Chi kim@naver.com 1992

lee Lee Bann lee@naver.com 1988

park Park Bann park@naver.com 1980

JAE 이재영 JAE@naver.co 2000

 

GUI 데이터 입력 프로그램

import pymysql
from tkinter import *
from tkinter import messagebox

## 함수 선언부
def insertData() :
    con, cur = None, None
    data1, data2, data3, data4 = "", "", "", ""
    sql=""

    conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='hanbitDB', charset='utf8')
    cur = conn.cursor()

    data1 = edt1.get();    data2 = edt2.get();    data3 = edt3.get();    data4 = edt4.get()
    try :
        sql = "INSERT INTO userTable VALUES('" + data1 + "','" + data2 + "','" + data3 + "'," + data4 + ")"
        cur.execute(sql)
    except :
        messagebox.showerror('오류', '데이터 입력 오류가 발생함')
    else :
        messagebox.showinfo('성공', '데이터 입력 성공')
    conn.commit()
    conn.close()

def selectData() :
    strData1, strData2, strData3, strData4  = [], [], [], []
    conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='hanbitDB', charset='utf8')
    cur = conn.cursor()
    cur.execute("SELECT * FROM userTable")
    strData1.append("사용자ID");      strData2.append("사용자이름")
    strData3.append("이메일");         strData4.append("출생연도")
    strData1.append("-----------");    strData2.append("-----------")
    strData3.append("-----------");    strData4.append("-----------")
    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()

## 메인 코드부
window = Tk()
window.geometry("600x300")
window.title("GUI 데이터 입력")

edtFrame = Frame(window);
edtFrame.pack()
listFrame = Frame(window)
listFrame.pack(side = BOTTOM,fill=BOTH, expand=1)

edt1= Entry(edtFrame, width=10);    edt1.pack(side=LEFT,padx=10,pady=10)
edt2= Entry(edtFrame, width=10);    edt2.pack(side=LEFT,padx=10,pady=10)
edt3= Entry(edtFrame, width=10);    edt3.pack(side=LEFT,padx=10,pady=10)
edt4= Entry(edtFrame, width=10);    edt4.pack(side=LEFT,padx=10,pady=10)

btnInsert = Button(edtFrame, text="입력", command = insertData)
btnInsert.pack(side=LEFT,padx=10,pady=10)
btnSelect = Button(edtFrame, text="조회", command =selectData )
btnSelect.pack(side=LEFT,padx=10,pady=10)

listData1 = Listbox(listFrame,bg = 'yellow');
listData1.pack(side=LEFT,fill=BOTH, expand=1)
listData2 = Listbox(listFrame,bg = 'yellow')
listData2.pack(side=LEFT,fill=BOTH, expand=1)
listData3 = Listbox(listFrame,bg = 'yellow')
listData3.pack(side=LEFT,fill=BOTH, expand=1)
listData4 = Listbox(listFrame,bg = 'yellow')
listData4.pack(side=LEFT,fill=BOTH, expand=1)

window.mainloop()

 

 

공간 데이터 조회 응용 프로그램

공간 데이터 실습 때 만든 데이터를 사용

SELECT ManagerName, Area AS '당탕이' FROM Manager WHERE ManagerName='당탕이';
SELECT ManagerName, Area AS '존밴이' FROM Manager WHERE ManagerName = '존밴이';
SELECT restName, ST_buffer(restLocation, 3) AS '체인점' FROM Restaurant;
SELECT roadName, ST_buffer(roadLine, 1) AS '강변북로' FROM Road;

 

전체 구성

import pymysql
from tkinter import *
from tkinter import messagebox
from tkinter.simpledialog import *
import random

## 함수 선언 부

def displayRestaurant() :
    global conn, curr, window, canvas
    pass

def displayManager() :
    global conn, curr, window, canvas
    pass

def displayRoad() :
    global conn, curr, window, canvas
    pass

def clearMap() :
    global conn, curr, window, canvas
    pass

def showResMan() :
    global conn, curr, window, canvas
    pass

def showNearest() :
    global conn, curr, window, canvas    
    pass

## 전역 변수부
conn, curr = None, None
window, canvas = None, None

SCR_WIDTH, SCR_HEIGHT = 360, 360

## 메인 코드부
window=Tk()
window.title("왕매워 짬뽕 Ver 0.1")
canvas = Canvas(window, height=SCR_HEIGHT, width=SCR_WIDTH) 
canvas.pack()

mainMenu = Menu(window)
window.config(menu=mainMenu)

gis1Menu = Menu(mainMenu)
mainMenu.add_cascade(label="GIS 데이터 보기", menu=gis1Menu)
gis1Menu.add_command(label="체인점 보기", command=displayRestaurant)
gis1Menu.add_command(label="관리자 보기", command=displayManager)
gis1Menu.add_command(label="도로 보기", command=displayRoad)
gis1Menu.add_separator()
gis1Menu.add_command(label="화면 지우기", command=clearMap)
gis1Menu.add_separator()

gis2Menu = Menu(mainMenu)
mainMenu.add_cascade(label="GIS 데이터 분석", menu=gis2Menu)
gis2Menu.add_command(label="관리자별 담당 체인점", command=showResMan)
gis2Menu.add_command(label="가장 가까운 체인점", command=showNearest)

window.mainloop()

 

공통 사용 함수 작성

def connectMySQL() :
    global conn, curr, window, canvas
    conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='KingHotDB', charset='utf8')
    curr = conn.cursor()

def closeMySQL() :
    global conn, curr, window, canvas
    curr.close()
    conn.close()
    curr, conn = None, None

def randomColor() :
    COLORS = ["black",  "red", "green", "blue", "magenta", "orange", "brown", "maroon", "coral"]
    return random.choice(COLORS)

def clearMap() :
    global conn, curr, window, canvas
    canvas.destroy()
    canvas = Canvas(window, height=SCR_HEIGHT, width=SCR_WIDTH) 
    canvas.pack()

 

[체인점 보기] 함수

def displayRestaurant() :
    global conn, curr, window, canvas
    connectMySQL()

    sql = "SELECT restName, ST_AsText((ST_Buffer(restLocation, 3))) FROM Restaurant"
    curr.execute(sql)
    
    while True :
        row = curr.fetchone()
        if not row :
            break
        restName, gisStr = row
        start = gisStr.index('(')
        start = gisStr.index('(', start + 1)
        start += 1
        end = gisStr.index(')')
        gisStr = gisStr[start:end]      # "x y,x y,...."
        gisList = gisStr.split(',')         # ["x y", "x y", ....]
        newGisList = []
        for xyStr in gisList :
            x, y = xyStr.split(' ') # "x y"를 "x"와  "y"로 분리
            x, y = float(x), float(y) # 실수로 형 변환
            xyList = [ (x+90)*2, SCR_HEIGHT-(y+90)*2] # [x, y] : 화면 좌표(90,90 추가) 및 2배 확대
            newGisList.append(xyList) #[ [x,y], [x,y] .... ]

        myColor = randomColor()
        canvas.create_line(newGisList, fill=myColor, width=3)
        # 해당 위치에 글자 쓰기.
        tx, ty = xyList[0], xyList[1]+15 # 오른쪽 아래에 쓰기
        canvas.create_text([tx, ty],fill=myColor,text=restName.split(' ')[2]) # 0호점 만 쓰기.

    closeMySQL()

 

[관리자 보기] 함수

def displayManager() :
    global conn, curr, window, canvas
    connectMySQL()

    sql = "SELECT ManagerName, ST_AsText(Area) FROM Manager ORDER BY ManagerName"
    curr.execute(sql)
    
    while True :
        row = curr.fetchone()
        if not row :
            break
        managerName, gisStr = row
        start = gisStr.index('(')
        start = gisStr.index('(', start + 1)
        start += 1
        end = gisStr.index(')')
        gisStr = gisStr[start:end]      # "x y,x y,...."
        gisList = gisStr.split(',')         # ["x y", "x y", ....]
        newGisList = []
        for xyStr in gisList :
            x, y = xyStr.split(' ') # "x y"를 "x"와  "y"로 분리
            x, y = float(x), float(y) # 실수로 형 변환
            xyList = [ (x+90)*2, SCR_HEIGHT-(y+90)*2] # [x, y] : 화면 좌표(90,90 추가) 및 2배 확대
            newGisList.append(xyList) #[ [x,y], [x,y] .... ]
        
        canvas.create_polygon(newGisList, fill=randomColor())

    closeMySQL()

 

 

[도로 보기] 함수

def displayRoad() :
    global conn, curr, window, canvas
    connectMySQL()

    sql = "SELECT RoadName, ST_AsText(ST_BUFFER(RoadLine,2)) FROM Road"
    curr.execute(sql)
    
    while True :
        row = curr.fetchone()
        if not row :
            break
        managerName, gisStr = row
        start = gisStr.index('(')
        start = gisStr.index('(', start + 1)
        start += 1
        end = gisStr.index(')')
        gisStr = gisStr[start:end]      # "x y,x y,...."
        gisList = gisStr.split(',')         # ["x y", "x y", ....]
        newGisList = []
        for xyStr in gisList :
            x, y = xyStr.split(' ') # "x y"를 "x"와  "y"로 분리
            x, y = float(x), float(y) # 실수로 형 변환
            xyList = [ (x+90)*2, SCR_HEIGHT-(y+90)*2] # [x, y] : 화면 좌표(90,90 추가) 및 2배 확대
            newGisList.append(xyList) #[ [x,y], [x,y] .... ]
        
        canvas.create_polygon(newGisList, fill=randomColor())
        
    closeMySQL()

 

[관리자 별 담당 체인점] 함수

def showResMan() :
    global conn, curr, window, canvas

    displayRestaurant() # 우선 지점을 출력
    
    connectMySQL()
    sql = "SELECT M.ManagerName, R.restName, ST_AsText((ST_Buffer(R.restLocation, 3))) FROM Restaurant R, Manager M"
    sql += " WHERE ST_Contains(M.area, R.restLocation) = 1 ORDER BY R.restName" # 체인점 순으로 정렬

    curr.execute(sql)

    saveRest = '' # 관리자가 중복된 체인점인지 체크.
    while True :
        row = curr.fetchone()
        if not row :
            break
        managerName, restName, gisStr = row
        start = gisStr.index('(')
        start = gisStr.index('(', start + 1)
        start += 1
        end = gisStr.index(')')
        gisStr = gisStr[start:end]      # "x y,x y,...."
        gisList = gisStr.split(',')         # ["x y", "x y", ....]
        newGisList = []
        for xyStr in gisList :
            x, y = xyStr.split(' ') # "x y"를 "x"와  "y"로 분리
            x, y = float(x), float(y) # 실수로 형 변환
            xyList = [ (x+90)*2, SCR_HEIGHT-(y+90)*2] # [x, y] : 화면 좌표(90,90 추가) 및 2배 확대
            newGisList.append(xyList) #[ [x,y], [x,y] .... ]

        myColor = randomColor()
        if saveRest == restName : # 중복 관리지역만 추가로 폴리곤으로 처리.
            canvas.create_polygon(newGisList, fill=myColor)

        # 해당 위치에 글자 쓰기.
        # 관리자가 2명인 경우에는 추가 관리자 이름에 뒤에 붙여서 쓰기.
        if saveRest == restName :
            tx, ty = xyList[0], xyList[1]+45 # 두번째 관리자. 아래아래에 쓰기
        else :
            tx, ty = xyList[0], xyList[1]+30 # 첫번째 관리자. 아래에 쓰기
            
        canvas.create_text([tx, ty],fill=myColor,text=managerName) # 관리자 이름만 추가로..
        saveRest = restName

    closeMySQL()

 

 

[가장 가까운 체인점] 함수

def showNearest() :
    global conn, curr, window, canvas    

    baseRest = '왕매워 짬뽕 ' +  askstring('기준 체인점', '체인점 번호를 입력하세요') + '호점'
    
    connectMySQL()    
    sql = "SELECT ST_AsText(R2.restLocation), ST_Distance(R1.restLocation, R2.restLocation) "
    sql += " FROM Restaurant R1, Restaurant R2 "
    sql += " WHERE R1.restName='" + baseRest + "' "
    sql += " ORDER BY ST_Distance(R1.restLocation, R2.restLocation) "
    curr.execute(sql)

    row = curr.fetchone() # 첫번째(가장 가까운 거리)는 자신
    gisStr, distance = row  # gisStr은 "POINT(-80 -30)" 형식 
    start = gisStr.index('(')
    start += 1
    end = gisStr.index(')')
    gisStr = gisStr[start:end]      # "x y"
    x, y = list(map(float, gisStr.split(' '))) # [x y]
    baseXY = [ (x+90)*2, SCR_HEIGHT-(y+90)*2] 

    lineWidth = 20
    while True :
        row = curr.fetchone()
        if not row :
            break
        gisStr, distance = row
        start = gisStr.index('(')
        start += 1
        end = gisStr.index(')')
        gisStr = gisStr[start:end]      # "x y"
        x, y = list(map(float, gisStr.split(' '))) # [x y]
        targetXY = [ (x+90)*2, SCR_HEIGHT-(y+90)*2] 

        myColor = randomColor()
        if lineWidth < 0 :
            lineWidth = 0
        canvas.create_line([baseXY, targetXY], fill=myColor, width=lineWidth)
        lineWidth -= 5 # 선두께 감소..        
    
    closeMySQL()

    displayRestaurant() # 지점을 출력 (위쪽에 보이려 제일 나중에 출력)

 

최종

import pymysql
from tkinter import *
from tkinter import messagebox
from tkinter.simpledialog import *
import random

## 함수 선언 부

def connectMySQL() :
    global conn, curr, window, canvas
    conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='KingHotDB', charset='utf8')
    curr = conn.cursor()

def closeMySQL() :
    global conn, curr, window, canvas
    curr.close()
    conn.close()
    curr, conn = None, None

def randomColor() :
    COLORS = ["black",  "red", "green", "blue", "magenta", "orange", "brown", "maroon", "coral"]
    return random.choice(COLORS)

def clearMap() :
    global conn, curr, window, canvas
    canvas.destroy()
    canvas = Canvas(window, height=SCR_HEIGHT, width=SCR_WIDTH) 
    canvas.pack()

def displayRestaurant() :
    global conn, curr, window, canvas
    connectMySQL()

    sql = "SELECT restName, ST_AsText((ST_Buffer(restLocation, 3))) FROM Restaurant"
    curr.execute(sql)
    
    while True :
        row = curr.fetchone()
        if not row :
            break
        restName, gisStr = row
        start = gisStr.index('(')
        start = gisStr.index('(', start + 1)
        start += 1
        end = gisStr.index(')')
        gisStr = gisStr[start:end]      # "x y,x y,...."
        gisList = gisStr.split(',')         # ["x y", "x y", ....]
        newGisList = []
        for xyStr in gisList :
            x, y = xyStr.split(' ') # "x y"를 "x"와  "y"로 분리
            x, y = float(x), float(y) # 실수로 형 변환
            xyList = [ (x+90)*2, SCR_HEIGHT-(y+90)*2] # [x, y] : 화면 좌표(90,90 추가) 및 2배 확대
            newGisList.append(xyList) #[ [x,y], [x,y] .... ]

        myColor = randomColor()
        canvas.create_line(newGisList, fill=myColor, width=3)
        # 해당 위치에 글자 쓰기.
        tx, ty = xyList[0], xyList[1]+15 # 오른쪽 아래에 쓰기
        canvas.create_text([tx, ty],fill=myColor,text=restName.split(' ')[2]) # 0호점 만 쓰기.

    closeMySQL()

def displayManager() :
    global conn, curr, window, canvas
    connectMySQL()

    sql = "SELECT ManagerName, ST_AsText(Area) FROM Manager ORDER BY ManagerName"
    curr.execute(sql)
    
    while True :
        row = curr.fetchone()
        if not row :
            break
        managerName, gisStr = row
        start = gisStr.index('(')
        start = gisStr.index('(', start + 1)
        start += 1
        end = gisStr.index(')')
        gisStr = gisStr[start:end]      # "x y,x y,...."
        gisList = gisStr.split(',')         # ["x y", "x y", ....]
        newGisList = []
        for xyStr in gisList :
            x, y = xyStr.split(' ') # "x y"를 "x"와  "y"로 분리
            x, y = float(x), float(y) # 실수로 형 변환
            xyList = [ (x+90)*2, SCR_HEIGHT-(y+90)*2] # [x, y] : 화면 좌표(90,90 추가) 및 2배 확대
            newGisList.append(xyList) #[ [x,y], [x,y] .... ]
        
        canvas.create_polygon(newGisList, fill=randomColor())

    closeMySQL()

def displayRoad() :
    global conn, curr, window, canvas
    connectMySQL()

    sql = "SELECT RoadName, ST_AsText(ST_BUFFER(RoadLine,2)) FROM Road"
    curr.execute(sql)
    
    while True :
        row = curr.fetchone()
        if not row :
            break
        managerName, gisStr = row
        start = gisStr.index('(')
        start = gisStr.index('(', start + 1)
        start += 1
        end = gisStr.index(')')
        gisStr = gisStr[start:end]      # "x y,x y,...."
        gisList = gisStr.split(',')         # ["x y", "x y", ....]
        newGisList = []
        for xyStr in gisList :
            x, y = xyStr.split(' ') # "x y"를 "x"와  "y"로 분리
            x, y = float(x), float(y) # 실수로 형 변환
            xyList = [ (x+90)*2, SCR_HEIGHT-(y+90)*2] # [x, y] : 화면 좌표(90,90 추가) 및 2배 확대
            newGisList.append(xyList) #[ [x,y], [x,y] .... ]
        
        canvas.create_polygon(newGisList, fill=randomColor())
        
    closeMySQL()   

def showResMan() :
    global conn, curr, window, canvas

    displayRestaurant() # 우선 지점을 출력
    
    connectMySQL()
    sql = "SELECT M.ManagerName, R.restName, ST_AsText((ST_Buffer(R.restLocation, 3))) FROM Restaurant R, Manager M"
    sql += " WHERE ST_Contains(M.area, R.restLocation) = 1 ORDER BY R.restName" # 체인점 순으로 정렬

    curr.execute(sql)

    saveRest = '' # 관리자가 중복된 체인점인지 체크.
    while True :
        row = curr.fetchone()
        if not row :
            break
        managerName, restName, gisStr = row
        start = gisStr.index('(')
        start = gisStr.index('(', start + 1)
        start += 1
        end = gisStr.index(')')
        gisStr = gisStr[start:end]      # "x y,x y,...."
        gisList = gisStr.split(',')         # ["x y", "x y", ....]
        newGisList = []
        for xyStr in gisList :
            x, y = xyStr.split(' ') # "x y"를 "x"와  "y"로 분리
            x, y = float(x), float(y) # 실수로 형 변환
            xyList = [ (x+90)*2, SCR_HEIGHT-(y+90)*2] # [x, y] : 화면 좌표(90,90 추가) 및 2배 확대
            newGisList.append(xyList) #[ [x,y], [x,y] .... ]

        myColor = randomColor()
        if saveRest == restName : # 중복 관리지역만 추가로 폴리곤으로 처리.
            canvas.create_polygon(newGisList, fill=myColor)

        # 해당 위치에 글자 쓰기.
        # 관리자가 2명인 경우에는 추가 관리자 이름에 뒤에 붙여서 쓰기.
        if saveRest == restName :
            tx, ty = xyList[0], xyList[1]+45 # 두번째 관리자. 아래아래에 쓰기
        else :
            tx, ty = xyList[0], xyList[1]+30 # 첫번째 관리자. 아래에 쓰기
            
        canvas.create_text([tx, ty],fill=myColor,text=managerName) # 관리자 이름만 추가로..
        saveRest = restName

    closeMySQL()

def showNearest() :
    global conn, curr, window, canvas    

    baseRest = '왕매워 짬뽕 ' +  askstring('기준 체인점', '체인점 번호를 입력하세요') + '호점'
    
    connectMySQL()    
    sql = "SELECT ST_AsText(R2.restLocation), ST_Distance(R1.restLocation, R2.restLocation) "
    sql += " FROM Restaurant R1, Restaurant R2 "
    sql += " WHERE R1.restName='" + baseRest + "' "
    sql += " ORDER BY ST_Distance(R1.restLocation, R2.restLocation) "
    curr.execute(sql)

    row = curr.fetchone() # 첫번째(가장 가까운 거리)는 자신
    gisStr, distance = row  # gisStr은 "POINT(-80 -30)" 형식 
    start = gisStr.index('(')
    start += 1
    end = gisStr.index(')')
    gisStr = gisStr[start:end]      # "x y"
    x, y = list(map(float, gisStr.split(' '))) # [x y]
    baseXY = [ (x+90)*2, SCR_HEIGHT-(y+90)*2] 

    lineWidth = 20
    while True :
        row = curr.fetchone()
        if not row :
            break
        gisStr, distance = row
        start = gisStr.index('(')
        start += 1
        end = gisStr.index(')')
        gisStr = gisStr[start:end]      # "x y"
        x, y = list(map(float, gisStr.split(' '))) # [x y]
        targetXY = [ (x+90)*2, SCR_HEIGHT-(y+90)*2] 

        myColor = randomColor()
        if lineWidth < 0 :
            lineWidth = 0
        canvas.create_line([baseXY, targetXY], fill=myColor, width=lineWidth)
        lineWidth -= 5 # 선두께 감소..        
    
    closeMySQL()

    displayRestaurant() # 지점을 출력 (위쪽에 보이려 제일 나중에 출력)

## 전역 변수부
conn, curr = None, None
window, canvas = None, None

SCR_WIDTH, SCR_HEIGHT = 360, 360

## 메인 코드부
window=Tk()
window.title("왕매워 짬뽕 Ver 0.1")
canvas = Canvas(window, height=SCR_HEIGHT, width=SCR_WIDTH) 
canvas.pack()

mainMenu = Menu(window)
window.config(menu=mainMenu)

gis1Menu = Menu(mainMenu)
mainMenu.add_cascade(label="GIS 데이터 보기", menu=gis1Menu)
gis1Menu.add_command(label="체인점 보기", command=displayRestaurant)
gis1Menu.add_command(label="관리자 보기", command=displayManager)
gis1Menu.add_command(label="도로 보기", command=displayRoad)
gis1Menu.add_separator()
gis1Menu.add_command(label="화면 지우기", command=clearMap)
gis1Menu.add_separator()

gis2Menu = Menu(mainMenu)
mainMenu.add_cascade(label="GIS 데이터 분석", menu=gis2Menu)
gis2Menu.add_command(label="관리자별 담당 체인점", command=showResMan)
gis2Menu.add_command(label="가장 가까운 체인점", command=showNearest)

window.mainloop()