Estimated reading time: 8 minutes
In this article, we are going to import weather data into a MySQL database. In addition to setting up the database tables, we will demonstrate techniques to import both historical weather data and weather forecast data.
We will include the ways to retrieve the weather data and how to set up a script so the weather data can be refreshed on a schedule.
Weather data provider
We are going to use a Weather API that supports retrieving weather data as a web service. The data includes historical weather observations and weather forecast data and is available in a range of formats, including JSON and CSV import files, so the data is perfect to load into MySQL. There is also a free API plan available.
If you don’t have an account, you can simply sign up for a free trial for Weather Data Services to obtain access. For help on getting started with the Weather Data Services page, see Getting Started With Weather Data Services.
Setting up the MySQL Database
Our first step is to create a MySQL database and create an empty table within the new database where the weather data will reside. In this example, we are using MySQL version 8. We won’t be doing anything particularly complex, however, and so other versions should work as well.
First, we set up a new database schema for holding the weather data. We have named our new schema ‘weather_data_schema’. Within this schema, we have created a single table called ‘weather_data’.
Both weather history data and weather forecast data hold the same fundamental data, and so we can use the same table for both history and forecast results. In more advanced cases, different tables may be useful to include some of the data that is specific to historical data or forecast data.
To create the table, we use the following script. To make things simple, we have named our columns with the same column names as the incoming weather data variable names.
CREATE TABLE `weather_data` (
`address` varchar(256) DEFAULT NULL,
`latitude` float DEFAULT NULL,
`longitude` float DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
`maxt` float DEFAULT NULL,
`mint` float DEFAULT NULL,
`temp` float DEFAULT NULL,
`precip` float DEFAULT NULL,
`wspd` float DEFAULT NULL,
`wdir` float DEFAULT NULL,
`wgust` float DEFAULT NULL,
`pressure` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
We now have a database with an empty weather data table. We are reading to load some weather data!
Loading the weather data into MySQL using Python
As we mentioned above, we are going to use a weather web service called the Visual Crossing Weather API to retrieve the weather data we are interested in. In our first example, we are going to create a Python script that we can run at regular intervals to retrieve the weather forecast data.
Prerequisites
Our Python script was written in Python 3.8.2. In addition to the core Python, we installed the MySQL Connector for Python. Finally, we had some errors about missing libraries for dns so we had to install DNSPython. This last dependency may not be required when you create script.
Here’s the full list of import statements in our script
import urllib.request
import json
import mysql.connector
from datetime import date, datetime, timedelta
Script part one – setting up the input parameters
Our Python script is split into two parts. The first part downloads the weather data and the second part inserts the weather data into the MySQL database table. The weather data is retrieved using a RESTful weather api. This means that we simply have to create a web query within the Python script and download the data.
The first part of the sets up some variables to customize the weather data that is entered. In your script you may want to extract these as parameters to the script.
# This is the core of our weather query URL
BaseURL = 'https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/weatherdata/'
ApiKey='INSERT_YOUR_KEY_HERE'
#UnitGroup sets the units of the output - us or metric
UnitGroup='us'
#Locations for the weather data. Multiple locations separated by pipe (|)
Locations='Washington,DC'
#FORECAST or HISTORY
QueryType='FORECAST'
#1=hourly, 24=daily
AggregateHours='24'
#Params for history only
StartDate = ''
EndDate=''
As the code demonstrates, you should enter your API key to set the value of the variable ‘ApiKey’. You can also switch the query between forecast and historical weather data. The Locations parameter is a pipe (|) separated list of location addresses, partial address or longitude,latitude values for the weather data retrieval. For more information on the full set of Weather API parameters, see the Weather API documentation.
Script part two – downloading the weather data
The next section of code creates the Weather API request from the parameters, submits the request to the server and then parses the result.
# 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'))
In this case, we are using the JSON result to retrieve the weather data for easy parsing when we insert the data into MySQL. The output JSON is formatted as follows. The top-level includes locations and columns information.
Each location’s weather data is found as an individual location instance. Below each location is an array of values. These values correspond to the individual time periods in the requested data, so they will be hourly or daily in our example.
For full information about the returned weather data, see the weather data documentation.
Script part three – insert the weather data into our MySQL table
Our next part of the script actually inserts the weather data into the database table. Here’s the code to set up the connection and sets up the SQL statements. Note that in this example we simply empty any existing data within the weather data table. There’s no reason to do that if you would prefer to build up a record of the weather data.
print( "Connecting to mysql database")
#connect to the database. Enter your host, username and password
cnx = mysql.connector.connect(host='127.0.0.1',
user='YOUR_USERNAME',
passwd='YOUR_PASSWORD',
database='weather_data_schema')
cursor = cnx.cursor()
# In this simple example, clear out the existing data in the table
delete_weather_data=("TRUNCATE TABLE `weather_data_schema`.`weather_data`")
cursor.execute(delete_weather_data)
cnx.commit()
The next part of the script then iterates through the locations and then the weather data values within the locations. The code creates statement to perform the insert. Then during the value loop it creates a parameters from the weather data.
# Create an insert statement for inserting rows of data
insert_weather_data = ("INSERT INTO `weather_data_schema`.`weather_data`"
"(`address`,`latitude`,`longitude`,`datetime`,`maxt`,`mint`,`temp`,`precip`,`wspd`,`wdir`,`wgust`,`pressure`)"
"VALUES (%(address)s, %(latitude)s, %(longitude)s, %(datetime)s, %(maxt)s,%(mint)s, %(temp)s, %(precip)s, %(wspd)s, %(wdir)s, %(wgust)s, %(pressure)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 = {
'address': location["address"],
'latitude': location["latitude"],
'longitude': location["longitude"],
'datetime': datetime.utcfromtimestamp(value["datetime"]/1000.),
'maxt': value["maxt"] if 'maxt' in value else 0,
'mint': value["mint"] if 'mint' in value else 0,
'temp': value["temp"],
'precip': value["precip"],
'wspd': value["wspd"],
'wdir': value["wdir"],
'wgust': value["wgust"],
'pressure': value["sealevelpressure"]
}
cursor.execute(insert_weather_data, data_wx)
cnx.commit()
cursor.close()
cnx.close()
print( "Database connection closed")
print( "Done")
After the script executes, we can see the data if we fresh the data inside MySQL.
Next steps
If you would like the full code, you can find it in our Github repository. In this article, we’ve seen how easy it is to insert weather data into a database such as MySQL. Do you have questions? Please ask below!
FAQs: Importing Weather Forecast and Historical Data into MySQL
How can I import weather forecast data into a MySQL database?
You can import data using a simple Python script and the Visual Crossing Weather API. The API retrieves forecast weather conditions in a structured format like JSON or CSV.
Once fetched, the weather data can be parsed and inserted into your MySQL database using standard SQL INSERT statements through a database connection. The sample script provided in the article shows how to set up the request, load the data, and push it to the database in one smooth process.
Can I store both historical and forecast weather data in the same MySQL table?
Yes. Forecast and historical weather data share many common weather elements such as temperature, humidity, pressure, and wind speed, making it easy to store both in the same table. The sample table in the article uses the same schema for both types, which simplifies the import process. You can later separate or filter records by timestamp, datetime, or other identifiers.
What format does the Visual Crossing Weather API use for data output?
The API supports both CSV-file and JSON output formats. The example script uses JSON because it’s well-suited for Python code and easy to parse.
Each API response includes fields such as maxt, mint, temp, precip, wspd, and pressure, aligned with real-world weather station observations. These values can be directly mapped to the corresponding SQL columns in your MySQL table.
Do I need to use Python to load data into MySQL?
Python is commonly used due to its flexibility, built-in libraries, and broad support for data processing. The tutorial uses Python 3.8.2 along with mysql.connector and urllib to create a database connection, retrieve weather data, and execute insert operations.
However, you can use other languages like PHP, Node.js, or Java—anything that can make HTTP requests and interact with MySQL. Python is simply convenient for automating and testing data import pipelines.
What API parameters are required to retrieve weather data for MySQL?
To build your weather data query, you’ll need to define key parameters in your script:
- Locations: a single address or a pipe-separated list
- QueryType: either FORECAST or HISTORY
- AggregateHours: typically 1 for hourly or 24 for daily data
- UnitGroup: choose us or metric for temperature and wind units
- StartDate and EndDate: used for historical weather requests
- ApiKey: your personal API key to authenticate the request
These variables are assembled into a RESTful URL, which the script uses to fetch data from Visual Crossing’s server. The endpoint supports flexible queries, whether you’re retrieving one day of past weather or a 15-day forecast.
Can I schedule weather data imports automatically using a script?
Absolutely. Once the Python script is working and tested, you can schedule it using tools like cron jobs (Linux/macOS) or Task Scheduler (Windows). You can configure it to run every day, hour, or based on any custom interval to regularly load data into your MySQL server.
This is particularly useful for building up weather logs over time or maintaining a live weather display or business intelligence dashboard with the most recent weather conditions.
How do I parse JSON weather data for MySQL insertion?
The API’s JSON response includes a list of locations, and under each, an array of values representing each time slice (hour or day). You parse these records using Python’s json module.
For each data row, extract relevant weather metrics such as temp, wspd, precip, pressure, etc., and map them to your SQL table using parameterized queries. The sample script uses Python’s datetime.utcfromtimestamp() to convert the datetime field into a proper SQL timestamp before inserting.