[인공지능]SQLite3를 활용하여 전화번호시스템 구현하기

[최현]

Main

'''
Created on 2021. 7. 22.

@author: pc358
'''
###########################################################################
## Python code generated with wxFormBuilder (version Jun 17 2015)
## http://www.wxformbuilder.org/
##
## PLEASE DO "NOT" EDIT THIS FILE!
###########################################################################

import wx
import wx.xrc

###########################################################################
## Class MyFrame2
###########################################################################

from sqlite_ex2 import crud
import sqlite_ex2

class MyFrame2 ( wx.Frame ):
    
    def __init__( self, parent ):
        wx.Frame.__init__ ( self, parent, id = wx.ID_ANY, title = u"전화번호부", pos = wx.DefaultPosition, size = wx.Size( 518,326 ), style = wx.DEFAULT_FRAME_STYLE|wx.TAB_TRAVERSAL )
        
        self.SetSizeHintsSz( wx.DefaultSize, wx.DefaultSize )
        self.SetForegroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_WINDOWTEXT ) )
        self.SetBackgroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_ACTIVECAPTION ) )
        
        bSizer8 = wx.BoxSizer( wx.VERTICAL )
        
        bSizer10 = wx.BoxSizer( wx.VERTICAL )
        
        gSizer5 = wx.GridSizer( 0, 10, 0, 0 )
        
        self.m_staticText1 = wx.StaticText( self, wx.ID_ANY, u"성", wx.DefaultPosition, wx.DefaultSize, 0 )
        self.m_staticText1.Wrap( -1 )
        gSizer5.Add( self.m_staticText1, 0, wx.ALL, 5 )
        
        self.txt_LastName = wx.TextCtrl( self, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.DefaultSize, 0 )
        self.txt_LastName.SetBackgroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_INFOBK ) )
        
        gSizer5.Add( self.txt_LastName, 0, wx.ALL, 5 )
        
        
        bSizer10.Add( gSizer5, 1, wx.EXPAND, 3 )
        
        gSizer51 = wx.GridSizer( 0, 10, 0, 0 )
        
        self.m_staticText11 = wx.StaticText( self, wx.ID_ANY, u"이름", wx.DefaultPosition, wx.DefaultSize, 0 )
        self.m_staticText11.Wrap( -1 )
        gSizer51.Add( self.m_staticText11, 0, wx.ALL, 5 )
        
        self.txt_FirstName = wx.TextCtrl( self, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.DefaultSize, 0 )
        self.txt_FirstName.SetBackgroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_INFOBK ) )
        
        gSizer51.Add( self.txt_FirstName, 0, wx.ALL, 5 )
        
        
        bSizer10.Add( gSizer51, 1, wx.EXPAND, 5 )
        
        gSizer511 = wx.GridSizer( 0, 10, 0, 0 )
        
        self.m_staticText111 = wx.StaticText( self, wx.ID_ANY, u"번호", wx.DefaultPosition, wx.DefaultSize, 0 )
        self.m_staticText111.Wrap( -1 )
        gSizer511.Add( self.m_staticText111, 0, wx.ALL, 5 )
        
        self.txt_PhoneNumber = wx.TextCtrl( self, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.DefaultSize, 0 )
        self.txt_PhoneNumber.SetBackgroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_INFOBK ) )
        self.txt_PhoneNumber.SetMinSize( wx.Size( 200,-1 ) )
        
        gSizer511.Add( self.txt_PhoneNumber, 0, wx.ALL, 5 )
        
        
        bSizer10.Add( gSizer511, 1, wx.EXPAND, 5 )
        
        gSizer5111 = wx.GridSizer( 0, 10, 0, 0 )
        
        self.m_staticText1111 = wx.StaticText( self, wx.ID_ANY, u"직장", wx.DefaultPosition, wx.DefaultSize, 0 )
        self.m_staticText1111.Wrap( -1 )
        gSizer5111.Add( self.m_staticText1111, 0, wx.ALL, 5 )
        
        self.txt_Job = wx.TextCtrl( self, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.Size( 430,-1 ), 0 )
        self.txt_Job.SetBackgroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_INFOBK ) )
        
        gSizer5111.Add( self.txt_Job, 0, wx.ALL, 5 )
        
        
        bSizer10.Add( gSizer5111, 1, wx.EXPAND, 5 )
        
        
        bSizer8.Add( bSizer10, 1, wx.EXPAND, 5 )
        
        bSizer3 = wx.BoxSizer( wx.HORIZONTAL )
        
        self.m_button1 = wx.Button( self, wx.ID_ANY, u"Insert", wx.DefaultPosition, wx.DefaultSize, 0|wx.NO_BORDER )
        self.m_button1.SetFont( wx.Font( wx.NORMAL_FONT.GetPointSize(), 70, 90, 90, False, wx.EmptyString ) )
        self.m_button1.SetForegroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_HIGHLIGHTTEXT ) )
        self.m_button1.SetBackgroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_WINDOWFRAME ) )
        
        bSizer3.Add( self.m_button1, 0, wx.ALL, 5 )
        
        self.m_button2 = wx.Button( self, wx.ID_ANY, u"Delete", wx.DefaultPosition, wx.DefaultSize, 0|wx.NO_BORDER )
        self.m_button2.SetForegroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_HIGHLIGHTTEXT ) )
        self.m_button2.SetBackgroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_WINDOWFRAME ) )
        
        bSizer3.Add( self.m_button2, 0, wx.ALL, 5 )
        
        self.m_button3 = wx.Button( self, wx.ID_ANY, u"Update", wx.DefaultPosition, wx.DefaultSize, 0|wx.NO_BORDER )
        self.m_button3.SetForegroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_HIGHLIGHTTEXT ) )
        self.m_button3.SetBackgroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_WINDOWFRAME ) )
        
        bSizer3.Add( self.m_button3, 0, wx.ALL, 5 )
        
        self.m_button4 = wx.Button( self, wx.ID_ANY, u"Find", wx.DefaultPosition, wx.DefaultSize, 0|wx.NO_BORDER )
        self.m_button4.SetForegroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_HIGHLIGHTTEXT ) )
        self.m_button4.SetBackgroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_WINDOWFRAME ) )
        
        bSizer3.Add( self.m_button4, 0, wx.ALL, 5 )
        
        self.m_button5 = wx.Button( self, wx.ID_ANY, u"SeletAll", wx.DefaultPosition, wx.DefaultSize, 0|wx.NO_BORDER )
        self.m_button5.SetForegroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_HIGHLIGHTTEXT ) )
        self.m_button5.SetBackgroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_WINDOWFRAME ) )
        
        bSizer3.Add( self.m_button5, 0, wx.ALL, 5 )
        
        
        bSizer8.Add( bSizer3, 1, wx.ALIGN_CENTER, 5 )
        
        self.resultArea = wx.TextCtrl( self, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.Size( 500,100 ), wx.HSCROLL|wx.TE_MULTILINE )
        self.resultArea.SetBackgroundColour( wx.Colour( 255, 255, 187 ) )
        
        bSizer8.Add( self.resultArea, 0, wx.ALL, 5 )
        
        
        self.SetSizer( bSizer8 )
        self.Layout()
        
        self.Centre( wx.BOTH )
        
        # Connect Events
        self.m_button1.Bind( wx.EVT_BUTTON, self.OnInsert )
        self.m_button2.Bind( wx.EVT_BUTTON, self.OnDelete )
        self.m_button3.Bind( wx.EVT_BUTTON, self.OnUpdate )
        self.m_button4.Bind( wx.EVT_BUTTON, self.OnFind )
        self.m_button5.Bind( wx.EVT_BUTTON, self.xxxxOnSelectAll )
    
    def __del__( self ):
        pass
    
    
    # Virtual event handlers, overide them in your derived class
    def OnInsert( self, event ):
        last_Name = self.txt_LastName.GetValue()
        first_Name = self.txt_FirstName.GetValue()
        phone_Number = self.txt_PhoneNumber.GetValue()
        job = self.txt_Job.GetValue()
        try:
            crud.insertData(last_Name, first_Name, phone_Number, job)
        except:
            print('예외발생!')
        finally:
            print('입력작업종료')
        print('자료입력 완료')
        event.Skip()
    
    def OnDelete( self, event ):
        last_Name = self.txt_LastName.GetValue()
        try:
            crud.delete(last_Name)
        except:
            print('')
        finally:
            print('삭제성공')
        
        event.Skip()
    
    def OnUpdate( self, event ):
        last_Name = self.txt_LastName.GetValue()
        first_Name = self.txt_FirstName.GetValue()
        phone_Number = self.txt_PhoneNumber.GetValue()
        job = self.txt_Job.GetValue()
        try:
            crud.update((last_Name, first_Name, phone_Number, job))
        except:
            print('예외발생')
        finally:
            print('입력작업종료')
        event.Skip()
    
    def OnFind( self, event ):
        key = self.txt_LastName.GetValue()
        row = crud.select(key)
        self.txt_LastName.SetValue(row[0])
        self.txt_FirstName.SetValue(row[1])
        self.txt_PhoneNumber.SetValue(row[2])
        self.txt_Job.SetValue(row[3])
        event.Skip()
    
    def xxxxOnSelectAll( self, event ):
        rows = crud.selectAll()
        for row in rows:
            self.resultArea.AppendText('{},{},{},{}\n'
                                       .format(row[0],row[1],row[2],row[3]))
        event.Skip()
        
    
    
