13. 원격(remote) db연동 – MariaDB

１.    import MySQLdb 로딩

 

２.    연결 conn = MySQLdb.connect(연결정보)

 

(1)     연결정보 직접 입력
conn = MySQLdb.connect(host = '127.0.0.1', user = 'db_user_name', password='db_password', database='database_name')

 

(2)     연결정보 dict형으로 따로 변수 선언

１)       변수선언: config = { 'host':'localhost'/'127.0.0.1', 'user':'db_user_name', 'password':'db_password', 'database':'database_name',                                     'port':port 번호, 'charset':'utf8', 'use_unicode':True }

２)       conn = MySQLdb.connect(**config)

①        connection은 dict타입을 맵핑

②        try블록에 선언

 

import MySQLdb

config = {'host':'localhost'/'127.0.0.1', 
		'user':'db_user_name', 
		'password':'db_password', 
		'database':'database_name',                                     
		'port':'port 번호', 
		'charset':'utf8', 
		'use_unicode':True }

try:
    conn = MySQLdb.connect(**config)
    cursor = conn.cursor()
    
    # sql문 실행 명령
    
except Exception as e:
    print('err: ' + str(e))

 

(3)     연결정보를 외부 파일에 저장 – 권장사항(보안)

１)       파일 읽기 활용 – open()

２)       형식
import ast: 연결정보 저장파일에 저장된 문자열을 ast모듈을 이용하여 dict타입으로 변환
with open('연결정보 저장파일명.확장자', mode='r') as 변수명1:
    변수명2 = 변수명1.read()
    변수명3 = ast.literal_eval(변수명2)
conn = MySQLdb.connect(**config)

import MySQLdb
import ast # mariadb.txt에 저장된 문자열을 ast모듈을 이용하여 dict타입으로 변환

with open('mariadb.txt', mode='r') as f:
	# mariadb.txt에 연결정보 저장
    aa = f.read()
    config = ast.literal_eval(aa) # mariadb.txt에 저장된 문자열을 ast모듈을 이용하여 dict타입으로 변환
    print(config)
    
try:
    conn = MySQLdb.connect(**config)
    cursor = conn.cursor()

    # sql문 실행 명령
    
except Exception as e:
    print('err: ' + str(e))

<mariadb.txt>

{'host':'localhost'/'127.0.0.1', 
'user':'db_user_name', 
'password':'db_password', 
'database':'database_name',                                     
'port':'port 번호', 
'charset':'utf8', 
'use_unicode':True }

 

３.    sql문 실행

(1)     sql문 처리할 커서 생성: cursor = conn.cursor()

(2)     sql문장 실행: cursor.execute(sql문, [입력 값 저장 변수명])

１)       db의 자료를 읽어서 cursor 객체가 그 결과를 기억

２)       램(local)에만 저장

３)       실제 원격db는 저장 안 됨

４)       insert, update, delete는 성공하면 1, 실패하면 0을 반환

(3)     sql문장 실행: cursor.executemany(sql문, [입력 값 저장 변수명])

１)       입력할 데이터가 2개 이상일 때 사용

data = [('mouse','sam',12.5,6000),('keyboard','lg',502.0,86000)] 
# 리스트안에 튜플로 한 세트씩 입력
stmt = "insert into test values(?, ?, ?, ?)"
conn.executemany(stmt, data) # 한번에 값 많이 넣을 때​

(4)     실제 원격db는 저장: conn.commit()

(5)     insert, delete, update는 내 컴퓨터 ram에만 저장됨. 마스터 데이터 베이스에 주려면 commit을 반드시 해줘야 함.

(6)     cursor.rowcount: 결과 행수를 알아보기

(7)     cursor.description: 각 필드 특징 알아보기

１)       (컬럼명, 데이터형 코드, 표시크기, 내부크기, 정확도, 비율, nullable): Tuple type

２)       컬럼 개수만큼 튜플 요소로 반환

３)       컬럼명을 모를 때 자주 사용

print(cursor.description)
print(type(cursor.description)) # 튜플안에 요소들도 튜플
print(*cursor.description) # 요소들 ​

실행결과

 

４.    sql문장 – 권장사항

