<jupyter_start><jupyter_text>Using SQL to update weather data<jupyter_code># TO DO LIST
#
# This is for my own purposes
#
# Streamline the code to update US and Global data in one run
#      This might entail putting table names and perhaps other values
#       into a dictionary
#
# Maybe make a function to generate column names automatically
#      Maybe it would read the name of the most recent column,
#      and then generate new column name up through maybe a few
#      days prior to the current date.
#
# Create way of programatically adding JHU's new location updates without having to re-call API
# for preexisting locations
# sqlite3 library to run operations on the database
import sqlite3

gen_path = '/home/imants/gits/COVID-19/'

db_path = gen_path + 'sql/weather.db'

# connecting to the weather database
con = sqlite3.connect(db_path)

# establishing database cursor
cursorObj = con.cursor()

# Getting table names

table_names = cursorObj.execute("SELECT name FROM sqlite_master WHERE type='table';")

table_names = table_names.fetchall()

# Taking the table names that were fetched, and making a usable lists out of the values.

US_names = []
global_names = []

for x in range(len(table_names)):
    if (table_names[x][0][-2:] == 'US'):
        US_names.append(table_names[x][0])
        
    else:
        global_names.append(table_names[x][0])
        
 
print('US names', US_names)

print('Global names', global_names)
    

# Reading API key

key_path = '/home/imants/gits/COVID-19/key.txt'

api_file = open(key_path, 'r')
API_KEY = api_file.read()
api_file.close()
API_KEY = API_KEY.strip()


# Creating API call function

import pandas as pd
import numpy as np
import requests
import json
from datetime import datetime as dt

def call_api (lat, long, time):
    
    # Building the URL for the API get
    url = 'https://api.darksky.net/forecast/' + API_KEY + '/' + lat + "," + long + ',' + time
    url = url + '?exclude=currently,flags&units=si'
    print(url)
    
    # Getting the API call
    # using the retry error handling established above
    response = http.get(url)
    
    # Putting the API response into the JSON thing
    info = json.loads(response.content)
    #info = url
    
    return info
# Sorting out JSON response from API call


def sortOut (info):

    # Dummy value
    dummy = '-1000'

    # dictionary of what will be pulled from JSON
    return_values = {'tMax': '',
                    'tMin': '',
                    'hum': '',
                    'uvee': '',
                    'clouds': '',
                    'precip': '',
                    'dew': '',
                    'pressure': '',
                    'wind': '',
                    'ozone': '',
                    'sunrise': '',
                    'sunset': ''}

    # print(info['daily']['data'][0])
    try:
    
        # Making a variable to more easily acccess JSON response data
        easy_info = info['daily']['data'][0]

        # Reading the JSON data
        return_values['tMax'] = str(easy_info['temperatureHigh'])
        return_values['tMin'] = str(easy_info['temperatureLow'])
        return_values['hum'] = str(easy_info['humidity'] * 100)
        return_values['uvee'] = str(easy_info['uvIndex'])
        return_values['clouds'] = str(easy_info['cloudCover'] * 100)
        return_values['precip'] = str(easy_info['precipProbability'] * 100)
        return_values['dew'] = str(easy_info['dewPoint'])
        return_values['pressure'] = str(easy_info['pressure'])
        return_values['wind'] = str(easy_info['windSpeed'])
        return_values['ozone'] = str(easy_info['ozone'])
        return_values['sunrise'] = str(easy_info['sunriseTime'])
        return_values['sunset'] = str(easy_info['sunsetTime'])

    except:

        # Creating dummy values in case of error
        print('Error encountered')
        
        for x in return_values:
            return_values[x] = dummy
    
    return return_values
# Updating US data
# Going through tables and adding data to new dates

import pandas as pd
import numpy as np
import requests
import json
from datetime import datetime as dt

# Trying to capture sqlite error messages
# found https://stackoverflow.com/questions/25371636/how-to-get-sqlite-result-error-codes-in-python
import traceback
import sys

