[Python] 22. [DBMS] Sqlite3 + Python 연동 실습, 140자 일기장 만들기

01. Sqlite3 + Python 연동 실습
1. SQL

▷ /sqlite3/diary140.sql
-------------------------------------------------------------------------------------

  CREATE TABLE diary (
    diary_id INTEGER PRIMARY KEY AUTOINCREMENT, 
    createdate DATETIME, 
    note CHAR(140)
  );

  CREATE TABLE diary_img (
    img_id INTEGER PRIMARY KEY AUTOINCREMENT, 
    img BLOB, 
    diary_id INTEGER, 
    FOREIGN KEY(diary_id) REFERENCES diary(diary_id)
  );


SELECT diary_id, createdate, note FROM diary;

DELETE FROM diary
WHERE diary_id = 5;

SELECT img_id, img, diary_id FROM diary_img;
SELECT img_id, diary_id FROM diary_img;

-- LEFT OUTER JOIN은 왼쪽 테이블의 것은 조건에 부합하지 않더라도 모두 결합되어야 함
SELECT D.DIARY_ID, D.CREATEDATE, D.NOTE, I.IMG 
FROM DIARY AS D LEFT OUTER JOIN DIARY_IMG AS I 
ON D.DIARY_ID = I.DIARY_ID 
ORDER BY D.CREATEDATE DESC
LIMIT 1 OFFSET 0;
            
             
           

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

 

 

2. database 및 테이블 생성 

 

▷ /sqlite3/create_db.py
-------------------------------------------------------------------------------------
# -*- coding: utf-8 -*-

import sqlite3

conn = sqlite3.connect('python.db')
cursor = conn.cursor()

cursor.execute("""
  CREATE TABLE diary (
  diary_id INTEGER PRIMARY KEY AUTOINCREMENT, 
  createdate DATETIME, 
  note CHAR(140))
""")

cursor.execute("""
  CREATE TABLE diary_img (
  img_id INTEGER PRIMARY KEY AUTOINCREMENT, 
  img BLOB, 
  diary_id INTEGER, 
  FOREIGN KEY(diary_id) REFERENCES diary(diary_id))
""")

cursor.execute("SELECT name FROM sqlite_master")
for row in cursor:
    print ("TABLE = ", row[0])
   
cursor.close()
conn.close()


-------------------------------------------------------------------------------------​

 

3. Python script
▷ /sqlite3/Diary140.py
-------------------------------------------------------------------------------------
# -*- coding: utf-8 -*-

from datetime import datetime
import sqlite3
import wx 
import wx.html2 # HTML Window
import ntpath    # FILE NAME Extraction
import base64    # IMAGE URI Encoding

RECORD_PER_PAGE = 5 # 한페이지당 출력할 레코드 갯수

def sum():
    pass

