(27)Python_파이썬과 MySQL연동

시작하기에 앞서 MySQL의 간단한 공부를 하고 오시면 좋습니다.

MySQL 시작 : https://jm-rograming.tistory.com/27

(1)MySQL_DBMS

 

1. mysqlclient

• 파이썬에서는 MySQL 서버와 통신할 수 있는 파이썬용 데이터베이스 커넥터에 종류가 여러가지 있음
• PyMySQL, mysqlclient를 가장 많이 사용함
• 사용법은 비슷하나 속도가 빠른 mysqlclient를 권장하고 있음
• import를 위해 해준다. (!pip install mysqlclient)

# host: IP주소, localhosh, 127.0.0.1
# user: 유저, root
# password: 비밀번호, 1234
# DB: 데이터베이스, test

# db = MySQLdb.connect(localhost','root','1234','test')

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

2. cursor 생성하기

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

cur = db.cursor()
sql = 'select userid , username, hp, email, gender from member'
cur.execute(sql)
# cur.execute(''select userid , username, hp, email, gender from member'')

결과: 테이블의 raw의 수가 6개가 있기때문에 결과는 6이 나온다.

3. SQL문 결과 가져오기

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

row = cur.fetchall()
print(row)

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

# fetchone()을 이용하여 루프를 돌면서 모든 데이터를 출력하기
cur.execute(sql)

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

# cursor에 dictionary 형식으로 row를 유지하도록 내부 타입을 명시
cur = db.cursor(MySQLdb.cursors.DictCursor) # 딕셔너리의 각각의 요소를 받아오고 싶을때 사용
cur.execute(sql)

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

4. Cursor와 Connection 닫아주기

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

5. 데이터 삽입하기

• 단일 튜플을 삽입할 때에는 execute()를 사용

sql = "insert into member(userid,userpw,username,hp,email,gender,ssn1,ssn2) values(%s,%s,%s,%s,%s,%s,%s,%s)"
data = ('avocado','0000','안카도','010-0000-0000','avocado@email','남자','000000','0000000')
cur.execute(sql, data) # sql을 실행하면서 값을 data를 넣어준다 %s에
db.commit()

• 다중 튜플을 삽입할 때에는 executemany()를 사용한다.

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','mango@email','남자','000000','0000000'),
        ('peach','0000','피이치','010-0000-0000','peach@email','여자','000000','0000000')]
cur.executemany(sql, data)
db.commit()

문제

• '회원가입'프로그램을 만들어보자
• 회원가입
• 아이디를 입력하세요:
• 비밀번호를 입력하세요:
• 이름을 입력하세요:
• 휴대폰번호를 입력하세요:
• 이메일을 입력하세요:
• 성별을 입력하세요
• 주민번호 앞자리를 입력하세요:
• 주민번호 뒷자리를 입력하세요:
• 우편번호를 입력하세요:
• 주소를 입력하세요:
• 상세주소를 입력하세요:
• 참고사항을 입력하세요:

import MySQLdb

db = MySQLdb.connect('localhost','root','1234','test')
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()

• DB 테이블에도 확인을 해보면 잘 들어간 것을 확인할 수 있을 것이다.

6. 데이터 수정하기

import MySQLdb

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

 

sql = "update member set zipcode = '11132', address1 = '서울시', address2 = '강남구', address3 = '역삼동' where userid = 'avocado'"
result = cur.execute(sql)
db.commit()
print(result)  # 결과 1(update를 성공했기 때문에 update할 것이 없으면 0)

• result값이 1이면 수정이 된 것이고 0이면 안됐기때문에 이런 프로그래밍도 할 수 있다.

sql = "update member set zipcode = '22222', address1 = '서울시', address2 = '강남구', address3 = '역삼동' where userid = 'avocado'"
result = cur.execute(sql)
db.commit()

if result > 0:
    print('수정되었습니다.')
else:
    print('에러!')

문제

• 로그인 프로그램을 작성해보자
• 아이디를 입력하세요: apple
• 비밀번호를 입력하세요: 1111
• 아이디를 입력하세요: apple
• 비밀번호를 입력하세요: 1234
• 아이디 또는 비밀번호가 틀렸습니다.

import MySQLdb

db = MySQLdb.connect('localhost','root','1234','test')
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()

• DB테이블의 userid 값과 userpw값을 비교후 참이면 로그인이 되고 참이 아닐경우 틀렸습니다를 출력

7. 데이터 삭제하기

• 삭제를 성공하면 result값이 0보다 큼으로 탈퇴되었습니다를 출력
• 삭제를 실패하거나 할 요소가 없을 경우 오류를 출력

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

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

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