굉장히 엄청나!

DB 프로그램 순서

1. 데이터베이스 설정 정보 준비
2. DB 접속하기 : 커넥션(connect)
3. 커서 받아오기 : cursor()
4. 구문 작성하기 : sql(조회, 입력, 수정, 삭제)
5. 구문 실행하기 :  execute() -> 결과값은 체크
6. 조회인 경우 커서에서 데이터 추출하기
        -> 한건 : fetchone()
        -> 여러건 : fetchall()
7. 조회결과 데이터프레임 또는 웹페이지에 출력
    -> 데이터프레임 : pd.DataFrame(여러건), pd.DataFrame([한건])
8. 조회가 아닌 경우 : 처리 결과 조건으로 확인하기 if "처리 성공" else "처리 실패"
9. DB정보 반환하기
        - 반환 순서 : cursor > connect
    

데이터 조회하기

🍀 데이터베이스 연결

import pymysql
### 접속 정보
# - 접속 허가를 위한 정보
# - 접속 id or 도메인
host = "localhost"
# - 사용자계정
user = "gjuser"
# - 패스워드
password = "dbdb"
# - 데이터베이스명
db = "gjdb"
# - 한글처리
charset = "utf8"
# - 조회시 컬럼명을 동시에 보여줄지 여부 설정
cursorclass = pymysql.cursors.DictCursor

🍀 DB 접속하기

• connect로 서버에 연결 허가를 요청하여 conn에 허가를 받아 옴

try :
    conn = pymysql.connect(host = host,
                           user = user,
                           password = password,
                           db = db,
                           charset = charset,
                           # autocommit = autocommit,
                           cursorclass = cursorclass)
    print("DB접속 성공 >>> ", conn)
except :
    print("DB Server Checking...")

🍀 커서 받아오기

cur = conn.cursor()
cur

 🍀 Select 문 생성 후 DB에게 요청 및 응답 받기

• execute가 "Select * from time_power_demand"라는 요청문을 서버에게 가지고 가서 그에 대한 답을 가져온다.

sql = "Select * from time_power_demand"
rs_cnt = cur.execute(sql)
print(f"{rs_cnt}건이 조회 되었습니다.")

🍀 조회결과가 여러 건인 경우

• non 맵핑 방식
• df.to_sql은 orm( object relation mapping, 객체 맵핑 방식)
• cursorclass설정 시 딕셔너리 형태로 컬럼명과 같이 가져옴
• cursorclass설정 안하면 튜플로 가져와서 컬럼명 없이 값만 나옴
  - [(), ()...] 

rows = cur.fetchall()
rows

🍀 조회결과 데이터프레임으로 출력

• cursorclass설정을 안해서 컬럼명 없이 받아온 경우
  - ymd_power_df = pd.DataFrame(rows, columns=["ymd", "time", "power"]) 처럼 컬럼명 지정해서 코드 작성

ymd_power_df = pd.DataFrame(rows)
ymd_power_df

🍀 조회결과가 한 건인 경우

• 한건 조회를 위한 sql 구문 생성하기
• sql 구문 쓸 때 컴마 뒤에 띄어쓰기 꼭 하기

sql = """
        Select * From time_power_demand
        Where ymd = '2021-01-01'
            And time = '1시'
"""
sql

• execute로 sql 구문을 DB서버에 요청하고, 결과 받아오기 

cur.execute(sql)

한 건의 결과가 나왔다는 의미

• 받아오고 데이터 추출하기

row = cur.fetchone()
row

• 데이터프레임에 담아서 보여주기
  - 데이터가 한 건만 추출 될 때는 딕셔너리 타입으로 추출되기 때문에 데이터프레임에 데이터를 담을 때는 리스트 타입으로 넣어야함

ymd_power_df = pd.DataFrame([row])
ymd_power_df

데이터 입력하기

🍀 입력을 위한 구문 생성하기

sql = """
    Insert Into time_power_demand(
        ymd, time, power
    ) Values (
        '2020-12-31', '24시', 1234
    )
"""
sql

🍀 구문 처리하고 결과 값 받아오기

• 저장 요청 시에는 결과값이 숫자값으로 반환된다
  - 몇 건이 수행 됐는지 숫자값으로 나옴

rs_cnt = cur.execute(sql)
rs_cnt
print(f'{rs_cnt}건이 처리되었습니다')

🍀 처리가 잘 되었는지 확인하기

• 0보다 크면 '1건이 입력되었습니다.' 출력
• 0이하인 경우에는  "입력되지 않았습니다." 출력 

if rs_cnt > 0 :
    print(f'{rs_cnt}건이 입력되었습니다.')
else :
    print(f'입력되지 않았습니다')

데이터 수정하기

### ymd가 2020-12-31이고, 시간이 24시 인 행을 찾아서 power의 값을 5678로 수정하기

sql = """
        Update time_power_demand set power = 5679
        Where ymd = '2020-12-31'
            And time = '24시'    
"""
rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
    print(f'{rs_cnt}건이 수정되었습니다.')
else :
    print(f'수정되지 않았습니다')

데이터 삭제하기

### ymd가 2020-12-31이고, 시간이 24시 인 행을 찾아서 삭제하기

sql = """
        Delete
        From time_power_demand
        Where ymd = '2020-12-31'
            And time = '24시'    
"""
rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
    print(f'{rs_cnt}건이 삭제되었습니다.')