# Code to increase number of retries on connection errors,
# and also to give it some time.
# Found on https://stackoverflow.com/questions/15431044/can-i-set-max-retries-for-requests-request
# And https://findwork.dev/blog/advanced-usage-python-requests-timeouts-retries-hooks/

from urllib3.util.retry import Retry
from requests.adapters import HTTPAdapter

s = requests.Session()

retries = Retry(total=30,
                backoff_factor=0.1,
                status_forcelist=[ 429, 500, 502, 503, 504 ],
                method_whitelist=["HEAD", "GET", "OPTIONS"])

adapter = HTTPAdapter(max_retries=retries)
http = requests.Session()
http.mount("https://", adapter)
http.mount("http://", adapter)


# Making list of columns that need to be updated from prior update, above
columns_to_update = []

for x in range(1, 16):
    
    day_str = str(x)
    date_str = '"9/' + day_str + '/20"'
    columns_to_update.append(date_str)

# Counter
counter = 0
    
# value for determining when to break
break_counter = 11000
    
# Iterating through the columns that need to be updated
for day in columns_to_update:
    
    print(day)
    
    # Querying all the latitudes and longitudes for the given day
    query_str = 'SELECT Lat, Long_, {} FROM {}'.format(day, US_names[0])
    print(query_str)
    cursorObj.execute(query_str)
    
    # putting query results into a variable 
    #rows = cursorObj.fetchall()
    
    # Create Unix time stamp out of the date column
    # stripping quotation marks from column name
    i = len(day)
    col_date = day[1:(i-1)]
    
    print(col_date)
    
    t = pd.to_datetime(col_date)
    t = int(t.value / 10**9)
    t = str(t)
    
    print(t)
    
    for row in cursorObj:
        print(row[0])
        print(row[1])
        print(row[2])
        
        latitude = row[0]
        longitude = row[1]
        
        print('Latitude: ', latitude, 'Longitude: ', longitude)
        
        if row[2] is None:
            
            print('Cell is Null')
            
            # Calling API based on the row's latitude, longitude, and the day in question
            info = call_api(str(latitude), str(longitude), t)

            counter = counter + 1
            # print(info)

            # putting the JSON response into a dictionary
            weatherData = sortOut(info)

            # US names: [0: 'cloud_US', 1: 'dew_US', 2: 'humidity_US', 3: 'ozone_US', 4: 'precip_US', 5: 'pressure_US', 
            #           6: 'sunrise_US', 7: 'sunset_US', 8: 'tMax_US', 9: 'tMin_US', 10: uv_US', 11: 'wind_US']

            try:

                updateCursor = con.cursor()

                # For Querying for specific longitude and latitude of location
                where_str = 'Lat = "{}" AND Long_ = "{}"'.format(latitude, longitude)

                # Updating cloud_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('cloud_US', day, weatherData['clouds'], where_str)
                updateCursor.execute(update_str)

                # Updating dew_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('dew_US', day, weatherData['dew'], where_str)
                updateCursor.execute(update_str)

                # Updating humidity_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('humidity_US', day, weatherData['hum'], where_str)
                updateCursor.execute(update_str)

                # updating ozone_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('ozone_US', day, weatherData['ozone'], where_str)
                updateCursor.execute(update_str)

                # updating precip_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('precip_US', day, weatherData['precip'], where_str)
                updateCursor.execute(update_str)

                # updating pressure_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('pressure_US', day, weatherData['pressure'], where_str)
                updateCursor.execute(update_str)

                # updating sunrise_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('sunrise_US', day, weatherData['sunrise'], where_str)
                updateCursor.execute(update_str)

                # updating sunset_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('sunset_US', day, weatherData['sunset'], where_str)
                updateCursor.execute(update_str)

                # updating tMax_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('tMax_US', day, weatherData['tMax'], where_str)
                updateCursor.execute(update_str)

                # updating tMin_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('tMin_US', day, weatherData['tMin'], where_str)
                updateCursor.execute(update_str)

                # updating uv_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('uv_US', day, weatherData['uvee'], where_str)
                updateCursor.execute(update_str)

                # updating wind_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('wind_US', day, weatherData['wind'], where_str)
                updateCursor.execute(update_str)

                con.commit()

                print(update_str)

            # displaying error
            except sqlite3.Error as er:
                print('SQLite error: %s' % (' '.join(er.args)))
                print("Exception class is: ", er.__class__)
                print('SQLite traceback: ')
                exc_type, exc_value, exc_tb = sys.exc_info()
                print(traceback.format_exception(exc_type, exc_value, exc_tb))

        else:
            print('Cell has a value')
        
        
        if counter == break_counter:
            break

            

    if counter == break_counter:
        break