if __name__ == '__main__':
    app = wx.App()
    frame = MyFrame2(None)
    frame.Show()
    app.MainLoop()
    

CRUD

'''
Created on 2021. 7. 22.

@author: pc358
'''

import sqlite3

################# C ############################
def createTable():
    conn = sqlite3.connect('phonenumberlist.db') #isolation_level = None 이거 안해줘서 커밋해줘야함
    c = conn.cursor()
    c.execute('''
        CREATE TABLE IF NOT EXISTS PHONELIST
        (LASTNAME TEXT PRIMARY KEY,
         FIRSTNAME TEXT,
         PHONENUMBER TEXT,
         JOB TEXT)
    ''')
    conn.commit()
    c.close()
    conn.close()
    pass

def insertData(last_Name, first_Name, phone_Number, job): #매개변수 필요
    conn = sqlite3.connect('phonenumberlist.db')
    c = conn.cursor()
    c.execute('''
        INSERT INTO PHONELIST(LAST_NAME, FIRST_NAME, PHONE_NUMBER, JOB)
        VALUES(?, ?, ?, ?) 
    ''',(last_Name, first_Name, phone_Number, job) )
    conn.commit()
    c.close()
    conn.close()
    pass

def insertManyData(tupleData):
    conn = sqlite3.connect('phonenumberlist.db')
    c = conn.cursor()
    c.executemany('''
        INSERT INTO PHONELIST(LAST_NAME, FIRST_NAME, PHONE_NUMBER, JOB)
        VALUES(?,?,?,?)''',tupleData)
    conn.commit()
    c.close()
    conn.close()
    pass
    
