Project in progress
OpenWeatherMap to SQL-Database Demo

OpenWeatherMap to SQL-Database Demo

Demo to show online and send data to your SQL server.

  • 8 respects

Components and supplies

Apps and online services

About this project


If you like to add weather info to your connected Arduino, is a free and easy to use service. It supplies weather info, but you can also use it to look up GPS position of a city, or receive the exact time (i.e. in case you don't want to use an NTP-server routine). Register on the site, generate an ApiKey and your are free to go. API calls look like this:, NL&APPID=dae1915abab0b947455a8dc969bb3b67&cnt=1&units=metric

and it returns a string of data in JSON-format. For this project we use the weather by city, but you can modify for other services like forecast info. Try this in your browser to verify the results. The JSON string can be parsed to pick out the data you need, and there is an excellent library by to have this done:


The library at is very easy to use, its well developed and very flexible - although its wise to dig a bit deeper into the use of strings and pointers. Install the library in your Arduino IDE, and take a look at some examples how JSON strings are parsed: link

As I like to avoid the use of the 'String' type (as its not always so stable out of my experience), I use Char* to store string data, and in the library I use array-copy functions. All this is combined in a library:

OpenWeather Arduino Library

This library creates a new object type called OpenWeather, by calling the object function .setup(char* key) the APi key can be attached. by calling .getweather(char* Location) the weather data is retrieved of the Location, and stored in the object-variables. easy, simple, straight forward - see example code.

Install the library in your Arduino Library-directory (unzip). As the library is build with the MKR1010 WiFi library (WifiNINA.h), you can - if required - change this for your own wifi-board by changing the wifi-lib in OpenWeather.h:

//#define DEBUG_ON
#include "Arduino.h"
#include <ArduinoJson.h>
#include <WiFiNINA.h>                  // Replace with your own Wifi Library
#define MAXBUFFER 2000                // MAXBUFFER DATA
class OpenWeather
 void setup(char *key);
 void setupserver(char *srvr);
 char* raw();
 void getweather(char *Loc);
 char WeatherApiKey[64];
 char WeatherServer[128]; 
 char WeatherBuffer[MAXBUFFER]; 
 float Longitude;
 float Latitude;
 char Main[24];
 char Description[64]; 
 float Temperature; 
 long Pressure;
 long Humidity; 
 float Windspeed;
 long Winddirection;    
 private:                      // 
 int _cstring(char *c1,char *c2);
 int _cstringc(const char *c1,char *c2);

MySQL-Server Upload

Second part in this demo is storing the weather data in an MySQL database. Its not really practical (as this info is on all the time anyway), but for IoT demo purposes its nice :) For this demo I'm using a local MySQL-server on my Synology: enabled Web-Server (Nginx back end), installed Mariadb (MySQL) and PhPMyAdmin (SQL-Manager)

Tricky parts to check for setting up the MySQL:

  • Create a new user (ie Arduino) with read/write allowance on your server-directory, give this user database Priviliges in PhPMyAdmin.
  • Install PhP (5.6 in this case), enable PHP - use phpinfo(); to check enables features
  • Enable PHP extensions -mysqli for MySQL access, and -gd (Graphics)

To setup the Database follow this example:

Example icreateproject - setup your database with PhPMyAdmin


To move the data from the Arduino to the SQL database, we call PHP-scripts. in this case I use the HTTP-Get method (POST method is also possible, especially if you have larger sets of data - future post). I don't use any encryption of secure-call (SSL), as this runs on my local network. Calls look like this :

My local web-server IP is, I made a web directory 'weather' and call the script write_data.php, and pass variable temp, press and hum. This is the php script that passes the code to the MyQSL database:

include ('connect.php');
   // Prepare the SQL statement
   $sql_insert = "INSERT INTO data (description,temp,hum,press,speed,dir) VALUES ('".$_GET["description"]."','".$_GET["temp"]."' ,'".$_GET["hum"]."','".$_GET["press"]."' ,'".$_GET["speed"]."' ,'".$_GET["dir"]."' )";  
   // Execute SQL statement
echo "Done";
echo "error is ".mysqli_error($con );

The connect.php include file creates the database connection-string ($con) - see zip-file. Edit thjis file with the Web-Server user and password name (ie arduino). We can use a second php-script to call the database info, and put it in a table: display.php, result looks like this :

PChart Graphics in PhP

Additional it's possible to set the information gathered over time in a graphics form. There are many graphics libraries available, for this example I'm using pchart, an class-oriented library withe many options.

Install the library in the webs-erver path, and be sure you have the -gd extension enabled in PHP. See graphics.php as an example :


OpenWeather library for Arduino
No preview (download only).
Php Script files for OpenWeather example
No preview (download only).
Arduino Script file
Open Weather Demo for MKR1010 (Wifi with UBLox NINA module / Espressif = wifi)
J.Vos 2019

Stream OpenweatherMaps data to your SQL server, and make PHP Graphics or lists.

Open Weather API :
SQL example :

#include <WiFiNINA.h>         // change to your Wifi or Ethernet library - also change this in the openweather.h library !
#include <WiFiUdp.h>          // for UDP NTP Server - get time
#include <RTCZero.h>          // for clock ticks
#include <OpenWeather.h>
#include "arduino_secrets.h"  // Secret info on Pass qwords and API keys etc.
#define DEBUG_X 1             // Debug info via Serial Print

