[MySQL] MySQL 연동

mysqlclient

• 파이썬에서 MySQL 데이터베이스와 상호작용하기 위해 사용하는 공식 라이브러리
• PyMySQL, mysqlclient를 가장 많이 사용함
• 사용법은 비슷하나 mysqlclient가 속도상으로 유리하기 때문에 mysqlclient를 권장함
• 실무에서는 SQLAlchemy를 가장 많이 사용함

import MySQLdb

1. MySQL 접속

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

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

# <_mysql.connection open to 'localhost' at 0000022775350F40>

2. cursor 생성

• 하나의 데이터베이스 connection에 대해 독립적으로 SQL문을 실행할 수 있는 작업환경을 제공하는 객체
• 하나의 connection에 동시에 한 개의 cursor만 생성할 수 있으며, cursor를 통해 SQL문을 실행하면 실행결과를 튜플단위로 반환해준다.
• cur.execute() : cursor를 통해 SQL문을 실행시키는 명령어

cur = db.cursor()
cur.execute('select * from member')

# 7 (데이터베이스의 행의 수를 반환해준다)

3. SQL문의 결과 반환

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

# 모든 tuple을 가져오기
data = cur.fetchall()

# 하나의 tuple만 가져오기 (다음 tuple을 가져오려면 같은 명령어 재실행)
row = cur.fetchone()

# 반복문을 통해서 데이터를 가져오는것도 가능하다
while True:
    row = cur.fetchone()
    if row:
        print(row)
    else:
        break

4. 딕셔너리 형태로 결과 반환

cursor(MySQLdb.cursors.DictCursor)

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

 

딕셔너리 형태로 반환 했으므로, key값으로 value를 나눌수도 있다.

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

5. Cursor와 Connection 닫기

불필요한 메모리 사용을 줄이기 위해 데이터베이스 사용이 끝나면 Cursor 와 Connection은 닫아줘야한다.

cur.close()
db.close()

데이터 삽입하기

회원가입 프로그램

# 데이터베이스와 상호작용을 위해 MySQL에 접속을 항상 해줘야한다.
db = MySQLdb.connect(host='localhost', user='root', password='1234', db='ai')
# SQL문 실행을 위해 cursor생성
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문을 작성해주고
        # 이때 입력값들은 보안을 위해 %s로 받아준다.
        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.commit()
        
        print('가입되었습니다!')
        yn = input('추가로 가입하시겠습니까? (y/n): ')
        if yn.lower() == 'n':
            print('프로그램을 종료합니다')
            break
    except Exception as e:
        print('다시 입력하세요')

# 항상 cursor와 connection을 닫으면서 끝내기
cur.close()
db.close()

로그인 프로그램

db = MySQLdb.connect(host='localhost', user='root', password='1234', db='ai')
cur = db.cursor()
cur.execute('select userid, userpw from member')

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


while True:
    row = cur.fetchone()
    if userid and userpw in row:
        print('로그인에 성공했습니다')
        break
    else:
        pass

cur.close()
db.close()

 

• while문을 통해 fetchone()으로 데이터를 한줄 한줄 가져오면서 값을 비교하게된다.
• 만일 데이터의 양이 많아진다면 속도면에서 비효율적이므로 사용하지 않는 코딩 방법이다.

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

• 데이터베이스에서 userid 그리고 userpw가 동시에 맞는 데이터의 idx를 불러오게 된다.
• 만일 아이디와 비밀번호를 모두 맞게 입력했다면 result값은 1이 나오게 된다.
• 입력값과 데이터베이스의 데이터를 모두 비교해볼 필요없이 로그인 여부를 알 수 있다.

회원정보 수정

# MySQL 접속과 cursor 생성
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('성별을 입력하세요 (남자 또는 여자): ')
        
        # 아이디를 기준으로 나머지 정보들을 수정해야 하므로
        # update memeber set 정보1, 정보2... where userid=%s로 update문 작성
        sql = 'update member set name=%s, hp=%s, email=%s, gender=%s, ssn1=%s, ssn2=%s, zipcode=%s, address1=%s, address2=%s, address3=%s where userid=%s'
        data = (name, hp, email, gender, ssn1, ssn2, zipcode, address1, address2, address3, userid)
        result = cur.execute(sql, data)
        db.commit()

        # 수정값이 있다면 result가 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()

회원 탈퇴

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

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

sql = 'delete from member where userid=%s'

# (userid)라고 적는다면 튜플이 아닌 문자열로 인식하기때문에 꼭 뒤에 , 를 붙여서 튜플로 만들어줘야함
data = (userid,)
result = cur.execute(sql, data)
db.commit()

if result > 0:
    print('탈퇴되었습니다')
else:
    print('아이디를 확인하세요')

cur.close()
db.close()