[TIL.20.12.22~28] Python과 MySQL

[20.12.26] 

>Python과 MySQL 연동

- 파이썬에서 MySQL을 활용하기 위해서는 MySQL 데이터베이스 연동 라이브러리 pymysql을 설치해야 한다.

 

pip install pymysql

- 터미널 및 프롬프트에서 파이썬 패키지 인스톨러(pip)를 이용해서 설치해 준다

 

데이터 조회 : 

1. MySQL에 연결 : 연결자 = pymysql.connect(연결옵션) 

import pymysql

conn = pymysql.connect(host = '127.0.0.1',user = '', password = '', db = 'sqldb', charset = 'utf8')

2. 커서 생성 :  커서 이름 = 연결자.cursor()

cur = conn.cursor()

3.  데이터 조회 : 커서이름.excute("SELECT문")

self.cur.execute("SELECT * FROM employees WHERE first_name = '" +first_name+"'")

4. 데이터 출력 : 커서이름.fetchone(), fetchone()함수는 더이상 조회할 데이터가 없을 시 False 를 리턴한다.

 *반복문을 이용하여 여러개의 데이터 출력할 수 있다.*

while (True) :
            row = cur.fetchone()
            if row== None :
                break
            data0 = row[0]
            data1 = row[1]
            data2 = row[2]
            data3 = row[3]
            data4 = row[4] 
            data5 = row[5]
            print("%5s   %8s   %8s   %5s  %5s  %8s" % (data0, data1, data2, data3, data4, data5))

5. MySQL종료 : 연결자.close()

conn.close()

 

>>데이터 조회 예시

import pymysql

def selectUserAll(conn) :  
    cur = conn.cursor()
    cur.excute("SELECT * FROM usertbl ")
    print("사용자ID    사용자이름     생년     주소")
    print("----------------------------------------------------")

    while (True) :
        row = cur.fetchone()
        if row== None :
            break
        data1 = row[0]
        data2 = row[1]
        data3 = row[2]
        data4 = row[3] 
        print("%5s   %8s   %8s   %5s" % (data1, data2, data3, data4)) 
        
def main():
    conn = pymysql.connect(host = '127.0.0.1',user = '', password = '', db = 'sqldb', charset = 'utf8')
    selectUserAll(conn)
    conn.cloase()        
    
if __name__=="__main__" :
    main()

---

*실습문제*

'''
Q. 다음과 같은 파이썬 프로그램을 작성하세요. 

-------------------------------------------
<사원 조회프로그램입니다.>
다음 메뉴 중 하나를 입력하세요.
1.   이름으로 검색
2.   사번으로 검색 
3.   생년으로 검색 
> 1 
조회할 사원의 이름(first_name)을 입력하세요? 
> Georgi 
10001  Georgi  Facello  M  19860626  
-------------------------------------------
<사원 조회프로그램입니다.>
다음 메뉴 중 하나를 입력하세요.
1.   이름으로 검색
2.   사번으로 검색 
3.   생년으로 검색 
> 1 
조회할 사번을 입력하세요? 
> 10001  
10001  Georgi  Facello  M  19860626 
-------------------------------------------
<사원 조회프로그램입니다.>
다음 메뉴 중 하나를 입력하세요.
1.   이름으로 검색
2.   사번으로 검색 
3.   생년으로 검색 
> 1 
조회할 생년을 입력하세요? 
> 1953 
 10001 	 1953-09-02 	 Georgi     Facello  	M 	 1986-06-26 
 10006 	 1953-04-20 	 Anneke     Preusig 	F 	 1989-06-02 
 10011 	 1953-11-07 	 Mary 	    Sluis 	F  	 1990-01-22 
 10019 	 1953-01-23 	 Lillian    Haddadi 	M 	 1999-04-30 
 10023 	 1953-09-29 	 Bojan 	Montemayor 	F 	 1989-12-17 
 10026 	 1953-04-03 	 Yongqiao Berztiss 	M 	 1995-03-20 
 10035 	 1953-02-08 	 Alain 	 Chappelet 	M 	 1988-09-05 
 10051 	 1953-07-28 	 Hidefumi Caine 	M 	 1992-10-15 
 10059 	 1953-09-19 	 Alejandro McAlpine 	F 	 1991-06-26 
 10067 	 1953-01-07 	 Claudi Stavenow 	M 	 1987-03-04 
 10100 	 1953-04-21 	 Hironobu  Haraldson 	F 	 1987-09-21 
 
 '''

>> 내가 작성한 프로그램 

import pymysql

def dbconnect() : 
    conn = pymysql.connect (host='127.0.0.1',user ='root',password='whdqkr003',db ='employees',charset='utf8')
    return conn 

