파이썬과 MySQL 연동하기

mysqlclient

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

!pip install mysqlclient

 

•  mysqlclient 설치하는 파일매니저 코드

import MySQLdb

MySQL 접속하기

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

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

Cursor 생성하기

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

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

 

6 -> 튜플단위로 반환

SQL문 결과 가져오기

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

data = cur.fetchall()
print(data)

fetchall(): 모든 tuple을 가져옴

data = cur.fetchall()
print(data)

 

() -> 한번 더 fetchall 할 시, 검색결과 나오지 않음. 다음값 반환(빈 값)

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

6

fetchone(): 한번에 하나의 tuple만 가져옴

fetchone()을 이용하여 member 테이블의 모든 테이블을 튜플로 출력해보자

cur.execute(sql)

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

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, 성별: XXX

cur.execute(sql)

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

cursor와 Connection 닫아주기

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

데이터 삽입하기

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

cur.execute(sql, data)

db.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', '4212345'), ('grape', '8888', '표도르', '010-8888-8888', 'grapes@grapes.com', '남자', '001010', '3212345')

cur.executemany(sql, data)
db.commit()

 

• 회원가입 프로그램을 만들어보자
• 단, 중복 데이터 또는 잘못된 데이터로 인한 오류시 "다시 입력하세요"라는 메세지와 함께 다시 등록
• 단, 회원 가입 후 "추가로 가입하시겠습니까?(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('주민번호 뒷자리를 입력하세요: ')
        zipcode = input('우편번호를 입력하세요: ')
        address1 = input('주소를 입력하세요: ')
        address2 = input('상세주소를 입력하세요: ')
        address3 = 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()
        print('가입되었습니다')
        yn = input('추가로 가입하시겠습니까?(Y/N): ')
        if yn.lower() == 'n':
            print('프로그램을 종료합니다')
            break
    except Exception as e:
        print('다시 입력하세요')
cur.close()
db.close()

데이터 수정하기

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

 

 

• 로그인 프로그램을 작성해보자
• 아이디, 비밀번호가 맞을 경우 "로그인 되었습니다", 틀린 경우 "아이디 또는 비밀번호를 확인하세요"라고 출력

import MySQLdb

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