|
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