[Data Wrangling] 데이터베이스 프로그램

 

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("이미 모든 커서와 접속 정보가 반납되었습니다.")