Industry_Energy DB 구축

도단위/연도별 산업에너지 DB 구축
RDS 이용

import pandas as pd
from typing import final
from venv import create
import multiprocessing
import base64
import os
import sys
import pymysql
import requests
from sqlalchemy import create_engine

df = pd.read_excel('', usecols = [0, 1, 3, 4], names=['addr','count','electricity','gas'], header=3, skipfooter=2)
df = df.astype({'addr':'string','count':'string','electricity':'string','gas':'string'})

for i in range(len(df['addr'])):
    df['count'][i] = df['count'][i].replace(',', '')
    df['electricity'][i] = df['electricity'][i].replace(',', '')
    df['gas'][i] = df['gas'][i].replace(',', '')
    if df['addr'][i] == '서울':
        df['addr'][i] = '1100000000'
    elif df['addr'][i] == '부산':
        df['addr'][i] = '2600000000'
    elif df['addr'][i] == '대구':
        df['addr'][i] = '2700000000'
    elif df['addr'][i] == '인천':
        df['addr'][i] = '2800000000'
    elif df['addr'][i] == '광주':
        df['addr'][i] = '2900000000'
    elif df['addr'][i] == '대전':
        df['addr'][i] = '3000000000'
    elif df['addr'][i] == '울산':
        df['addr'][i] = '3100000000'
    elif df['addr'][i] == '세종':
        df['addr'][i] = '3600000000'
    elif df['addr'][i] == '경기':
        df['addr'][i] = '4100000000'
    elif df['addr'][i] == '강원':
        df['addr'][i] = '4200000000'
    elif df['addr'][i] == '충북':
        df['addr'][i] = '4300000000'
    elif df['addr'][i] == '충남':
        df['addr'][i] = '4400000000'
    elif df['addr'][i] == '전북':
        df['addr'][i] = '4500000000'
    elif df['addr'][i] == '전남':
        df['addr'][i] = '4600000000'
    elif df['addr'][i] == '경북':
        df['addr'][i] = '4700000000'
    elif df['addr'][i] == '경남':
        df['addr'][i] = '4800000000'
    elif df['addr'][i] == '제주':
        df['addr'][i] = '5000000000'

df.insert(0, 'year', '2021')
df.insert(2, 'type', '2')

db_connection_str=''
db_connection=create_engine(db_connection_str)
conn = pymysql.connect(host="",
                       user='',
                       password='',
                       db='EnergyConsumption',
                       charset='utf8')
df.to_sql(name='industry',con=db_connection,if_exists='append',index=False)
#print(df)

pandas read_excel()로 xls파일 읽고 DataFrame으로 변환 후 다시 RDS data로 변환
python에서 pymysql package로 MySQL database import 할 수 있음