[Python] 3. 데이터베이스 테이블에 저장하기 (한국전력거래소 시간별 전력수요량 데이터 수집, Python-mariaDB 연결, 데이터 랭글링)

 

 

 

 

 

 

 

 

 

 

프로그래밍 세계를 탐구합시다.

 

 

 

 

 

 

 

 

 

 

1, 2편은 이전 게시글 참고 바랍니다.

 

 

2023.12.01 - [IT/Python] - [Python] 1. 데이터 수집하기 (한국전력거래소 시간별 전력수요량 데이터 수집, 데이터 랭글링)

[Python] 1. 데이터 수집하기 (한국전력거래소 시간별 전력수요량 데이터 수집, 데이터 랭글링)

 

 

2023.12.02 - [IT/Python] - [Python] 2. 데이터베이스 테이블에 저장하기 (한국전력거래소 시간별 전력수요량 데이터 수집, Python-mariaDB 연결, 데이터 랭글링)

[Python] 2. 데이터베이스 테이블에 저장하기 (한국전력거래소 시간별 전력수요량 데이터 수집, Pyth

 

 

 

 

 

 

 

 

 

<  DB 프로그램 순서  >

 

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

 

 

 

 

 

<  조회 결과가 여러건인 경우  >

 

 

rows = cur.fetchall()
rows

 

 

 

 

 

 

 

ymd_power_df = pd.DataFrame(rows)
# - 컬럼명 없이 받아온 경우
# - [(), ()...]
# ymd_power_df = pd.DataFrame(rows, colums=["ymd", "time", "power"])
ymd_power_df

 

 

 

 

 

 

 

 

 

 

< 한 건 조회하기 >

 

 

 

한 건 조회를 위한 sql 구문 생성하기

 

### 한 건 조회를 위한 sql 구문 생성하기
sql = """
    Select * From time_power_demand
    Where ymd = '2021-01-01'
     And time = '1시'
"""

sql

 

 

 

 

 

 

 

 

SQL 구문을 DB 서버에 요청하고, 결과 받아오기

 

 

 

### SQL 구문을 DB 서버에 요청하고, 결과 받아오기
cur.execute(sql)

 

 

 

 

 

 

 

 

row = cur.fetchone()
row

 

 

 

 

 

 

 

 

 

데이터프레임에 담아서 보여주기

 

 

### 데이터프레임에 담아서 보여주기
ymd_power_one = pd.DataFrame([row])
ymd_power_one

 

 

 

 

 

 

 

 

<  데이터 입력하기 (insert)  >

 

 

 

입력을 위한 구문 생성하기

 

 

### 입력을 위한 구문 생성하기
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("입력되지 않았습니다.")

 

 

 

 

 

 

 

 

 

 

< 수정하기 처리하기 >

 

 

 

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

 

 

### ymd가 2020-12-31이고, 시간이 24시인 행을 찾아서
#   power의 값을 5678로 수정하기
sql = """
    UPDATE time_power_demand
	 SET POWER = 3333
    WHERE ymd = '2020-12-31'
	 AND TIME = '24시'
"""
sql

 

 

 

 

 

 

 

 

 

 

 

 

 

<  삭제하기 처리하기  >

 

 

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

 

 

### ymd가 2020-12-31이고, 시간이 24시인 행을 찾아서
#   삭제시키기
sql = """
  DELETE FROM time_power_demand
  WHERE ymd = '2020-12-31'
	AND TIME = '24시'
"""

 

 

### DB에 SQL 구문 요청하고 결과 받아오기
rs_cnt = cur.execute(sql)

if rs_cnt > 0 :
    print(f"{rs_cnt}건이 삭제되었습니다.")
else :
    print("삭제 실패 ㅠ")

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<  한 번도 구매한 적이 없는 회원정보를 조회  >

 

 

조회컬럼 : 회원아이디, 회원이름

 

 

 

 

### 한 번도 구매한 적이 없는 회원정보를 조회해주세요
# - 조회컬럼 : 회원아이디, 회원이름

sql = """
  SELECT mem_id, mem_name
  FROM member
  WHERE not EXISTS(SELECT cart_member from cart
					WHERE cart_member = mem_id)

"""
sql

 

 

 

 

 

 

rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
    print(f"{rs_cnt}건 조회되었습니다.")
else :
    print("조회 실패....ㅠㅠ")

 

 

 

 

 

 

 

 

 

커서가 가지고 있는 결과 추출하기

 

 

### 커서가 가지고 있는 결과 추출하기
rows = cur.fetchall()
rows

 

 

 

 

 

 

 

 

 

 

 

데이터프레임으로 만들기 

 

 

 

### 데이터프레임으로 만들기
noMember = pd.DataFrame(rows)
noMember

 

 

 

 

 

 

 

 

 

 

1. 클래스 생성
 - 클래스 이름 : PowerClass
2. 클래스 내에 함수들 정의
3. 외부에서 조회/한건입력/수정/삭제 처리하기

 

 

import pandas as pd
import pymysql

class PowerClass:
    def __init__(self, host, port, user, password, db, charset='utf8', autocommit=True, cursorclass=pymysql.cursors.DictCursor):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db
        self.charset = charset
        self.autocommit = autocommit
        self.cursorclass = cursorclass
        self.connection = None

    def connect_db(self):
        try:
            self.connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                db=self.db,
                charset=self.charset,
                autocommit=self.autocommit,
                cursorclass=self.cursorclass
            )
            print("DB 연결 성공")
        except Exception as e:
            print(f"DB 연결 에러: {e}")

    def close_db(self):
        if self.connection:
            self.connection.close()
            print("DB 연결 종료")

    def select_data(self, sql):
        try:
            with self.connection.cursor() as cursor:
                indata = cursor.execute(sql)
                result = cursor.fetchall()
                df_result = pd.DataFrame(result)
                print(f"{indata} 건의 데이터가 조회되었습니다.")
                print(result)
                print(df_result)
        except Exception as e:
            print(f"데이터 조회 에러: {e}")
    
    def insert_data(self, sql):
        try:
            with self.connection.cursor() as cursor:
                indata = cursor.execute(sql)
                if(indata>0): print(f"{indata} 건의 데이터 입력 성공")
                else: print("데이터 입력 실패")
        except Exception as e:
            print(f"데이터 입력 에러: {e}")

    def update_data(self, sql):
        try:
            with self.connection.cursor() as cursor:
                indata = cursor.execute(sql)
                if(indata>0): print(f"{indata} 건의 데이터 수정 성공")
                else: print("데이터 수정 실패")
        except Exception as e:
            print(f"데이터 수정 에러: {e}")

    def delete_data(self, sql):
        try:
            with self.connection.cursor() as cursor:
                indata = cursor.execute(sql)
                if(indata>0): print(f"{indata} 건의 데이터 삭제 성공")
                else: print("데이터 삭제 실패")
        except Exception as e:
            print(f"데이터 삭제 에러: {e}")

 

 

 

 

