Skip to main content

Gold filling

What could possibly be worse than a routine trip to a dentist that culminates in the "surprise" discovery of a gargantuan dental cavity that needs to be treated?  Like, urgently.  Of course, there are some things that we can think of off the top of our aching heads; like if our local pharmacy announced that they will only accept payments in cryptocurrencies, or if there was a world wide shortage in quality I.P.A., otherwise known as ibuprofen, paracetamol and aspirin. 

But - arguably - staggering home after the expensive appointment with a face paralysed from anaesthetics, only to find that we have a mysterious gap in our sensor data is worse.  Much worse.  Because - like teeth - lost sensor readings when gone, are gone forever.  But unlike decaying teeth, they cannot be partially filled.  Or repaired. And if these are minutely temperature measurements from that smart, Internet-connected, integrated thermometer thingummy attached to the compost bin outside, well, our entire groundbreaking research into the statistical correlation between global warming and mid-afternoon pong is in jeopardy.  What a stink.

Apparently it was OK for the home broadband router to be powered off while we were strapped to the dentist's chair having our enamels carved into miniature statues of SpongeBob, because the little ones needed a wall socket for their Hello Kitty radio.  But never ones to admit defeat, we're going to fightback.  Because our devices should perform equally questionably, consistently dodgily, whether connected to a network or not.  And we ought to be able to back-fill any data, like a true £150 an hour professional.


There's not a lot of hardware spaghetti needed for this particular recipe.  In fact, we only require these off-the-shelf pasta shapes:

  • A Raspberry Pi running Raspbian OS.  The only requirement we have to place on this thing is that it can run Python and SQLite, and that it can (every now and again if requested to do so nicely) connect to the Wild Wild Web.  This is all pretty standard stuff for the humble Pi, however.  And not worth the additional sentence.  Yes, this additional sentence.  And this one.
  • If we are out and about again in the wilderness, which we will be, then the only likely way in which we can connect to the aforementioned inter-webular network of people's unsolicited thoughts expressed in Tweets and VBlogs, is via a mobile phone with the occasional signal.  A minute or two of the smartphone configured as a hotspot for tethering should be long enough for our strange curiosity to make contact with its mothership - AWS IoT.  In short, sufficient time to dispatch back all accumulated readings since the last successful update.
  • In theory, we are sending back sensor readings.  Or GPS coordinates.  Or probably both.  But we've already done a tonne of this back in I-O-Tea.  And it has become a tad tedious already.  In this outing, we'll simply pretend that we have some made-up temperature readings.  Like a true climate change denier

Ultimately, it doesn't matter.  If whatever package we have sits snugly inside a brightly-coloured 3D-printed case, and has an innocuous-looking aerial protruding out of it, we're in business.  Welcome to our family of bizarre, portable devices.  Everything else (including reliability and usability) becomes secondary to the illusion of something looking smart from this point onwards.

'Um disease:

Um... Sure. So this is actually the very first blog in our brand spanking new series, um... focusing on AWS IoT usage in the wild.  It's called I-O-Mr-T.  And there's nothing much else to say here, really.

Um... the previous series was I-O-Tea, and after 10 posts, it... um... generally descended into controlled mayhem and anarchy.  Except we did achieve a lot.  And it did set the foundations for pretty much everything we do in this new blog series.  Um. Um.


Your upcoming dental (dis)appointment:

Here's a very simple treatment plan, for all 21 of our decaying teeth (hopefully all covered by our dental insurance).  Because like munching through a super-sized bag of Haribos before breakfast every morning, all elements need to be carefully tackled in a certain order.  Enjoy!

  • Install SQLite on the Raspberry Pi, or more specifically, sqlite3.  It's better than 1 or 2.  Just because it's 3.
  • Play around with sqlite3 (the client) and its estranged cousin once removed, sqlite3 (the Python library).  Create a database, table and number of records containing wholly doctored temperature data.  Then, run some SQL queries.  Why not?  This is the table we'll use to store our cached sensor readings that haven't yet been shipped back to AWS IoT.
  • Right, let's give this a try.  We'll upload our cached data to AWS IoT using its REST API HTTP endpoint.  We can use the Python Requests library for this.
  • ...Once we confirm that everything is working in isolation, we will need to write two simple applications; one to insert sensor readings into the table, and another to retrieve them and dispatch them back to AWS IoT.

