Grafana, InfluxDB and Python, simple sample

I recently came across an interesting contract position which uses Grafana and InfluxDB. I’d had a play with ElasticSearch before, and done some work with KairosDB, so was already familiar with time series and json-based database connections. Having manually created a dashboard, Grafana looked rather interesting. So I thought I’d do a quick trial – generate some random data, store it in InfluxDB and show it with Grafana

Starting with a clean virtual machine:


  1. Set up InfluxDB
    1. I followed InfluxDB’s installation instructions, which worked first time without any problems
    2. Start it
      sudo /etc/init.d/influxdb start
  2. Test InfluxDB
    > create database mydb
    > show databases
    name: databases
    > use mydb
    > INSERT cpu,host=serverA,region=us_west value=0.64
    > SELECT host, region, value FROM cpu
    name: cpu
    time            host    region  value
    1466603916401121705 serverA us_west 0.64
  3. Set up and test influxdb-python, so we can access InfluxDB using Python
    sudo apt-get install python-pip
    pip install influxdb
    >>> import influxdb
  4. Run through this example of writing and reading some InfluxDB data using Python
    >>> from influxdb import InfluxDBClient
    >>> json_body = [
    ...     {
    ...         "measurement": "cpu_load_short",
    ...         "tags": {
    ...             "host": "server01",
    ...             "region": "us-west"
    ...         },
    ...         "time": "2009-11-10T23:00:00Z",
    ...         "fields": {
    ...             "value": 0.64
    ...         }
    ...     }
    ... ]
    >>> client = InfluxDBClient('localhost', 8086, 'root', 'root', 'example')
    >>> client.switch_database('mydb')
    >>> client.write_points(json_body)
    >>> print client.query('select value from cpu_load_short;')
    ResultSet({'(u'cpu_load_short', None)': [{u'value': 0.64, u'time': u'2009-11-10T23:00:00Z'}]})
  5. Create some more data, using a slimmed down version of this tutorial script
    import argparse
    from influxdb import InfluxDBClient
    from influxdb.client import InfluxDBClientError
    import datetime
    import random
    import time
    USER = 'root'
    PASSWORD = 'root'
    DBNAME = 'mydb'
    def main():
        nb_day = 15  # number of day to generate time series
        timeinterval_min = 5  # create an event every x minutes
        total_minutes = 1440 * nb_day
        total_records = int(total_minutes / timeinterval_min)
        now =
        metric = "server_data.cpu_idle"
        series = []
        for i in range(0, total_records):
            past_date = now - datetime.timedelta(minutes=i * timeinterval_min)
            value = random.randint(0, 200)
            hostName = "server-%d" % random.randint(1, 5)
            # pointValues = [int(past_date.strftime('%s')), value, hostName]
            pointValues = {
                    "time": past_date.strftime ("%Y-%m-%d %H:%M:%S"),
                    # "time": int(past_date.strftime('%s')),
                    "measurement": metric,
                    'fields':  {
                        'value': value,
                    'tags': {
                        "hostName": hostName,
        client = InfluxDBClient(host, port, USER, PASSWORD, DBNAME)
        print("Create a retention policy")
        retention_policy = 'awesome_policy'
        client.create_retention_policy(retention_policy, '3d', 3, default=True)
        print("Write points #: {0}".format(total_records))
        client.write_points(series, retention_policy=retention_policy)
        query = 'SELECT MEAN(value) FROM "%s" WHERE time > now() - 10d GROUP BY time(500m);' % (metric)
        result = client.query(query, database=DBNAME)
        print (result)
        print("Result: {0}".format(result))
    if __name__ == '__main__':
  6. Save as, run and test it
    Visit to register for updates, InfluxDB server management, and monitoring.
    Connected to http://localhost:8086 version 0.13.0
    InfluxDB shell version: 0.13.0
    > use database mydb
    > SELECT MEAN(value) FROM "server_data.cpu_idle" WHERE time > now() - 10d GROUP BY time(500m)
    time			mean
    1466280000000000000	94.03846153846153
    1466310000000000000	98.47
    1466340000000000000	95.43
    1466370000000000000	104.3
    1466400000000000000	104.01
    1466430000000000000	114.18
    1466460000000000000	106.19
    1466490000000000000	96.67
    1466520000000000000	107.77
    1466550000000000000	103.08
    1466580000000000000	100.53
    1466610000000000000	94


  1. Install Grafana using the installation instructions:
    $ wget
    $ sudo apt-get install -y adduser libfontconfig
    $ sudo dpkg -i grafana_3.0.4-1464167696_amd64.deb
  2. Start the server and automatically start the server on boot up
    sudo service grafana-server start
    sudo systemctl enable grafana-server.service
  3. Test
    1. In your browser, go to localhost:3000
    2. Log in as (user) admin, (password) admin
  4. Connect to the InfluxDB database
    1. I followed the Instructions at
    2. Click on the Grafana icon
    3. Select “Data Sources”
    4. Click on “+ Add data source”
      1. Name: demo data
      2. Type: InfluxDB
      3. URL: http://localhost:8086
      4. Database: mydb
      5. User: root
      6. Password: root
      7. Click on “Save and Test”
    5. Create a new Dashboard
      1. Click on the Grafana icon
      2. Select “Dashboards”
      3. Click on “New”
    6. Define a metric (graph)
      1. Click on the row menu, i.e. the green icon (vertical bar) to the left of the row
      2. Select “Add Panel”
      3. Select “Graph”
      4. On the Metrics tab (selected by default)
        1. Click on the row just below the tab, starting with “> A”
        2. Click on “select measurement” and select “server_data.cpu_idle”
          1. You should now see a chart
        3. Close this, by clicking on the cross, top right hand corner of the Metrics panel
    7. Save the dashboard
      1. Click on the save icon (top of the screen)
      2. Click on the yellow star, next to the dashboard name (“New dashboard”)
    8. Test it
      1. In a new browser tab or window, go to http://localhost:3000/
      2. Log in (admin, admin)
      3. The “New dashboard” will now show up in the list of starred dashboards (and probably also under “Recently viewed dashboards”)
      4. Click on “New dashboard” to see the chart

You should now see something like this:

Grafana InfluxDB

Namepy step 7 – Bringing it all together

(This is part of the namepy project. Start at Namepy – on the shoulders of giants)

Time to show some real results on a web page.

  1. Extend the API to show the letter scoring tables, no pagination, in add:
    manager.create_api(models.Set, methods=['GET'], results_per_page=0) 
  2. Rename helloworld.html to index.html
  3. At the end of, update the template name to index.html, and stop passing in ‘names’ since this is now done through the API, and rename the endpoint function to ‘index’:
    def index(): 
        return render_template('index.html') 

That’s it for the changes to the back end. The rest of the changes will all be in the front end, in index.html

  1. Rename the app from HelloWorldApp to NamePyApp
  2. Rename the controller from HelloWorldController to NamePyController
  3. Load the letter scoring table, and simplify it for faster lookup
    $scope.sets = [];
    angular.forEach(, function(set, index) {
        scores = {};
        angular.forEach(set.scores, function(score, index) {
            scores[score.letter] = score.score;
        $scope.sets.push({ name:, scores: scores});
  4. Calculate the score for each of the sets
    angular.forEach($scope.sets, function(set, index) {
        var total = 0;
        var error = false;
        angular.forEach(name.split(''), function(character, index2) {
            if (character in set.scores) {
                total += set.scores[character];
            } else {
                error = true;
        if (error == false) {
            result.push([, total]);
        $scope.sort_on_element(result, 1);
        $scope.scores = result;
  5. Show the result on the page, using Highcharts. For the code see the source code, function “showLetterScores”

Show baby name distribution

  1. Get data for entered name
    var filters = [{ name: 'name', 
        op: 'ilike', 
        val: $scope.visitor_name}];
        method: 'GET',
        url: 'api/name',
        params: {"q": JSON.stringify({"filters": filters})}
            function(response) {            
  2. Restructure the results for Highcharts
    var boy_frequency = [];
    var girl_frequency = [];
    var boys_found = false;
    var girls_found = false;
        function(frequency) {
                Date.UTC(frequency.year, 1, 1), 
                Date.UTC(frequency.year, 1, 1), 
            if (frequency.boys_count) boys_found = true;
            if (frequency.girls_count) girls_found = true;
    $scope.sort_on_element(boy_frequency, 0);
    $scope.sort_on_element(girl_frequency, 0);
  3. Show the results using Highcharts. See the source code, function “show_name_distribution”


Done Done

This is the final blog post for this little project. I hope you found it useful.

Namepy step 6 – Load the data into the database

(This is part of the namepy project. Start at Namepy – on the shoulders of giants)

We will need the following data in the database:

  • Name frequencies – baby names by year
  • Scrabble™ letter values, by (country) Scrabble™ set

Name frequencies

  1. Download the data from
  2. Unzip it in <project_root>/raw_data/yob1880.txt, etc
  3. You may want to add raw_data to .gitignore, so it doesn’t get stored in your git repo
  4. Create some code to read files and store in PostgreSQL –
    def read_frequencies_from_file(filename, names):
        year = int(filename[3:7])
        year_frequencies = {}
        for name in names:
            year_frequencies[name] = {'F': 0, 'M': 0}
        with open('raw_data/%s' % filename) as file:
            for line in file.readlines():
                    name_text, sex, count = line.split(",")
                    print("Couldn't parse line")
                if name_text not in names:
                    name = Name(name=name_text)
                    names[name_text] =
                    year_frequencies[name_text] = {'F': 0, 'M': 0}
                year_frequencies[name_text][sex] = int(count)
            for name, name_frequency in year_frequencies.iteritems():
                if name_frequency['F'] + name_frequency['M']:
                    name_id = names[name]
                    frequency_record = NameFrequency(name_id=name_id,
    def read_name_frequencies():
        # Start with an empty list
        print("Deleting any previous data")
        names = {}
        # Get file list
        for filename in listdir('raw_data'):
            if filename[:3] == 'yob':
                read_frequencies_from_file(filename, names)
  5. Run the code. Note that this may take a while to run. On my development machine it took about 8 minutes
  6. Check this in the database, for instance with phpPgAdmin or pgAdmin

Scrabble™ letter values

For a list of Scrabble™ letter values by Scrabble™ set see this Wikipedia entry. The following code will grab this page, extract the letter values and save this in the database

  1. Add the new tables to
    class Set(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String())
        scores = db.relationship('LetterScore', backref='set', lazy='dynamic')
    class LetterScore(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        set_id = db.Column(db.Integer, db.ForeignKey(''))
        score = db.Column(db.Integer)
        letter = db.Column(db.String(1))
  2. Write some code to parse this page and store the results in the database. See the source code in my GitHub repo
  3. Run the code
  4. Check the results in the database. See above (end of name frequencies section) for some suggested tools



Time to pull it all together and show some real charts

Continue to Step 7 – Bringing it all together

Namepy step 5 – Flask-Restless

(This is part of the namepy project. Start at Namepy – on the shoulders of giants)

We will need Angular to use an Ajax call to request the data from Flask, using a REST-style request, and show it in Highcharts.

The two main Flask libraries for creating a REST API are Flask-Restful and Flask-Restless. We will be using Flask-Restless, because it is particularly suited for what we’re trying to do: “Flask-Restless provides simple generation of ReSTful APIs for database models defined using SQLAlchemy (or Flask-SQLAlchemy)” – from the Flask-Restless documentation.

Create and test the REST API

  1. Install flask-restless
    (virtualenv) pip install flask-restless
  2. import at at the start of __init__py:
    import flask.ext.restless
  3. Create the API endpoint, add following to end of
    manager = flask.ext.restless.APIManager(app, flask_sqlalchemy_db=db) 
    manager.create_api(models.Name, methods=['GET']) 
  4. Test this – python; point your browser to, this should show a JSON structure with the names and frequencies

Use Angular to request and process the REST data from the back end system

  1. Create a new function which takes a response object, extracts the json data, formats it, and passes it to Highcharts
                        $scope.showChart = function(response_data) {
                            chart_data = []
                            angular.forEach(response_data.objects, function(name_object, key) {
                                boys_count = []
                                angular.forEach(name_object.frequencies, function(frequency, key) {
                                chart_data.push({ name:, data: boys_count });
                                chart: {
                                    type: 'column'
                                title: {
                                    text: 'Name frequencies'
                                series: chart_data

    Note that this doesn’t quite make sense, for instance the year isn’t being shown in the chart. We’ll fix all of that later. For now the aim is to get the infrastructure set up – database, REST API, Angular, etc.

  2. Use Angular’s $http.get() function to call the api, and pass the response object to the showChart function upon completion
                        	.then(function(response) { 
  3. Test: Make sure the Flask app is running and go to You should still see the name frequencies chart



That completes the technical set up, for now. We’re ready to do some real coding, starting with getting the data into the database.

Continue to Step 6 – Load the data into the database

Namepy step 4 – PostgreSQL, SQLAlchemy and Flask-SQLAlchemy

(This is part of the namepy project. Start at Namepy – on the shoulders of giants)

I’ve used PostgreSQL for a number of different projects. It is the database of choice for Django developers, with MySQL a close second. It is fast, stable, able to hand large databases and open source.

For a small project like this PostgreSQL is overkill, and SQLite would be easier to set up. However, I wanted to show how to integrate the key elements of a serious Flask/etc project, which has to include a production-grade database.

  1. Create a new database
    • For instructions on creating a new database in PostgreSQL you’ll find many helpful posts online, including my own blogpost
    • Make a note of the database name, user name and password
  2. Grab the required libraries (psycopg2 is needed for using flask-sqlalchemy with PostgreSQL, but not automatically installed with it)
    (virtualenv) pip install flask-sqlalchemy
    (virtualenv) pip install psycopg2
  3. Import flask-sqlalchemy
    from flask_sqlalchemy import SQLAlchemy
  4. Configure it. Replace <user name>, etc, in the text below with the actual user name, etc
    app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://<user name>:<password>@localhost/<database>'
  5. Define the models in <project_root>/
    from hello import db
    class Name(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String())
        frequencies = db.relationship('NameFrequency', backref='name', lazy='dynamic')
    class NameFrequency(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        name_id = db.Column(db.Integer, db.ForeignKey(''))
        year = db.Column(db.Integer)
        boys_count = db.Column(db.Integer)
        girls_count = db.Column(db.Integer)
  6. Test the database and create the tables
    >>> from hello import db
    >>> db.create_all()

    As long as you see no error message this has worked

  7. Create a script for adding some test data. In add:
    def create_test_data():
        for name in ('Fred', 'Sue'):
            new_name = Name(name=name)
            for year in range(1990, 1996):
                new_frequency = NameFrequency(
                    boys_count=random.randint(50, 100),
                    girls_count=random.randint(50, 100))
  8. And run the script
    >>> from hello import create_test_data
    >>> create_test_data()
  9. Test this:
    >>> from models import Name
    >>> names = Name.query.all()
    >>> names[0].name
    >>> names[0].frequencies[0].boys_count

    Or some other number between 50 and 100

Whilst this works so far, there are a couple of problems. The database details, including password, are in the main source code. And when you try running it as a Flask script (python you get a circular import error. Let’s tidy this up

  1. Create a new file, <project_root>\, containing the configuration (with the correct login and database details):
    app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://&lt;username&gt;:&lt;password&gt;@localhost/&lt;database name&gt;'
  2. If you’re using git, make sure this isn’t under version control. Create/update .gitignore to include the following:
  3. Create a new file,, containing create_test_data from, plus necessary imports, and the code to run the create_test_data function when called from the command line
  4. Create
    import os
    import sys
    CURRENT_FILE = os.path.abspath(__file__)
    CURRENT_DIR = os.path.dirname(CURRENT_FILE)
    PROJECT_DIR = os.path.dirname(CURRENT_DIR)
    sys.path.append(PROJECT_DIR + '/src/')
    sys.path.append(PROJECT_DIR + '/virtualenv/lib/python3.5/site-packages/')
    from app import app as application
    if __name__ == '__main__':
  5. Create an “app” folder in the project_root folder
  6. In <project_root>/app create
    from flask import Flask
    from flask.ext.sqlalchemy import SQLAlchemy
    app = Flask(__name__)
    db = SQLAlchemy(app)
    from app import views, models
  7. Move into the app folder and change the first line to:
    from app import db
  8. In the app folder, create, containing @app.route(“/”) and hello() function from, plus necessary imports
  9. Test this. You should get

    Welcome, today is June 7th 2016

Let’s show some data

  1. In, add:
    from models import Name
  2. In render_template, pass in the Name objects:
    return render_template('helloworld.html', names=Name.query.all())
  3. In the helloworld.html template, get the series data from the Name objects:
                        series: [
                            {% for name in names %}
                                    name: '{{ }}',
                                    data: [
                                        {% for frequency in name.frequencies %}
                                            {{ frequency.boys_count }},
                                        {% endfor %}
                            {% endfor %}
  4. The chart title no longer makes sense. Change this to “Name frequencies”
  5. Test this. You should see another chart



Continue to Step 5 – Flask-Restless

Namepy step 3 – Adding in Highcharts

(This is part of the namepy project. Start at Namepy – on the shoulders of giants)
Highcharts is a JavaScript library for creating high quality interactive charts. It is not open source but requires a license fee for commercial projects

  1. Grab Highcharts from
  2. Copy highcharts.src.js (for a production site use highcharts.js) into static/external
  3. Include this in helloworld.html:
     <script src="static/external/highcharts.src.js"></script> 
  4. Ditto for jQuery, download from, and include in helloworld.html, before the highcharts link
  5. Create a container for the chart:
    <div id="container" style="min-width: 310px; height: 400px; margin: 0 auto;"></div>
  6. Create a very simple chart, e.g.:
    			$(function () { 
    			        chart: { 
    			            type: 'column' 
    			        title: { 
    		                text: 'Rabbit sightings' 
    			        xAxis: { 
    			            categories: [ 
    			        series: [{ 
    			            name: '2015', 
    			            data: [5, 7, 8, 3] 
    			        }, { 
    			            name: '2016', 
    			            data: [6, 6, 10, 5] 
  7. Start the Flask/python script (python and view the result in your browser (




Continue to Step 4 – PostgreSQL, SQLAlchemy and Flask-SQLAlchemy

Namepy step 2 – Flask and Angular

(This is part of the namepy project. Start at Namepy – on the shoulders of giants)

We’re going to need some server-side processing, for which I’ll be using Python and the Flask micro framework

  1. I recommend you use a virtualenv for this project
  2. Install flask
    pip install flask
  3. Create /
    from flask import Flask
    app = Flask(__name___)
    app.config['DEBUG'] = True
    def hello():
        return "Hello world!"
    if __name__ == "__main__":

Test this:

  1. Start Flask and the Python script
  2. In your browser, go to This should show

    “Hello world!”

Show the Angular page from step 1

  1. In, add
    from flask import render_template
  2. hello() function, replace last line with
    return render_template('helloworld.html')
  3. Move helloworld.html to (project root)/templates
  4. Test this (see above). This will bring up

    Welcome, today is {{ dateToday }}

As you can see, this isn’t quite right. If you open up the browser’s console (e.g. ctrl-shift-j in Google Chrome) you’ll see that the browser can’t find the external files (AngularJS and MomentJS). Let’s fix this first

  1. Create (project root)/static. This is where Flask will be looking for anything in (url)/static, without doing any further processing
  2. Move /external to /static. You should now have /static/external, containing angular.js and moment.js
  3. In templates/helloworld.html, change the link from “external/angular.js” to “static/external/angular.js”
  4. Do the same for moment.js
  5. Test this again. This will show

    Welcome, today is

Almost there. If you check in your console you’ll see that the error messages are no longer there. However, the {{ dateToday }} has disappeared. If you view the web page’s source code you’ll see that the line has been replaced with “<p>Welcome, today is </p>”. This is because the Jinja template engine used by Flask also uses double curly brackets {{ }}, just like Angular. Jinja processes (and removes) the double curly brackets before it gets to Angular. There are a number of solutions for this, such as telling Angular to use double square brackets [[ ]] instead:

  1. In helloworld.html, after the line “angular.module(‘HelloWorldApp’, [])”, add:
  2. And replace the {{ }} brackets with [[ ]]:
    Welcome, today is [[ dateToday ]]
  3. Test this. You should now see

    Welcome, today is June 7th 2016

Going live



Continue to Step 3 – Adding in Highcharts

Namepy step 1 – Angular “Hello World”

(This is part of the namepy project. Start at Namepy – on the shoulders of giants)

Let’s make a very simple start, with a webpage with a basic Angular controller, just enough to show that Angular is loaded and active.

Each step will build upon the previous and will live in its own folder/subpath. The external libraries will live in “external”.

Full code is in the git repo. The step-by-step instructions contain only the most relevant, new and/or tricky parts

  1. Download latest version of Angular from and store in project_root/external
  2. For date processing we’ll be using MomentJs. Download it from and store in project_root/external
  3. Create a html page, called helloworld.html and save it in project_ root
  4. Load angular.js and moment.js, using the <script src=”…”></script> tag
  5. Create a simple Angular module and controller
    angular.module('HelloWorldApp', [])
    .controller('HelloWorldController', function($scope) {
    $scope.dateToday = moment().format('MMMM Do YYYY')
  6. Load the module and controller:
    <body ng-app="HelloWorldApp" ng-controller="HelloWorldController"</body>
  7. Create a curly-bracket expression to show the dateToday
    Welcome, today is {{dateToday}}
  8. Save the page



Continue to Step 2 – Flask and Angular

Namepy – on the shoulders of giants

Whilst my core skill/tool is Python, I’m always learning new things, either inside or outside the Python ecosystem. I recently had the pleasure of working with Angular and Python/Flask. Here is a playful application based on these, plus Highcharts.

Going through “Python for Data Analysis”, some of the examples use a database of frequency of (US) baby names since 1880. I thought I’d combine this with a bit of Scrabble™.

In the Python world it’s common to add “py” to a word when making up names, so I’m calling this project “namepy”.

Since I’ll be using various frameworks and libraries, all created by others, I’ve subtitled this “On the shoulders of giants”.

Taking small steps often results in faster progress, so that’s what I’m be doing here.

Technical set up

The source code is at, with one branch per step.

Many production sites Content Delivery Networks for serving Javascript frameworks and libraries, usually minified, which helps to take the load of the server and may speed up first page load. To keep things simple and stable over time, I’m using full-sized, downloaded, copies.

I’m using WebFaction (affiliate link) as the host, since they make it easy to create Flask, Django and similar projects. And, as a popular host for developers, you’ll find lots of helpful documentation for developers online.

Getting started

Create a project folder

mkdir namepy
cd namepy

At the start of each of the steps

cd (my folder for personal projects)
cd namepy
git clone -b step1 step1

Note: “-b step1” specifies the name of the branch to clone. The second “step1” is the target folder, i.e. namepy/step1.


Continue to Step 1 – Angular “Hello World”

Investment Tracking System – Django/Python

My client, a start up with a lot of experience in their field, had identified an important gap in the market. Large sums of money were being invested, with very long payback periods, without access to effective performance tracking tools.

They designed a tool to cover the gap and asked me to create a demonstration system in preparation for generating interest and raising capital.

I developed the system in Django, Python, PostgreSQL and Javascript. The front end uses a dashboard template based on Bootstrap and jQuery. Graphs are created using the excellent Highcharts charting library.

The resulting system imports the base data and generates monthly cost and revenue forecasts, taking into account seasonal variations, tax allowances and more.


The main management screen gives quick access to some key performance indicators.


Constraints can be defined, and potential investments can be checked against them.


Actual results can be compared against the projections.


Different heat maps show absolute or relative performance by state or county.


This was an eight month intensive project, resulting in a demo site which generated a lot of interest in the industry and allowed the client to achieve their first round of funding.