[파이썬, Python] mysqlclient 을 이용하여 파이썬과 MySQL 연동하기!

💻 내 컴퓨터 MySQL 서버와 연동해주기 위해 Jupyter Notebook을 사용함.

 

주피터 노트북 사용하기 ⬇⬇⬇

https://coding-yesung.tistory.com/41

[파이썬, Python] 주피터 노트북 (Jupyter notebook) 설치하기 & 주피터 노트북 사용하기!

---

1. mysqlclient

• 파이썬에서는 MySQL 서버와 통신할 수 있는 파이썬용 데이터베이스 커넥터의 종류가 여러가지 있음
• PyMySQL, mysqlclient는 가장 많이 사용되는 커넥터
• 사용법은 비슷하다 속도가 빠른 mysqlclient를 권장하고 있음

 

📌 PyMySQL 과 MySQLdb 특징과 설치방법 알아보기!

https://coding-yesung.tistory.com/70

---

✅ MySQL database 연동하기

import MySQLdb

# host: IP주소, localhost, 127.0.0.1(내 컴퓨터)
# user: root
# password: 1234
# db: 데이터베이스, kdt
# db = MySQLdb.connect('localhost', 'root', '1234', 'kdt') -> 파라미터 제거하고 순서대로 입력
db = MySQLdb.connect(host='localhost', user='root', password='1234', db='kdt')

---

2. cursor 생성하기

• 하나의 DataBase Connection에 대하여 독립적으로 SQL문을 실행할 수 있는 작업환경을 제공하는 객체
• 하나의 Connection에 동시에 한 개의 cursor만 생성할 수 있으며, cursor를 통해 SQL문을 실행하면 실행결과를 튜플 단위로 반환

cur = db.cursor()   # 커서 생성

# 멤버 테이블에서 userid, username, hp, email, gender 필드만 조회
sql = 'select userid, username, hp, email, gender from member'  

cur.execute(sql)     # sql구문 실행

>>> 6     # 테이블에 저장된 데이터의 수를 반환

member 테이블

✅ cursor에 dictionary 타입으로 저장하도록 내부 타입을 지정하기

cur = db.cursor(MySQLdb.cursors.DictCursor)   # dict형식의 cursor지정

---

3. SQL문 결과 가져오기

• fetchall(): 한번에 모든 tuple을 가져옴. 검색 결과가 매우 크다면 메모리 오버헤드가 발생할 수 있음.
• fetchone(): 한번에 하나의 tuple을 가져옴. 다시 메서드를 호출하면 다음 데이터 하나를 가져옴

sql = 'select userid, username, hp, email, gender from member' 
cur = db.cursor()
row = cur.fetchall()   # sql문의 모든 결과 튜플을 반환함
print(row)

>>> (('apple', '김사과', '010-1111-1111', 'apple@apple.com', 'female'), 
('banana', '반하나', '010-2222-2222', 'banana@naver.com', 'female'), 
('berry', '배에리', '010-9999-9999', 'berry@berry.com', 'male'), 
('cherry', '최애리', '010-5555-5555', 'cherry@cherry.com', 'female'), 
('melon', '이메론', '010-4444-4444', 'melon@melon.com', 'male'), 
('orange', '오렌지', '010-3333-3333', 'orange@orange.com', 'male'))

💡 cursor가 이미 끝까지 가져왔기 때문에 한번 더 실행하면  None값 반환  ➡ cursor execute 재실행

row = cur.fetchall()
print(row)

>>> ()

 

✅ 한 줄의 데이터만 조회하기

sql = 'select userid, username, hp, email, gender from member' 
cur = db.cursor()
cur.execute(sql)   # sql 재실행

row = cur.fetchone()
print(row)
>>> ('apple', '김사과', '010-1111-1111', 'apple@apple.com', 'female')

# 메서드를 재실행하면 다음 데이터를 하나씩 가져옴
row = cur.fetchone()
print(row)
>>> ('banana', '반하나', '010-2222-2222', 'banana@naver.com', 'female')

 

✅ fetchone()을 이용하여 루프를 돌면서 모든 데이터 출력하기

cur = db.cursor()
sql = 'select userid, username, hp, email, gender from member' 
cur.execute(sql)

whilt True:
	row = cur.fetchone()  # 데이터를 한줄씩 가져옴
    if row:   			  # row 데이터가 있으면
    	print(row)
    else:				  # row 데이터가 없으면
    	break			  # loop를 멈춤
        
>>> ('apple', '김사과', '010-1111-1111', 'apple@apple.com', 'female')
('banana', '반하나', '010-2222-2222', 'banana@naver.com', 'female')
('berry', '배에리', '010-9999-9999', 'berry@berry.com', 'male')
('cherry', '최애리', '010-5555-5555', 'cherry@cherry.com', 'female')
('melon', '이메론', '010-4444-4444', 'melon@melon.com', 'male')
('orange', '오렌지', '010-3333-3333', 'orange@orange.com', 'male')

 

✅ dict형식의 cursor에서 필드이름으로 원하는 데이터 출력하기

cur = db.cursor(MySQLdb.cursors.DictCursor)   # dict  형식으로 지정
sql = 'select userid, username, hp, email, gender from member' 
cur.execute(sql)

while True:
	row = cur.fetchone()
    if row:   # 필드이름을 dict의 key로 가져올 수 있음
    	print(f"아이디: {row['userid']}, 이름: {row['username']}, 전화번호: {row['hp']}, 이메일: {row['email']}, 성별: {row['gender']}")
     else:
        break
        