/root filling:

All high jinks, jollity and merriment aside, there is actually a serious point behind all of this (sort of... stay with us).  We have a peculiar attachment to going outdoors equipped with random DIY IoT paraphernalia.  And these walks can be quite long (like, 40km+ long).  And we keep encountering fundamental issues when we start to venture away from the nearest branch of Costa Coffee, or Starbucks.  There is generally no Wi-Fi.  And no toilet facilities, for that matter.  Or someone nearby pretending to work for a hip start-up, who is in fact looking up filtered Instagram pictures of cats that resemble celebrities on their MacBook (like all day).

Take the 10 peaks of Brecon Beacons as an example.

We get sporadic cellular coverage (up to 4G) along the ridges and on the peaks, but rarely in-between.  And since we spend a vast proportion of our time lost in the valleys, our sensor readings go nowhere fast.  We could use LoRa, but there are no guarantees that there are LoRaWAN gateways in range, nor is it appropriate for large volumes of data, or uploads at regular intervals.  All a bit disappointing, because we end up storing all our data locally on the device, and retrieving them manually once back at base.  Not very smart.  Not very connected.

But we do have Internet, right?  Sometimes?  Sure.

What we therefore need for any solution to be more interactive outdoors is a way to cache our sensor readings.  Store them on the device in the first instance, but upload them in batches whenever there is temporary connectivity.  Of course, this won't happen in real-time.  But it's better than no-time.  And we shouldn't lose any measurements.  Any interested parties can (semi-)track actual readings as the journey progresses.  Everyone wins, except unlucky family members who get spammed with cryptic email messages like {"temperature": 13}.  Sorry, GDPR.  These recipients implicitly signed up to to this service when they agreed to have us in the family.

And the most efficient way to smooth over this online / offline malarkey would be to use a local database on the device.  To this end, we'll use a SQLite database running to cache our sensor readings.  Then, we'll have a separate application that queries this database's table for readings that haven't been successfully uploaded yet, and uploads them using AWS IoT's HTTP endpoint whenever there is connectivity.

Family members: be aware.  You are all about to receive regular spam during our trips that contain meaningless sensor readings, and if you are lucky, GPS coordinates.

You never know.  It might actually be of use if we fall in to the rough sea off the Jurassic Coast.

Drill surgeon:

What would be lovely, is if we had access to a small, fast, self-contained, high-reliability, full-featured, SQL database engine. Preferably, the most used database engine in the world.  Oh look.  Here's one we found on the web.  How very convenient.

SQLite might not be so appropriate if we need a more fully fledged database engine which can store 10 petabytes of data on the colour and height of individual blades of glass found during our trek.  Or if the database is to be accessed by 10,233 botanists with an interest in the dataset.  Who all inexplicably run blockchain and machine learning on it.  But for the very little we want to achieve, this should be more than adequate.

Let's start by installing sqlite3 on the Raspberry Pi running RaspbianOS (if it isn't there already).  Being a commonly used, lightweight relational database, sqlite3 has a habit of already being installed on the operating system. 

sudo apt-get install sqlite3

Once installed, let's check the version.  Why?  We don't know, really.  Perhaps it makes us look like we know what we're doing?  Ah, no.  Let's do it just to check that it's actually there.

sqlite3 -version

Wow, that enlightened us somewhat.  Next, we'll create a database.  Which is actually as simple as running sqlite3 with the name of the new database file, in the directory that we want the file to be created.

sqlite3 riot.db

Then, we can run a whole bunch of SQL statements which we'll describe in a second.

Oops.  Here's what we just did...