PowerClass 인스턴스 생성

 

 

# PowerClass 인스턴스 생성
host = "localhost"
port = 3306
user = "gjuser"
password = "dbdb"
db = "gjdb"
power_instance = PowerClass(host, port, user, password, db)

 

 

 

 

데이터베이스 연결

 

 

# 데이터베이스 연결
power_instance.connect_db()

 

 

 

데이터 조회

 

# 데이터 조회
select_query = '''
    SELECT time 
    FROM time_power_demand 
    WHERE power >= 90000
'''

power_instance.select_data(select_query)
# df_result = pd.DataFrame(result)
# print("조회 결과:", result)
# df_result

 

 

 

 

데이터 입력

 

# 데이터 입력
insert_query = '''
    INSERT INTO time_power_demand VALUES ('2020-01-05', '3시', 65210)
    '''
power_instance.insert_data(insert_query)

 

 

 

 

데이터 수정

 

# 데이터 수정
update_query = '''
    UPDATE time_power_demand SET ymd = '2020-01-20'
    WHERE ymd = '2020-01-05' AND power = 65210
'''

power_instance.update_data(update_query)

 

 

 

데이터 삭제

 

# 데이터 삭제
delete_query = '''
    DELETE FROM time_power_demand
    WHERE ymd = '2020-01-20'

'''

power_instance.delete_data(delete_query)

 

 

 

데이터베이스 연결 종료 

 

# 데이터베이스 연결 종료
power_instance.close_db()