6-0. Python과 MySQL의 연동 시 필요한 사전 작업
6-1. mysqlclient
6-1-1. mysqlclient의 설치
6-1-2. cursor 생성
6-1-3. SQL문 결과 가져오기
6-1-4. Cursor와 Connection을 닫아주기.
6-1-5. 하나의 데이터 삽입하기.
6-1-6. 복수의 데이터 삽입하기.
6-1-7. 데이터 수정하기
6-1-8. 데이터 삭제하기
예제
6-0. Python과 MySQL의 연동 시 필요한 사전 작업
1. 새로운 폴더를 생성.
해당 폴더에 MySQL에서 작성한 .sql파일을 저장하여 Jupyter notebook을 참고할 예정.
(경로 : C:\yjcho\python\jupyter\Database)
2. Terminal을 열어 'cd C:\yjcho\python\jupyter\Database' → 'jupyter notebook' 을 입력.
jupyter notebook으로 작성한 .ipynb파일이 해당 경로에 자동저장될 예정.
3. jupyter notebook을 실행 후 코드를 작성.
6-1. mysqlclient
MySQL 데이터베이스와 상호 작용하기 위한 Python 클라이언트 라이브러리.
Python에서는 MySQL 서버와 통신할 수 있는 Python용 데이터베이스 커넥터의 종류가 여러가지 있음.
→ 그 중 가장 많이 사용되는 커넥터로 PyMySQL, mysqlclient가 있음.
사용법은 비슷하지만 mysqlclient의 속도가 3.5배 빠르기 때문에 mysqlclient의 사용을 권장함.
C 언어로 작성된 MySQL 클라이언트 라이브러리를 binding하여 파이썬에서 사용할 수 있도록 한 것.
'데이터베이스 연결', '쿼리 실행', '결과 처리' 등의 기능을 제공.
대부분의 운영 체제에서 사용 가능, 다른 파이썬 라이브러리와 함께 사용하기 용이함.
6-1-1. mysqlclient의 설치
! pip install mysqlclient |
mysqlclient의 모듈인 MySQLdb 불러오기.
import MySQLdb |
' connect ' 메서드를 사용해 상기 정보들을 연동.
host: IP주소, localhost, 127.0.0.1
user: 유저, root
password: 비밀번호, 1234
db: 데이터베이스, kdt
db = MySQLdb.connect ( host = ' localhost ' , user = ' root ', password = ' 1234 ' , db = ' kdt ' ) db = MySQLdb.connect ( ' localhost ' , ' root ' , ' 1234 ' , ' kdt ' ) |
'host=', 'user=', 'password=', 'db='는 생략 가능. 순서만 잘 지켜 입력하면 정상적으로 작동.
해당 코드 작성 시, MySQL을 다룰 수 있는 객체 db가 생성됨.
(MySQL Workbench에서 생성한 user의 password가 다르면, 에러가 발생.)
6-1-2. cursor 생성
cursor(커서) 하나의 Database Connection에 대해서 독립적으로 SQL문을 실행할 수 있는 작업환경을 제공하는 객체. 데이터베이스 연결을 통해 쿼리를 실행하고 결과를 처리하는 데 사용되는 객체. 하나의 connection에 동시에 한 개의 cursor만 생성할 수 있음. cursor를 통해 SQL문을 실행하면 실행결과를 tuple단위로 반환. |
쿼리를 실행할 때마다 새로운 cursor 객체가 생성.
→ 여러 쿼리를 동시에 실행, 쿼리 실행 후에도 커넥션을 유지하면서 다른 쿼리를 실행할 수 있음.
쿼리 실행 중에 발생하는 오류를 처리하는 데도 사용됨.
cursor 객체는 connection 객체에서 cursor() method를 호출하여 생성.
이후에는 execute() 메서드를 사용하여 쿼리를 실행하고,
fetchone(), fetchmany(), fetchall() 등의 method를 사용하여 실행 결과를 처리.
db 객체를 통해 cursor를 생성
cur = db.cursor() |
변수 sql을 생성하여 임의의 SQL문을 실행.
sql = ' select userid, username, hp, email, gender from member ' cur.execute(sql) ---------------------------------------------------------------------------------------------- 6 |
출력되는 6 : '데이터의 갯수'가 6개라는 의미. (mysqlclient의 특징 중 하나.)
SQL문이 저장된 경로 = 위에서 생성한 경로.
python editor에서 SQL문을 작성할 시, 기존 SQL문 맨 뒤의 세미콜론(;)은 삭제.
다시 한번 실행하면, 이미 출력된 내용은 읽어올 수 없으므로 갯수는 6개에서 0개로 출력됨.
6-1-3. SQL문 결과 가져오기
fetchall()
한번에 모든 tuple을 가져옴. 만약 검색결과가 매우 방대하다면, 메모리 오버헤드가 발생할 수 있음.
fetchone()
한번에 하나의 tuple을 가져옴. 다시 메서드를 호출하면 다음 데이터 하나를 가져옴.
메모리 오버헤드 어떤 작업을 수행할 때 필요한 최소한의 메모리 공간보다 더 많은 메모리를 사용하는 현상. SQL에서 메모리 오버헤드는 일반적으로 SQL 서버가 처리하는 쿼리의 복잡도와 처리하는 데이터의 양에 비례함. SQL 쿼리가 복잡하고 대량의 데이터를 처리할 때, SQL 서버가 처리할 때 많은 메모리를 사용하게 되고, 이로 인해 메모리 오버헤드가 발생할 수 있음. 메모리 오버헤드는 일반적으로 SQL 쿼리의 실행 시간과 성능에 영향을 미침. 메모리가 부족하면 SQL 서버가 느려지고 쿼리의 실행 시간이 길어질 수 있음. 따라서, 메모리 오버헤드를 최소화하고 메모리를 효율적으로 사용하는 것이 중요함. |
변수 row를 생성하여 모든 tuple을 가져옴.
cur.execute(sql) row = cur.fetchall() print(row) |
다시 실행하면 공란이 뜨는데, 위에서 작성한 코드부터 다시 재실행을 해주면 정상적으로 출력됨.
변수 row를 생성. 하나의 tuple을 가져옴.
cur.execute(sql) row = cur.fetchone() print(row) |
fetchone()을 이용하여 loop를 돌면서 모든 data의 tuple을 가져오고 싶은 경우.
cur.execute(sql) while True: row = cur.fetchone() if row: # data가 있다면, print(row) # row를 계속 출력. else: # data가 없다면 break # 멈춤. |
cursor에 dictionary형식으로 row를 유지하도록 내부 타입을 명시한 후,
Query의 결과를 dictionary의 형태로 반환.
cur = db.cursor(MySQLdb.cursors.DictCursor) cur.execute(sql) while True: row = cur.fetchone() if row: print(f"아이디:{row['userid']}, 이름:{row['username']}, 전화번호:{row['hp']}, 이메일:{row['email']}, 성별:{row['gender']}") dictionary의 형태로 반환됨. formatting을 이용하여 row의 필드명을 입력. else: break |
6-1-4. Cursor와 Connection을 닫아주기.
cur.close() # cursor 닫기 db.close() # connection 닫기 |
Cursor와 Connection을 닫아주는 이유: 1. 자원 해제: Cursor와 Connection은 자원(resource)을 사용하므로, 이를 해제하지 않으면 메모리나 다른 자원이 부족해질 수 있음. 따라서, 이를 닫아주는 것은 자원을 효율적으로 관리하는 데에 중요함. 2. 보안: Cursor와 Connection을 닫아주지 않으면, 해당 연결이 계속 유지되어 있어 악의적인 공격에 취약해질 수 있음. 따라서, 이를 닫아주는 것은 시스템 보안에도 중요함. 3. 트랜잭션 관리: MySQL에서는 트랜잭션(Transaction)을 사용하여 데이터 일관성을 유지함. Cursor와 Connection을 닫아주지 않으면, 해당 트랜잭션이 계속 유지되어 있을 수 있으므로, 이를 닫아주는 것은 트랜잭션 관리에도 중요함. 4. 성능: Cursor와 Connection을 닫아주지 않으면, 해당 연결이 계속 유지되어 있어 성능이 저하될 수 있음. 따라서, 이를 닫아주는 것은 시스템 성능 향상에도 중요함. |
6-1-5. 하나의 데이터 삽입하기.
SQL = "INSERT INTO ... VALUES ... " 로 SQL 삽입문을 작성
data를 변수로 지정하여 소괄호 ( ( ) ) 로 묶어 저장함.
cursor를 이용해 sql문에 data를 반영하여 실행.
데이터의 변경을 반영.
'cha'라는 userid를 가진 data를 새롭게 삽입하고 싶은 경우.
위의 코드를 입력 시 오류가 발생. 변수를 지정하는 단계가 필요하기 때문. data라는 변수를 생성하여 tuple로 저장. sql = " insert into member (userid, userpw, username, hp, email, gender, ssn1, ssn2) values (%s, %s, %s, %s, %s, %s, %s, %s) " data = ('cha', '1234', '차', '7777', 'bbbb', '여자', '000000', '0000000') cur.execute(sql, data) # sql에 data를 넣어서 execute하겠다. db.commit() # 데이터의 변경을 반영하는 코드. 이 코드를 입력하지 않은경우 오류가 발생. |
해당 코드를 실행한 후,
MySQL Workbench에서 select * from member ; 을 입력하여 정상적인 데이터의 삽입을 확인할 수 있음.
6-1-6. 복수의 데이터 삽입하기.
SQL = "INSERT INTO ... VALUES ... " 로 SQL 삽입문을 작성
data를 변수로 지정하여 대괄호 ( [ ] ) 로 묶고, 내부를 소괄호 ( ( ) ) 로 묶어 저장함.
cursor를 이용해 sql문에 data를 반영하여 실행.
데이터의 변경을 반영.
'lim', 'kwon'이라는 userid를 가진 data들을 새롭게 삽입하고 싶은 경우.
sql = "insert into member (userid, userpw, username, hp, email, gender, ssn1, ssn2) values (%s, %s, %s, %s, %s, %s, %s, %s)" list의 형태인 대괄호 ( [ ] ) 로 묶고, 그 안의 각각의 data들을 tuple의 형태인 소괄호(())로 묶어 저장함. data = [('lim', '1234', '임', '7777', 'bbbb', '여자', '000000', '0000000'), ('kwon', '1234', '권', '8888', 'bbbb', '남자', '000000', '0000000')] cur.executemany(sql, data) # 하나는 execute이나, 복수는 executemany이다. db.commit() |
해당 코드를 실행한 후,
MySQL Workbench에서 select * from member ; 을 입력하여 정상적인 데이터의 삽입을 확인할 수 있음.
6-1-7. 데이터 수정하기
SQL = "UPDATE ... SET ... WHERE ... " 로 SQL 수정문을 작성
result로 cursor를 이용해 sql문을 실행.
데이터의 변경을 반영.
result를 출력.
userid가 lee인 data의 내용을 변경하고 싶은 경우.
sql = "update member set zipcode='12345', address1='서울시', address2='강남구', address3='역삼동' where userid='lee'" result = cur.execute(sql) db.commit() print(result) -------------------------------------------------------------------------- 1 정상적으로 최초실행 시 1이 출력되나, 반복적으로 출력 시 0이 출력됨. |
userid가 park인 data의 내용을 변경하고,
'정상적으로 변경될 시'와 '오류가 발생할 시'의 경우를 나누어 알림을 출력하고 싶은 경우.
sql = "update member set zipcode='12345', address1='서울시', address2='강남구', address3='역삼동' where userid='park'" result = cur.execute(sql) db.commit() if result > 0 : print('수정되었습니다.') else: print('에러가 발생했습니다.') -------------------------------------------------------------------------- 1 정상적으로 최초실행 시 1이 출력되나, 반복적으로 출력 시 0이 출력됨. 이러한 부분을 이용하여 result > 0 인 경우로 조건을 구분. |
6-1-8. 데이터 삭제하기
SQL = "DELETE FROM ... WHERE ... " 로 SQL 수정문을 작성
result로 cursor를 이용해 sql문을 실행.
데이터의 변경을 반영.
result를 출력.
userid가 1인 data의 값을 member 테이블에서 삭제하고 싶은 경우
sql = " delete from member where userid = '1' " result = cur.execute(sql) db.commit() if result>0: print('탈퇴되었습니다.') else: print('오류.') |
해당 코드를 실행한 후,
MySQL Workbench에서 select * from member ; 을 입력하여 정상적인 데이터의 삽입을 확인할 수 있음.
예제
문제1
* '회원가입' 프로그램을 만들어보자. (member 테이블 활용)
********** 회원 가입 **********
아이디를 입력하세요:
비밀번호를 입력하세요:
이름을 입력하세요:
휴대폰번호를 입력하세요:
이메일을 입력하세요:
성별을 입력하세요:
주민번호 앞자리를 입력하세요:
주민번호 뒷자리를 입력하세요:
우편번호를 입력하세요:
주소를 입력하세요:
상세주소를 입력하세요:
참고사항을 입력하세요: '가입되었습니다!' 에러가 발생! 오류처리 → 다시 입력해주세요
#1. MySQL Workbench에서 select * from member; 를 통해 member 테이블을 확인.
#2. mysqlclient의 모듈인 MySQLdb 불러오기.
import MySQLdb
#3. db 연결하기.
db = MySQLdb.connect('localhost', 'root', '1234', 'kdt')
#4. cursor 생성하기.
cur = db.cursor()
#5. 데이터를 입력받는 코드 작성.
print('********** 회원 가입 **********') while True: try: userid = input('* 아이디를 입력하세요:') userpw = input('* 비밀번호를 입력하세요:') username = input('* 이름을 입력하세요:') hp = input('* 휴대폰번호를 입력하세요:') email = input('* 이메일을 입력하세요:') gender = input('* 성별을 입력하세요:') ssn1 = input('* 주민번호 앞자리를 입력하세요:') ssn2 = input('* 주민번호 뒷자리를 입력하세요:') zipcode = input('* 우편번호를 입력하세요:') address1 = input('* 주소를 입력하세요:') address2 = input('* 상세주소를 입력하세요:') address3 = input('* 참고사항을 입력하세요:') sql = "insert into member (userid, userpw, username, hp, email, gender, ssn1, ssn2, zipcode, address1, address2, address3) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" data = (userid, userpw, username, hp, email, gender, ssn1, ssn2, zipcode, address1, address2, address3) cur.execute(sql, data) db.commit() print('가입되었습니다!') break except: print('다시 입력해주세요.') |
#6. cursor와 connection을 닫기.
cur.close()
db.close()
문제2
로그인 프로그램을 작성해보자.
********** 로그인 **********
아이디를 입력하세요: apple
비밀번호를 입력하세요: 1111
로그인 되었습니다.
or
아이디를 입력하세요: apple
비밀번호를 입력하세요: 1234
아이디 또는 비밀번호가 틀렸습니다.
import MySQLdb db = MySQLdb.connect('localhost', 'root', '1234', 'kdt') cur = db.cursor() print('********** 로그인 **********') userid = input('아이디를 입력하세요:') userpw = input('비밀번호를 입력하세요:') sql = 'select userid, userpw from member where userid=%s and userpw=%s' data = (userid, userpw) result = cur.execute(sql, data) if result>0: print('로그인 되었습니다.') else: print('아이디 또는 비밀번호가 틀렸습니다.') cur.close() db.close() |
문제3
14일차 예제3의 영어단어장 프로그램을 DB와 연동해보자.
(MySQL과 Python 코드를 연동하는 작업.)
#1. SQL에 words table 생성.
#2. jupyter notebook에 DTO class 작성하기.
class Words: def __init__(self, eng, kor, lev=1): self.eng = eng self.kor = kor self.lev = lev def setEng(self, eng): self.eng = eng def getEng(self): return self.eng def setKor(self, kor): self.kor = kor def getKor(self): return self.kor def setLev(self,lev): self.lev = lev def getLev(self): return self.lev |
#3. jupyter notebook에 DAO class 작성하기.
import MySQLdb class WordsDao: def __init__(self): self.db = None def connect(self): #부르기만하면 연결이 가능하도록 하는 connect method 생성 self.db = MySQLdb.connect('localhost','root','1234','kdt') def disconnect(self): # disconnect할 시 끊어주는 disconnect method 생성 self.db.close() def insert(self, word): self.connect() cur = self.db.cursor() sql = "insert into words values (%s, %s, %s)" data = (word.getEng(), word.getKor(), word.getLev()) # word에서 가져온 영어, 한글, 레벨을 넣어줌. cur.execute(sql, data) self.db.commit() self.disconnect() def selectAll(self): self.connect() cur = self.db.cursor() sql = "select eng, kor, lev from words" cur.execute(sql) row = cur.fetchall() # fetchall()함수를 써서 모두 가져옴. self.disconnect() return row def search(self, eng): self.connect() cur=self.db.cursor() sql = "select eng, kor, lev from words where eng=%s" data = (eng,) # tuple로 감쌀 때 data가 1개이면 뒤에 콤마(,)를 붙임. cur.execute(sql, data) row=cur.fetchone() self.disconnect() return row def update(self, word): self.connect() cur=self.db.cursor() sql = "update words set kor=%s, lev=%s where eng=%s" data = (word.getKor(), word.getLev(), word.getEng()) result = cur.execute(sql, data) self.db.commit() if result > 0: print('수정되었습니다.') else: print('해당 단어가 없습니다.') self.disconnect() def delete(self, eng): self.connect() cur=self.db.cursor() sql = "delete from words where eng=%s" data = (eng,) result = cur.execute(sql, data) self.db.commit() if result > 0: print('삭제되었습니다.') else: print('해당 단어가 없습니다.') self.disconnect() |
#4. jupyter notebook에 Service class 작성하기.
class WordsService: def __init__(self): self.dao = WordsDao() def insertWord(self): eng = input('단어를 입력하세요:') kor = input('뜻을 입력하세요:') lev = input('레벨을 입력하세요:') word = Words(eng, kor, lev) self.dao.insert(word) def printAll(self): datas = self.dao.selectAll() print(datas) def searchWord(self): eng = input('검색할 단어를 입력하세요:') word = self.dao.search(eng) if word: print(word) else: print('찾는 단어가 없습니다.') def editWord(self): eng = input('수정할 단어를 입력하세요:') word = self.dao.search(eng) if word == None: print('수정할 단어를 찾지 못했습니다.') else: kor = input('새로운 뜻을 입력하세요.') lev = input('새로운 레벨을 입력하세요.') word = Words(eng, kor, lev) self.dao.update(word) def delWord(self): eng = input('삭제할 단어를 입력하세요:') self.dao.delete(eng) |
#5. jupyter notebook에 Menu class 작성하기.
class Menu: def __init__(self): self.service = WordsService() def run(self): while True: try: menu = int(input('1. 등록하기 \n2. 출력하기 \n3. 검색하기 \n4. 수정하기 \n5. 삭제하기 \n6. 종료하기 \n')) if menu == 1: self.service.insertWord() elif menu == 2: self.service.printAll() elif menu == 3: self.service.searchWord() elif menu == 4: self.service.editWord() elif menu == 5: self.service.delWord() elif menu == 6: break except Exception as e: print(e) print('다시 입력하세요.') |
#6. jupyter notebook에서 Menu class를 실행하기.
start = Menu() start.run() |
