MySQL과 파이썬 연동

1. mysqlclient

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

!pip install mysqlclient

mtsqlclient 모듈 설치하기

Requirement already satisfied: mysqlclient in c:\users\administrator\appdata\local\programs\python\python310\lib\site-packages (2.2.4)



[notice] A new release of pip is available: 23.0.1 -> 24.0
[notice] To update, run: python.exe -m pip install --upgrade pip

import MySQLdb

1-1. MySQL 접속하기

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

db = MySQLdb.connect(host='localhost', user='root', password='****', db='KDT')

# 실행결과
<_mysql.connection open to 'localhost' at 000001E3DEB452A0>

1-2. cursor 생성하기

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

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

# 실행결과
11

실행이 되면 몇개의 데이터가 있는지 반환

1-3. SQL문 결과 가져오기

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

data = cur.fetchall()
print(data)

# 출력
(('apple', '김사과', '010-1111-1111', '여자'), ('avocado', '안가도', '010-5555-5555', '남자'), ('banana', '반하나', '010-2222-2222', '여자'), ('berry', '배애리', '010-0000-0000', '여자'), ('cherry', '채애리', '010-6666-6666', '여자'), ('doorian', '두리안', '010-1234-1546', '여자'), ('grapes', '표도르', '010-8888-8888', '남자'), ('kiwi', 'asdf1239864', '', ''), ('mango', '마앙고', '010-9999-9999', '남자'), ('melon', '이메론', '010-4444-4444', '남자'), ('orange', '오렌지', '010-3333-3333', '남자'))

data = cur.fetchall()
print(data)

데이터가 이미 출력이 되었기 때문에 아무것도 출력이 되지 않는다.

 

 

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

11

다시 execute를 해주면 데이터를 다시가져올수 있다

row = cur.fetchone()
print(row)

# 출력
('apple', '김사과', '010-1111-1111', '여자')

row = cur.fetchone()
print(row)

# 출력
('avocado', '안가도', '010-5555-5555', '남자')

row = cur.fetchone()
print(row)

# 출력
('banana', '반하나', '010-2222-2222', '여자')

출력을 할때마다 다음 데이터가 나오는걸 볼수있다.

# fetchone()을 이용하여 member 테이블의 모든 데이터를 튜플로 출력
# 나의 풀이
row = cur.execute(sql)
tu = ()
for i in range(row):
    tu += cur.fetchone()
print(tu)

# 출력
('apple', '김사과', '010-1111-1111', '여자', 'avocado', '안가도', '010-5555-5555', '남자', 'banana', '반하나', '010-2222-2222', '여자', 'berry', '배애리', '010-0000-0000', '여자', 'cherry', '채애리', '010-6666-6666', '여자', 'doorian', '두리안', '010-1234-1546', '여자', 'grapes', '표도르', '010-8888-8888', '남자', 'kiwi', 'asdf1239864', '', '', 'mango', '마앙고', '010-9999-9999', '남자', 'melon', '이메론', '010-4444-4444', '남자', 'orange', '오렌지', '010-3333-3333', '남자')

# 강사님 풀이
db = MySQLdb.connect(host='localhost', user='root', password='1234', db='KDT')
cur = db.cursor()
sql = 'select userid, name, hp, gender from member'
cur.execute(sql)
while True:
    row = cur.fetchone()
    if row:
        print(row)
    else:
        break

# 출력
('apple', '김사과', '010-1111-1111', '여자')
('avocado', '안가도', '010-5555-5555', '남자')
('banana', '반하나', '010-2222-2222', '여자')
('berry', '배애리', '010-0000-0000', '여자')
('cherry', '채애리', '010-6666-6666', '여자')
('grapes', '표도르', '010-8888-8888', '남자')
('mango', '마앙고', '010-9999-9999', '남자')
('melon', '이메론', '010-4444-4444', '남자')
('orange', '오렌지', '010-3333-3333', '남자')

1-4. dict 형태로 결과를 반환하기

• cusor(MySQLdb.curors.DictCursor)

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

# 출력
({'userid': 'apple', 'name': '김사과', 'hp': '010-1111-1111', 'gender': '여자'}, {'userid': 'avocado', 'name': '안가도', 'hp': '010-5555-5555', 'gender': '남자'}, {'userid': 'banana', 'name': '반하나', 'hp': '010-2222-2222', 'gender': '여자'}, {'userid': 'berry', 'name': '배애리', 'hp': '010-0000-0000', 'gender': '여자'}, {'userid': 'cherry', 'name': '채애리', 'hp': '010-6666-6666', 'gender': '여자'}, {'userid': 'doorian', 'name': '두리안', 'hp': '010-1234-1546', 'gender': '여자'}, {'userid': 'grapes', 'name': '표도르', 'hp': '010-8888-8888', 'gender': '남자'}, {'userid': 'kiwi', 'name': 'asdf1239864', 'hp': '', 'gender': ''}, {'userid': 'mango', 'name': '마앙고', 'hp': '010-9999-9999', 'gender': '남자'}, {'userid': 'melon', 'name': '이메론', 'hp': '010-4444-4444', 'gender': '남자'}, {'userid': 'orange', 'name': '오렌지', 'hp': '010-3333-3333', 'gender': '남자'})