We ran a CREATE TABLE... statement to guess what... create our table named sensor_data.  This is our tablette that we'll use to store our sensor readings in, and track if they have been uploaded or not.  Specifically, our JSON string will be stored in a payload TEXT column.  Column processed simply marks if the last upload of the record to AWS IoT Core was successful or not.

The auto-incrementing id INTEGER PRIMARY KEY column is also pivotal in all of this, since this will act as the unique identifier for that particular entry.

These are probably the bare minimum columns required for the functionality.  Nonetheless, we could add some more if there is a reason to.

We also ran two INSERT INTO statements to test that we can store our totally fictitious temperature readings.  Note that id is the primary key and auto-increments, and processed simply defaults to 0, so we only need to supply the payload.  This action is followed up by a SELECT * statement to see if the data has indeed been stored.

CREATE TABLE sensor_data (id INTEGER PRIMARY KEY, payload TEXT, processed INTEGER DEFAULT 0);
INSERT INTO sensor_data (payload) VALUES ('{"temp":10}');
INSERT INTO sensor_data (payload) VALUES ('{"temp":11}');
SELECT * FROM sensor_data;

This is all very good. But not very exciting.  It's time to finish the tiny filling on that molar, and move onto the root canal.  And there's no need for modern dentistry techniques, like anaesthetics.  Bite from a certain, ambivalent reptile will work just fine, here.

Python has a sqlite3 library, and we can use it to interact with our SQLite database programmatically.  It's apparently one degree warmer than from when we last run the INSERT INTO statement, so here is another reading to throw at this database.

import sqlite3
conn = sqlite3.connect("riot.db")
sensor_data_table = "sensor_data"
test_data = {"temp": 12}
cur = conn.cursor()
cur.execute("INSERT INTO " + sensor_data_table + " (payload) VALUES (?)", (str(test_data),)) 

Notice how we commit(), then close() our connection afterwards. SQLite can be left in a locked state if connections aren't closed properly (even when our code fails midway).

If we query the table again using sqlite3, we see our new entry there.  That worked, then.

In Herbal Essence, this is all that the application reading and storing sensor readings would have to do (instead of sending them directly to a message broker which would be the more typical action for a permanently connected device).  Once it has committed its JSON payload to the database, it can go on and do something else.  Like watch TV.  Or make a cup of I-O-Tea.

On the other hand, a separate application would need to retrieve values from this same database, and send them to AWS IoT Core.  That's fine.  We can use SELECT statements to achieve this.  Later, we would need to alter the statement to add a WHERE processed = 0 to make sure we only dispatch readings that haven't yet been uploaded.

import json
rows = cur.execute("SELECT payload FROM " + sensor_data_table)
payloads = []
for row in rows:
    payloads.append(json.loads(row[0].replace("'", '"')))

Great!  We have returned all three rows that we've inserted into the database so far, and have them in a list of dictionary objects.

Next, we need a little trick to test if the Pi has Internet connectivity.  What better way to test this, than to check if there is connectivity to the specific AWS IoT HTTP endpoint that we'll be using later to upload our data.

import socket
def test_connection(url, port):
    connection = False
        host = socket.gethostbyname(url)
        _socket = socket.create_connection((host, port), 2)
        connection = True
    return connection
test_url = ""
test_url_port = 443
test_connection(test_url, test_url_port)

test_url_port = 777 # do we feel lucky, punk?
test_connection(test_url, test_url_port)

When the remote AWS IoT Core HTTP endpoint was reachable, our function returns a True.  When we deliberately tried a different port on which the service isn't running, it failed.