(1)     %s를 사용하여 입력 받을 값을 별도 변수에 저장하여 관리 - %s가 여러 개일 때는 순서대로 값 입력

(2)     “~ '{0}'”.format(변수명|값)을 사용

１)       { }에 반드시 ‘ ‘ 작은 따옴표로 감싸주기

２)       키보드로 입력 받은 값을 변수에 저장할 때 사용

(3)     sql문장에 + 연산자 사용은 secure coding 가이드라인에 위배 – 비 권장 방법

 

５.    select문 수행 시

(1)     데이터가 복수 개 일 때 cursor.fetchall() 메소드로 읽어 옴

１)       for문으로 데이터 출력

２)       tuple형으로 반환

(2)     데이터가 한 개일 때 cursor.fetchone() 메소드로 읽어 옴 - tuple형으로 반환

 

６.    try ~ except ~ finally~ 사용

 

７.    생성한 자원 conn, cursor는 반드시 close()로 자원반납 해야 한다. finally블록에 선언

 

예제 1) 

import MySQLdb

#위의 conn을 dict타입으로 만든 것 
config = {'host':'localhost'/'127.0.0.1', 
		'user':'db_user_name', 
		'password':'db_password', 
		'database':'database_name',                                     
		'port':'port 번호', 
		'charset':'utf8', 
		'use_unicode':True }

try:
    conn = MySQLdb.connect(**config) #connection은 dict타입을  맵핑 
    cursor = conn.cursor() #sql문 처리용
    
    #자료 추가1-----------------------------------------------------
    sql = "insert into sangdata(code, sang, su, dan) values(10, '새우깡', 5, 1200)" #문자와 날짜는 반드시 작은따옴표, 숫자는 마음대로
    cursor.execute(sql) # db의 자료를 읽어서 cursor 객체가 그 결과를 기억, 램(local)에만 저장되었음, 실제 원격db는 저장안됨
    conn.commit() #커밋을 해야 원격db에도 저장됨
    #insert, delete, update는 내 컴퓨터 ram에만 저장됨. 마스터 데이터 베이스에 주려면 commit을 반드시 해줘야 함.
    
    #자료추가2 - 권장사항
    sql = "insert into sangdata values(%s,%s,%s,%s)"
    sql_data = '11', '감자깡', 10, 2000 #튜플타입이라서 괄호('11', '감자깡', 10, 2000)를 빼도 됨, 테이블의 컬럼 순서대로 써줘야 함
    #cursor.execute(sql, sql_data)
    #print(cursor) #<MySQLdb.cursors.Cursor object at 0x000001C4A8D1B4C0>
    cou = cursor.execute(sql, sql_data) #insert 성공하면 1, 실패하면 0을 반환
    print('성공건수: ',cou)
    conn.commit()
    
    #수정--------------------------------------------------------
    sql = "update sangdata set sang=%s, su=%s, dan=%s where code=%s"
    sql_data = '파래깡', 7, 2200, 10 # %s순서대로
    #cursor.execute(sql, sql_data)
    cou = cursor.execute(sql, sql_data) #update 성공하면 1, 실패하면 0을 반환
    print('성공건수: ',cou)
    conn.commit()
    
    #자료삭제------------------------------------------------------
    code = '11'
    #sql = "delete from sangdata where code = " + code #secure coding 가이드라인에 위배: 문자열을 +연산 사용 비권장
    cou = cursor.execute(sql) #delete 성공하면 1, 실패하면 0을 반환
    print('성공건수: ',cou)
    
    #권장방법1
    sql = "delete from sangdata where code = %s"
    cursor.execute(sql,(code,))
    
    #권장방법2
    sql = "delete from sangdata where code = '{0}'".format(code)
    cursor.execute(sql)
    conn.commit()
    
    #자료읽기------------------------------------------------------
    sql = "select code, sang, su, dan from sangdata"
    cursor.execute(sql) # db의 자료를 읽어서 cursor 객체가 그 결과를 기억
    
    print('읽기1')
    for data in cursor.fetchall():
        #print(data) #튜플로 읽힘
        print('%s %s %s %s'%data)
    
    print('읽기2')   
    for r in cursor:
        print(r[0], r[1], r[2], r[3]) 
    
    print('읽기3-1') #(code, sang, su, dan): 컬럼명 아니고 그냥 변수임.
    for (code, sang, su, dan) in cursor:
        print(code, sang, su, dan)
    
    print('읽기3-2')    
    for (a, b, 수량, 단가) in cursor:
        print(a, b, 수량, 단가)    
    
