[14일차-2] MySQL 연동

# MySQL 연동 (DB 연결하기)

1. mysqlclient 설치

• 파이썬에서 MySQL 데이터베이스와 상호작용하기 위해 사용하는 공식 라이브러리
• PyMySQL, mysqlclient 를 가장 많이 사용 (SQLAlchemy를 실무에서는 가장 많이 사용)
  -> 사용법이 비슷하나 mysqlclient가 속도상으로도 유리하기 때문에 권장
• venv에서 아래 명령어 입력하여 설치

python -m pip install mysqlclient

• ipynb 파일에서 import 되는지 확인 (설치명과 모듈명이 다름 주의)

import MySQLdb

 

2. MySQL 접속

MySQLdb.connect(host='IP주소(or도메인주소)', user='사용자명', password='비밀번호', db='데이터베이스명')

# db 연결
# 객체를 받을 변수(db) 선언해서 받아오기
db = MySQLdb.connect(host='localhost', user='root', password='1234', db='ai')
db		

# <_mysql.connection open to 'localhost' at 000002375B69B430>
# db를 조회해보면 connection 객체임을 알 수 있다

 

3. cursor 생성

• 하나의 데이터베이스 connection에 대해 독립적으로 SQL문을 실행할 수 있는 작업 환경을 제공하는 객체
• 하나의 connection에 동시에 한 개의 cursor만 생성 가능
  (변수 여러 개를 사용한다고 cursor가 여러 개인 것이 아님. 같은 경로를 사용)
• cursor를 통해 SQL문을 실행하면 실행 결과를 튜플 단위로 반환
• execute안에 sql문을 변수에 넣어서 사용 가능
  ex) cur.execute(sql, date)
• 1개 값을 튜플로 넣을 때 튜플 형식 지키기
  ex) cur.execute(sql, (userid,))

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

# SQL문 실행
cur.execute("select * from member") 	# 7

# 7개의 행이 반환된 것을 확인 가능
# SQL문에 세미콜론 안써도 됨

 

4. SQL문의 결과 반환 - 튜플 형태

• cursor는 기본적으로 tuple형 결과를 반환
• 결과 반환 방법 2가지
  1. fetchall() : 한 번에 모든 tuple을 가져옴. 검색 결과가 매우 많다면 메모리 오버헤드 발생할 수 있음
  2. fetchone() : 한 번에 하나의 tuple을 가져옴. 다시 fetchone() 메서드를 호출하면 다음 데이터를 가져옴
• 마지막 데이터까지 도달하면 데이터가 나오지 않음 (이미 데이터 한 바퀴를 다 돌아서)
  -> 그럴 땐 다시 execute 해주면 됨
  (fetchall() 또는 fetchone()으로 마지막 데이터를 가져온 오는 경우)

 data = cur.fetchall()
 data

cur.execute("select userid, name, gender from member")

# 실행할 때마다 다음 데이터로 넘어감
row = cur.fetchone()
row

# SQL문을 변수에 넣어 사용해도 결과는 같음
sql = "select userid, name, gender, hp from member"
cur.execute(sql)

row = cur.fetchone()
row

• 반복문으로 데이터를 가져올 수 있음

sql = "select userid, name, gender, hp from member"
cur.execute(sql)

while True:
	row = cur.fetchone()	# 데이터를 하나씩 가져오는데
    if row:			# 데이터가 있으면 출력
    	print(row)
    else:			# 데이터가 없으면 반복문 빠져나가기
    	break

 

5. SQL문의 결과 반환 - 딕셔너리 형태

• 커서 생성 시 괄호 안에 MySQLdb.cursors.DictCursor 추가
• 튜플로 반환하면 인덱스가 아닌 컬럼명으로 접근이 가능하여 편리

cursor(MySQLdb.cursors.DictCursor)

sql = "select userid, name, gender, hp from member"
cur = db.cursor(MySQLdb.cursors.DictCursor)
cur.execute(sql)

result = cur.fetchall()
result

# 결과
({'userid': 'apple', 'name': '김사과', 'gender': '여자', 'hp': '010-1111-1111'},
 {'userid': 'banana', 'name': '반하나', 'gender': '여자', 'hp': '010-2222-2222'},
 {'userid': 'orange', 'name': '오렌지', 'gender': '남자', 'hp': '010-3333-3333'},
 {'userid': 'melon', 'name': '이메론', 'gender': '남자', 'hp': '010-4444-4444'},
 {'userid': 'cherry', 'name': '채리', 'gender': '여자', 'hp': '010-5555-5555'},
 {'userid': 'berry', 'name': '배애리', 'gender': '여자', 'hp': '010-6666-6666'},
 {'userid': 'avocado', 'name': '안가도', 'gender': '남자', 'hp': '010-7777-7777'},
 {'userid': 'mango', 'name': '마앙고', 'gender': '남자', 'hp': '010-8888-8888'},
 {'userid': 'pear', 'name': '배철수', 'gender': '남자', 'hp': '010-9999-9999'},
 {'userid': 'peach', 'name': '복숭아', 'gender': '남자', 'hp': '010-0000-0000'})