else :
    print(f'삭제되지 않았습니다')

데이터 조회 문제

🍀조회해보기

• 한번도 구매한적이 없는 회원정보를 조회하기
• 조회 컬럼 : 회원아이디, 회원이름

sql = """
    SELECT mem_id, mem_name
    FROM member
    WHERE not exists (SELECT cart_member FROM cart
WHERE cart_member = mem_id)
"""
rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
    print(f'{rs_cnt}건이 조회되었습니다.')
else :
    print(f'조회되지 않았습니다')

• 커서가 가지고 있는 결과 추출하여 데이터프레임 형식으로 나타내기

rows = cur.fetchall()
noMember = pd.DataFrame(rows)
noMember

종료하기

🍀커서 및 커넥션 정보 반납(종료)하기

try:
    cur.close()
    conn.close()
except :
    print("이미 모든 커서와 접속 정보가 반납되었습니다.")

📥MariaDB 설치

📍 Download URL : https://mariadb.org

※ 10.11.6 버전 다운로드 : 버전이 낮을수록 안정화가 되어있기 때문에 최신 버전 사용을 선호하진 않는다.

※ X86-64 : 64 bit

📍  DB Client Tool

- HeidiSQL  사용

📍  MariaDB or MySQL 절대 권한 계정 및 패스워드

- 계정 : root

- 패스워드 : 설치 시 입력한 패스워드

- 사용하는 port : 3306

- 기본 IP : 127.0.0.1 (프라이빗 ip로 자신의 노트북에서만 접속할 수 있음)

- 기본 Host : localhost

- 데이터 베이스를 관리하는 영역 mysql

🔍DB 사용 방법

📍  절대권한(root)으로 접속해서 생성해야 함

📍  사용자 생성 : 계정 및 패스워드 생성

📍 Database 생성 : 사용자가 사용할 공간 만들기

(Oracle은 제외한다. Oracle은 사용자 생성이 곧 DB 생성과 같기 때문이다.)

📍 권한 부여 : 사용자가 접속하고,DB를 사용할 수 있는 권한 부여

📍 사용자, DB, 권한부여 후 할일

1. 사용자로 접속
2. Table 설계(DB 설계)
   → 테이블을 어떻게 만들지 모델링 하는 것
   → 데이터를 저장할 수 있는 공간 : 테이블
   → 테이블 내에 데이터를 저장하는 원소값 : 컬럼(열)
   → 테이블 하나를 만들기 위해서는 컬럼(열)이 하나 이상 존재해야 한다.
   → 컬럼은 타입(또는 길이와 함께)으로 정의된다.
   → CHAR(10)는 고정형 데이터 타입
   → VARCHAR(10)는 가변형 데이터 타입
   → 성격에 맞는 데이터끼리 테이블로 모아야 함
   

   

3. Table을 이용해서 입력, 수정, 삭제, 조회 → (CRUD)를 수정함

📌관계형 데이터 베이스

📍 1 : m 형태

📍 1개인 쪽이 부모 테이블, m개인 쪽이 자식 테이블

📍 부모 쪽 테이블에는 PK(기본키)가 존재하야 하고 자식 테이블에는 PK를 참조하는 FK(외래키)가 존재해야 함

📍 PK - 고유한 값, 중복 불가

✏️데이터 베이스 입력

📍 규칙 타입 이름

📍 제약조건 :   constraint 제약조건 이름 primary key (컬럼명)

📍commit /  rollback

• 데이터에 대한 입력/수정/삭제 시에는 commit 수행
• 만약 입력/수정/삭제를 되돌리려면 rollback 수행
• commit 이후에는 rollback이 안된다
• mysql or mariadb는 자동 commi된다
• oracle은 항상 commit 해야 한다
  

📍 데이터 입력 / 수정 / 삭제 / 조회

• 데이터 입력

Insert Into 테이블명 (컬럼명1, ...컬럼명N) Values (값1, ...값N)

• 데이터 수정

Update 테이블명
Set 수정할컬럼명1 = 수정할값, 수정할컬럼명2 = 수정할값
Where 수정 조건 제시 (필수)

• 데이터 삭제

Delete From 테이블명
Where 삭제 조건 제시 (필수)

• 데이터 조회

Select 조회할컬럼1, 조회할컬럼2, ...
From 테이블명

📍 데이터 조회 / 수정/ 삭제 해보기

• 회원정보 전체 조회하기
  - 회원정보 테이블 : member
  - 모든 컬럼의 데이터를 조회하라는 의미

SELECT * FROM member;

• 조건 걸어서 조회하기
  - 회원중에 아이디가 a001인 회원의 이름 조회하기
  - 컴파일(해석) 순서 : select > from member > where 조건들 > 조회할 컬럼

SELECT mem_id, mem_name
FROM member
WHERE mem_id = 'a001';

• 조건 걸어서 수정하기
  - 상품분류코드가 P201에 대해서 상품분류명을 "향수"로 수정하기

UPDATE lprod
SET lprod_nm = '향수'
WHERE lprod_gu = 'P201';

• 조건 걸어서 삭제하기
  - 상품분류코드가 P201인 데이터를 삭제하기
  - 삭제는 조건에 맞는 행이 삭제됨
  - 자식 테이블에서 참조를 하고 있기 때문에 삭제 불가

Delete From lprod
Where lprod_gu = 'P201';