RabbitMQ and Excel

RabbitMQ is one of the most popular messaging queue systems out there. If you’re a programmer there are client packages available for almost of the programming languages. However if you’re not a programmer your choices are limited in trying to interact with RabbitMQ.

In this blog I wanted to document some of my work in using Excel to read and write messages from RabbitMQ.

Getting Started

There are some great RabbitMQ guides. To get Excel talking to RabbitMQ I used the REST API that will be need to be installed.

For my Excel work I used Visual Basic for Applications (VBA) that is launched by Alt-F11. Next I created a Module (this is a container for VBA code), and I created two functions:

  • RMQ_getqueue – to get the first value in a queue
  • RMQ_exchangeWrite – to write a value to an exchange  (note: if an exchange has a binding to a queue. Writing to an exchange will be also be writing to a queue).

VBA_insert_module

Read Message Queues

For my example I focused on getting the first item in a queue. Message in a queue can be cleared as they are read if the option requeue = False.

 Function RMQ_getqueue(RabbitMQ_Server As String, QueueName As String, Optional rmq_user As String, Optional rmq_pswd As String, Optional requeue As Boolean)  
 ' Create a REST API get request to a Rabbitmq server and get the first item in a queue  
   Dim theJSON As Object, http As Object, scriptControl As Object  
   Dim theurl As String, theparams As String, rqmsg As String  
   Set http = CreateObject("MSXML2.XMLHTTP")  
 ' Use a Jscript eval command to evaluate the JSON, (so you don't have to add a JSON component  
   Set scriptControl = CreateObject("MSScriptControl.ScriptControl")  
   scriptControl.Language = "JScript"  
     
   theurl = "http://" & RabbitMQ_Server & "/api/queues/%2f/" & QueueName + "/get"  
    
 ' define a parameter list this must be JSON and the synax is pickie  
   If IsMissing(requeue) Then requeue = True  
   theparams = "{""count"":1,""requeue"":""" & LCase(requeue) & """,""encoding"":""auto"",""truncate"":50000}"  
   'Debug.Print theparams  
     
   http.Open "POST", theurl, False, rmq_user, rmq_pswd  
   http.setRequestHeader "Content-type", "application/xml"  
   http.send (theparams)  
 ' create a simple JSON string  
   thestr = Mid(http.responseText, 2, Len(http.responseText) - 2)  
   Set theJSON = scriptControl.Eval("(" & thestr & ")")  
   'Debug.Print http.responseText  
   
   RMQ_getqueue = theJSON.payload  
 End Function  

The RMQ_getqueue function creates a URL string that is passed to the http request object. The RabbitMQ response text is JSON formatted. I didn’t want to load external JSON addins so I used the ScriptControl with a Javascript eval function to parse the JSON. The payload item of the response is the first message in the queue.

To use this new function in Excel select the category “User Defined”.

Excel_insert_function

Then fill in the parameters as per your requirement.

Excel_rmq_get

Write a Message to an Exchange

As mentioned earlier you can’t write directly to a queue with the REST API instead you write to an exchange. The exchange has a routing key that “binds” a messages to a queue.

Like the get queue function the RMQ_exchangeWrite function formats a URL with all the required parameters.

 Function RMQ_exchangeWrite(RabbitMQ_Server As String, ExchangeName As String, RouteKey As String, theMessage As String, Optional rmq_user As String, Optional rmq_pswd As String)  
 ' Create a REST API post a new message to a Rabbitmq Exchange with a Routing Key  
   Dim http As Object  
   Dim theurl As String, theparams As String  
   Set http = CreateObject("MSXML2.XMLHTTP")  
     
   theurl = "http://" & RabbitMQ_Server & "/api/exchanges/%2f/" & ExchangeName + "/publish"  
    
 ' define a parameter list this must be JSON and the synax is pickie  
   theparams = "{""properties"":{},""routing_key"":""" & RouteKey & """,""payload"":""" & theMessage & """,""payload_encoding"":""string""}"  
     
   Debug.Print theparams  
     
   http.Open "POST", theurl, False, rmq_user, rmq_pswd  
   http.setRequestHeader "Content-type", "application/xml"  
   http.send (theparams)  
   
   'Debug.Print http.responseText  
   
   RMQ_exchangeWrite = http.statusText & " " & http.responseText  
 End Function  

Like the earlier function, fill in all the parameter as per your system.

Excel_rmq_write

Final Comments

This was an introductory project, and there are still a lot of features and error checking that should be included.

If I had more time I would put some dialogs in to dynamically show queues, exchanges etc.

Arduino Yún for IoT

There are some great Internet of Things hardware platforms, such as Raspberry Pi’s, ESP-32 based Arduino modules, Particle and Beaglebone to name just a few. Each of these systems have their strengths and weakness. Some are strong on the hardware side, like the Arduino modules, and others excel on the programming side like the Raspberry Pi.  

The Yún is somewhat unique in that it’s a module with two processors, one that supports standard Arduino programming and a second processor that supports Linux and the OpenWrt wireless stack. The Yún ($59) has an Arduino Uno form factor and there are clones like the LinkIt Smart 7688 Duo ($18) in an Arduino Nano form factor.

yun_overview

In this blog I wanted to document some of key feature and functions that I worked through, namely:

  • moving file – scp and ftp
  • Python bridging to Arduino
  • uhttpd Web Server – with Python CGI
  • Yún REST API
  • MQTT
  • Yún mailbox

Yún Overview

The Yún, now in revision 2, seems to have been somewhat overlooked because of the all the low cost ESP-8266 and ESP32 based Arduino modules starting around $2.

Some the interesting features of the Yún include:

  • Arduino code can launch and get feedback from Linux apps
  • A bridge library allows Linux programs and Arduino to share data
  • Arduino libraries for:
    • Web client
    • Web server
    • Mailbox to Linux
  • Yún has a lightweight web server, uhttpd, that can be used to Python, PHP, Lua CGI web programs
  • Yún has a read/write REST API web service

Getting Started

The Arduino side of the Yún is available “out of the box” like any other Arduino module when you connect a USB cable into the module.

The Linux side however requires some configuration. Please see one of the guides on the Linux setup. Once your module is connected to your network, the Yun webserver can be used to configure features and add software components.

yun_software1

Another option for loading software is to use the opkg package manager from an SSH connection.

yun_opkg

So for example to install nano, the simple text editor, enter:

 opkg install nano

Moving File and Working in OpenWrt Linux

The OpenWrt Linux isn’t loaded with with an X-windows environment and this means that you can not run idle or leafpad etc. to do easy editing of program files. Nano is a good command line text editor but it’s not the same as a windows based editor.

To move files between a PC and OpenWrt you have some options. The two that I like are:

  • scp – secure copy, this is built in both OpenWrt and Microsoft Window 10
  • ftp – file transfer protocol. Needs to be installed in OpenWrt.

There are a few ftp servers that could be installed in OpenWrt, vsftp is a lightweight option and it can be installed by:

opkg update
opkg install vsftpd

Once vsftpd is installed it needs to be enabled, this can be done from the command line or via the web interface.

yun_ftp_start.png

Yún Bridge

The Yun Arduino bridge library allow variables to be passed between the Arduino code and a Python program. Below is an example that writes two random variables available, and it creates a “bridge1” variable that can be written from the Python side.

// Simple Yun Bridge Example
//

#include <Bridge.h>
// create a bridge variable to get remote data
char bridge_Value[10];

void setup()
{
    Bridge.begin();     // this launches /usr/bin/run-bride on Linino
}

void loop()
{    
    // create 2 random bridge values, that are sourced from the Arduino
    Bridge.put("random1", String(random(1, 100)));
    Bridge.put("random2", String(random(1, 100)));
    // Called the bridge value "bridge1". This is the name used on the Python side
    Bridge.get("bridge1", bridge_Value, 6);
    delay(1000);  
}

An example to read/write in Python:


#!/usr/bin/python

import sys

sys.path.insert(0, '/usr/lib/python2.7/bridge/')

from bridgeclient import BridgeClient as bridgeclient
value = bridgeclient()

message = value.get("random1") # get a value from Arduino

print "Random1: ", message

value.put("bridge1","1111")  # set a value to Arduino

Yún uhttpd web server

The uhttp web server is used for Yun setup and configuration. This web server can also be used to for custom static pages and user web apps. To view/modify the web server settings:

nano /etc/config/uhttpd

Within this config file, you can enable custom web applications by defining an interpreter:

# List of extension->interpreter mappings.
# Files with an associated interpreter can
# be called outside of the CGI prefix and do
# not need to be executable.
# list interpreter ".php=/usr/bin/php-cgi"
# list interpreter ".cgi=/usr/bin/perl"
list interpreter ".py=/usr/bin/python

# Lua url prefix and handler script.
# Lua support is disabled if no prefix given.
# option lua_prefix /luci
# option lua_handler /usr/lib/lua/luci/sgi/uhttpd.lua

The default directory for user programs is: /www/cgi-bin

Python CGI – Get Values

To read the Arduino bridge values in a Python CGI program, add a file to the /www/cgi-bin directory. For my example I called the file p1.py :


#!/usr/bin/python

import sys
import cgi

sys.path.insert(0, '/usr/lib/python2.7/bridge/')

from bridgeclient import BridgeClient as bridgeclient
value = bridgeclient()

print "Content-type:text/html\r\n\r\n"
print '<html>'
print '<head>'
print '<title>Python CGI Bridge</title>'
print '</head>'
print '<body>'
print '<h2>Python CGI Bridge</h2>'
print 'Random Value1: ' + value.get("random1")
print 'Random Value2: ' + value.get("random2")
print '</body>'
print '</html>'

To run the python script on the web page you will need to change the file right to executable:

chmod +x p1.py

You can debug and see output from the command line.


root@yun1:/www/cgi-bin# ./p1.py
Content-type:text/html

<html>
<head>
<title>Python CGI Bridge</title>
</head>
<body>
<h2>Python CGI Bridge</h2>
Random Value1: 13
Random Value2: 24
</body>
</html>

If the output looks good, try the app from the Web page:

CGI_bridge1

Python CGI – Put Values

There are a number of methods that can be used to send user input from a web page. A simple approach is to use a form. The form data can be read from the cgi.FieldStorage object, using a form.getvalue() call.

#!/usr/bin/python

# Import modules for CGI handling
import cgi
import sys

sys.path.insert(0, '/usr/lib/python2.7/bridge/')
from bridgeclient import BridgeClient as bridgeclient
value = bridgeclient()

# Create instance of FieldStorage
form = cgi.FieldStorage()

# Get data from fields
bridge1 = form.getvalue('bridge1')
if bridge1 != "" :
    value.put("bridge1",bridge1)


print "Content-type:text/html\r\n\r\n"
print """
<html>
<head>
<title>Python CGI - Put Bridge Value</title>
</head>
<body>
<h2>Python CGI Form - Put Bridge Value</h2>
<form action = "/cgi-bin/p2.py" method = "post">
Enter BRIDGE1 value: <input type = "text" name = "bridge1"><br />