################# R - READ(다른 말로 SELECT) ############################   

def selectAll():
    conn = sqlite3.connect("phonenumberlist.db")
    c = conn.cursor()
    c.execute('SELECT * FROM PHONELIST') #파이썬은 전부 execute로 함, 자바처럼 구분을 안한당~
    rows = c.fetchall()
    conn.commit()
    c.close()
    conn.close()
    return rows
    pass
    #1방식 - 여러 줄로 찍음
    # print('---1방식 출력---')
    # readData = c.fetchall()
    # for row in readData:
    #     print(row)
    #
    # #2방식
    # print('---2방식 출력---')
    # for row in c.execute('SELECT * FROM MYMEMBER'):
    #     print(row)
    #
    # #3방식 - 한 줄로 찍음
    # print('---3방식 출력---')
    # c.execute('SELECT * FROM MYMEMBER')
    # print(c.fetchall())

def select(key):
    conn = sqlite3.connect('phonenumberlist.db')
    c = conn.cursor()
    c.execute('SELECT * FROM PHONELIST WHERE LAST_NAME = ?',(key,)) #유의! 튜플형태로 받쳐줘라 (콤마까지 해줘야함)
    print(c.fetchone())
    
    conn.commit()
    c.close()
    conn.close()
    pass

################# U - UPDATE ############################
def update(vo):
    conn = sqlite3.connect('phonenumberlist.db')
    c = conn.cursor()
    c.execute('''
        UPDATE PHONELIST SET FIRST_NAME=?,PHONE_NUMBER=?,JOB=? WHERE LAST_NAME=?
    ''',vo)
    
    conn.commit()
    c.close()
    conn.close()
    pass

def delete(key):
    conn = sqlite3.connect('phonenumberlist.db') #반환타입이 있는 메소드
    c = conn.cursor()
    #
    res = c.execute('''DELETE FROM PHONELIST WHERE LAST_NAME=?''',(key,))
    conn.commit()
    c.close()
    conn.close()
    return len(list(res)) #삭제된 것이 있다면 리스트에 만들어진 개수만큼 리턴시킴
    pass

본 화면

Insert 구현 - 1

Insert 구현 - 2

Delete 구현

Update 구현

Find 구현

SelectAll 구현