mySQL 연동 + 폴더내 파일 자동 업로드

from tkinter import *
from tkinter.simpledialog import *
from tkinter.filedialog import *
import math
import os
import os.path
import pymysql
import datetime
 
 
#######################
#######전역 변수########
#######################
IP_ADDR   = '192.168.56.107'
USER_NAME = "root"
USER_PW   = "1234"
DB_NAME   = "BigData_DB"
CHAR_SET  = 'utf8'
 
 
#######################
#######함수 모음########
#######################
 
def selectFile():
 
    filename = askopenfilename(parent=window,
                filetypes=(("RAW 파일", "*.raw"), ("모든 파일", "*.*")))
 
    if filename == '' or filename == None:
        return
 
    edt1.insert(0, str(filename))
 
def selectfolder():
 
    global rawFileList
    foldername = askdirectory(parent=window)
    if foldername == '' or foldername == None:
        return
 
    edt1.insert(0, str(foldername))
 
    # 파일 목록 뽑기
    rawFileList = []
    for dirName, subDirList, fnames in os.walk(foldername) :
        for fname in fnames :
            filename, extname = os.path.basename(fname).split(".")
            if extname.upper().strip() == 'RAW' :
                rawFileList.append(os.path.join(dirName,fname))
 
def uploadData():
    con = pymysql.connect(host=IP_ADDR, user = USER_NAME, password = USER_PW, db = DB_NAME, charset = CHAR_SET)
    cur = con.cursor()
 
    fullname = edt1.get()
    with open(fullname, 'rb') as rfp:
        binData = rfp.read()
 
    print(type(binData))
 
    fname   = os.path.basename(fullname)
    fsize   = os.path.getsize(fullname)
    height  = width  = int(math.sqrt(fsize))
    now     = datetime.datetime.now()
    upDate  = now.strftime('%Y-%m-%d')
    upUser  = USER_NAME
 
    sql = "INSERT INTO rawImage_TBL2(id , raw_height , raw_width"
    sql += ", raw_name , raw_update , raw_uplaoder , raw_avg , raw_data) "
    sql += " VALUES(NULL," + str(height) + "," + str(width) + ",'"
    sql += fname + "','" + upDate +"','" + upUser + "',0 , "
    sql += " %s )"
 
    tupleData = (binData,)   # place holder에 넣을 인자들을 모두
 
    cur.execute(sql, tupleData)
 
    con.commit()
    cur.close()
    con.close()
 
def malloc(h, w, initValue = 0) :
    l = []
    for i in range(h) :
        tmpList = []
        for j in range(w) :
            tmpList.append(initValue)
        l.append(tmpList)
    return l
 
 
def findStat(fname):
    # 파일 열고, 읽기.
    fsize = os.path.getsize(fname) # 파일의 크기(바이트)
    inH = inW = int(math.sqrt(fsize)) # 핵심 코드
    ## 입력영상 메모리 확보 ##
    inImage=[]
    inImage=malloc(inH,inW)
    # 파일 --> 메모리
    with open(fname, 'rb') as rFp:
        for i in range(inH) :
            for k in range(inW) :
                inImage[i][k] = int(ord(rFp.read(1)))
    sum = 0
    for i in range(inH):
        for k in range(inW) :
            sum += inImage[i][k]
    avg = sum // (inW * inH)
    maxVal = minVal = inImage[0][0]
    for i in range(inH):
        for k in range(inW):
            if inImage[i][k] < minVal:
                minVal = inImage[i][k]
            elif inImage[i][k] > maxVal:
                maxVal = inImage[i][k]
    return avg, maxVal, minVal
 
 
def uploadDataFromFolder():
    global rawFileList
    con = pymysql.connect(host=IP_ADDR, user = USER_NAME, password = USER_PW, db = DB_NAME, charset = CHAR_SET)
    cur = con.cursor()
 
    try :
        sql = '''
        CREATE TABLE rawImage_TBL2 (
        raw_id INT AUTO_INCREMENT PRIMARY KEY,
        raw_fname VARCHAR(30),
        raw_extname CHAR(5),
        raw_height SMALLINT,
        raw_width SMALLINT,
        raw_avg TINYINT UNSIGNED ,
        raw_max TINYINT UNSIGNED ,
        raw_min TINYINT UNSIGNED ,
        raw_data LONGBLOB
          
        );
        
        '''
        cur.execute(sql)
 
    except :
        pass
 
        for fullname in rawFileList:
 
            with open(fullname, 'rb') as rfp:
                binData = rfp.read()
 
            fname, extname = os.path.basename(fullname).split(".")
            fsize   = os.path.getsize(fullname)
            height  = width  = int(math.sqrt(fsize))
            now     = datetime.datetime.now()
            upDate  = now.strftime('%Y-%m-%d')
            upUser  = USER_NAME
 
            avgVal, maxVal, minValue = findStat(fullname) # 평균 최대, 최소
 
            sql = "INSERT INTO rawImage_TBL2(raw_id , raw_fname,raw_extname,"
            sql += "raw_height,raw_width,raw_avg,raw_max,raw_min,raw_data) "
            sql += " VALUES(NULL,'" + fname + "','" + extname +"',"
            sql += str(height) + "," + str(width) + ","
            sql += str(avgVal)+ "," + str(maxVal) + "," + str(minValue)
            sql += ", %s )"
            tupleData = (binData,)   # place holder에 넣을 인자들을 모두
 
            cur.execute(sql, tupleData)
 
            con.commit()
    cur.close()
    con.close()
 
 
import tempfile
 
def downloadData():
    con = pymysql.connect(host=IP_ADDR, user = USER_NAME, password = USER_PW, db = DB_NAME, charset = CHAR_SET)
    cur = con.cursor()
 
    sql = "select raw_fname, raw_data from rawImage_tbl where id = 1"
 
    cur.execute(sql)
    fname, binData = cur.fetchone()
 
    fullPath = tempfile.gettempdir() + "/" + fname
    with open(fullPath, 'wb') as wfp:
        wfp.write(binData)
    print(fullPath)
    cur.close()
    con.close()
 
 
#메인 코드부
window = Tk()
 
window.geometry("500x500")
window.title("raw --> DB Ver 0.0.2")
 
edt1      = Entry (window, width = 50);
btnFile   = Button(window, text = "file select", command = selectFile)
btnDir    = Button(window, text = "folder select", command = selectfolder)
btnUpload = Button(window, text = "file upload", command = uploadData)
btnUpload = Button(window, text = "folder upload", command = uploadDataFromFolder)
 
btnDownload = Button(window, text = "download", command = downloadData)
 
edt1.pack()
btnFile.pack()
btnDir.pack()
btnUpload.pack()
btnDownload.pack()
 
window.mainloop()