//Define ServerSettings
#define VERSION "1.1.0"              // Software verson 
#define SSERVER_PORT 80              // SQL Server Port
char apiKey[]= SECRET_APIKEY; 
char ssid[] = SECRET_SSID;           // your network SSID (name) - arduino_secrets.h
char pass[] = SECRET_PASS;           // your network password - arduino_secrets.h
IPAddress SQLServer(192,168,200,11); //  SQL server for dAta logging (local IP adress of my Synlogy server)

// Define RTC timers
#define LIFETICK 300                 // Life tick in seconds 
RTCZero rtc;                         // RTC Clock
long LogCheckTime;
unsigned long epoch= 1541062800UL;   // epoch time global variable

OpenWeather MyWeather;               // OpenWeathermaps Object
char Loc[]= "Amsterdam,NL";          // Location string  - see how to use the API interface
void setup()

/*********** Serial SETUP  **********/
int t=10;  //Initialize serial and wait for port to open, max 10 second waiting
  while (!Serial) {
    ; delay(1000);
    if ( (t--)== 0 ) break;
/*********** RTC SETUP  **********/
rtc.setEpoch(epoch);                             // Set Time RTC to base time
/*********** WIFI SETUP  **********/
if (WiFi.status() == WL_NO_SHIELD) {             // check for the presence of the shield:
  #if DEBUG_X
    Serial.println("WiFi shield not present");
  while (true);                                  // don't continue if no shield
  #if DEBUG_X
   Serial.println("WiFi starting...");
StartMyWifi();                                   // Start Wifi login 
/*********** Weather Object SETUP  **********/
Serial.print("\n# Main Weather : ");Serial.print(MyWeather.Main);
Serial.print(" / ");Serial.println(MyWeather.Description);
LogCheckTime = rtc.getEpoch() + LIFETICK;        // Set initial Log Check timer
} // endSetup

// Main SUPER Loop
void loop()
  Serial.print("."); delay(5000);
} // end Main loop

// Time Keeper , do the house keeping : check log time, send data to SQL server
void ManageTimeKeeper()

if ( rtc.getEpoch()>= LogCheckTime ){                                 // if log Timer: check weather and log SQL
#if DEBUG_X  
   Serial.print("\n# Log SQL");
  LogCheckTime = rtc.getEpoch()+LIFETICK;
  if ( (WiFi.status() != WL_CONNECTED) || (WiFi.RSSI() <= -90) || (WiFi.RSSI() ==0) ) StartMyWifi();  // check wifi in case not connected : re-connect

/**** SQLroutine  *****/
void  LogSQLServer(){

WiFiClient clients;

if (clients.connected() ) {                                                   // check connected
  clients.print("GET /weather/write_data.php?");
  clients.print("description=");clients.print(MyWeather.Description);         // write PhP command
  clients.println(" HTTP/1.1");
  clients.print("Host: ");clients.println(SQLServer);
  clients.println("Connection: close");
while ( clients.connected() ) clients.stop();
    Serial.print("*SQL data logged");           // print a message out the serial port

 #if DEBUG_X
    Serial.println("*SQL not connected");           // print a message out the serial port

/**** WIFI ROUTINES  *****/
// Login to local network  //
void StartMyWifi(){
int t=0;
int status = WiFi.status();

WiFi.disconnect(); delay(3000); printWiFiStatus();

if ( (status != WL_CONNECTED) || (WiFi.RSSI() <= -90) ||(WiFi.RSSI() ==0) ) {                  // only try if not yet connected
  while ( ((status != WL_CONNECTED) || (WiFi.RSSI() <= -90) || (WiFi.RSSI() ==0) ) && t<5) {   // attempt to connect to WiFi network, max 5 times
       Serial.print("Attempting to connect to Network named: ");
       Serial.print(ssid);                                                                     // print the network name (SSID);
       status = WiFi.begin(ssid, pass);                                                        // Connect to WPA/WPA2 network.
       delay(2000);                                                                            // wait 2 seconds for connection:
       t=t+1;                                                                                  // try-counter
  if ( status == WL_CONNECTED ) {
       //server.begin();                                                                       // start the web server outside this loop!
       printWiFiStatus();                                                                      // you're connected now, so print out the status
  else{                                                                                        // no connection possible : exit without server started
    Serial.println("Connection not possible after several retries.");                   
    Serial.println("Already connected.");                                                     // you're already connected

// SERIALPRINT Wifi Status - only for debug
void printWiFiStatus() {
  if (Serial) {
    // print the SSID of the network you're attached to:
    Serial.print("\nSSID: ");

    // print your WiFi shield's IP address:
    IPAddress ip = WiFi.localIP();
    Serial.print(" - IP Address: ");

    // print the received signal strength:
    long rssi = WiFi.RSSI();
    Serial.print("- Rssi: ");
    Serial.println(" dBm");


simple diagram
Simple schematic obadp4ynhx


Similar projects you might like

Alexa Controlled Door Sign Demo

Project tutorial by 3magku

  • 8 respects

Arduino + Raspberry Pi + Ubidots Made Easy

Project tutorial by David Escobar

  • 43 respects

Arduino WeatherShield

Project tutorial by Freedomotic Team

  • 4 respects

HomeLife Nightstand

Project showcase by Florian Siess

  • 1 comment
  • 6 respects

Close Loop Control System Demo Using LED Light and LDR

Project tutorial by ambhatt

  • 1 comment
  • 3 respects
Add projectSign up / Login