(파이썬) 파이썬과 MySQL 연동

 

1. mysqlclient

• pyMySQL은 pythone으로 만들어진 라이브러리며 자료가 많지만 속도적으로 c언어로 만들어진 mysqlclient 클라이언트가 더 빠르다.
• 파이썬에서는 MySQL 서버와 통신할 수 있는 파이썬용 데이터베이스 커넥터의 종류가 여러가지 있음
• PyMySQL, mysqlclient를 가장 많이 사용함
• 사용법은 비슷하나 속도가 빠른 mysqlclient를 권장하고 있음

 

In [1]:

!pip install mysqlclient

 

 

Collecting mysqlclient
  Downloading mysqlclient-2.1.1-cp38-cp38-win_amd64.whl (178 kB)
Installing collected packages: mysqlclient
Successfully installed mysqlclient-2.1.1

 

WARNING: You are using pip version 20.2.1; however, version 23.0.1 is available.
You should consider upgrading via the 'c:\users\acer\appdata\local\programs\python\python38\python.exe -m pip install --upgrade pip' command.

 

In [76]:

import MySQLdb

 

In [3]:

# 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문을 실행하면 실행결과를 튜플 단위로 반환

 

In [4]:

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')

 

Out[4]:

6

 

 

3. SQL문 결과 가져오기

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

 

In [6]:

row = cur.fetchall()
print(row)

 

 

(('apple', '김사과', '01012345678', 'apple@gmail.com', '여자'), ('banana', '반하나', '01087654321', 'banana@naver.com', '여자'), ('berry', '배애리', '01000000000', 'berry@gmail.com', '여자'), ('grapes', '박포도', '01097979797', 'grapes@gmail.com', '남자'), ('melon', '이메론', '01025809631', 'melon@gmail.com', '여자'), ('orange', '오렌지', '01011111111', 'orange@gmail.com', '남자'))

 

In [8]:

cur.execute(sql)

row = cur.fetchone()
print(row)

 

 

('apple', '김사과', '01012345678', 'apple@gmail.com', '여자')

 

In [9]:

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

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

 

 

('apple', '김사과', '01012345678', 'apple@gmail.com', '여자')
('banana', '반하나', '01087654321', 'banana@naver.com', '여자')
('berry', '배애리', '01000000000', 'berry@gmail.com', '여자')
('grapes', '박포도', '01097979797', 'grapes@gmail.com', '남자')
('melon', '이메론', '01025809631', 'melon@gmail.com', '여자')
('orange', '오렌지', '01011111111', 'orange@gmail.com', '남자')

 

In [15]:

# 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

 

 

아이디: apple, 이름: 김사과, 전화번호: 01012345678, 이메일: apple@gmail.com, 성별: 여자
아이디: banana, 이름: 반하나, 전화번호: 01087654321, 이메일: banana@naver.com, 성별: 여자
아이디: berry, 이름: 배애리, 전화번호: 01000000000, 이메일: berry@gmail.com, 성별: 여자
아이디: grapes, 이름: 박포도, 전화번호: 01097979797, 이메일: grapes@gmail.com, 성별: 남자
아이디: melon, 이름: 이메론, 전화번호: 01025809631, 이메일: melon@gmail.com, 성별: 여자
아이디: orange, 이름: 오렌지, 전화번호: 01011111111, 이메일: orange@gmail.com, 성별: 남자

 

 

4. Cursor와 Connection 닫아주기

 

In [22]:

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

 

 

5. 데이터 삽입하기

 

In [18]:

sql = "insert into member (userid, userpw, username, hp, email, gender, ssn1, ssn2) values (%s, %s, %s, %s, %s, %s, %s, %s)"
data = ('avocado', '0000', '안카도', '01000000000', 'avocado@avocado.com', '남자', '000000', '0000000')
cur.execute(sql, data)
db.commit()

 

In [20]:

sql = "insert into member (userid, userpw, username, hp, email, gender, ssn1, ssn2) values (%s, %s, %s, %s, %s, %s, %s, %s)"
data = [('mango', '0000', '마앙고', '01000000000', 'mango@mango.com', '남자', '000000', '0000000'), ('peach', '0000', '피이치', '01000000000', 'peach@peach.com', '여자', '000000', '0000000')]
cur.executemany(sql, data)
db.commit()

 

 

문제

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

************** 회원가입 ********************