class Search():
    def __init__(self, conn):
        self.conn = conn
        self.cur = self.conn.cursor()

    def search_by_name(self,first_name) :  
        self.cur.execute("SELECT * FROM employees WHERE first_name = '" +first_name+"'")
        
        while (True) :
            row = self.cur.fetchone()
            if row== None :
                break
            data0 = row[0]
            data1 = row[1]
            data2 = row[2]
            data3 = row[3]
            data4 = row[4] 
            data5 = row[5]
            print("%5s   %8s   %8s   %5s  %5s  %8s" % (data0, data1, data2, data3, data4, data5))

    def search_by_num(self,emp_no) :  
        self.cur.execute("SELECT * FROM employees WHERE emp_no = '" +emp_no+"'")
        
        row = self.cur.fetchone()

        data0 = row[0]
        data1 = row[1]
        data2 = row[2]
        data3 = row[3]
        data4 = row[4]
        data5 = row[5] 
        print("%5s   %8s   %8s   %5s  %5s  %8s" % (data0, data1, data2, data3, data4, data5))

    def search_by_birth(self,birth_date) :  
        self.cur.execute("SELECT * FROM employees WHERE birth_date LIKE '" +birth_date+'%'+"'")
        
        while (True) :
            row = self.cur.fetchone()
            if row== None :
                break
            data0 = row[0]
            data1 = row[1]
            data2 = row[2]
            data3 = row[3]
            data4 = row[4] 
            data5 = row[5]
            print("%5s   %8s   %8s   %5s  %5s  %8s" % (data0, data1, data2, data3, data4, data5))

def main():
    conn = dbconnect()  # DB 연결  
    srch = Search(conn)
    menu = input("다음 메뉴 중 하나를 입력하세요. \n1. 이름으로 검색 \n2. 사번으로 검색 \n3. 생년월일으로 검색 \n")
    if menu == '1':
        first_name = input("조회할 사원 이름을 입력하세요")
        srch.search_by_name(first_name)
        conn.close()        # DB 연결 끊기 
    elif menu == '2':
        emp_no = input("조회할 사원 번호를 입력하세요")
        srch.search_by_num(emp_no)
        conn.close()        # DB 연결 끊기
    else:
        birth_date = input("조회할 사원의 생년월일을 입력하세요")
        srch.search_by_birth(birth_date)
        conn.close()        # DB 연결 끊기

if __name__=="__main__" :
    main()

---

데이터 입력 & 업데이트 :

- 기본적인 구조는 데이터 조회와 동일하며. 다만 excute() 함수 실행 시 괄호 안에 INSERT/UPDATE문을 입력한다

- 파이썬의 insert()문을 이용하여 값을 입력 받는다.

- excute()문을 실행하여 INSERT쿼리문을 실행하면 데이터베이스에 완전히저장되는 것이 아니고 임시저장된 상태이다.

- 연결자.commint()를 실행하여 확실하게 데이터베이스에 저장한다.

 

>>데이터 입력 예시

import pymysql

conn = pymysql.connect(host = '127.0.0.1',user = '', password = '', db = 'sqldb', charset = 'utf8')

def insertUser(conn):
	cur = conn.cursor()
    while (True):
      print("새로운 사용자 등록")
      data1 = input("사용자 ID : ")
      if data1 == ''
          print("사용자 등록을 마칩니다.")
          break
      data2 = input("사용자 이름 : ")
      data3 = input("사용자 생년 : ")
      data4 = input("사용자 주소 : ")
      sql = "INSERT INTO usertbl (userid, name, birthYear, addr)
                  VALUES('"+data1+"','"+data2+"','"+data3+"','"+data4+"')"
      cur.excute(sql)
   conn.commit()

 

데이터 삭제 : 

- 기본적인 구조는 데이터 조회와 동일하며. 다만 excute() 함수 실행 시 괄호 안에 DELETE문을 입력한다

- 특정 속성 값을 가지는 데이터 삭제하도록 함

 

>>데이터 삭제 예시

import pymysql

conn = pymysql.connect(host = '127.0.0.1',user = '', password = '', db = 'sqldb', charset = 'utf8')

def deleteUser(conn) : 
    name = input("삭제할 사용자의 이름을 입력하세요 ")  
    cur = conn.cursor()
    cur.excute("SELECT * FROM usertbl where name= '" +name+"'") 
    
    row = cur.fetchone()
    if row == None :
        print(" 사용자가 존재하지 않습니다.")
    else :
        print(row)
        confirm = input(" 정말로 삭제할까요?(Y/N) ") 
        if confirm == "Y" :
            try : 
                cur.execute("delete FROM usertbl where userID ='" + row[0]+"'") 
                print(" 삭제에 성공하였습니다. ")
            except :
                print(" 삭제에 실패하였습니다. ")
            finally : 
                conn.commit()

---

 20.12.22. ~ 20.12.26일 까지 데이터베이스 및 SQL언어 전반에 걸쳐 학습하였다. 또한 데이터베이스를 활용할 수 있는 여러 프로그래밍 언어 중에서 파이썬을 이용하여 데이터베이스에 접근하고 MySQL쿼리문을 실행시키는 법을 익히게 되었다. 이로써 데이터베이스에 내의 데이터를 이용해서 동작하는 프로그램을 만들 수 있게 된 것이다!!

 

 하나 하나 새로운 것들을 배우고 이 새로운 지식을 기술적으로 결합하여 더욱 강력한 기능을 가지는 프로그램을 만들 수 있다는 점이 매우 흥미롭다. 아직까지 아는것이 많지 않지만 프로그래밍을 통해 어떤 아이디어든지 실현해 낼 수 있지 않을까...?