<input type = "submit" value = "Submit" />
</form>
</body>
</html>"""

 

The web page will call itself when the submit button is pressed.

CGI_bridge2

Yún REST API

The Yún REST API is a web service that allow remote users and web browsers to view and set bridge values. I found the REST API to be a good tool for testing my Web CGI and Python applications

To view all bridge variables enter: http://yourYun_IP/data/get

yun_rest_1

To get a specific bridge value enter:  http://yourYun_IP/data/get/my_bridge_value

yun_rest_2

To put a bridge value enter: http://yourYun_IP/data/put/my_bridge_value/value 

yun_rest_3

IoT Connections – MQTT

For Internet of Things (IoT) projects you need to pass data from the Arduino and some server. The communications and the server would be something like MQTT or Redis.

The Yún does not have direct access to the Wifi or ethernet port so the standard Arduino libraries for MQTT or Redis etc. will not work. An alternative approach is load the protocol’s command line client on the Linux side and then Arduino can shell out to the Linux tool.

For example to load the  Mosquitto MQTT command line tools:

opkg update
opkg install mosquitto-client

To test MQTT publishing to a topic (mytag1) with a message of 249 on remote client (192.168.0.116) :

mosquitto_pub -h 192.168.0.116 -u username -P password -t mytag1 -m 249

To remotely subscribe to

mosquitto_sub -h 192.168.0.116 -u username -P password -t mytag1

An example of sending out four MQTT messages in Arduino:

/*
Shell out to pass values to a MQTT command line pub
*/
#include <Process.h>

void setup() {
  Bridge.begin();	// Initialize the Bridge
  Serial.begin(9600);	// Initialize the Serial

  // Wait until a Serial Monitor is connected.
  while (!SerialUSB);
}

void loop() {
  Process p;
  String thecmd;
  String strval = String( random(0,100));
  // create a string with host, username and password, -t is for the topic
  String theparams = "-h 192.168.0.116 -u pete -P pete -t ";
  int numtopics = 4;
  String topics[4] = {"tag1","tag2","tag3","tag4"};

  for (int i=0; i < numtopics ; i++) {
    strval = String( (i*100) + random(1,99)); // create a random value - 0,100+,200+,300+
    thecmd = "mosquitto_pub " + theparams + topics[i] + " -m " + strval;
    Serial.println(thecmd);
    p.runShellCommand(thecmd);
    // do nothing until the process finishes, so you get the whole output:
    while (p.running());
  }
  // Wait 5 seconds and repeat
  delay(5000);  // wait 5 seconds before you do it again
}

It is also possible to create some bridge variables and pass them to a Python program that could do the MQTT communications.

Yún Mailbox

At present the mailbox only works in one direction, and this into the Arduino. Both the REST web interface and Python have writing functionality but no read capabilities (on the forums this has been identified, so a future revision may add this).

To write using REST:

http://yun_ip/mailbox/mymessagetext

To write using Python:


import sys
sys.path.insert(0, '/usr/lib/python2.7/bridge')
from bridgeclient import BridgeClient
client = BridgeClient()
client.mailbox("my_message")

The Arduino code to read message:

// Mailbox Read Example
//
#include <Mailbox.h>

void setup() {

  // Initialize Bridge and Mailbox
  Bridge.begin();
  Mailbox.begin();

  // Initialize Serial
  SerialUSB.begin(9600);

  // Wait until a Serial Monitor is connected.
  while (!SerialUSB);
  SerialUSB.println("Mailbox Read Message\n");
}

void loop() {
  String message;

  // if there is a message in the Mailbox
  if (Mailbox.messageAvailable()) {
    // read all the messages present in the queue
    while (Mailbox.messageAvailable()) {
      Mailbox.readMessage(message);
      SerialUSB.println(message);
    }

    SerialUSB.println("Waiting 10 seconds before checking the Mailbox again");
  }
  // wait 30 seconds
  delay(10000);
}

I’m not totally sure when I’d use a mailbox. The mailbox is a generic message and it’s not queued (only 1 message) so I think that using the standard bridging of values with get/put is more useful.

Final Comments

The Yun is missing a lot of networking libraries that are available with the ESP-8266 and ESP32 family of Arduino modules. In some cases like with MQTT that was a good Linux command line tool that could be used, but in cases like where you want to connect to a Redis server, you might have some challenges.

I really liked Yun’s built in uhttpd web server, this is far superior to the ESP-8266 Arduino web server library calls.

I found that putting logic into a combination of Python and Arduino could be a little confusing, but for projects with a lot of text or json data using Python would be a real plus. Also for projects where multitasking is required use the Linux process would be ideal.

RabbitMQ REST API

RabbitMQ is an open source middleware message solution that natively uses AMQP communications but it has a good selection of plug-ins to support features like: MQTT, MQTT Web Sockets, HTTP REST API and server-to-server communications.

rabbitmq_overview

RabbitMQ has a very good selection of interfacing clients for most of the major programming languages. For applications where there is no custom programming library the REST API can be used.

Applications where the REST API could be used might include:

  • Arduino IoT projects
  • Web Clients (not using NodeJS)
    • For simple reading and writing of messages
    • For monitoring of RabbitMQ loading
  • AppInventor Android apps

Unfortunately I didn’t find the RabbitMQ REST API to be 100% straightforward, so in this blog I could like to document some of my notes and testing.

What is REST API

Representational State Transfer (REST) is a software style for creating Web services. A client issues an HTTP request to a server with a GET/POST/PUT or DELETE method and the server responds with a JSON (Javascript Object Notation) formatted message.

RESTAPI_overview

The most common tool to test REST API is curl. Curl is supported on all major OS’s. The curl syntax will vary slightly between Windows and Linux, especially in how the systems handle strings.

RabbitMQ Setup

RabbitMQ can be installed on Window, Linux, MacOS systems and there are also some cloud based offerings. For small systems lower end hardware like a Raspberry Pi can be used.  For complete RabbitMQ installation instructions see: https://www.rabbitmq.com/download.html . To install and run RabbitMQ on a Ubuntu system enter:

sudo apt-get update
sudo apt-get install rabbitmq-server
sudo service rabbitmq-server start

The next step is to add some plug-ins. For the Web Administration and REST API plug-ins:

sudo rabbitmq-plugins enable rabbitmq-management

The rabbitmqctl command line tool allows you to configure and review the RabbitMQ server. To add a user pete,  with password pete, that has config, write and read rights for management and administrator access, enter:

sudo rabbitmqctl add_user pete pete
sudo rabbitmqctl set_permissions -p / pete ".*" ".*" ".*"
sudo rabbitmqctl set_user_tags pete management administrator

After you’ve defined an administrative user the RabbitMQ web management plug-in can be accessed by: http://ip_address:15672 .

Rabbit_webadmin

Once the Web Management component is installed it is much easier to test and play with REST API calls.

REST API Calls Using CURL

For a complete set of RabbitMQ REST API calls see: https://pulse.mozilla.org/api/ .

Typically you would use the RabbitMQ tools to create and exchanges and queues but it is possible to do this via the REST API interface.

Below are some examples using user: pete and a password of pete . Note: %2f is used for default (blank name) virtual host.

Create an exchange: webex

curl -i -u pete:pete -H “content-type:application/json” -X PUT http://192.168.0.116:15672/api/exchanges/%2f/webex -d”{“”type””:””direct””,””auto_delete””:false,””durable””:true,””internal””:false,””arguments””:{}}”

Create a Queue: webq1

curl -i -u pete:pete -H “content-type:application/json” -X PUT http://192.168.0.116:15672/api/queues/%2f/webq1 -d”{“”auto_delete””:false,””durable””:true,””arguments””:{}}”

Create Binding: Bind queue webq1 to exchange webex with a routing key of webq1

curl -i -u pete:pete -H “content-type:application/json” -X POST http://192.168.0.116:15672/api/bindings/%2f/e/webex/q/webq1 -d”{“”routing_key””:””webq1“”,””arguments””:{}}”

Comments: you need to bind a queue to an exchange if you wish to write to it. The routing key does the binding. The key can be any name but using the queue name makes it simple. A queue can also have binding to multiple exchanges.

Get a message: from queue webq1

curl -i -u pete:pete -H “content-type:application/json” -X POST http://192.168.0.116:15672/api/queues/%2f/webq1/get -d”{“”count””:5,””requeue””:true,””encoding””:””auto””,””truncate””:50000}”

Comments: A queue can have multiple messages. A requeue=false, will remove messages that are already read.

Post a message (44.4) : to exchange webex with routing key webq1

curl -i -u pete:pete -H “content-type:application/json” -X POST http://192.168.0.116:15672/api/exchanges/%2f/webex/publish -d”{“”properties””:{},””routing_key””:””webq1“”,””payload””:””44.4“”,””payload_encoding””:””string””}”

 

Javascript REST API Client

I had some Web browser issues when my test PC and my RabbitMQ server were in different domains. Web Browsers like Chrome and  Firefox will complain about a CORS policy problem.

Cross-origin resource sharing (CORS) is a mechanism that allows restricted resources on a web page to be requested from another domain outside the domain from which the first resource was served.

If you are crossing domains and you want to do some quick testing Microsoft Explorer will work.

Javascript GET a Queue Message

I used the AJAX XMLHttpRequest object to do my GET and POST requests.

<!DOCTYPE html>
<html>
<body>
<h2>RabbitMQ REST API - Get Queue Message</h2>

URL/API Call: <input type="text" id="theurl" value="http://192.168.0.116:15672/api/queues/%2f/"  size = "50" /> <br><br>
Queue Name:   <input type="text" id="qname" value="webex"/> <br><br>
Requeue: <input type="text" id="rq" value="true" /> (false = remove old values)<br><br>
Message Count: <input type="text" id="mcount" value="5"/> <br><br>
<button type="button" onclick="sendoutput()">Get Message(s)</button>
<br>

<hr>
<p id="demo"></p>

<script>
function sendoutput() {
	// RabbitMQ username and password
	var username = "pete";
	var password = "pete";
	var xhttp = new XMLHttpRequest();
	
    var fullurl = document.getElementById("theurl").value + document.getElementById("qname").value + "//get";
	
	xhttp.open("POST", fullurl, true,username,password);
	
	// change to your Auth username and password

	xhttp.setRequestHeader("Content-Type","application/jsonp");

	var rq = document.getElementById("rq").value;
	var mcount = document.getElementById("mcount").value;
	var params = JSON.stringify({"count":mcount,"requeue":rq,"encoding":"auto","truncate":"50000"});
	// Define a handler function when the response comes back
	xhttp.onreadystatechange = function() {
		// Get the response and put on it the screen
		if (this.readyState == 4 ) {	
			document.getElementById("demo").innerHTML = "Result=" +xhttp.responseText;
		}
	}

	xhttp.send(params);
}

</script>
</body>
</html>

r_get

Javascript POST an Exchange Message

Unfortunately you can not send a message directly to a queue, instead you need to send a message to an exchange with routing key. This routing key is linked to the queue that you wish to connect to.

<!DOCTYPE html>
<html>
<body>
<h2>RabbitMQ REST API - An Exchange Message</h2>

URL/API Call: <input type="text" id="theurl" value="http://192.168.0.116:15672/api/exchanges/%2f/"  size = "50" /> <br><br>
Exchange Name:   <input type="text" id="exname" value="webex"/> <br><br>
Routing Key:   <input type="text" id="rkey" value="webq1"/> <br><br>
Value to Send:   <input type="text" id="theval" value="11.1"/> <br><br>

<button type="button" onclick="sendoutput()">Send Output</button>
<br>

<hr>
<p id="demo"></p>

<script>
function sendoutput() {
	// RabbitMQ username and password
	var username = "pete"; 
	var password = "pete";  
	
	var xhttp = new XMLHttpRequest();
	
	var fullurl = document.getElementById("theurl").value + document.getElementById("exname").value + "//publish";
	
	xhttp.open("POST", fullurl, true,username,password);

	xhttp.setRequestHeader("Content-Type","application/jsonp");
	
	var rkey = document.getElementById("r").value
	var theval = document.getElementById("theval").value
	var params = JSON.stringify({"properties":{},"routing_key":rkey,"payload":theval,"payload_encoding":"string"});
	
	// Define a handler function when the response comes back
	xhttp.onreadystatechange = function() {
		// Get the response and put on it the screen
		if (this.readyState == 4 ) {	
			document.getElementById("demo").innerHTML = "Result=" +xhttp.responseText;
		}
	}
	xhttp.send(params);
}
</script>
</body>
</html>

r_post

Python – get message(s) from a queue

The requests library is extremely simple to use for REST interfacing. It is installed by:

pip install requests

# importing the requests library
import requests

# defining the api-endpoint
API_ENDPOINT = "http://192.168.0.116:15672/api/queues/%2f/webq1/get"

headers = {'content-type': 'application/json'}
# data to be sent to api
pdata = {'count':'5','requeue':'true','encoding':'auto','truncate':'50000'}

# sending post request and saving response as response object
r = requests.post(url = API_ENDPOINT ,auth=('pete', 'pete'), json = pdata, headers=headers)

# extracting response text
pastebin_url = r.text
print("Response :%s"%pastebin_url)

Python – Write a message to an exchange


# importing the requests library
import requests
import json

# defining the api-endpoint
API_ENDPOINT = "http://192.168.0.116:15672/api/exchanges/%2f/webex/publish"

# your source code here

headers = {'content-type': 'application/json'}
# data to be sent to api

pdata = {'properties':{},'routing_key':'webq1','payload':'90.4','payload_encoding':'string'}

# sending post request and saving response as response object
r = requests.post(url = API_ENDPOINT ,auth=('pete', 'pete'), json = pdata, headers=headers)

# extracting response text
pastebin_url = r.text
print("Response :%s"%pastebin_url)

Final Comments

The RabbitMQ REST interface is not designed for high throughput, but it is an excellent way to connect devices that have no native API support.

For my next round of testing I’d like to work on an Arduino and a AppInventor Android app.

Get Your Duolingo Stats

Unfortunately the interface to https://www.duolingo.com has changed so the examples in the blog are no longer working. I left the page up for reference and I’ll try and get more information on an updated Duolingo API.

Doulingo is one of the most popular applications for learning a new language. Duolingo has a good selection of languages and it’s free.

If you wanted to create your apps to look at your progress or do some comparisons with other Doulingo users it’s a little challenging because there isn’t a documented API (Application Programming Interface).

In this blog I wanted to look at two programming choices that are available. The first is a Web page (REST API) query and the second is a Python library.

It is important to note that last year the Doulingo interface changed so a lot of the documentation on the Internet is not 100% valid.

The Web Page (REST API) Query

If you enter : https://www.duolingo.com/users/a_user_name

You will see a very large amount of JSON (JavaScript Object Notation) text. This undocumented text is quite ugly.

web_restapi

It is possible to use some Python code to call the Web page, and try to make the JSON a little more readable. The code below uses the requests library to get the Web data, and the json library to format the web output (JSON) data.

import requests
import json

r = requests.get('https://www.duolingo.com/users/some_users')

jtext = json.loads(r.text)

with open('user_data.txt', 'w', encoding='utf8') as outfile:
     json.dump(jtext, outfile, sort_keys = False, indent = 4,
               ensure_ascii = False)

This example created an outfile that is much easier (but far from simple) to analyze. Below is a section of the output file where we can see the languages section broken out.

    "tts_base_url_http": "http://d7mj4aqfscim2.cloudfront.net/",
    "id": 141898265,
    "dict_base_url": "http://d2.duolingo.com/",
    "cohort": null,
    "daily_goal": 10,
    "delete-permissions": false,
    "ads_enabled": true,
    "languages": [         {
            "streak": 4,
            "language_string": "Greek",
            "points": 0,
            "learning": false,
            "language": "el",
            "level": 1,
            "current_learning": false,
            "sentences_translated": 0,
            "to_next_level": 60
        },
        {
            "streak": 4,
            "language_string": "Esperanto",
            "points": 0,
            "learning": false,
            "language": "eo",
            "level": 1,
            "current_learning": false,
            "sentences_translated": 0,
            "to_next_level": 60
        },

Show the Languages and Work History

By looking at the outfile the languages studied and the work history can be determined:

import requests
import json
from datetime import datetime

r = requests.get('https://www.duolingo.com/users/pete_xxxxx')

j = json.loads(r.text)

print ("Languages Studied\n")
for thelang in j["languages"]:
    if thelang["points"] > 0 :
        print (thelang["language_string"], ",Level = ", thelang["level"], ",Points=", thelang["points"])

print ("\n\nWork History\n")

for ical in j["calendar"]:
    ddate =  datetime.fromtimestamp(ical["datetime"] / 1e3)
    sdate = ddate.strftime("%m/%d/%Y")
    print(sdate, ", Improvement = ", ical['improvement'])

For my example this gave the following output:


Languages Studied

Indonesian ,Level = 6 ,Points= 570
Spanish ,Level = 13 ,Points= 5102
Chinese ,Level = 2 ,Points= 70
French ,Level = 9 ,Points= 1710
German ,Level = 6 ,Points= 616
Japanese ,Level = 10 ,Points= 2297

Work History

02/22/2019 , Improvement = 10
02/22/2019 , Improvement = 10
02/23/2019 , Improvement = 10
02/23/2019 , Improvement = 10
02/24/2019 , Improvement = 10

Duolingo Python Library

There is an unofficial Duolingo Python Library, the author did an excellent job of putting together some useful calls. Unfortunately since the Duolingo interface changed last year not all the functions are now available or working. However the key ones are still usable.

To install the library go to a terminal window or command prompt and enter:

pip install duolingo-api

A Python example would be:

import duolingo
from datetime import datetime

lingo  = duolingo.Duolingo('pete_xxxxx')

# get_user_info : works but outputs too much info
#print (lingo.get_user_info())

print ("Languages I am studying:")
print (lingo.get_languages(abbreviations=False))

print("\n\nLanguage Progress\n")
for ilang in lingo.get_languages(abbreviations=False):
       jlang = lingo.get_language_details(ilang)
       print( jlang["language_string"]," ,Points: ",jlang["points"], "Level = ", jlang["level"])

print("\n\nWork History\n")
for ical in lingo.get_calendar():
    ddate =  datetime.fromtimestamp(ical["datetime"] / 1e3)
    sdate = ddate.strftime("%m/%d/%Y")
    print(sdate, ", Improvement = ", ical['improvement'])

For my example the output is:


Languages I am studying:
['Indonesian', 'Spanish', 'Chinese', 'French', 'German', 'Japanese']

Language Progress

Indonesian ,Points: 570 Level = 6
Spanish ,Points: 5102 Level = 13
Chinese ,Points: 70 Level = 2
French ,Points: 1710 Level = 9
German ,Points: 616 Level = 6
Japanese ,Points: 2297 Level = 10

Work History

02/22/2019 , Improvement = 10
02/22/2019 , Improvement = 10
02/23/2019 , Improvement = 10
02/23/2019 , Improvement = 10
02/24/2019 , Improvement = 10

Summary

Now that we have a basic interface to Duolingo it’s possible to start doing some trends and charts. Below a is some simple chart code for language levels.

import duolingo
import numpy as np
import matplotlib.pyplot as plt

lingo  = duolingo.Duolingo('pete_xxxx')

thelang = lingo.get_languages(abbreviations=False)

thelevel = []
for ilang in lingo.get_languages(abbreviations=False):
       jlang = lingo.get_language_details(ilang)
       thelevel.append(jlang["level"])


plt.bar(thelang,thelevel, color='r')

plt.legend()
plt.xlabel('Language')
plt.ylabel('Level')

plt.title('My Duolingo Progress')

plt.show()

Duo_progress

Javascript talking to littleBits Cloud API

LittleBits is a set of components that allow kids to build their own electrical circuits. The Cloudbit will send and receive values from the Internet.

For this project we wanted to build some Web pages with Javascript that could interact with the littleBits Cloud API.

littleBits Hardware Setup

cloudbit_setup

The Cloudbit enables an input to be sent to the Internet, and it will receive a value (output) from the Internet. For a test setup we used a dimmer bit to adjust the input value with a number bit to show the value. On the output side we used a bargraph bit. Other combinations of bits are possible. The key is to be able to test and see your input and output values.

Cloudbits Setup

For the Cloudbit setup you will need to create a sign-in on the littleBits web site. For details on how to setup your Cloudbit use the following link.

The Cloud API is a RESTful interface, that uses http requests with some header definitions. Before you get started you’ll need your specific littleBits device ID and authorization token. In your specific Cloudbit definition go to the Settings icon, and get your Device ID and AccessToken.

littlebit_id

Cloudbit Rest API – First Example Testing with curl

The Cloudbit API reference can be found at : http://developers.littlebitscloud.cc/#devices. 

The Cloudbit API is based on the REST API standard, and this means that an HTTP request is made with some extra parameters that are passed in the header. The most important header item is the littleBits authorization token.

Curl is command line software tool that allows you to issue an HTTP request with header and method parameters and it returns the results in a text format. Curl is available for Windows, OSX and Linux. As an example to query the CloudBit API to get all my devices the following command is issued (note you’ll need to enter your own authorization code) :

curl "https://api-http.littlebitscloud.cc/v2/devices" \
-H "Authorization: Bearer 4f3830b44e1d4b2789b50b0xxxxxx"

[
{"label":"twinbit",
"id":"00e04c0379bb",
"subscriptions":[],
"subscribers":[],
"user_id":118217,
"is_connected":true,
"input_interval_ms":200}
]

Javascript Device Monitor

The curl example can be done in Javascript using the Javascript built in XMLHttpRequest object.

A monitor page can be useful if you have a number of Cloudbits and you’re interested is checking their status. Below is a sample web page and the associated HTML/javascript code. It’s important to note that text that is returned needs to have the first few characters removed in order to have a clean JSON string.

js_status

<!DOCTYPE html>
<html>
<body>
<h1 id='title'>Cloudbit Status Monitor</h1>
<h2>
Label : <font color='red' id='LB_label'></font><br>
Is Connected : <font color='red' id='is_connected'></font><br>
</h2>
 
<script>
var xhttp = new XMLHttpRequest();
// change the authtoken to match your settings
var authtoken = "4f3830b44e1dxxxxxxxxxxxxxxxx";
var theURL =  "https://api-http.littlebitscloud.cc/devices/";
xhttp.open("GET", theURL, true);
xhttp.setRequestHeader("Accept","application/vnd.littlebits.v2+json");
xhttp.setRequestHeader("Authorization", "Bearer " + authtoken);
xhttp.onreadystatechange = function() {
  if (xhttp.readyState == 4 ) { // when the response is complete get the data
    // remove leading "[" and trailing ']'
    var theresponse = xhttp.responseText.substring(1, xhttp.responseText.length -1);
    var lb_data = JSON.parse( theresponse );  
    
    document.getElementById('LB_label').innerText =  lb_data.label;
    document.getElementById('is_connected').innerHTML = lb_data.is_connected;
  }
}
xhttp.send();
</script>

</body>


</html>

Read Value Example

Reading a CloudBit value is a little tricky because the data is returned as a stream that updates every second. I saw a lot of people asking for a single point read, so hopefully this will be available soon.

The read value HTTP request is a little more complex than the simple monitor device example because you need to include the device that you are querying and a head parameter of :  Accept: application/json.

A curl example would be:

curl -XGET "https://api-http.littlebitscloud.cc/v2/devices/00e04c0379bb/input" -H "Authorization: Bearer 4f3830b44e1d4b2789b50b09cb493f06750b968cff5d45331c75006025fa0dc9" -H Accept: application/vnd.littlebits.v2+json"

data:{"type":"input","timestamp":1543536039623,"percent":6,"absolute":66,"name":"amplitude","payload":{"percent":6,"absolute":66},"from":{"device":{"id":"00e04c0379bb","mac":"00e04c0379bb"}}}

data:{"type":"input","timestamp":1543536039650,"percent":6,"absolute":65,"name":"amplitude","payload":{"percent":6,"absolute":65},"from":{"device":{"id":"00e04c0379bb","mac":"00e04c0379bb"}}}

...

For an HTML/Javascript single point read the key to check the xhttp.readyState == 3 , this will catch the first streamed response.  Below is a single point example with code.

js_in

<!DOCTYPE html>
<html>
<head>
<title>littleBits Get Input</title>
<script>
var theinput = 0;

function get_input() {
// update with your deviceid and authtoken
  var deviceid = "00e04c0379bb";
  var authtoken = "4f3830b44e1d4b27xxxx";
  var theurl = "https://api-http.littlebitscloud.cc/devices/";
  var xhttp = new XMLHttpRequest();
  xhttp.onreadystatechange = function() {

    if (xhttp.readyState == 3 ) {
	  	var datapackage = xhttp.responseText.split("\n\ndata:");
		var lb_data = JSON.parse( datapackage[1] );		 
		document.getElementById("thevalue").innerText =  lb_data.percent;
		xhttp.open("GET","",true);
		xhttp.send();
    }
  }
  xhttp.open("GET", theurl + deviceid + "/input", true);
  xhttp.setRequestHeader("Accept","application/json");
  xhttp.setRequestHeader("Authorization", "Bearer " + authtoken);
  xhttp.send();
}
</script>
</head>
<body>

<h1 id='title'>littleBit Get Input</h1>
The value : <font id="thevalue"> XXXX </font>
<button type="button" onclick="get_input()">Request data</button>
<br>

</body>
</html>

Output Example

A CloudBit output can be either sustained or it can be timed out. The curl command is:

curl "https://api-http.littlebitscloud.cc/v2/devices/yourdevice_id/output" \
  -X POST \
  -H "Authorization: your_auth_code" \
  -H "Content-type: application/json" \
  -d '{ "percent": 100, "duration_ms": 3000 }'

For an HTML/Javascipt web the http response is a POST, and the value’s percent and duration are put into a JSON string:

var params = JSON.stringify({duration_ms: duration,percent: thevalue});

An example page and code is shown below:

js_out

<!DOCTYPE html>
<html>
<body>
<h2>CloudBit Output Test</h2>
<button type="button" onclick="sendoutput()">Send Output</button>
<br>
<pre>
Output Time (ms): <input type="text" id="duration" value="-1"/> (constant = -1)</br>
Output Value	: <input type="text" id="thevalue" value="80"/> (percent 0-100)</br> 
</pre>
<p id="demo"></p>


function sendoutput() {
	var xhttp = new XMLHttpRequest();
        // change to your device id
	xhttp.open("POST", "https://api-http.littlebitscloud.cc/devices/00e04c0379bb/output?", true);
	xhttp.setRequestHeader("Accept","application/vnd.littlebits.v2+json");
	// change to your Auth Token
	xhttp.setRequestHeader("Authorization", "Bearer 4f3830b44e1d4b27xxxx");
	xhttp.setRequestHeader("Content-Type","application/json");

	var duration = document.getElementById("duration").value;
	var thevalue = document.getElementById("thevalue").value;

	var params = JSON.stringify({duration_ms: duration,percent: thevalue});

	xhttp.onreadystatechange = function() {
		document.getElementById("demo").innerHTML = "Result=" +xhttp.responseText;
	}

	xhttp.send(params);
}


</body>
</html>

Gauges

Once you’ve got the basics down it’s possible to start making some more advanced applications. There are lot of Javascript charting libraries. For this example I used the Google  Charts library.

js_gauge

<html>
  <head>
   https://www.gstatic.com/charts/loader.js
   
	  var littleBitinput = 0;
      google.charts.load('current', {'packages':['gauge']});
      google.charts.setOnLoadCallback(drawChart);
      function drawChart() {

        var data = google.visualization.arrayToDataTable([
          ['Label', 'Value'],
          ['littleBits', 100],
        ]);

        var options = { 
          width: 600, height: 600,
          redFrom: 90, redTo: 100,
          yellowFrom:75, yellowTo: 90,
		  greenFrom: 60, greenTo: 75,
          minorTicks: 5
        };

        var chart = new google.visualization.Gauge(document.getElementById('chart_div'));

        chart.draw(data, options);

        setInterval(function() {
		  get_input();
          data.setValue(0, 1, littleBitinput);
          chart.draw(data, options);
        }, 500);
 
      }
	  function get_input() {
	  // change to your deviceid and authtoken
		  var deviceid = "00e04c0379bb";
		  var authtoken = "4f3830b44e1d4b27xxx";
		  var theurl = "https://api-http.littlebitscloud.cc/devices/";		  
		  var xhttp = new XMLHttpRequest();
		  
		  xhttp.onreadystatechange = function() {
			if (xhttp.readyState == 3 ) {
				var datapackage = xhttp.responseText.split("\n\ndata:");
				var lb_data = JSON.parse( datapackage[1] );		 
				littleBitinput =  lb_data.percent;
				xhttp.open("GET","",true);
				xhttp.send();
			}
		  }
		  xhttp.open("GET", theurl + deviceid + "/input", true);
		  xhttp.setRequestHeader("Accept","application/vnd.littlebits.v2+json");
		  xhttp.setRequestHeader("Authorization", "Bearer " + authtoken);
		  xhttp.send();

}
    
  </head>
  <body>

 

Final Comment

There are some great projects that you could do with littleBits and the CloudAPI.

One still project I did was to use smoothie charts http://smoothiecharts.org/ for the real time charts.

lb_chart2