This should provide us with sufficient assurances to confirm that there is network connectivity, and that the application should start uploading cached rows.  Of course, it can still fail later on during individual uploads.  But we can trap those exceptions individually as well (and make sure we don't set the processed flag column).

This is where we sort of have a choice.  We could either:

  • Use MQTT to upload our cached reading to AWS IoT Core, using the paho.mqtt library
  • Use REST API (HTTP) to upload our cached reading to AWS IoT Core, using the Requests library

We are providing an example each for both ('cause we're kind like that), but we do end up settling for the latter option for reasons explained later.

We've done MQTT literally a million times before.  So why not do it again.  Using certificates issued to our thing in AWS IoT Thing's registry, we can use paho.mqtt.publish.single() to send our payload to topic riot/brick.

import paho.mqtt.publish as mqtt_publish
import paho.mqtt.client as mqtt
import ssl
import json

broker_address = ""
broker_port = 8883
client_name = "riot-brick"
topic = "riot/brick"
client = mqtt.Client(client_name)
data = {"temperature": 13}
tls = {
    "certfile": "oursecret-certificate.pem.crt.txt",
    "keyfile": "oursecret-private.pem.key",
    "ca_certs": "",
    "tls_version": ssl.PROTOCOL_TLSv1_2,

Not a lot of fuss.  Nice and simple.  Perhaps too casual for our liking.

Did it get there?  Sure, it did.

But does the use of MQTT give us absolute assurances from the client-side that the data has been received successfully?  We're not so sure.

An alternative would be to use AWS IoT's HTTP(S) endpoint to deliver our payload.  Since this will be delivered through a REST API interface, it should provide us with an immediate response to state if the procedure succeeded.

Let's quickly test this out using curl to see if works.

curl --tlsv1.2 --cacert --cert oursecret-certificate.pem.crt.txt  --key oursecret.pem.key -X POST -d "{ \"temperature\": 50}" ""

There it is.  A rather reassuring instant response confirming the transaction.  This sure feels like a more reliable way of ensuring that our readings have been uploaded.  At which point, we can mark our record back in the SQLite database as processed.

Wow, it's getting hot around here quite quickly.  We better get the air-con on.  Oh, we forgot they don't exist in houses over here in the UK. We better spend the afternoon pretending to do some shopping in the local Sainsbury's frozen food aisle.

From an AWS IoT Core standpoint, MQTT or HTTP, it appears to make no difference.  The message is still received.  And that means it can go on and cause all the mischief it wants in the remainder of the AWS IoT portfolio, and beyond.  AWS IoT Analytics, IoT Events, Lambda, Elasticsearch Service - we're looking at you...

Lastly, we replace curl with the Python Requests module.   We do pretty much the same thing as we did before, but programmatically now from within Python using

import requests
import json
aws_iot_endpoint = ""
aws_iot_certs = ("oursecret-certificate.pem.crt.txt", "oursecret-private.pem.key")
aws_ca_certfile = ""
data = {"temperature": 60}
r =, cert=aws_iot_certs, verify=aws_ca_certfile, data=json.dumps(data))

HTTP status code 200 is good.  Very good.  Except the temperature.  It's time to relocate to Greenland.  Global warming is really starting to take its toll.

That's it.  We can bombard AWS IoT Core with our REST API POST requests using the magic of HTTP. And be reassured that they worked.

All in all, it doesn't take a stretch of our imagination to combine all this code to make two distinct applications (with only the database in common):

  • An application to take and store sensor readings in the table.  By default, these entries will be marked as processed = 0.  That's it.  The REST will be done by...
  • A separate application to retrieve from the table all sensor readings marked as processed = 0 and upload them to AWS IoT Core using the HTTP endpoint... only when connection is available.  Records successfully uploaded will be marked as processed = 1 while those that failed should remain unprocessed.  Repeat every few minutes.

Here's our rudimentary version in action (running at 1 minute intervals).  The exact Python code used forms part of our larger project, and can be found here on Github:

When we insert some records into the database between runs, they are eventually picked up during the uploader's next run, and posted to AWS IoT in one big batch.

Of course, there is one VERY important consideration to be made amidst all this.

Records aren't going to be arriving in AWS IoT Core in real-time, and not necessarily in the right order either.  In order to reassemble this data correctly in any time-series datastore, for example for graphical analysis using Kibana or Grafana, we are going to have to include a timestamp in the JSON payload.  This is not the timestamp of when the payload was sent up to the cloud.  Not when it was received by the cloud.  But when it was originally recorded back at source.  This way, the timestamp key can be used later in the datastore (such as the one underpinning Elasticsearch) to organise the records in the correct sequence.

This data from an afternoon run wasn't uploaded until we were back at home.  But thanks to the use of a timestamp attribute, the events can be reconstructed retrospectively.

It also means that this technique isn't always the right approach, especially if there are near real-time responses required.  For example, we wouldn't want a sprinkler system not to come on immediately because data from a fire detector has been cached somewhere.  Nor would we want it to suddenly spray the building full of water because it received the delayed payload several hours later (and after the system had in fact already been reset at the time, because it was a false alarm).

Throughout, we would also would want to monitor for the unusual absence of events, for which we could use a technique involving AWS IoT Events that we trialled in Pear Force One.  Clearly, we are expecting the device to remain offline for a while... but how long before we start to become suspicious?

Oh, it's done?  Already?

Our face is still numb from the anaesthetic, and we can't speak properly.  But at least we can now make our escape from the dentist's chair and get ourselves a nice coffee.  Interesting.  A fresh coffee from Caffè Nero is normally a lot hotter when it is first served... Oh well.

Gulp. Gulp.

Floss the rest:

SQLite documentation can be found here.  You should probably read it.
The official Python sqlite3 library is pretty essential if you want to do anything with Python and SQLite.
AWS IoT's HTTP REST interface is described blow.  It's worth a read if MQTT has been your thing so far.
We are using the ever-formidable Python Requests library to interact with the AWS IoT HTTP endpoint:



LoRa-Wan Kenobi

In the regurgitated words of Michael BublĂ©: It's a new dawn .  It's a new day .  It's a new Star Wars film .  For me .  And I'm (George Lucas, and I'm) feeling good .  Unfortunately for Canadian Mike, the Grammy that year was won by the novelty disco classic with the famous refrain: We love IoT, even in Planet Tatooine * . *Not true. Clearly, the Star Wars producers didn't sincerely mean the last Jedi the previous time around.  Return of the Jedi, released during the decade that spearheaded cultural renaissance 2.0 with the mullet and hair-metal , was less economic with the truth.  Either way, we're going to take inspiration from the impressive longevity of the money-spinning space-opera and reboot our franchise with some Jedi mind tricks.  Except this particular flick doesn't require an ever-growing cast of unrecognisable characters, unless ASCII or UTF counts.  In place of an ensemble gathering of Hollywood stars and starlets, we will b

Battle of BLEtain

The trolling . The doxing . An army of perplexing emojis. And endless links to the same - supposedly funny - viral video of a cat confusing a reflection from a dangling key for a golden hamster, while taking part in the mice bucket challenge. Has social media really been this immense force for good? Has it actually contributed significantly to the continued enlightenment of the human (or feline) race? In order to answer these poignant existential questions about the role of prominent platforms such as Critter, StinkedIn and Binterest, employing exceptional scientific rigour equal to that demonstrated by Theranos , we're going to set up a ground-breaking experiment using the Bluetooth Low Energy feature of MicroPython v1.12, and two ESP32 development boards with inexplicable hatred for one another.  And let them hurl quintessentially British expressions (others call them abuse) at each other like two Wiltshire residents who have had their internet access curbed by the co

Hard grapht

You would all be forgiven for assuming that bar , pie and queue line are favourite pastimes of the British .  Yet, in fact – yes, we did learn this back in GCSE maths – they are also mechanisms through which meaningless, mundane data of suspect origin can be given a Gok Wan -grade makeover, with the prime objective of padding out biblical 187-page PowerPoint presentations and 871-page Word reports (*other Microsoft productivity tools are available).  In other words, documents that nobody has the intention of ever reading.  But it becomes apparent over the years; this is perhaps the one skill which serves you well for a lifetime in certain careers.  In sales.  Consultancy.  Politics.  Or any other profession in which the only known entry requirement is the ability to chat loudly over a whizzy graph of dubious quality and value, preferably while frantically waving your arms around. Nevertheless, we are acutely conscious of the fact that we have spent an inordinate amount