27. 파이썬과 MySQL 연동

오늘은 jupyter notebook 에서 작성했다

 

1. mysqlclient

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

mysqlclient을 사용하기 위해 설치해준다

!pip install mysqlclient

그다음 import를 써서 MySQL데이터베이스를 불러온다

import MySQLdb

db에 연결해준다

# 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구문을 사용할 수 있게 됨
sql = 'select userid, username, hp, email, gender from member'
cur.execute(sql)
# cur.execute('select userid, username, hp, email, gender from member') # 이렇게 바로 써도 됨

>> 6  

member 테이블에 데이터가 6줄 들어있어서 6이라고 나왔다

---

3. SQL문 결과 가져오기

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

fetchall()을 사용해서 전부 가져오기

row = cur.fetchall()
print(row)

>> (('apple', '김사과', '0', 'A@mail', 'F'), ('banana', '바나나', '0', 'B@mail', 'F'), ('berry', '배애리', '010-9999-9999', 'berry@mail', 'F'), ('grapes', '포도', '0', 'G@mail', 'F'), ('melon', '이메론', '0', 'M@mail', 'M'), ('orange', '오렌지', '0', 'O@mail', 'M'))

row = cur.fetchone()  
print(row)

>> None

*위에서 이미 한번 돌았기 때문에 가져오지 않음, so 다시 실행하고 불러야 함

cur.execute(sql)   
row = cur.fetchone() 
print(row)

>>('apple', '김사과', '0', 'A@mail', 'F')

 

fetchone()을 이용해서 루프를 돌며 모든 데이터 출력하기

cur.execute(sql)

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

>> ('apple', '김사과', '0', 'A@mail', 'F') ('banana', '바나나', '0', 'B@mail', 'F') ('berry', '배애리', '010-9999-9999', 'berry@mail', 'F') ('grapes', '포도', '0', 'G@mail', 'F') ('melon', '이메론', '0', 'M@mail', 'M') ('orange', '오렌지', '0', 'O@mail', 'M')

 

cursor에 dictionary형식으로 row를 유지하도록 내부 타입을 명시

cur = db.cursor(MySQLdb.cursors.DictCursor)   # 이걸 해줘야 str 가져오기 가능
cur.execute(sql)

while True:
    row = cur.fetchone()
    if row:
#         print(row) # 딕셔너리 굳이 안뽑아도 됨!
        print(f"아이디: {row['userid']}, 이름: {row['username']}, 전화번호: {row['hp']}, 이메일: {row['email']}, 성별: {row['gender']}")
    else:
        break

>>

아이디: apple, 이름: 김사과, 전화번호: 0, 이메일: A@mail, 성별: F

아이디: banana, 이름: 바나나, 전화번호: 0, 이메일: B@mail, 성별: F

아이디: berry, 이름: 배애리, 전화번호: 010-9999-9999, 이메일: berry@mail, 성별: F

아이디: grapes, 이름: 포도, 전화번호: 0, 이메일: G@mail, 성별: F

아이디: melon, 이름: 이메론, 전화번호: 0, 이메일: M@mail, 성별: M

아이디: orange, 이름: 오렌지, 전화번호: 0, 이메일: O@mail, 성별: M

 

---

4. Cursor와 Connection 닫아주기

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

 

---

5. 데이터 삽입하기

sql = "insert into member (userid, userpw, username, hp, email, gender, ssn1, ssn2) values(%s, %s, %s, %s, %s, %s, %s, %s);"
data = ('mandarin', '0000','귤','010-1111-1111', 'cit@cit', '남자', '000000','0000000')
cur.execute(sql, data)
db.commit()

sql = "insert into member (userid, userpw, username, hp, email, gender, ssn1, ssn2) values(%s, %s, %s, %s, %s, %s, %s, %s);"
data = [('mango', '0000','마앙고','010-0000-0000', 'm@mail', '남자', '000000','0000000'),('peach', '0000','복숭아','010-0000-0000', 'peach@mail', '남자', '000000','0000000')]
cur.executemany(sql, data)
db.commit()

이렇게 해주고 MySQL workbench로 돌아가서 테이블을 확인해보면 입력이 되어있는 것을 발견할 수 있다!

 

 

 

 

문제:

회원가입 프로그램 만들기

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

 

---

6. 데이터 수정하기

db = MySQLdb.connect('localhost', 'root', '1234', 'kdt')
cur = db.cursor()
sql = "update member set zipcode='12345', address1='서울시', address2='강남구' "
result = cur.execute(sql)
db.commit()
if result > 0:
    print('수정되었습니다')
else:
    print('에러!')

 

문제:

로그인하기

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

 

---

7. 데이터 삭제하기

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

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

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