{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ElementTree 모듈을 로드\n",
    "from xml.etree import ElementTree"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# parse() 함수로 파일을 읽고 ElementTree 객체를 생성\n",
    "tree = ElementTree.parse('rss.xml')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# getroot() 메서드로 XML의 루트 element를 추출\n",
    "root = tree.getroot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>일시</th>\n",
       "      <th>최저기온</th>\n",
       "      <th>최고기온</th>\n",
       "      <th>날씨</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-25 00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-25 12:00</td>\n",
       "      <td>21</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-26 00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>29</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-26 12:00</td>\n",
       "      <td>21</td>\n",
       "      <td>29</td>\n",
       "      <td>구름많음</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-27 00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>29</td>\n",
       "      <td>구름많음</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-29 00:00</td>\n",
       "      <td>23</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-29 12:00</td>\n",
       "      <td>23</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-30 00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-07-01 00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-07-02 00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>27</td>\n",
       "      <td>흐림</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>533 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  일시 최저기온 최고기온     날씨\n",
       "0   2020-06-25 00:00   21   26  흐리고 비\n",
       "0   2020-06-25 12:00   21   26  흐리고 비\n",
       "0   2020-06-26 00:00   21   29  흐리고 비\n",
       "0   2020-06-26 12:00   21   29   구름많음\n",
       "0   2020-06-27 00:00   22   29   구름많음\n",
       "..               ...  ...  ...    ...\n",
       "0   2020-06-29 00:00   23   26  흐리고 비\n",
       "0   2020-06-29 12:00   23   26  흐리고 비\n",
       "0   2020-06-30 00:00   22   26  흐리고 비\n",
       "0   2020-07-01 00:00   22   26  흐리고 비\n",
       "0   2020-07-02 00:00   22   27     흐림\n",
       "\n",
       "[533 rows x 4 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "데이터프레임_리스트 = []\n",
    "for item in root.findall('channel/item/description/body/location/data'):\n",
    "    # find() 메서드로 element 탐색, text 속성으로 값을 추출\n",
    "    tm_ef = item.find('tmEf').text\n",
    "    tmn = item.find('tmn').text\n",
    "    tmx = item.find('tmx').text\n",
    "    wf = item.find('wf').text\n",
    "    데이터프레임 = pd.DataFrame({\n",
    "        '일시':[tm_ef],\n",
    "        '최저기온':[tmn],\n",
    "        '최고기온':[tmx],\n",
    "        '날씨':[wf],\n",
    "    })\n",
    "    데이터프레임_리스트.append(데이터프레임)\n",
    "날씨정보 = pd.concat(데이터프레임_리스트)\n",
    "날씨정보  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(날씨정보)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "날씨정보.to_csv('날씨정보.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "엑셀 = pd.ExcelWriter('날씨정보.xlsx')\n",
    "날씨정보.to_excel(엑셀, '.', index=False )\n",
    "엑셀.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "날씨정보.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "날씨정보.to_json('날씨정보.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "from pandas.io import sql\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "with sqlite3.connect(os.path.join('.','sqliteDB')) as con: # sqlite DB 파일이 존재하지 않는 경우 파일생성\n",
    "    try:\n",
    "        날씨정보.to_sql(name = 'WEATHER_INFO', con = con, index = False, if_exists='append') \n",
    "        #if_exists : {'fail', 'replace', 'append'} default : fail\n",
    "    except Exception as e:\n",
    "        print(str(e))\n",
    "    \n",
    "    query = 'SELECT * FROM WEATHER_INFO'\n",
    "    데이터프레임1 = pd.read_sql(query, con = con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>일시</th>\n",
       "      <th>최저기온</th>\n",
       "      <th>최고기온</th>\n",
       "      <th>날씨</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-25 00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-06-25 12:00</td>\n",
       "      <td>21</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-06-26 00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>29</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-06-26 12:00</td>\n",
       "      <td>21</td>\n",
       "      <td>29</td>\n",
       "      <td>구름많음</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-06-27 00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>29</td>\n",
       "      <td>구름많음</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3193</th>\n",
       "      <td>2020-06-29 00:00</td>\n",
       "      <td>23</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3194</th>\n",
       "      <td>2020-06-29 12:00</td>\n",
       "      <td>23</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3195</th>\n",
       "      <td>2020-06-30 00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3196</th>\n",
       "      <td>2020-07-01 00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3197</th>\n",
       "      <td>2020-07-02 00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>27</td>\n",
       "      <td>흐림</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3198 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    일시 최저기온 최고기온     날씨\n",
       "0     2020-06-25 00:00   21   26  흐리고 비\n",
       "1     2020-06-25 12:00   21   26  흐리고 비\n",
       "2     2020-06-26 00:00   21   29  흐리고 비\n",
       "3     2020-06-26 12:00   21   29   구름많음\n",
       "4     2020-06-27 00:00   22   29   구름많음\n",
       "...                ...  ...  ...    ...\n",
       "3193  2020-06-29 00:00   23   26  흐리고 비\n",
       "3194  2020-06-29 12:00   23   26  흐리고 비\n",
       "3195  2020-06-30 00:00   22   26  흐리고 비\n",
       "3196  2020-07-01 00:00   22   26  흐리고 비\n",
       "3197  2020-07-02 00:00   22   27     흐림\n",
       "\n",
       "[3198 rows x 4 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "데이터프레임1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "엑셀 = pd.ExcelWriter('날씨정보2.xlsx')\n",
    "데이터프레임1.to_excel(엑셀, '.', index=False )\n",
    "엑셀.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('날씨정보2.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>일시</th>\n",
       "      <th>최저기온</th>\n",
       "      <th>최고기온</th>\n",
       "      <th>날씨</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-25 00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-06-25 12:00</td>\n",
       "      <td>21</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-06-26 00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>29</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-06-26 12:00</td>\n",
       "      <td>21</td>\n",
       "      <td>29</td>\n",
       "      <td>구름많음</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-06-27 00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>29</td>\n",
       "      <td>구름많음</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3193</th>\n",
       "      <td>2020-06-29 00:00</td>\n",
       "      <td>23</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3194</th>\n",
       "      <td>2020-06-29 12:00</td>\n",
       "      <td>23</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3195</th>\n",
       "      <td>2020-06-30 00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3196</th>\n",
       "      <td>2020-07-01 00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>26</td>\n",
       "      <td>흐리고 비</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3197</th>\n",
       "      <td>2020-07-02 00:00</td>\n",
       "      <td>22</td>\n",
       "      <td>27</td>\n",
       "      <td>흐림</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3198 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    일시  최저기온  최고기온     날씨\n",
       "0     2020-06-25 00:00    21    26  흐리고 비\n",
       "1     2020-06-25 12:00    21    26  흐리고 비\n",
       "2     2020-06-26 00:00    21    29  흐리고 비\n",
       "3     2020-06-26 12:00    21    29   구름많음\n",
       "4     2020-06-27 00:00    22    29   구름많음\n",
       "...                ...   ...   ...    ...\n",
       "3193  2020-06-29 00:00    23    26  흐리고 비\n",
       "3194  2020-06-29 12:00    23    26  흐리고 비\n",
       "3195  2020-06-30 00:00    22    26  흐리고 비\n",
       "3196  2020-07-01 00:00    22    26  흐리고 비\n",
       "3197  2020-07-02 00:00    22    27     흐림\n",
       "\n",
       "[3198 rows x 4 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}