except MySQLdb.connections.Error as err:
    print('db err: ' + str(err))
    conn.rollback()

except Exception as e:
    print('err: ' + str(e))

finally:
    cursor.close()
    conn.close()​

 

예제2)

import wx
import wx.xrc
import MySQLdb
import sys
import ast # mariadb.txt에 저장된 문자열을 ast모듈을 이용하여 dict타입으로 변환

with open('mariadb.txt', mode='r') as f:
    aa = f.read()
    config = ast.literal_eval(aa) # mariadb.txt에 저장된 문자열을 ast모듈을 이용하여 dict타입으로 변환
    print(config)
    
class MyGogek ( wx.Frame ):
    
    def __init__( self, parent ):
        wx.Frame.__init__ ( self, parent, id = wx.ID_ANY, title = u"고객자료보기", pos = wx.DefaultPosition, size = wx.Size( 525,483 ), style = wx.DEFAULT_FRAME_STYLE|wx.TAB_TRAVERSAL )
        
        #self.SetSizeHintsSz( wx.DefaultSize, wx.DefaultSize )
        self.SetBackgroundColour( wx.SystemSettings.GetColour( wx.SYS_COLOUR_MENU ) )
        
        bSizer1 = wx.BoxSizer( wx.VERTICAL )
        
        self.m_panel1 = wx.Panel( self, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.TAB_TRAVERSAL )
        bSizer2 = wx.BoxSizer( wx.HORIZONTAL )
        
        self.m_staticText1 = wx.StaticText( self.m_panel1, wx.ID_ANY, u"사번:", wx.DefaultPosition, wx.DefaultSize, 0 )
        self.m_staticText1.Wrap( -1 )
        bSizer2.Add( self.m_staticText1, 0, wx.ALL, 5 )
        
        self.txtNo = wx.TextCtrl( self.m_panel1, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.DefaultSize, 0 )
        bSizer2.Add( self.txtNo, 0, wx.ALL, 5 )
        
        self.m_staticText2 = wx.StaticText( self.m_panel1, wx.ID_ANY, u"직원명:", wx.DefaultPosition, wx.DefaultSize, 0 )
        self.m_staticText2.Wrap( -1 )
        bSizer2.Add( self.m_staticText2, 0, wx.ALL, 5 )
        
        self.txtName = wx.TextCtrl( self.m_panel1, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.DefaultSize, 0 )
        bSizer2.Add( self.txtName, 0, wx.ALL, 5 )
        
        self.btnLogin = wx.Button( self.m_panel1, wx.ID_ANY, u"로그인", wx.DefaultPosition, wx.DefaultSize, 0 )
        bSizer2.Add( self.btnLogin, 0, wx.ALL, 5 )
        
        
        self.m_panel1.SetSizer( bSizer2 )
        self.m_panel1.Layout()
        bSizer2.Fit( self.m_panel1 )
        bSizer1.Add( self.m_panel1, 0, wx.EXPAND |wx.ALL, 5 )
        
        self.m_panel2 = wx.Panel( self, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.TAB_TRAVERSAL )
        bSizer3 = wx.BoxSizer( wx.VERTICAL )
        
        self.staMsg = wx.StaticText( self.m_panel2, wx.ID_ANY, u"고객목록", wx.DefaultPosition, wx.DefaultSize, 0 )
        self.staMsg.Wrap( -1 )
        bSizer3.Add( self.staMsg, 0, wx.ALL, 5 )
        
        
        self.m_panel2.SetSizer( bSizer3 )
        self.m_panel2.Layout()
        bSizer3.Fit( self.m_panel2 )
        bSizer1.Add( self.m_panel2, 0, wx.EXPAND |wx.ALL, 5 )
        
        self.m_panel3 = wx.Panel( self, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.TAB_TRAVERSAL )
        bSizer4 = wx.BoxSizer( wx.VERTICAL )
        
        self.lstGogek = wx.ListCtrl( self.m_panel3, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.LC_REPORT )
        bSizer4.Add( self.lstGogek, 1, wx.ALL|wx.EXPAND, 5 )
        
        
        self.m_panel3.SetSizer( bSizer4 )
        self.m_panel3.Layout()
        bSizer4.Fit( self.m_panel3 )
        bSizer1.Add( self.m_panel3, 1, wx.EXPAND |wx.ALL, 5 )
        
        self.m_panel4 = wx.Panel( self, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.TAB_TRAVERSAL )
        bSizer5 = wx.BoxSizer( wx.HORIZONTAL )
        
        self.staCount = wx.StaticText( self.m_panel4, wx.ID_ANY, u"인원수", wx.DefaultPosition, wx.DefaultSize, 0 )
        self.staCount.Wrap( -1 )
        bSizer5.Add( self.staCount, 0, wx.ALL, 5 )
        
        
        self.m_panel4.SetSizer( bSizer5 )
        self.m_panel4.Layout()
        bSizer5.Fit( self.m_panel4 )
        bSizer1.Add( self.m_panel4, 0, wx.EXPAND |wx.ALL, 5 )
        
        
        self.SetSizer( bSizer1 )
        self.Layout()
        
        self.Centre( wx.BOTH )
        
        #lstGogek 객체에 제목
        self.lstGogek.InsertColumn(0, '고객번호', width=100)
        self.lstGogek.InsertColumn(1, '고객명', width=100)
        self.lstGogek.InsertColumn(2, '고객전화번호', width=100)
        
        # Connect Events
        self.btnLogin.Bind( wx.EVT_BUTTON, self.OnLogin )
    
    def __del__( self ):
        pass
    
    # Virtual event handlers, overide them in your derived class
    def OnLogin( self, event ):
        if self.txtNo.GetValue() == '':
            wx.MessageBox('사번입력', '알림', wx.OK)
            self.txtNo.SetFocus()
            return
    
        elif self.txtName.GetValue() == '':
            wx.MessageBox('직원명입력', '알림', wx.OK)
            self.txtName.SetFocus()
            return
        self.LoginCheck()
        
    def LoginCheck(self):
        try:
            conn = MySQLdb.connect(**config)
            cursor = conn.cursor()
            
            no = self.txtNo.GetValue()
            name = self.txtName.GetValue()
            #print(no, name)
            
            sql = " select count(*) from jikwon where jikwon_no = '{0}' and jikwon_name = '{1}'".format(no, name)
            #print(sql)
            
            cursor.execute(sql)
            count = cursor.fetchone()[0]
            #print(count)
            
            if count == 0:
                wx.MessageBox('로그인 실패', '알림', wx.OK)
                return
            else:
                self.staMsg.SetLabelText(no + '번 직원의 관리고객 목록')
                self.DisplayData(no) #직원자료 출력 메소드 호출
                
        except Exception as e:
            print('LoginCheck err: ' + str(e))

        finally:
            cursor.close()
            conn.close()
    
    def DisplayData(self, no):
        try:
            conn = MySQLdb.connect(**config)
            cursor = conn.cursor()
            sql = "select gogek_no, gogek_name, gogek_tel from gogek where gogek_damsano={}".format(no)
            #print(sql)
            
            cursor.execute(sql)
            datas = cursor.fetchall()
            
            self.lstGogek.DeleteAllItems() #ListCtrl의 초기화
                
            for d in datas:
                i = self.lstGogek.InsertItem(1000, 0) # ListCtrl의 최대 행 수를 적어줌
                self.lstGogek.SetItem(i, 0, str(d[0]))
                self.lstGogek.SetItem(i, 1, str(d[1]))
                self.lstGogek.SetItem(i, 2, str(d[2]))
                
            self.staCount.SetLabelText('인원수: ' + str(len(datas)))
        except Exception as e:
            print('DisplayData err: ' + str(e))

        finally:
            cursor.close()
            conn.close()
if __name__ == '__main__':
    app = wx.App()
    MyGogek(None).Show()
    app.MainLoop()   

실행결과