class MainFrame (wx.Frame):
    def __init__(self):
        wx.Frame.__init__ (self, 
                           None, 
                           title = "140자 일기장", 
                           size = wx.Size(815,655), 
                           style = wx.DEFAULT_FRAME_STYLE)
        
        self.MaxImageSize = 300 # 미리보기 이미지의 크기 200 X 200
        
        self.mainPanel = wx.Panel(self) # 화면 분할하여 Widget의 배치
        
        # 일기 입력 창
        self.leftPanel = wx.Panel(self.mainPanel)
        self.leftPanel.SetMaxSize(wx.Size(300,-1))        
        self.input_image_path = ""
        self.inputTextCtrl = wx.TextCtrl(  # 일기 내용 입력
            self.leftPanel, size=wx.Size(300,100), style=wx.TE_MULTILINE)  # 여러라인 지원

        self.inputTextCtrl.SetMaxLength(140) # 최대 글자수 140                                      # 최대 입력 글자
        self.inputTextCtrl.Bind(wx.EVT_TEXT, self.OnTypeText) # 입력 발생 이벤트 처리
        # 글자수 출력 레이블 생성
        self.lengthStaticText = wx.StaticText(self.leftPanel, style=wx.ALIGN_RIGHT)
        self.selectImageButton = wx.Button(self.leftPanel, label="이미지 추가")
        # 버튼 클릭시 OnFindImageFile 메소드 실행
        self.selectImageButton.Bind(wx.EVT_BUTTON, self.OnFindImageFile)
        self.imageStaticBitmap = wx.StaticBitmap(self.leftPanel)  # 이미지 미리보기 객체 선언       
        
        # 버튼 클릭시 OnInputButton 메소드 실행
        self.inputButton = wx.Button(self.leftPanel, label="저장")
        self.inputButton.Bind(wx.EVT_BUTTON, self.OnInputButton)
        
        # 일기 표시 창
        self.rightPanel = wx.Panel(self.mainPanel)
        self.outputHtmlWnd = wx.html2.WebView.New(self.rightPanel) # 일기 내용을 출력할 WebView 객체 생성
        
        # Python + HTML 이벤트 연동
        self.outputHtmlWnd.Bind(wx.html2.EVT_WEBVIEW_NAVIGATING, self.OnNavigating) 

        # 위젯 배치        
        leftPanelSizer = wx.StaticBoxSizer(
            wx.VERTICAL, self.leftPanel, "글 남기기")
        leftPanelSizer.Add(self.inputTextCtrl, 0, wx.ALL, 5)
        leftPanelSizer.Add(self.lengthStaticText, 0, wx.ALIGN_RIGHT|wx.RIGHT, 5)
        leftPanelSizer.Add(self.selectImageButton, 0, wx.ALIGN_RIGHT|wx.RIGHT, 5)
        leftPanelSizer.Add(self.imageStaticBitmap, 0, wx.ALIGN_RIGHT|wx.ALL, 5)
        leftPanelSizer.Add(self.inputButton, 0, wx.ALIGN_RIGHT|wx.RIGHT, 5)
        self.leftPanel.SetSizer(leftPanelSizer)
        
        htmlWndSizer = wx.GridSizer(1, 1, 0, 0)
        htmlWndSizer.Add(self.outputHtmlWnd, 0, wx.ALL|wx.EXPAND, 5)

        self.rightPanel.SetSizer(htmlWndSizer)
        self.rightPanel.Layout()
        htmlWndSizer.Fit(self.rightPanel)
        
        mainSizer = wx.BoxSizer(wx.HORIZONTAL)
        mainSizer.Add(self.leftPanel, 1, wx.ALIGN_RIGHT|wx.ALL|wx.EXPAND, 5)
        mainSizer.Add(self.rightPanel, 1, wx.ALL|wx.EXPAND , 5)
        self.mainPanel.SetSizer(mainSizer)
        self.Layout()

        # Database 초기화
        self.conn = sqlite3.connect("python.db")
        self.cursor = self.conn.cursor() # SQL 실행 및 결과 수신
        self.CheckSchema()
        self.LoadDiary(0)
                
    def CheckSchema(self):
        self.cursor.execute("""
        CREATE TABLE IF NOT EXISTS DIARY (
        DIARY_ID INTEGER PRIMARY KEY AUTOINCREMENT, 
        CREATEDATE DATETIME, 
        NOTE CHAR(140))
        """)

        self.cursor.execute("""
        CREATE TABLE IF NOT EXISTS DIARY_IMG (
        IMG_ID INTEGER PRIMARY KEY AUTOINCREMENT, 
        IMG BLOB, 
        DIARY_ID INTEGER, 
        FOREIGN KEY(DIARY_ID) REFERENCES DIARY(DIARY_ID))
        """)

    def OnTypeText(self, event):
        self.lengthStaticText.SetLabel(
            "현재 글자 수 : {0}".format(len(self.inputTextCtrl.GetValue())))
        self.leftPanel.Layout()   # lengthStaticText 크기 변경에 따라 레이아웃 재출력

    def OnFindImageFile(self, event):
        openFileDialog = wx.FileDialog(
            self, "Open", 
            wildcard="Image files (*.png, *.jpg, *.gif)|*.png;*.jpg;*.jpeg;*.gif",
            style=wx.FD_OPEN | wx.FD_FILE_MUST_EXIST)  # FileDialog 객체 생성

        if openFileDialog.ShowModal() == wx.ID_OK:
            # 선택한 이미지 경로 추출
            self.input_image_path = openFileDialog.GetPath()
            
            # 비율을 유지하면서 이미지 크기를 윈도우에 맞추기
            img = wx.Image(self.input_image_path, wx.BITMAP_TYPE_ANY) # 이미지 열기
            Width = img.GetWidth()
            Height = img.GetHeight()
            if Width > Height:
                NewWidth = self.MaxImageSize
                NewHeight = self.MaxImageSize * Height / Width
            else:
                NewWidth = self.MaxImageSize
                NewHeight = self.MaxImageSize * Width / Height
            img = img.Scale(NewWidth, NewHeight)  # 이미지 객체의 크기 조절
            self.imageStaticBitmap.SetBitmap(wx.Bitmap(img)) # 크기가 변경된 이미지 적용
            self.leftPanel.Layout()
            self.leftPanel.Refresh()
        
        openFileDialog.Destroy()

    # 저장 
    def OnInputButton(self, event):
        # 선택한 파일명 가져오기
        fileName = ntpath.basename(self.input_image_path)
        # 문자열만 저장
        self.cursor.execute(
            "INSERT INTO DIARY (DIARY_ID, CREATEDATE, NOTE) VALUES(NULL, ?, ? )", 
            (str(datetime.now()), self.inputTextCtrl.GetValue()))
        
        # 일련번호 PK 추출
        diary_id = self.cursor.lastrowid
        # print('diary_id: ' + str(diary_id));  # AUTOINCREMENT의 값과 동일

        if self.input_image_path.strip() != "": # 이미지가 존재한다면
            # 파일을 DBMS에 저장
            self.cursor.execute(
                # NULL: AUTOINCREMENT가 선언된 컬럼에 명시
                "INSERT INTO DIARY_IMG (IMG_ID, IMG, DIARY_ID) VALUES(NULL, ?, ?)",
                (sqlite3.Binary(open(self.input_image_path,"rb").read()), diary_id))
        
        self.conn.commit() # 적용

        wx.MessageBox("저장되었습니다.", "140자 일기장", wx.OK) 

        self.inputTextCtrl.SetValue("")     # 위젯 초기화   
        self.input_image_path = ""
        self.imageStaticBitmap.SetBitmap(wx.Bitmap(0,0))

        self.leftPanel.Layout()
        self.leftPanel.Refresh()
        self.LoadDiary(0)  # 신규 등록후 일기 출력

    def LoadDiary(self, page):
        self.cursor.execute(
            "SELECT D.DIARY_ID, D.CREATEDATE, D.NOTE, I.IMG FROM DIARY "
            + "AS D LEFT OUTER JOIN DIARY_IMG AS I "
            + "ON D.DIARY_ID = I.DIARY_ID ORDER BY D.CREATEDATE DESC "
            + "LIMIT {0} OFFSET {1}"
            .format(RECORD_PER_PAGE, page*RECORD_PER_PAGE))

        html = """
            <html>
            <head>
            </head><body>{0}</body></html>
            """
        diary_id = 0
        body = ""
        for row in self.cursor: # 하나의 레코드씩 추출
            # print(row); # tuple
            diary_id = int(row[0]);  # 첫번재 컬럼의 값 산출
            imgTag = ""
            if row[3] != None: # 이미지가 존재한다면
                imgTag = """<img src='data:image/png;base64,{0}' 
                style='width:400px; height:auto;' align=center>
                """.format(
                    base64.b64encode(row[3]).decode("ascii")) # Sqlite3에서 이미지 추출
            
            noteBR = row[2].replace('\n', '<br>')  # Enter를 <br> 태그로 변경
            
            content = """<a name="neural">
                <p style="word-wrap:break-word;font-size=12px;">
                <font size=2><b><i>
                {1}
                </i></b>
                <a href="del:{0}">[삭제]</a></font><br> 
                {2}
                <br>
                {3}
                """.format(diary_id, row[1], noteBR, imgTag) # 날짜, 삭제 링크, 내용, 이미지 출력
            body += content

        pageNavigation = "<p align='center' style='font-size=12px'>"
        
        # Prev 버튼(링크)
        if page > 0:
            pageNavigation += "<a href='nav:{0}'>Prev</a>".format(page - 1)
        
        pageNavigation += " "

        # Next 버튼(링크)
        self.cursor.execute(
            "SELECT count(*) FROM DIARY WHERE DIARY_ID<{0}".format(diary_id))
        row = self.cursor.fetchone() 
        
        if row != None:
            nextRowCount = row[0]
            if nextRowCount > 0:
                pageNavigation += "<a href='nav:{0}'>Next</a>".format(page + 1)

        pageNavigation += "</p>"
        body += pageNavigation

        self.outputHtmlWnd.SetPage(html.format(body), "") # 최종적인 HTML 출력

    def OnNavigating(self, event):
        if event.URL.startswith("del:") == True:
            diary_id = event.URL.rpartition(":")[-1]  # url의 뒤에서 첫번재 문자 추출
            self.cursor.execute(
                "DELETE FROM DIARY WHERE DIARY_ID={0}".format(diary_id))
            self.cursor.execute(
                "DELETE FROM DIARY_IMG WHERE DIARY_ID={0}".format(diary_id))
            self.conn.commit()
            self.LoadDiary(0)
            wx.MessageBox("삭제했습니다.", "140자 일기장", wx.OK)
        elif event.URL.startswith("nav:") == True:
            page = event.URL.rpartition(":")[-1]
            self.LoadDiary(int(page))
        
        event.Skip(False) # WebView 위젯이 해당 이벤트를 받아서 해당 URL로 이동하려는 것을 막기 위함.

if __name__ == "__main__":
    app = wx.App() # wxPython을 사용하기위한 메인 객체 생성
    frame = MainFrame() # 객체 생성, Widget 생성, Event 등록 DBMS 연결
    frame.Show()

    app.MainLoop()  # Widget 이벤트 수신 시작
    
    
       
-------------------------------------------------------------------------------------