# closing connection to database
con.close()
# close database connection

con.close()
# Updating GLOBAL data

# Going through tables and adding data to new dates

import pandas as pd
import numpy as np
import requests
import json
from datetime import datetime as dt

# Trying to capture sqlite error messages
# found https://stackoverflow.com/questions/25371636/how-to-get-sqlite-result-error-codes-in-python
import traceback
import sys

# Code to increase number of retries on connection errors,
# and also to give it some time.
# Found on https://stackoverflow.com/questions/15431044/can-i-set-max-retries-for-requests-request
# And https://findwork.dev/blog/advanced-usage-python-requests-timeouts-retries-hooks/

from urllib3.util.retry import Retry
from requests.adapters import HTTPAdapter

s = requests.Session()

retries = Retry(total=30,
                backoff_factor=0.1,
                status_forcelist=[ 429, 500, 502, 503, 504 ],
                method_whitelist=["HEAD", "GET", "OPTIONS"])

adapter = HTTPAdapter(max_retries=retries)
http = requests.Session()
http.mount("https://", adapter)
http.mount("http://", adapter)


# Making list of columns that need to be updated from prior update, above
columns_to_update = []

for x in range(1, 16):
    
    day_str = str(x)
    date_str = '"9/' + day_str + '/20"'
    columns_to_update.append(date_str)

# Counter
counter = 0
    
# value for determining when to break
break_counter = 11000
    

