hermosillo_weather.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyowm\n",
    "import pandas as pd\n",
    "from time import sleep\n",
    "from sqlalchemy import create_engine\n",
    "from datetime import datetime\n",
    "from pytz import timezone\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2022, 5, 25, 10, 34, 56, 366940, tzinfo=<DstTzInfo 'America/Hermosillo' MST-1 day, 17:00:00 STD>)"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "utc_timestamp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'OWM' object has no attribute 'weather_at_place'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-4-a22781a3da2a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      5\u001b[0m     \u001b[0;31m#Call\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m     \u001b[0mowm\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpyowm\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mOWM\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'4924239659ffd4f536f72b1beed83650'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 7\u001b[0;31m     \u001b[0mobservation\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mowm\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mweather_at_place\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Hermosillo,MX'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      8\u001b[0m     \u001b[0mw\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mobservation\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_weather\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      9\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mAttributeError\u001b[0m: 'OWM' object has no attribute 'weather_at_place'"
     ]
    }
   ],
   "source": [
    "weather_datadf_list = []\n",
    "\n",
    "for i in range(0,5): #Minutes\n",
    "    \n",
    "    #Call\n",
    "    owm = pyowm.OWM('4924239659ffd4f536f72b1beed83650')\n",
    "    observation = owm.weather_at_place('Hermosillo,MX')\n",
    "    w = observation.get_weather()\n",
    "    \n",
    "    #Get Vars\n",
    "    local_timestamp = datetime.datetime.now()\n",
    "    obs_timestamp = w.get_reference_time(timeformat='date')\n",
    "    wind_speed = w.get_wind()['speed'] \n",
    "    humidity = w.get_humidity()  \n",
    "    temperature = w.get_temperature('celsius')['temp']\n",
    "    temperature_max = w.get_temperature('celsius')['temp_max']\n",
    "    temperature_min = w.get_temperature('celsius')['temp_min']\n",
    "    sunrise_time = w.get_sunset_time('iso') \n",
    "    sunset_time = w.get_sunset_time('iso') \n",
    "    \n",
    "    # Store\n",
    "    weather_dic = {\n",
    "        \"local_timestamp\":[local_timestamp],\n",
    "        \"obs_timestamp\":[obs_timestamp],\n",
    "        \"wind_speed\":[wind_speed],\n",
    "        \"humidity\":[humidity],\n",
    "        \"temperature\":[temperature],\n",
    "        \"temperature_max\":[temperature_max],\n",
    "        \"temperature_min\":[temperature_min],\n",
    "        \"sunrise_time\":[sunrise_time],\n",
    "        \"sunset_time\":[sunset_time]\n",
    "    }\n",
    "    \n",
    "    # create a list of strings\n",
    "    columns = ['local_timestamp','obs_timestamp', 'wind_speed','humidity','temperature','temperature_max','temperature_min','sunrise_time','sunset_time']\n",
    "    #index = ['1', '2', '3','4','5','6','7','8','9']\n",
    "    \n",
    "    df = pd.DataFrame(weather_dic, columns=columns)\n",
    "    \n",
    "    weather_datadf_list.append(df)\n",
    "    print(str(local_timestamp))\n",
    "    print(datetime.now(timezone('US/Pacific')))\n",
    "    print(obs_timestamp)\n",
    "    sleep(3) # Seconds\n",
    "    \n",
    "\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2019, 4, 17, 1, 5, 18, tzinfo=<pyowm.utils.timeformatutils.UTC object at 0x110501c18>)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "obs_timestamp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2019, 4, 16, 18, 5, 18, tzinfo=<DstTzInfo 'US/Pacific-New' PDT-1 day, 17:00:00 DST>)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # Right way!\n",
    "# datetime_obj_pacific = timezone('US/Pacific-New').localize(obs_timestamp)\n",
    "# # datetime_obj_pacific\n",
    "\n",
    "now_pacific = obs_timestamp.astimezone(timezone('US/Pacific-New'))\n",
    "now_pacific"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>local_timestamp</th>\n",
       "      <th>obs_timestamp</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>humidity</th>\n",
       "      <th>temperature</th>\n",
       "      <th>temperature_max</th>\n",
       "      <th>temperature_min</th>\n",
       "      <th>sunrise_time</th>\n",
       "      <th>sunset_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-04-15 13:48:51.638274</td>\n",
       "      <td>2019-04-15 20:48:18+00:00</td>\n",
       "      <td>6.7</td>\n",
       "      <td>11</td>\n",
       "      <td>30.01</td>\n",
       "      <td>32.22</td>\n",
       "      <td>27.78</td>\n",
       "      <td>2019-04-16 01:49:33+00</td>\n",
       "      <td>2019-04-16 01:49:33+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-04-15 13:48:55.225741</td>\n",
       "      <td>2019-04-15 20:48:18+00:00</td>\n",
       "      <td>6.7</td>\n",
       "      <td>11</td>\n",
       "      <td>30.01</td>\n",
       "      <td>32.22</td>\n",
       "      <td>27.78</td>\n",
       "      <td>2019-04-16 01:49:33+00</td>\n",
       "      <td>2019-04-16 01:49:33+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-04-15 13:48:58.432694</td>\n",
       "      <td>2019-04-15 20:48:18+00:00</td>\n",
       "      <td>6.7</td>\n",
       "      <td>11</td>\n",
       "      <td>30.01</td>\n",
       "      <td>32.22</td>\n",
       "      <td>27.78</td>\n",
       "      <td>2019-04-16 01:49:33+00</td>\n",
       "      <td>2019-04-16 01:49:33+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-04-15 13:49:01.611966</td>\n",
       "      <td>2019-04-15 20:48:18+00:00</td>\n",
       "      <td>6.7</td>\n",
       "      <td>11</td>\n",
       "      <td>30.01</td>\n",
       "      <td>32.22</td>\n",
       "      <td>27.78</td>\n",
       "      <td>2019-04-16 01:49:33+00</td>\n",
       "      <td>2019-04-16 01:49:33+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-04-15 13:49:04.781390</td>\n",
       "      <td>2019-04-15 20:48:18+00:00</td>\n",
       "      <td>6.7</td>\n",
       "      <td>11</td>\n",
       "      <td>30.01</td>\n",
       "      <td>32.22</td>\n",
       "      <td>27.78</td>\n",
       "      <td>2019-04-16 01:49:33+00</td>\n",
       "      <td>2019-04-16 01:49:33+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             local_timestamp              obs_timestamp  wind_speed  humidity  \\\n",
       "0 2019-04-15 13:48:51.638274  2019-04-15 20:48:18+00:00         6.7        11   \n",
       "0 2019-04-15 13:48:55.225741  2019-04-15 20:48:18+00:00         6.7        11   \n",
       "0 2019-04-15 13:48:58.432694  2019-04-15 20:48:18+00:00         6.7        11   \n",
       "0 2019-04-15 13:49:01.611966  2019-04-15 20:48:18+00:00         6.7        11   \n",
       "0 2019-04-15 13:49:04.781390  2019-04-15 20:48:18+00:00         6.7        11   \n",
       "\n",
       "   temperature  temperature_max  temperature_min            sunrise_time  \\\n",
       "0        30.01            32.22            27.78  2019-04-16 01:49:33+00   \n",
       "0        30.01            32.22            27.78  2019-04-16 01:49:33+00   \n",
       "0        30.01            32.22            27.78  2019-04-16 01:49:33+00   \n",
       "0        30.01            32.22            27.78  2019-04-16 01:49:33+00   \n",
       "0        30.01            32.22            27.78  2019-04-16 01:49:33+00   \n",
       "\n",
       "              sunset_time  \n",
       "0  2019-04-16 01:49:33+00  \n",
       "0  2019-04-16 01:49:33+00  \n",
       "0  2019-04-16 01:49:33+00  \n",
       "0  2019-04-16 01:49:33+00  \n",
       "0  2019-04-16 01:49:33+00  "
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_weather = pd.concat(weather_datadf_list,axis=0)\n",
    "df_weather.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-142-9dbadfe15472>, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-142-9dbadfe15472>\"\u001b[0;36m, line \u001b[0;32m1\u001b[0m\n\u001b[0;31m    CREATE TABLE hermosillo_weather (\u001b[0m\n\u001b[0m               ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "# CREATE TABLE hermosillo_weather (\n",
    "#  index text,\n",
    "#  local_timestamp timestamp without time zone,\n",
    "#  obs_timestamp timestamp without time zone,\n",
    "#  wind_speed double precision,\n",
    "#  humidity bigint,\n",
    "#  temperature double precision,\n",
    "#  temperature_max double precision,\n",
    "#  temperature_min double precision,\n",
    "#  sunrise_time timestamp without time zone,\n",
    "#  sunset_time timestamp without time zone\n",
    "# );\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add table to psql table\n",
    "engine = create_engine('postgresql://diego:password@localhost:5432/sonora_sensors')\n",
    "df_weather.to_sql('hermosillo_weather', engine, if_exists='append')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Get data to see how it's looking\n",
    "# Create engine and bring in data\n",
    "# Write this in terminal first:\n",
    "# ssh -N db.xinampa.io -L 5433:127.0.0.1:5432 &\n",
    "engine = create_engine('postgresql://diego@localhost:5433/sonora_sensors')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_weather = pd.read_sql(\"select * from hermosillo_weather order by obs_timestamp desc;\",engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_weather.to_csv(\"/Users/Diego/Desktop/hermosillo_weather.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "###################\n",
    "###################"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Update Hermosillo 2022"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_datadf_list = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "owm = pyowm.OWM('4924239659ffd4f536f72b1beed83650')\n",
    "mgr = owm.weather_manager()\n",
    "observation = mgr.weather_at_place('Hermosillo,MX')\n",
    "weather = observation.weather"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "utc_timestamp = datetime.now(timezone('UTC'))\n",
    "sonora_timestamp = datetime.now(timezone('America/Hermosillo'))\n",
    "temperature = weather.temperature('celsius')['temp']\n",
    "temperature_max = weather.temperature('celsius')['temp_max']\n",
    "temperature_min = weather.temperature('celsius')['temp_min']\n",
    "temperature_feelslike = weather.temperature('celsius')['feels_like']\n",
    "sunrise_time = weather.sunrise_time(timeformat='date') #UTC time\n",
    "sunset_time = weather.sunset_time(timeformat='date') #UTC time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_dic = {\n",
    "    \"utc_timestamp\":[utc_timestamp],\n",
    "    \"sonora_timestamp\":[sonora_timestamp],\n",
    "    \"temperature\":[temperature],\n",
    "    \"temperature_max\":[temperature_max],\n",
    "    \"temperature_min\":[temperature_min],\n",
    "    \"temperature_feelslike\":[temperature_feelslike],\n",
    "    \"sunrise_time\":[sunrise_time],\n",
    "    \"sunset_time\":[sunset_time]\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a list of strings\n",
    "columns = ['utc_timestamp','sonora_timestamp','temperature','temperature_max','temperature_min','temperature_feelslike','sunrise_time','sunset_time']\n",
    "\n",
    "df = pd.DataFrame(weather_dic, columns=columns)\n",
    "weather_datadf_list.append(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[                     utc_timestamp                 sonora_timestamp  \\\n",
       " 0 2022-06-27 22:13:38.781368+00:00 2022-06-27 15:13:38.781485-07:00   \n",
       " \n",
       "    temperature  temperature_max  temperature_min  temperature_feelslike  \\\n",
       " 0        38.68            39.95            36.82                  39.93   \n",
       " \n",
       "                sunrise_time               sunset_time  \n",
       " 0 2022-06-27 12:27:09+00:00 2022-06-28 02:26:33+00:00  ]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_datadf_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "    weather_datadf_list = []\n",
    "    \n",
    "    for i in range(0,13): #Loops\n",
    "        \n",
    "        # CAll\n",
    "        owm = pyowm.OWM('4924239659ffd4f536f72b1beed83650')\n",
    "        mgr = owm.weather_manager()\n",
    "        observation = mgr.weather_at_place('Hermosillo,MX')\n",
    "        weather = observation.weather\n",
    "\n",
    "        #Get Vars\n",
    "        utc_timestamp = datetime.now(timezone('UTC'))\n",
    "        sonora_timestamp = datetime.now(timezone('America/Hermosillo'))\n",
    "        temperature = weather.temperature('celsius')['temp']\n",
    "        temperature_max = weather.temperature('celsius')['temp_max']\n",
    "        temperature_min = weather.temperature('celsius')['temp_min']\n",
    "        temperature_feelslike = weather.temperature('celsius')['feels_like']\n",
    "        sunrise_time = weather.sunrise_time(timeformat='date') #UTC time\n",
    "        sunset_time = weather.sunset_time(timeformat='date') #UTC time\n",
    "\n",
    "        # Store\n",
    "        weather_dic = {\n",
    "            \"utc_timestamp\":[utc_timestamp],\n",
    "            \"sonora_timestamp\":[sonora_timestamp],\n",
    "            \"temperature\":[temperature],\n",
    "            \"temperature_max\":[temperature_max],\n",
    "            \"temperature_min\":[temperature_min],\n",
    "            \"temperature_feelslike\":[temperature_feelslike],\n",
    "            \"sunrise_time\":[sunrise_time],\n",
    "            \"sunset_time\":[sunset_time]\n",
    "        }\n",
    "\n",
    "        # create a list of strings\n",
    "        columns = ['utc_timestamp','sonora_timestamp','temperature','temperature_max','temperature_min','temperature_feelslike','sunrise_time','sunset_time']\n",
    "\n",
    "        df = pd.DataFrame(weather_dic, columns=columns)\n",
    "        weather_datadf_list.append(df)\n",
    "        #sleep(300) # Seconds\n",
    "\n",
    "        # \tCREATE TABLE hermosillo_weather (\n",
    "        # \t utc_timestamp timestamp without time zone,\n",
    "        # \t sonora_timestamp timestamp without time zone,\n",
    "        # \t temperature double precision,\n",
    "        # \t temperature_max double precision,\n",
    "        # \t temperature_min double precision,\n",
    "        # \t temperature_feelslike double precision,\n",
    "        # \t sunrise_time timestamp without time zone,\n",
    "        # \t sunset_time timestamp without time zone\n",
    "        # \t);\n",
    "        \n",
    "    # Add table to psql table\n",
    "    # Create Data Frame\n",
    "    df_weather = pd.concat(weather_datadf_list,axis=0)\n",
    "    engine = create_engine('postgresql://diego:password@localhost:5432/sonora_sensors')\n",
    "    df_weather.to_sql('hermosillo_weather', engine, if_exists='append')\n",
    "    return(print(\"Weather updated!\"))\n",
    "\n",
    "##### Set as function and run\n",
    "if __name__ == \"__main__\":\n",
    "\n",
    "\tget_weather_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'weather_datadf_list' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-3-6a5c4d3eb723>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# Create Data Frame\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mdf_weather\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconcat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mweather_datadf_list\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'weather_datadf_list' is not defined"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Weather updated!\n"
     ]
    }
   ],
   "source": [
    "def get_weather_data():\n",
    "    weather_datadf_list = []\n",
    "    \n",
    "    for i in range(0,13): #Loops\n",
    "        \n",
    "        # CAll\n",
    "        owm = pyowm.OWM('4924239659ffd4f536f72b1beed83650')\n",
    "        mgr = owm.weather_manager()\n",
    "        observation = mgr.weather_at_place('Hermosillo,MX')\n",
    "        weather = observation.weather\n",
    "\n",
    "        #Get Vars\n",
    "        utc_timestamp = datetime.now(timezone('UTC'))\n",
    "        sonora_timestamp = datetime.now(timezone('America/Hermosillo'))\n",
    "        temperature = weather.temperature('celsius')['temp']\n",
    "        temperature_max = weather.temperature('celsius')['temp_max']\n",
    "        temperature_min = weather.temperature('celsius')['temp_min']\n",
    "        temperature_feelslike = weather.temperature('celsius')['feels_like']\n",
    "        sunrise_time = weather.sunrise_time(timeformat='date') #UTC time\n",
    "        sunset_time = weather.sunset_time(timeformat='date') #UTC time\n",
    "\n",
    "        # Store\n",
    "        weather_dic = {\n",
    "            \"utc_timestamp\":[utc_timestamp],\n",
    "            \"sonora_timestamp\":[sonora_timestamp],\n",
    "            \"temperature\":[temperature],\n",
    "            \"temperature_max\":[temperature_max],\n",
    "            \"temperature_min\":[temperature_min],\n",
    "            \"temperature_feelslike\":[temperature_feelslike],\n",
    "            \"sunrise_time\":[sunrise_time],\n",
    "            \"sunset_time\":[sunset_time]\n",
    "        }\n",
    "\n",
    "        # create a list of strings\n",
    "        columns = ['utc_timestamp','sonora_timestamp','temperature','temperature_max','temperature_min','temperature_feelslike','sunrise_time','sunset_time']\n",
    "\n",
    "        df = pd.DataFrame(weather_dic, columns=columns)\n",
    "        weather_datadf_list.append(df)\n",
    "        #sleep(300) # Seconds\n",
    "\n",
    "#         \tCREATE TABLE hermosillo_weather (\n",
    "#         \t utc_timestamp timestamp without time zone,\n",
    "#         \t sonora_timestamp timestamp without time zone,\n",
    "#         \t temperature double precision,\n",
    "#         \t temperature_max double precision,\n",
    "#         \t temperature_min double precision,\n",
    "#         \t temperature_feelslike double precision,\n",
    "#         \t sunrise_time timestamp without time zone,\n",
    "#         \t sunset_time timestamp without time zone\n",
    "#         \t);\n",
    "        \n",
    "    # Add table to psql table\n",
    "    df_weather = pd.concat(weather_datadf_list,axis=0)\n",
    "    engine = create_engine('postgresql://diego:password@localhost:5432/sonora_sensors')\n",
    "    df_weather.to_sql('hermosillo_weather', engine, if_exists='append')\n",
    "    return(print(\"Weather updated!\"))\n",
    "\n",
    "##### Set as function and run\n",
    "if __name__ == \"__main__\":\n",
    "\n",
    "\tget_weather_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "########## Weather for Merida"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyowm\n",
    "import pandas as pd\n",
    "from time import sleep\n",
    "from sqlalchemy import create_engine\n",
    "from datetime import datetime\n",
    "from pytz import timezone\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#Make the request\n",
    "def get_weather_data():\n",
    "    weather_datadf_list = []\n",
    "    \n",
    "    for i in range(0,13): #Loops\n",
    "        \n",
    "        # CAll\n",
    "        owm = pyowm.OWM('4924239659ffd4f536f72b1beed83650')\n",
    "        mgr = owm.weather_manager()\n",
    "        observation = mgr.weather_at_place('Merida,MX')\n",
    "        weather = observation.weather\n",
    "\n",
    "        #Get Vars\n",
    "        utc_timestamp = datetime.now(timezone('UTC'))\n",
    "        merida_timestamp = datetime.now(timezone('America/Merida'))\n",
    "        temperature = weather.temperature('celsius')['temp']\n",
    "        temperature_max = weather.temperature('celsius')['temp_max']\n",
    "        temperature_min = weather.temperature('celsius')['temp_min']\n",
    "        temperature_feelslike = weather.temperature('celsius')['feels_like']\n",
    "        sunrise_time = weather.sunrise_time(timeformat='date') #UTC time\n",
    "        sunset_time = weather.sunset_time(timeformat='date') #UTC time\n",
    "\n",
    "        # Store\n",
    "        weather_dic = {\n",
    "            \"utc_timestamp\":[utc_timestamp],\n",
    "            \"merida_timestamp\":[merida_timestamp],\n",
    "            \"temperature\":[temperature],\n",
    "            \"temperature_max\":[temperature_max],\n",
    "            \"temperature_min\":[temperature_min],\n",
    "            \"temperature_feelslike\":[temperature_feelslike],\n",
    "            \"sunrise_time\":[sunrise_time],\n",
    "            \"sunset_time\":[sunset_time]\n",
    "        }\n",
    "\n",
    "        # # create a list of strings\n",
    "        columns = ['utc_timestamp','merida_timestamp','temperature','temperature_max','temperature_min','temperature_feelslike','sunrise_time','sunset_time']\n",
    "\n",
    "        df = pd.DataFrame(weather_dic, columns=columns)\n",
    "        weather_datadf_list.append(df)\n",
    "        sleep(300) # Seconds\n",
    "\n",
    "    df_weather = pd.concat(weather_datadf_list,axis=0)\n",
    "        # CREATE TABLE merida_weather (\n",
    "        #  index text,\n",
    "        #  utc_timestamp TIMESTAMPTZ,\n",
    "        #  merida_timestamp timestamp,\n",
    "        #  temperature double precision,\n",
    "        #  temperature_max double precision,\n",
    "        #  temperature_min double precision,\n",
    "        #  temperature_feelslike double precision,\n",
    "        #  sunrise_time timestamp without time zone,\n",
    "        #  sunset_time timestamp without time zone\n",
    "        #    \t);\n",
    "\n",
    "    # Add table to psql table\n",
    "    df_weather = pd.concat(weather_datadf_list,axis=0)\n",
    "    engine = create_engine('postgresql://diego:E4SJq3yxNA@localhost:5432/sonora_sensors')\n",
    "    df_weather.to_sql('merida_weather', engine, if_exists='append')\n",
    "    return(print(\"Weather updated!\"))\n",
    "\n",
    "##### Set as function and run\n",
    "if __name__ == \"__main__\":\n",
    "\n",
    "\tget_weather_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# CAll\n",
    "owm = pyowm.OWM('4924239659ffd4f536f72b1beed83650')\n",
    "mgr = owm.weather_manager()\n",
    "observation = mgr.weather_at_place('Merida,MX')\n",
    "weather = observation.weather\n",
    "\n",
    "#Get Vars\n",
    "utc_timestamp = datetime.now(timezone('UTC'))\n",
    "merida_timestamp = datetime.now(timezone('America/Merida'))\n",
    "temperature = weather.temperature('celsius')['temp']\n",
    "temperature_max = weather.temperature('celsius')['temp_max']\n",
    "temperature_min = weather.temperature('celsius')['temp_min']\n",
    "temperature_feelslike = weather.temperature('celsius')['feels_like']\n",
    "sunrise_time = weather.sunrise_time(timeformat='date') #UTC time\n",
    "sunset_time = weather.sunset_time(timeformat='date') #UTC time\n",
    "\n",
    "# Store\n",
    "weather_dic = {\n",
    "    \"utc_timestamp\":[utc_timestamp],\n",
    "    \"merida_timestamp\":[merida_timestamp],\n",
    "    \"temperature\":[temperature],\n",
    "    \"temperature_max\":[temperature_max],\n",
    "    \"temperature_min\":[temperature_min],\n",
    "    \"temperature_feelslike\":[temperature_feelslike],\n",
    "    \"sunrise_time\":[sunrise_time],\n",
    "    \"sunset_time\":[sunset_time]\n",
    "}\n",
    "\n",
    "# # create a list of strings\n",
    "columns = ['utc_timestamp','merida_timestamp','temperature','temperature_max','temperature_min','temperature_feelslike','sunrise_time','sunset_time']\n",
    "\n",
    "df = pd.DataFrame(weather_dic, columns=columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>utc_timestamp</th>\n",
       "      <th>merida_timestamp</th>\n",
       "      <th>temperature</th>\n",
       "      <th>temperature_max</th>\n",
       "      <th>temperature_min</th>\n",
       "      <th>temperature_feelslike</th>\n",
       "      <th>sunrise_time</th>\n",
       "      <th>sunset_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-06-02 12:43:04.238880+00:00</td>\n",
       "      <td>2023-06-02 07:43:04.317281-05:00</td>\n",
       "      <td>23.01</td>\n",
       "      <td>26.12</td>\n",
       "      <td>23.01</td>\n",
       "      <td>23.82</td>\n",
       "      <td>2023-06-02 11:16:45+00:00</td>\n",
       "      <td>2023-06-03 00:36:27+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     utc_timestamp                 merida_timestamp  \\\n",
       "0 2023-06-02 12:43:04.238880+00:00 2023-06-02 07:43:04.317281-05:00   \n",
       "\n",
       "   temperature  temperature_max  temperature_min  temperature_feelslike  \\\n",
       "0        23.01            26.12            23.01                  23.82   \n",
       "\n",
       "               sunrise_time               sunset_time  \n",
       "0 2023-06-02 11:16:45+00:00 2023-06-03 00:36:27+00:00  "
      ]
     },
     "execution_count": 3,
     "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.9.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}