Hello,
I followed the example here to try and get more data into mysql table. https://www.visualcrossing.com/resources/blog/how-to-import-weather-data-into-mysql/
However, when I run the script I'm getting errors like keyerror: 'tempmax'. I think this is in SQL? Anyway, here's my script and SQL table create. Could use another set of eyes on my config to catch any settings that I'm missing. Thanks in advance!
SQL -------------------
CREATE TABLE `weather_data` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`datetime` DATETIME NULL DEFAULT NULL,
`tempmax` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`tempmin` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`temp` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`feelslikemax` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`feelslikemin` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`feelslike` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`dew` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`humidity` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`precip` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`precipcover` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`precipprob` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`preciptype` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`snow` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`snowdepth` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`winddir` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`windgust` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`windspeed` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`sealevelpressure` DECIMAL(6,2) NOT NULL,
`cloudcover` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`visibility` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`solarradiation` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`solarenergy` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`uvindex` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`severerisk` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`sunrise` DATETIME NULL DEFAULT NULL,
`sunset` DATETIME NULL DEFAULT NULL,
`moonphase` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
`conditions` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_general_ci',
`description` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_general_ci',
`icon` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_general_ci',
PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
------------------------
Python -------------------------------------------------------------------------------
import urllib.request
import json
import mysql.connector
from datetime import date, datetime, timedelta
# This is the core of our weather query URL
BaseURL = 'https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/weatherdata/'
ApiKey='xxxx'
#UnitGroup sets the units of the output - us or metric
UnitGroup='us'
#Locations for the weather data. Multiple locations separated by pipe (|)
Locations='Woodstock,GA'
#FORECAST or HISTORY
QueryType='FORECAST'
#1=hourly, 24=daily
AggregateHours='24'
#Params for history only
StartDate = ''
EndDate=''
# Set up the specific parameters based on the type of query
if QueryType == 'FORECAST':
print(' - Fetching forecast data')
QueryParams = 'forecast?aggregateHours=' + AggregateHours + '&unitGroup=' + UnitGroup + '&shortColumnNames=true'
else:
print(' - Fetching history for date: ', DateParam)
# History requests require a date. We use the same date for start and end since we only want to query a single date in this example
QueryParams = 'history?aggregateHours=' + AggregateHours + '&unitGroup=' + UnitGroup +'&startDateTime=' + StartDate + 'T00%3A00%3A00&endDateTime=' + EndDate + 'T00%3A00%3A00'
Locations='&locations='+Locations
ApiKey='&key='+ApiKey
# Build the entire query
URL = BaseURL + QueryParams + Locations + ApiKey+"&contentType=json"
print(' - Running query URL: ', URL)
print()
response = urllib.request.urlopen(URL)
data = response.read()
weatherData = json.loads(data.decode('utf-8'))
print( "Connecting to mysql database")
#connect to the database. Enter your host, username and password
cnx = mysql.connector.connect(host='192.168.1.101',
user='user',
passwd='pw',
database='weather')
cursor = cnx.cursor()
# In this simple example, clear out the existing data in the table
delete_weather_data=("TRUNCATE TABLE `weather`.`weather_data`")
cursor.execute(delete_weather_data)
cnx.commit()
# Create an insert statement for inserting rows of data
insert_weatherdata = ("INSERT INTO `weather`.`weather_data'"
"('datetime', 'tempmax', 'mint', 'temp', 'feelslikemax', 'feelslikemin', 'feelslike', 'dew', 'humidity', 'precip', 'precipprob', 'precipcover', 'preciptype', 'snow', 'snowdepth', 'windgust', 'windspeed', 'winddir', 'sealevelpressure', 'cloudcover', 'visibility', 'solarradiation', 'solarenergy', 'uvindex', 'severerisk', 'sunrise', 'sunset', 'moonphase', 'conditions', 'description', 'icon')"
"VALUES (%(datetime)s, %(tempmax)s, %(mint)s, %(temp)s, %(feelslikemax)s, %(feelslikemin)s, %(feelslike)s, %(dew)s, %(humidity)s, %(precip)s, %(precipprob)s, %(precipcover)s, %(preciptype)s, %(snow)s, %(snowdepth)s, %(windgust)s, %(windspeed)s, %(winddir)s, %(sealevelpressure)s, %(cloudcover)s, %(visibility)s, %(solarradiation)s, %(solarenergy)s, %(uvindex)s, %(severerisk)s, %(sunrise)s, %(sunset)s, %(moonphase)s, %(conditions)s, %(description)s, %(icon)s")
# Iterate through the locations
locations=weatherData["locations"]
for locationid in locations:
location=locations[locationid]
# Iterate through the values (values are the time periods in the weather data)
for value in location["values"]:
data_wx = {
'datetime': datetime.utcfromtimestamp(value["datetime"]/1000.),
'tempmax': value["tempmax"],
'tempmin': value["tempmin"],
'temp': value["temp"],
'feelslikemax': value["feelslikemax"],
'feelslikemin': value["feelslikemin"],
'feelslike': value["feelslike"],
'dew': value["dew"],
'humidity': value["humidity"],
'precip': value["precip"],
'precipcover': value["precipcover"],
'precipprob': value["precipprob"],
'preciptype': value["preciptype"],
'snow': value["snow"],
'snowdepth': value["snowdepth"],
'winddir': value["winddir"],
'windgust': value["windgust"],
'windspeed': value["windspeed"],
'sealevelpressure': value["sealevelpressure"],
'cloudcover': value["cloudcover"],
'visibility': value["visibility"],
'solarradiation': value["solarradiation"],
'solarenergy': value["solarenergy"],
'uvindex': value["uvindex"],
'severerisk': value["severerisk"],
'sunrise': value["sunrise"],
'sunset': value["sunset"],
'moonphase': value["moonphase"],
'conditions': value["conditions"],
'description': value["description"],
'icon': value["icon"],
}
cursor.execute(insert_weatherdata, data_wx)
cnx.commit()
cursor.close()
cnx.close()
print( "Database connection closed")
print( "Done")