• 위 코드를 사용하여 데이터를 파악하기 쉽게 출력해보자

sql = "select userid, name, gender, hp from member"
cur = db.cursor(MySQLdb.cursors.DictCursor)
cur.execute(sql)        

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

# 결과
아이디:apple, 이름:김사과, 성별:여자, 전화번호:010-1111-1111
아이디:banana, 이름:반하나, 성별:여자, 전화번호:010-2222-2222
아이디:orange, 이름:오렌지, 성별:남자, 전화번호:010-3333-3333
아이디:melon, 이름:이메론, 성별:남자, 전화번호:010-4444-4444
아이디:cherry, 이름:채리, 성별:여자, 전화번호:010-5555-5555
아이디:berry, 이름:배애리, 성별:여자, 전화번호:010-6666-6666
아이디:avocado, 이름:안가도, 성별:남자, 전화번호:010-7777-7777
아이디:mango, 이름:마앙고, 성별:남자, 전화번호:010-8888-8888
아이디:pear, 이름:배철수, 성별:남자, 전화번호:010-9999-9999
아이디:peach, 이름:복숭아, 성별:남자, 전화번호:010-0000-0000

 

6. cursor와 connection 닫기

• DB 사용이 끝난 후 연결을 끊지 않으면 계속 사용하는 줄 알고 -> 메모리를 차지하게 되어 -> 속도가 느려질 수 있음

# cursor 연결 끊기
cur.close()

# db 연결 끊기
db.close()

---

3. DB 데이터 삽입하기

• 단일 데이터 삽입

1. MySQL 연결
2. 커서 생성
3. insert문 작성해서 변수에 저장
4. insert문에 들어갈 데이터를 튜플 형태로 변수에 저장
5. 커서로 sql문 실행
6. db에 반영

db = MySQLdb.connect(host='localhost', user='root', password='1234', db='ai')
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', '8888', '마앙고', '010-8888-8888', 'mango@mango.com', '남자', '000000', '0000000')
cur.execute(sql, data)
db.commit()

 

• 여러 데이터 삽입

1. insert문에 들어갈 여러 건의 데이터를 튜플로 만들고 튜플들을 리스트로 묶어서 저장
2. executemany()로 리스트 안의 튜플을 순서대로 %s에 매핑하여 여러 건 한번에 insert

sql = "insert into member (userid, userpw, name, hp, email, gender, ssn1, ssn2) values (%s, %s, %s, %s, %s, %s, %s, %s)"
# 튜플 여러 개를 리스트로 묶어 사용
data = [('pear', '9999', '배철수', '010-9999-9999', 'pear@pear.com', '남자', '000000', '0000000'),
        ('peach', '0000', '복숭아', '010-0000-0000', 'peach@peach.com', '남자', '000000', '0000000')]
# 여러 데이터 넣을 때 사용
cur.executemany(sql, data)
db.commit()

---

4. 예제

1. 회원가입 프로그램

1. MySQL 연결 및 커서 생성
2. while문 안에 try-except문으로 잘못된 입력 처리
3. while문으로 userid부터 gender까지 12개의 컬럼에 대해 input 작성
4. sql문 작성해서 변수에 저장
5. input한 12개의 변수를 튜플로 묶어 저장
6. 커서로 sql문 실행
7. db에 반영
8. '가입되었습니다' 문구 출력
9. 추가로 가입하는지 여부 확인 후,아니오 선택 시 프로그램 종료, 네 선택 시 while문 반복
10. 커서 및 db 연결 제거

더보기

# db와 cursor 생성
db = MySQLdb.connect(host='localhost', user='root', password='1234', db='ai')
cur = db.cursor()

while True:
    try:
        userid = input('아이디를 입력하세요: ')
        userpw = input('비밀번호를 입력하세요: ')
        name = input('이름을 입력하세요: ')
        hp = input('전화번호를 입력하세요: ')
        email = input('이메일을 입력하세요: ')
        ssn1 = input('주민등록번호 앞자리를 입력하세요: ')
        ssn2 = input('주민등록번호 뒷자리를 입력하세요: ')
        zipcode = input('우편번호를 입력하세요: ')
        address1 = input('주소를 입력하세요: ')
        address2 = input('상세주소를 입력하세요: ')
        address3 = input('참고사항을 입력하세요: ')
        gender = input('성별을 입력하세요(남자 또는 여자): ')
        sql = "insert into member (userid, userpw, name, 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, name, hp, email, gender, ssn1, ssn2, zipcode, address1, address2, address3)
        cur.execute(sql, data)
        db.commit()     # db에 반영
        print('가입되었습니다!')
        yn = input('추가로 가입하시겠습니까? (y/n): ')
        if yn.lower() == 'n':
            print('프로그램을 종료합니다')
            break
    except Exception as e:
        print('다시 입력하세요')

