28. 파이썬과 MySQL 연동

1.  mysqlclient 

• 파이썬에서는 MySQL 서버와 통신할 수 있는 파이썬용 데이터베이스 커넥터를 다양하게 지원
• PyMySQL, mysqlclient를 가장 많이 사용
• PyMySQL은 속도가 느리고 연습용, 사용법은 비슷하고 속도가 빠른 mysqlclient 권장

!pip install mysqlclient
import MySQLdb

 

1-1) MySQL 접속하기

# MySQLdb.connect(host='IP주소', user='사용자', password='비밀번호', db='DB명')
db = MySQLdb.connect(host='localhost', user='root', password='1234', db='kdt')

 

1-2) cursor 생성하기

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

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

 

1-3) SQL문 결과 가져오기

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

data = cur.fetchall()
print(data)

결과

sql = 'select userid, name, hp, gender from member'
cur.execute(sql)   # 결과 : 6

row = cur.fetchone()
print(row)             # 결과 : ('apple', '김사과', '010-1111-1111', '여자')

row = cur.fetchone()
print(row)             # 결과 : ('avocado', '안가도', '010-6666-6666', '남자')

row = cur.fetchone()
print(row)             # 결과 : ('banana', '반하나', '010-2222-2222', '여자')

 

※ 실습 : fetchone()을 이용하여 member 테이블의 모든 데이터를 튜플로 출력

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

결과

 

1-4) dict 형태로 결과를 반환하기

# cursor(MySQLdb.cursors.DictCursor)
cur = db.cursor(MySQLdb.cursors.DictCursor)
sql = 'select userid, name, hp, gender from member'
cur.execute(sql)
result = cur.fetchall()
print(result)

 

결과

※ 실습 : member 테이블의 데이터를 하나의 row씩 dict으로 가져와 아래와 같이 출력
  - 아이디 : XXX, 이름: XXX, 전화번호 : XXX, 성별 : XX

while True:
    row = cur.fetchone()
    if row:
         print(f"아이디: {row['userid']}, 이름: {row['name']}, 전화번호: {row['hp']}, 성별: {row['gender']}")
    else:
        break

결과

 

1-5) cursor와 connection 닫아주기

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

 

2. 데이터 삽입하기

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

sql = 'insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) \
         values(%s, %s, %s, %s, %s, %s, %s, %s)'
data = ('mango', '9999', '마앙고', '010-9999-9999', 'mango@mango.com', '남자', \
        '001010', '3722901')

cur.execute(sql, (data))       # 실제 데이터가 바뀌지 않음
db.commit()                    # commit까지 해야 적용

sql = 'insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) \
        values(%s, %s, %s, %s, %s, %s, %s, %s)'
data = [('berry', '0000', '배애리', '010-0000-0000', 'berry@berry.com', '여자', \
         '001010', '4231901'), \
       ('grapes', '8888', '표도르', '010-8888-8888', 'grapes@grapes.com', '여자', \
         '008018', '4461101')]
       
cur.executemany(sql, data)    # executemany : 한번에 여러 데이터 변경
db.commit()

 

※ 문제1 : 회원가입 프로그램 만들기

• 단, 중복 데이터 또는 잘못된 데이터로 인한 오류시 '다시 입력하세요' 라는 메시지와 함께 다시 등록
• 단, 회원 가입 후 '추가로 가입하시겠습니까? (Y/N)' 을 입력받아 추가로 입력할 수 있도록

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

while True:
    try:
        userid = input('아이디 입력 : ')
        userpw = input('비밀번호 입력 : ')
        name = input('이름 입력 : ')
        hp = input('전화번호 입력 : ')
        email = input('이메일 입력 : ')
        gender = input('성별 입력 : ')
        ssn1 = input('주민등록번호 앞자리 입력 : ')
        ssn2 = input('주민등록번호 뒷자리 입력 : ')
        
        sql = 'insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) \
               values(%s, %s, %s, %s, %s, %s, %s, %s)'
        data = (userid, userpw, name, hp, email, gender, ssn1, ssn2)
        cur.execute(sql, (data)) 
        db.commit()
        print('가입되었습니다')
        
        add = input('추가로 가입하시겠습니까? (Y/N): ')
        if add.lower() == 'n':
            print('프로그램을 종료합니다.')
            break
            
    except Exception as e:
        print(e)
        print('다시 입력하세요')
        
cur.close()
db.close()

 

3. 데이터 수정하기

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

sql = 'update member set zipcode=%s, address1=%s, address2=%s, address3=%s where userid=%s'
data = ('12345','서울', '서초구', '양재동', 'banana')
result = cur.execute(sql, data)
db.commit()

if result > 0:
    print('수정되었습니다.')
else:
    print('에러')
    
cur.close()
db.close()          # 결과 : 수정되었습니다.

 

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

sql = 'delete from member where userid=%s'
result = cur.execute(sql, ('kiwi',))
db.commit()

if result > 0:
        print('탈퇴되었습니다')
else:
        print('오류')
        
cur.close()
db.close()               # 결과 : 탈퇴되었습니다.

 

※ 문제2 : 로그인 프로그램 만들기

• 아이디, 비밀번호가 맞을 경우 '로그인 되었습니다', 틀린 경우 '아이디 또는 비밀번호 확인' 출력

db = MySQLdb.connect('localhost', 'root', '1234', 'kdt')
cur = db.cursor()
userid = input('아이디 입력 :')
userpw = input('비밀번호 입력 :')

sql = 'select userid 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()

로그인 성공시

로그인 실패시