>>> 아이디: apple, 이름: 김사과, 전화번호: 010-1111-1111, 이메일: apple@apple.com, 성별: female
아이디: banana, 이름: 반하나, 전화번호: 010-2222-2222, 이메일: banana@naver.com, 성별: female
아이디: berry, 이름: 배에리, 전화번호: 010-9999-9999, 이메일: berry@berry.com, 성별: male
아이디: cherry, 이름: 최애리, 전화번호: 010-5555-5555, 이메일: cherry@cherry.com, 성별: female
아이디: melon, 이름: 이메론, 전화번호: 010-4444-4444, 이메일: melon@melon.com, 성별: male
아이디: orange, 이름: 오렌지, 전화번호: 010-3333-3333, 이메일: orange@orange.com, 성별: male

---

4. Cursor와 Connection 닫아주기

• close(): 커서 사용을 마쳤을 때 사용
• 커서를 닫고 모든 결과를 재설정하며 커서 개체가 원래 연결 개체를 참조하지 않도록 함

cur.close()  # 커서 닫기
db.close()   # 커넥션 닫기

---

5. 데이터 삽입하기

5-1. 한 개의 데이터 삽입하기

# SQL 구문
# 입력할 data는 의 필드의 value갯수를 맞춰 넣어야 함!
sql = sql = "insert into member(userid, userpw, username, hp, email, gender, ssn1, ssn2) values (%s, %s, %s, %s, %s, %s, %s, %s)"
data = ('avocado','0000','안카도','010-0000-0000', 'avocado@avocado.com','male','000000', '0001000')

cur.execute(sql, data)   # data를 넣은 sql을 실행함
db.commit()    # 반영시켜야 함

✅ WorkBench에서 확인해보자!

select * from member;

---

5-2. 여러개의 데이터 삽입하기

sql = "insert into member (userid, userpw, username, hp, email, gender, ssn1, ssn2) values(%s, %s, %s, %s, %s, %s, %s, %s)"

# 리스트로 튜플을 묶어줌
data = [('peach','0000','피이치','010-9876-9876', 'peach@peach.com','female','010000', '2001000'), 
       ('mango','0000','마앙고','010-1234-1234', 'mango@mango.com','male','000001', '0011000'),]
cur.executemany(sql, data)  # data를 넣은 sql을 실행함
db.commit() # 반영

두 개의 데이터가 한번에 추가됨

---

6. 데이터 수정하기

✅ userid가 'avocado'인 회원의 주소를 변경해보자!

db = MySQLdb.connect('localhost', 'root', '1234', 'kdt')  # db 접속
cur = db.cursor()  # 커서 생성

sql = "update member set zipcode='12348', address1 = '서울시', address2='강남구', address3='양재동' where userid='avocado'"
result = cur.execute(sql)  # 성공하면 수정된 데이터의 갯수 반환, 아니면 0
db.commit()

if result >0:    # 수정된 갯수가 있음 
    print('정상적으로 수정됨')
else:
    print('에러!')
    
>>> 정상적으로 수정됨

✅ WorkBench에서 확인

select * from member;

avocado의 주소가 수정됨

---

7. 데이터 삭제하기

✅ userid가 'avocado'인 회원의 데이터를 삭제해보자!

db = MySQLdb.connect('localhost', 'root', '1234', 'kdt')
cur = db.cursor()

sql = "delete from member where userid='avocado'"
result = cur.execute(sql)  # 성공하면 삭제된 데이터의 갯수 반환, 아니면 0
db.commit()

if result >0 :
    print('탈퇴되었습니다')
else:
    print('오류발생!')
>>> 탈퇴되었습니다

 

✅ WorkBench에서 확인

select * from member;

avocado 회원 데이터 삭제됨

---

🔍 문제1

• 회원 테이블을 이용하여 회원가입 프로그램을 만들어보자!

--------- 회원가입 ---------
아이디를 입력하세요:
비밀번호를 입력하세요:
이름을 입력하세요:
휴대폰번호를 입력하세요:
이메일을 입력하세요:
성별을 입력하세요:
주민번호 앞자리를 입력하세요:
주민번호 뒷자리를 입력하세요:
우편번호를 입력하세요:
주소를 입력하세요:
상세주소를 입력하세요:
참고사항을 입력하세요:
가입되었습니다!

에러가 발생! -> 오류처리, 다시입력하도록

💡  코드

import MySQLdb
db = MySQLdb.connect('localhost', 'root', '1234', 'kdt')   # 데이터베이스에 연결한 db 객체 생성
cur = db.cursor()   # 커서 생성

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문 작성(데이터 갯수 맞추기!!)
        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)  # data를 넣은 sql을 실행함
        db.commit()
        print('가입되었습니다')
        break
    except:
        print('다시 입력하세요')
        
cur.close()  # 커서 닫기
db.close()	 # 커넥션 닫기

 

---

🔍 문제2

• 회원 테이블을 이용하여 로그인 프로그램을 만들어보자.

---------- 로그인 -----------
아이디를 입력하세요: apple
비밀번호를 입력하세요: 1111
로그인 되었습니다!

아이디를 입력하세요: apple
비밀번호를 입력하세요: 2222
아이디 또는 비밀번호가 틀렸습니다!

💡 코드

import MySQLdb
db = MySQLdb.connect('localhost', 'root', '1234', 'kdt')    # 데이터베이스 연결한 db 객체 생성
cur = db.cursor()  # 커서 생성

userid = input('아이디를 입력하세요: ')
userpw = input('비밀번호를 입력하세요:')

sql = 'select userid from member where userid=%s and userpw=%s'   #userid와 userpw만 필요
data= (userid, userpw)
result = cur.execute(sql, data)  # 정상적으로 실행되면 나오는 결과 값 반환

if result > 0:   
    print('로그인 되었습니다.')
else:   # 실행 실패시 0
    print('아이디 또는 비밀번호를 확인하세요')

cur.close()
db.close()