# Iterating through the columns that need to be updated
for day in columns_to_update:
    
    print(day)
    
    # Querying all the latitudes and longitudes for the given day
    query_str = 'SELECT Lat, Long, {} FROM {}'.format(day, global_names[0])
    print(query_str)
    cursorObj.execute(query_str)
    
    # putting query results into a variable 
    #rows = cursorObj.fetchall()
    
    # Create Unix time stamp out of the date column
    # stripping quotation marks from column name
    i = len(day)
    col_date = day[1:(i-1)]
    
    print(col_date)
    
    t = pd.to_datetime(col_date)
    t = int(t.value / 10**9)
    t = str(t)
    
    print(t)
    
    for row in cursorObj:
        print(row[0])
        print(row[1])
        print(row[2])
        
        latitude = row[0]
        longitude = row[1]
        
        print('Latitude: ', latitude, 'Longitude: ', longitude)
        
        if row[2] is None:
            
            print('Cell is Null')
            
            # Calling API based on the row's latitude, longitude, and the day in question
            info = call_api(str(latitude), str(longitude), t)

            counter = counter + 1
            # print(info)

            # putting the JSON response into a dictionary
            weatherData = sortOut(info)

            #Global names ['cloud_Global', 'dew_Global', 'humidity_Global', 'ozone_Global', 'precip_Global', 'pressure_Global', 
            #              'sunrise_Global', 'sunset_Global', 'tMax_Global', 'tMin_Global', 'uv_Global', 'wind_Global']

            try:

                updateCursor = con.cursor()

                # For Querying for specific longitude and latitude of location
                where_str = 'Lat = "{}" AND Long = "{}"'.format(latitude, longitude)

                # Updating cloud_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('cloud_Global', day, weatherData['clouds'], where_str)
                updateCursor.execute(update_str)

                # Updating dew_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('dew_Global', day, weatherData['dew'], where_str)
                updateCursor.execute(update_str)

                # Updating humidity_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('humidity_Global', day, weatherData['hum'], where_str)
                updateCursor.execute(update_str)

                # updating ozone_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('ozone_Global', day, weatherData['ozone'], where_str)
                updateCursor.execute(update_str)

                # updating precip_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('precip_Global', day, weatherData['precip'], where_str)
                updateCursor.execute(update_str)

                # updating pressure_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('pressure_Global', day, weatherData['pressure'], where_str)
                updateCursor.execute(update_str)

                # updating sunrise_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('sunrise_Global', day, weatherData['sunrise'], where_str)
                updateCursor.execute(update_str)

                # updating sunset_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('sunset_Global', day, weatherData['sunset'], where_str)
                updateCursor.execute(update_str)

                # updating tMax_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('tMax_Global', day, weatherData['tMax'], where_str)
                updateCursor.execute(update_str)

                # updating tMin_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('tMin_Global', day, weatherData['tMin'], where_str)
                updateCursor.execute(update_str)

                # updating uv_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('uv_Global', day, weatherData['uvee'], where_str)
                updateCursor.execute(update_str)

                # updating wind_US table
                update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('wind_Global', day, weatherData['wind'], where_str)
                updateCursor.execute(update_str)

                con.commit()

                print(update_str)

            # displaying error
            except sqlite3.Error as er:
                print('SQLite error: %s' % (' '.join(er.args)))
                print("Exception class is: ", er.__class__)
                print('SQLite traceback: ')
                exc_type, exc_value, exc_tb = sys.exc_info()
                print(traceback.format_exception(exc_type, exc_value, exc_tb))

        else:
            print('Cell has a value')
        
        
        if counter == break_counter:
            break

            

    if counter == 200:
        break

# closing connection to database
con.close()
# adding dates to tables
# current last date is 8/31/20

for x in range(1, 16):
    
    day_str = str(x)
    date_str = '"9/' + day_str + '/20"'
    print(date_str)
    
    for y in US_names:
        
        add_str = 'ALTER TABLE {} ADD COLUMN {} text'.format(y, date_str)
        cursorObj.execute(add_str)
        print(add_str)
    
    for y in global_names:
        
        add_str = 'ALTER TABLE {} ADD COLUMN {} text'.format(y, date_str)
        cursorObj.execute(add_str)
        print(add_str)
# Setting cell values to null

import sqlite3

gen_path = '/home/imants/gits/COVID-19/'

db_path = gen_path + 'sql/weather.db'

# connecting to the weather database
con = sqlite3.connect(db_path)

# establishing database cursor
cursorObj = con.cursor()

import pandas as pd
import numpy as np
import requests
import json
from datetime import datetime as dt

# Trying to capture sqlite error messages
# found https://stackoverflow.com/questions/25371636/how-to-get-sqlite-result-error-codes-in-python
import traceback
import sys

table_names = cursorObj.execute("SELECT name FROM sqlite_master WHERE type='table';")

table_names = table_names.fetchall()

# Taking the table names that were fetched, and making a usable lists out of the values.
US_names = []
global_names = []

for x in range(len(table_names)):
    if (table_names[x][0][-2:] == 'US'):
        US_names.append(table_names[x][0])
        
    else:
        global_names.append(table_names[x][0])

# Making list of columns that need to be updated from prior update, above
columns_to_update = []

for x in range(13, 32):
    
    day_str = str(x)
    date_str = '"8/' + day_str + '/20"'
    columns_to_update.append(date_str)

# Counter
counter = 0
    

