27. 파이썬과 MySQL 연동

1. mysqlclient

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

!pip install mysqlclient

import MySQLdb

# 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') # 생략도 가능

2. cursor 생성하기

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

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

3. SQL문 결과 가져오기

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

row = cur.fetchall() # 모든 튜플 가져오기
print(row)

cur.execute(sql)

row = cur.fetchone() # 하나의 튜플만 가져오기
print(row)
# ('apple', '김사과', '01011111111', 'apple@naver.com', '남자')

# fetchone()을 이용하여 루프를 돌면서 모든 데이터를 출력하기
cur.execute(sql)

while True:
    row = cur.fetchone()
    if row:
        print(row)
    else:
        break

# cursor에 dictionary 형식으로 row를 유지하도록 내부 타입을 명시
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']}")
    else:
        break

 

4. Cursor와 Connection 닫아주기

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

5. 데이터 삽입하기

db = MySQLdb.connect(host = 'localhost', user =  'root', password = '1234', db = 'kdt')
cur = db.cursor()
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-1515-1515', 'avocado@avocado.com', '남자', '000000', '000000')
cur.execute(sql, data)
db.commit() # insert, delete, update를 사용할때는 commit()를 해줘야 한다

sql = "insert into member (userid, userpw, username, hp, email, gender, ssn1, ssn2) values ( %s, %s, %s, %s, %s, %s, %s, %s)"
data = [('abcd1234', '0000', '이사과', '0100000000', 'abcd1234@apple.com', '여자', '000000', '000000'),('abcd1111', '0000', '박메론', '0100000000', 'abcd1111@melon.com', '여자', '000000', '000000')] # 데이터가 여러개 일때 리스트 안에 튜플 넣기
cur.executemany(sql, data) # 여러개일 때는 executemany() 사용
db.commit()

문제

• '회원가입' 프로그램을 만들어보자

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

while True:
    print('************* 회원 가입 *************')
    userid = input('아이디를 입력하세요 : ')
    userpw = input('비밀번호를 입력하세요 : ')
    username = input('이름을 입력하세요 : ')
    hp = input('휴대폰 번호를 입력하세요 : ')
    email = input('이메일을 입력하세요 : ')
    gender = input('성별을 입력하세요 : ')
    ssn1 = input('주민번호 앞자리를 입력하세요 :')
    ssn2 = input('주민번호 뒷자리를 입력하세요 :')
    zipcode = input('우편번호를 입력하세요 : ')
    address = input('주소를 입력하세요 :')
    address2 = input('상세주소를 입력하세요 :')
    address3 = input('참고사항을 입력하세요 : ')
    
    data = (userid, userpw, username, hp, email, gender, ssn1, ssn2, zipcode, address, address2, address3)
    
    try:
        cur.execute(sql, data)
        db.commit()
        print('가입되었습니다!')
        break
    except:
        print('오류가 발생!')
        continue

cur.close()
db.close()

6. 데이터 수정하기

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

sql = "update member set zipcode = '12345', address = '서울시', address2 = '강남구', address3 = '역삼동111' where userid = 'avocado'"
result = cur.execute(sql)
db.commit()

print(result) # 성공했으면 성공한 숫자만큼 리턴, 실패했으면 0
# 출력결과 : 1

sql = "update member set zipcode = '11111', address = '서울시', address2 = '강남구', address3 = '역삼동111' where userid = 'avocado'"
result = cur.execute(sql)
db.commit()

if result > 0:
    print("수정되었습니다")
else:
    print('에러!')


cur.close()
db.close()

문제

• 로그인 프로그램을 만들어보자

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

userid = input('아이디를 입력하세요 : ')
userpw = input('비밀번호를 입력하세요 : ')
sql = "select * 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()

7. 데이터 삭제하기

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

sql = "delete from member where userid = 'avocado'"
result = cur.execute(sql)
db.commit()

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