4. 데이터 베이스(Data Base) Day 4

1. 파이썬을 활용한 데이터베이스 연결

MYSQL client

- 파이썬에서 MYSQL 데이터베이스와 상호작용하기 위해 라이브러리

- pyMySQL, MySQLClient를 가장 많이 사용함

-사용법은 비슷하나 MySQLClient가 속도상으로 유리하기 떄문에 MySQLClient를 권장하고 있음

 

2.데이터베이스 연결 설치

!pip install mysqlclient

 

1-1. MySQL 접속하기

import MySQLdb

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

 

예시)

import MySQLdb

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

print(db)

 

1-2. Cursor 생성하기

-하나의 데이터베이스 connection에 대해 독립적으로 sql문을 실행할 수 있는 작업환경을 제공하는 객체

-하나의 connection에 동시에 한개의 cursor만 생성 할 수 있으며, cursor를 통하여 SQL문을 실행하면 실행결과를 튜플 단위로 변환

 

예)

cur = db.cursor()

cur.excecute('select userid, name, gender, hp from member')

 

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

 

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

- FetchAll() : 한번에 모든 Tuple을 가져옴. 검색 결과가 매우 많다면 메모리 오버헤드가 발생할 수 있음

- FetchOne() : 한번에 하나의 Tuple을 가져옴. 다시 FetchOne() 메서드를 호출하면 다음 데이터를 가져올 수 있음

data = cur.fetchall() print(data) member 테이블의 모든 tuple을 가져온다

 

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

-->9

- FetchOne() : 한번에 하나의 Tuple을 가져옴.

 

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

row = cur.fetchone()
print(row)

-->

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

 

1-4. while True:

           row = cur.fetchone()

         if row:

            print(row)

        else:

           break

 

 

# 행을 파이썬 반복문으로 통해 출력할 수가 있다.
while True:
    row = cur.fetchone()
    if row:
        print(row)
    else:
        break

 

1-4. 딕셔너리 형태로 결과를 반환하기

-cursor(MySQLdb.cursors.DictCursor)

 

 

#member 테이블의 모든데이터 출력해보기

({'userid': 'apple', 'name': '김사과', 'gender': '여자', 'hp': '010-1111-1111'}, {'userid': 'avocado', 'name': '안가도', 'gender': '남자', 'hp': '010-5555-5555'}, {'userid': 'banana', 'name': '바나나', 'gender': '여자', 'hp': '010-2222-2222'}, {'userid': 'cherry', 'name': '채리', 'gender': '여자', 'hp': '010-6666-6666'}, {'userid': 'grape', 'name': '표도르', 'gender': '남자', 'hp': '010-8888-8888'}, {'userid': 'kiwi', 'name': '기위', 'gender': '여자', 'hp': '010-1234-1234'}, {'userid': 'mango', 'name': '마앙고', 'gender': '남자', 'hp': '010-0000-0000'}, {'userid': 'melon', 'name': '이메론', 'gender': '남자', 'hp': '010-4444-4444'}, {'userid': 'orange', 'name': '오렌지', 'gender': '남자', 'hp': '010-3333-3333'})

 

cur = db.cursor(MySQLdb.cursord.DictCursor)

sql = 'select userid, name, gender, hp from member'

cur.execute(sql)

result = cur.fetchall()

print(result)

 

============

#문제

member 테이블의 데이터를 하나의 row씩 딕셔너리로 가져와 아래와 같이 출력해보자.

아이디: XXX

이름 : XXX

성별 : XXX

전화번호 : XXX

 

-->

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

 

1-5. Cursor와  Connection 닫기

#커서 닫기

cur.close()

#커넥션 닫기

db.close()

 

2. 데이터 삽입하기

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

sql = 'insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) value (%s, %s, %s, %s, %s, %s, %s, %s)'

data = ('mango','0000','마앙고', '010-0000-0000', 'mango@mango.com', '남자', '000000', '1111111')

cur.execute(sql, data)

de.commit()

cur.close() 
db.close()

 

## 문제2

회원가입 프로그램을 만들어보자

(단, 중복 데이터로 인한 오류시 "다시 입력하세요" 라는 오류 처리와 함께 다시 등록 할 수 있도록 함

(단, 회원 가입 후 "추가로 가입하겠습니까? (Y/N)"를 입력 받아 추가로 입력 할 수 있도록 함

 아이디를 입력하세요 :

 비밀번호를 입력하세요 :

 이름을 입력하세요 : 

 전화번호를 입력하세요 : 

 이메일을 입력하세요 : 

 성별을 입력하세요 :

 주민등록번호 앞자리를 입력하세요 : 

 주민등록 뒷자리를 입력하세요 : 

 우편번호를 입력하세요 : 

 주소를 입력하세요 : 

 상세 주소를 입력하세요 : 

 참고 사항을 입력하세요 : 

-->

import MySQLdb
db = MySQLdb.connect(host='localhost', user='root', password='1234', db='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('다시 입력하세요')

-->여기서 부터 손 코딩 시작

3. 데이터 수정하기

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

4. 데이터 삭제하기

db = MySQLdb.connect(host='localhost', user='root', password='1234', db='kdt')
cur = db.cursor()
sql = 'delete from member where userid=%s'
result = cur.execute(sql, ('berry',))
# result = cur.execute(sql, data)
db.commit()

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

cur.close()
db.close()

 

### 문제 3
* 로그인 프로그램을 만들어보자
*아이디와 비밀번호가 맞은 유저가 있을 경우 "로그인 되었습니다.", 유저가 없을 경우 "아이디 또는 비밀번호를 확인하세요" 라고 출력

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

-->

#*아이디와 비밀번호가 맞은 유저가 있을 경우 "로그인 되었습니다.", 유저가 없을 경우 "아이디 또는 비밀번호를 확인하세요" 라고 출력
import MySQLdb
db = MySQLdb.connect(host='localhost', user='root', password='1234', db='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()

3. Workbench 메뉴에서 ERD 생성하기

# 워크 밴티 베뉴 Database -> Reverse Engineer