• 아이디를 입력하세요:
• 비밀번호를 입력하세요:
• 이름을 입력하세요:
• 휴대폰번호를 입력하세요:
• 이메일을 입력하세요:
• 성별을 입력하세요:
• 주민번호 앞자리를 입력하세요:
• 주민번호 뒷자리를 입력하세요:
• 우편번호를 입력하세요:
• 주소를 입력하세요:
• 상세주소를 입력하세요:
• 참고사항을 입력하세요:

가입되었습니다.

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

 

In [21]:

import MySQLdb
db = MySQLdb.connect(host='localhost', user='root', password='1234', db='kdt')
cur = db.cursor(MySQLdb.cursors.DictCursor)
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)"

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

 

 

아이디를 입력하세요: apple
비밀번호를 입력하세요: 1234
이름을 입력하세요: 김시과
휴대폰번호를 입력하세요: 01012341234
이메일을 입력하세요: apple@apple.com
성별을 입력하세요: 여자
주민번호 앞자리를 입력하세요: 000000
주민번호 뒷자리를 입력하세요: 0000000
우편번호를 입력하세요: 12345
주소를 입력하세요: 서울시 은평구
상세주소를 입력하세요: 응암동
참고사항을 입력하세요: 응암아파트 101호
에러가 발생!
아이디를 입력하세요: lhj
비밀번호를 입력하세요: 1234
이름을 입력하세요: 이현준
휴대폰번호를 입력하세요: 01012341234
이메일을 입력하세요: lhj@naver.com
성별을 입력하세요: 남자
주민번호 앞자리를 입력하세요: 971229
주민번호 뒷자리를 입력하세요: 1234567
우편번호를 입력하세요: 12345
주소를 입력하세요: 서울시 서대문구
상세주소를 입력하세요: 홍은2동
참고사항을 입력하세요: 아파트 101호

 

In [28]:

import MySQLdb
db = MySQLdb.connect(host='localhost', user='root', password='1234', db='kdt')
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 = "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('다시 입력하세요')
        
cur.close()
db.close()

 

 

아이디를 입력하세요: apple
비밀번호를 입력하세요: 1234
이름을 입력하세요: 김사과
휴대폰번호를 입력하세요: 01011111111
이메일을 입력하세요: apple@apple.com
성별을 입력하세요: 여자
주민번호 앞자리를 입력하세요: 000000
주민번호 뒷자리를 입력하세요: 0000000
우편번호를 입력하세요: 12345
주소를 입력하세요: 서울시 강남구
상세주소를 입력하세요: 역삼동
참고사항을 입력하세요: 111-11
다시 입력하세요
아이디를 입력하세요: ruzye
비밀번호를 입력하세요: 1111
이름을 입력하세요: 류정원
휴대폰번호를 입력하세요: 01011111111
이메일을 입력하세요: ruzye@naver.com
성별을 입력하세요: 여자
주민번호 앞자리를 입력하세요: 000000
주민번호 뒷자리를 입력하세요: 0000000
우편번호를 입력하세요: 12345
주소를 입력하세요: 서울시 서초구
상세주소를 입력하세요: 양재동
참고사항을 입력하세요: 111-11
가입되었습니다.

 

 

6. 데이터 수정하기

 

In [24]:

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

 

In [28]:

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

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

 

 

에러

 

 

문제

• 로그인 프로그램을 작성해보자

 

In [30]:

import MySQLdb
db = MySQLdb.connect(host='localhost', user='root', password='1234', db='kdt')
cur = db.cursor()
sql = 'select userid from member where userid=%s and userpw=%s'

while True:
    userid = input('아이디를 입력하세요: ')
    userpw = input('비밀번호를 입력하세요: ')
    
    data = (userid, userpw)
    
    if cur.execute(sql, data):
        print('로그인 되었습니다!')
        break
    else:
        print('아이디 또는 비밀번호가 틀렸습니다.')

cur.close()
db.close()

 

 

아이디를 입력하세요: apple
비밀번호를 입력하세요: apple2
아이디 또는 비밀번호가 틀렸습니다.
아이디를 입력하세요: apple
비밀번호를 입력하세요: apple1
로그인 되었습니다!

 

In [33]:

import MySQLdb

db = MySQLdb.connect(host='localhost', user='root', password='1234', db='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()

 

 

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

 

 

7. 데이터 삭제하기

 

In [34]:

import MySQLdb

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('오류')

 

 

탈퇴되었습니다