cur.close()
db.close()

2. 로그인 프로그램

1. MySQL 연결 및 커서 생성
2. userid, userpw를 input 받기
3. sql문 작성해서 변수에 저장
4. input한 2개 변수를 튜플로 묶어 저장
5. 커서로 sql문 실행
6. 5번의 결과에 따라 로그인 되었는지, 아닌지 조건문 작성
7. 커서 및 db 연결 제거

더보기

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

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

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

3. 회원정보 수정 프로그램

1. MySQL 연결 및 커서 생성
2. while문 안에 try-except문으로 잘못된 입력 처리
3. while문으로 비밀번호를 제외한 모든 정보에 대해 input 작성
   (userid를 입력받아 아이디에 해당하는 name~gender 컬럼을 수정)
4. sql문 작성 후 변수에 저장
5. sql문에 매핑되도록 튜플로 저장
6. 커서로 sql문 실행
7. db에 반영
8. 6번의 결과에 따라 변경되었는지, 아닌지 조건문 작성
9. 변경할 내용이 더 있는지 여부 확인 후, 아니오 선택 시 프로그램 종료, 네 선택 시 while문 반복
10. 커서 및 db 연결 제거

더보기

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

while True:
    try:
        userid = input('변경할 아이디를 입력하세요: ')
        name = input('이름을 입력하세요: ')
        hp = input('전화번호를 입력하세요: ')
        email = input('이메일을 입력하세요: ')
        ssn1 = input('주민등록번호 앞자리를 입력하세요: ')
        ssn2 = input('주민등록번호 뒷자리를 입력하세요: ')
        zipcode = input('우편번호를 입력하세요: ')
        address1 = input('주소를 입력하세요: ')
        address2 = input('상세주소를 입력하세요: ')
        address3 = input('참고사항을 입력하세요: ')
        gender = input('성별을 입력하세요(남자 또는 여자): ')

        sql = 'update member set name=%s, hp=%s, email=%s, ssn1=%s, ssn2=%s, zipcode=%s, address1=%s, address2=%s, address3=%s, gender=%s where userid=%s'
        data = (name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, gender, userid)
        result = cur.execute(sql, data)
        db.commit()

        # 몇개가 update 되었는지 반환되기 때문에 그걸로 0보다 크면 변경되었다, 아니면 에러
        if result > 0:
            print('변경되었습니다')
        else:
            print('에러!')

        yn = input('변경할 내용이 더 있으십니까?(y/n): ')
        if yn.lower() == 'n':
            print('프로그램을 종료합니다')
            break

    except Exception as e:
        print('다시 입력하세요')

cur.close()
db.close()

4. 회원 탈퇴 프로그램

1. MySQL 연결 및 커서 생성
2. 탈퇴할 아이디를 입력 받음
3. sql문 작성 후 변수에 저장
4. 커서로 sql문 실행
5. db에 반영
6. 5번의 결과에 따라 탈퇴되었는지, 아닌지 조건문 작성
7. 커서 및 db 연결 제거

더보기

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

userid = input("탈퇴할 아이디를 입력하세요: ")

sql= 'delete from member where userid=%s'
result = cur.execute(sql, (userid,))        # 1개 값을 튜플로 넣을 때 쉼표
db.commit()

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

cur.close()
db.close()

---

 

* cur.execute() 시 세미콜론 안써도 되는 이유 :

MySQL에서는 한 문장이 끝났다는 의미로 세미콜론이 필요했지만, 파이썬에서는 한 문장씩 사용하기 때문에 없어도 된다

 

* DB만 끊지 않고 cursor와 db를 모두 끊는 이유 : 

- 커서와 DB연결(connection)은 서로 다른 리소스이기 때문

- cursor : 쿼리를 실행하고 결과를 가져오는 객체 (메모리 점유)

- db(connection) : 데이터베이스와 실제 네트워크 연결

- db를 닫으면 커서도 같이 닫히지만 경우에 따라 경고나 에러가 발생할 수 있음

- 따라서 커서를 먼저 닫고 db를 끊기

 

* commit() : DML(insert, update, delete)은 반드시 커밋해야 DB에 반영

 

* select, update, delete는 execute하면 몇 개를 조회, 수정, 삭제 했는지 개수를 반환

 

* sql문 작성해서 변수에 저장 시, f-string으로도 가능하지만 보안상 좋지 않음

(SQL Injection 공격을 받을 수 있으니 사용을 권장하지 않음)

- 입력 폼에 악성 SQL 구문을 주입하여 DB 조작하는 공격

- 사용자 개인 정보 탈취, 관리자 권한 우회, DB 삭제 및 변조

ex. sql = f"select idx from member where userid={userid} and userpw={userpw}"