DictCurSor를 통해 데이터가 키값으로 출력된다.

 

 

문제

member 테이블의 데이터를 하나의 row씩 dick으로 가져와 아래와 같이 출력

아이디: XXX, 이름:XXX, 전화번로:XXX, 성별:XXX

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

11

# 나의풀이
len = cur.execute(sql) 
# execute를 하면 데이터의 갯수가 나오기때문에 이를 통해 갯수만큼 반복을 해준다
for i in range(len):
    result = cur.fetchone() 
    print(f'아이디: {result["userid"]}, 이름: {result["name"]}, 전화번호: {result["hp"]}, 성별: {result["gender"]}')

# 출력
아이디: apple, 이름: 김사과, 전화번호: 010-1111-1111, 성별: 여자
아이디: avocado, 이름: 안가도, 전화번호: 010-5555-5555, 성별: 남자
아이디: banana, 이름: 반하나, 전화번호: 010-2222-2222, 성별: 여자
아이디: berry, 이름: 배애리, 전화번호: 010-0000-0000, 성별: 여자
아이디: cherry, 이름: 채애리, 전화번호: 010-6666-6666, 성별: 여자
아이디: doorian, 이름: 두리안, 전화번호: 010-1234-1546, 성별: 여자
아이디: grapes, 이름: 표도르, 전화번호: 010-8888-8888, 성별: 남자
아이디: mango, 이름: 마앙고, 전화번호: 010-9999-9999, 성별: 남자
아이디: melon, 이름: 이메론, 전화번호: 010-4444-4444, 성별: 남자
아이디: orange, 이름: 오렌지, 전화번호: 010-3333-3333, 성별: 남자

딕셔너리의 키를 통해 값을  호출 하였다.

# 강사님 풀이
cur = db.cursor(MySQLdb.cursors.DictCursor)
sql = 'select userid, name, hp, gender from member'
cur.execute(sql)
while True:
    row = cur.fetchone()
    if row:
        print(f"아이디: {row['userid']}, 이름: {row['name']}, 전화번호: {row['hp']}, 성별: {row['gender']}")
    else:
        break

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

1-5. Cursor와 Connection 닫아주기

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

커서를 사용중이면 sql에서 동시에 수정할수 없다

2. 데이터 삽입

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

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

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

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

입력할 데이터 만큼 %s 를 써주고 해당 필드 순서대로 값을 넣어주어야 한다

문제

• 회원가입 프로그램을 만들어보자
• 단, 중복 데이터 또는 잘 못된 데이터로 인한 오류시 "다시 입력하세요"라는 메세지와 함께 다시 등록
• 단, 회원 가입 후 "추가로 가입하시겠습니까? (Y/N)을 입력받아 추가로 입력할 수 있도록"

# 나의 풀이
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 = []
while True:
    try:
        id = input("아이디를 입력하세요: ")
        pw = input("비밀번호를 입력하세요: ")
        name = input("이름을 입력하세요: ")
        hp = input("핸드폰번호를 입력하세요: ")
        email = input("이메일을 입력하세요: ")
        gender = input("성별을 입력하세요: ")
        ssn1 = input("주민번호 앞자리를 입력하세요: ")
        ssn2 = input("주민번호 뒷자리를 입력하세요: ")
        zipcode = input("우편번호를 입력하세요: ")
        address1 = input("도시를 입력하세요: ")
        address2 = input("구/동을 입력하세요: ")
        address3 = input("상세주소를 입력하세요: ")
        data = (id, pw, name, hp, email, gender, ssn1, ssn2, zipcode, address1, address2, address3)
        cur.execute(sql, data)
        db.commit()
    except:
        print('다시 입력하세요')
    choice = input("추가로 가입하시겠습니다?(Y/N)")

    if choice.upper() == 'N':
        break
cur.close()
db.close()

# 강사님 풀이
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()

3. 데이터 수정하기

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

# 출력
수정되었습니다

4. 데이터 삭제하기

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

sql = 'delete from member where userid=%s'
result = cur.execute(sql, ('doorian',))
db.commit()

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

cur.close()

탈퇴되었습니다

문제

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

# 나의 풀이
db = MySQLdb.connect('localhost', 'root', '1234', 'kdt')
cur = db.cursor()

while True:
    id = input("아이디를 입력하세요:")
    pw = input("비밀번호를 입력하세요:")
    sql = 'SELECT USERID FROM MEMBER WHERE USERID=%s AND USERPW=%s'
    data = (id,pw)
    result = cur.execute(sql, data)
    if result:
        print('로그인 되었습니다')
        break
    else:
        print('아이디 또는 비밀번호를 확인하세요')
cur.close()

# 출력
아이디를 입력하세요: doorian
비밀번호를 입력하세요: 1111


아이디 또는 비밀번호를 확인하세요


아이디를 입력하세요: apple
비밀번호를 입력하세요: 1111


로그인 되었습니다

import MySQLdb

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

userid = input("아이디를 입력하세요:")
userpw = input("비밀번호를 입력하세요:")
sql = 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
비밀번호를 입력하세요: 1111


로그인 되었습니다

MySQL의 AND 함수를 통해 해당 데이터들이 테이블에 있는지 검사해서 결과를 출력한다