# Iterating through the columns that need to be updated
for day in columns_to_update:
    
    print(day)
    
    # Querying all the latitudes and longitudes for the given day
    query_str = 'SELECT Lat, Long_, {} FROM {}'.format(day, US_names[0])
    print(query_str)
    cursorObj.execute(query_str)
    
     
    # Create Unix time stamp out of the date column
    # stripping quotation marks from column name
    i = len(day)
    col_date = day[1:(i-1)]
    
    print(col_date)
    
    t = pd.to_datetime(col_date)
    t = int(t.value / 10**9)
    t = str(t)
    
    print(t)
    
    for row in cursorObj:
        print(row[0])
        print(row[1])
        print(row[2])
        
        latitude = row[0]
        longitude = row[1]
        
        print('Latitude: ', latitude, 'Longitude: ', longitude)
        
        counter = counter + 1
                
        
        # US names: [0: 'cloud_US', 1: 'dew_US', 2: 'humidity_US', 3: 'ozone_US', 4: 'precip_US', 5: 'pressure_US', 
        #           6: 'sunrise_US', 7: 'sunset_US', 8: 'tMax_US', 9: 'tMin_US', 10: uv_US', 11: 'wind_US']
        
        try:
                   
            updateCursor = con.cursor()

            # For Querying for specific longitude and latitude of location
            where_str = 'Lat = "{}" AND Long_ = "{}"'.format(latitude, longitude)

            # Updating cloud_US table
            update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('cloud_US', day, 'NULL', where_str)
            updateCursor.execute(update_str)
            
            # Updating dew_US table
            update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('dew_US', day, 'NULL', where_str)
            updateCursor.execute(update_str)
            
            # Updating humidity_US table
            update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('humidity_US', day, 'NULL', where_str)
            updateCursor.execute(update_str)
            
            # updating ozone_US table
            update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('ozone_US', day, 'NULL', where_str)
            updateCursor.execute(update_str)
            
            # updating precip_US table
            update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('precip_US', day,'NULL', where_str)
            updateCursor.execute(update_str)
            
            # updating pressure_US table
            update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('pressure_US', day, 'NULL', where_str)
            updateCursor.execute(update_str)
            
            # updating sunrise_US table
            update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('sunrise_US', day, 'NULL', where_str)
            updateCursor.execute(update_str)
            
            # updating sunset_US table
            update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('sunset_US', day, 'NULL', where_str)
            updateCursor.execute(update_str)
            
            # updating tMax_US table
            update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('tMax_US', day, 'NULL', where_str)
            updateCursor.execute(update_str)
            
            # updating tMin_US table
            update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('tMin_US', day, 'NULL', where_str)
            updateCursor.execute(update_str)
            
            # updating uv_US table
            update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('uv_US', day, 'NULL', where_str)
            updateCursor.execute(update_str)
            
            # updating wind_US table
            update_str = 'UPDATE {} SET {} = {} WHERE {}'.format('wind_US', day, 'NULL', where_str)
            updateCursor.execute(update_str)
            
            con.commit()

            print(update_str)
        
        # displaying error
        except sqlite3.Error as er:
            print('SQLite error: %s' % (' '.join(er.args)))
            print("Exception class is: ", er.__class__)
            print('SQLite traceback: ')
            exc_type, exc_value, exc_tb = sys.exc_info()
            print(traceback.format_exception(exc_type, exc_value, exc_tb))
        
        if counter == 1000:
            break
        
        
    
    if counter == 1000:
        break

con.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11611572061986918, 'fraction_numerical': 0.010404319768207559, 'mean_word_length': 2.2302892796369824, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 10, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '41152', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 6939, 'n_tokens_neox': 6496, 'n_words': 1772, 'path': 'notebooks/.ipynb_checkpoints/SQL-checkpoint.ipynb', 'repo_name': 'imantsm/COVID-19', 'size': '22779'}