Writing a Postgresql connector in FlaskSQLAlchemy

We want to use the postgresql database for authentification of our web application. The username and password is stored clientside in a SecureCookie (see flask.session). This cookie has to be read before every request and the database connection has to be established:

from flask import request_started, session
request_started.connect(connect_db, app)

def connect_db(app, data=session):
    if 'u' in data and 'p' in data:
        app.config['SQLALCHEMY_DATABASE_URI'] = \
            app.config.get('DATABASE') % (data.get('u'), data.get('p'))
        db.init_app(app)

A few problems arise with this scenario:

1) The database connector in FlaskSQLAlchemy has to be reset, because reading the connector of another user is not wanted.

subclassing of SQLAlchemy this way solves this issue:

from flaskext.sqlalchemy import SQLAlchemy, get_state

class MySQLAlchemy(SQLAlchemy):
    """ subclass of Flask-SQLAlchemy, because we need to clean the
    connector on every request. Improvement: save connector for every
    user session?!
    """

    def get_engine(self, app, bind=None):
        """Returns a specific engine.
        changed for using a new connector on EVERY request.
        """
        with self._engine_lock:
            connector = self.make_connector(app, bind)
            state = get_state(app)
            state.connectors[bind] = connector
            return connector.get_engine()

2) Armin added an AssertionError if the request was already handled (see commit 5500986971b28f270a27db633acf19984eee609e). In our case the request is handled more than one time on every request. Because of this problem we use version 0.7.3 of Flask and not 0.8 at the moment.

This assertion is totally reasonable, but there should be an option to disable it!

One possible solution may be this branch: first-request-decorator on github/mfa.

Create Plots using rrdtool

Next step for my humidity logger is to get nice plots. Here rrdtool seems to be the tool of choice.

First create a few rrd-files. For example:

rrdtool create humidity.rrd --step 30 \
  DS:humidity:GAUGE:120:0:100 \
  RRA:AVERAGE:0.5:1:1200 \
  RRA:MIN:0.5:12:2400 \
  RRA:MAX:0.5:12:2400 \
  RRA:AVERAGE:0.5:12:2400

With update every 30 seconds, a GAUGE value from 0 to 100. Additional calculating average, minimum and maximum of the humidity value.

Adding new values for the plot in python using python-rrdtool.

def log_to_rrd(data):
  values = json.loads(data)
  dt = time.mktime(datetime.datetime.now().timetuple())

  ret = rrdtool.update('humidity.rrd','%d:%f' %
                       (dt, values.get('humidity')))
  if ret:
      print rrdtool.error()

And build a plot for one day:

def graph():
    ret = rrdtool.graph( fn, "--start", "-1d",
                         "--lower-limit=0",
                         "--title=Humidity",
                         "--vertical-label=-" + str(cnt) + str(size),
                         "DEF:hum=humidity.rrd:humidity:AVERAGE",
                         "LINE1:hum#0000FF:Humidity\\r",
                         "COMMENT:\\n",
                         "GPRINT:hum:AVERAGE:Avg Humidity\: %2.2lf%S%%",
                         "COMMENT:    ",
                         "GPRINT:hum:MAX:Max Humidity\: %2.2lf%S%%\\r")

Final example (with humidity, temperature and calculated dew point):

rrdtool example

Humidity Logger Version 0.1

Now merging SHT11, DS107 and HTTP POST.

Some problems occured:

  • pin 10+11 are reserved for Ethernet -- SHT11 has to use pin 8+9
  • there is no float to String

After fixing the issues and some code cleanup the final version:

#include <SPI.h>
#include <Ethernet.h>
#include <SHT1x.h>
#include "Wire.h"

////////////////////////////////////////////////////////////////////////////////
// Specify data and clock connections and instantiate SHT1x object
#define dataPin  8
#define clockPin 9
SHT1x sht1x(dataPin, clockPin);

////////////////////////////////////////////////////////////////////////////////
// Ethernet data
byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xDD };
byte ip[] = { 192,168,1, 42 };
byte gateway[] = { 192, 168, 1, 1};
byte subnet[] = { 255, 255, 255, 0 };

byte server[] = { 192, 168, 1, 2 };

// initialize the library instance:
Client client(server, 80);

const int postingInterval = 29000;   // delay between updates
long lastConnectionTime = 0;         // last time connected to the server
boolean lastConnected = false;       // state of the connection
long counter = 1;

String txtMsg="";

void sendData() {

  Serial.println("...");

  if (client.connect()) {
    Serial.println("connecting...");

    client.print("POST /input/ HTTP/1.1\n");

    client.print("Host: HOSTNAME\n");
    client.print("X-ApiKey: RANDOM_API_KEY\n");

    client.println("User-Agent: Arduino");
    client.println("Accept: text/html");
    client.print("Content-Length: ");
    client.println(txtMsg.length(), DEC);

    client.print("Content-Type: text/json\n");
    client.println("Connection: close\n");

    client.println(txtMsg);

    lastConnectionTime = millis();
    counter += 1;
    Serial.println("sent...");
  }
  else {
    Serial.println("connection failed");
    delay(1000);
  }
}

////////////////////////////////////////////////////////////////////////////////
// DS1307
#define DS1307_I2C_ADDRESS 0x68

// Convert normal decimal numbers to binary coded decimal
byte decToBcd(byte val)
{
  return ( (val/10*16) + (val%10) );
}

// Convert binary coded decimal to normal decimal numbers
byte bcdToDec(byte val)
{
  return ( (val/16*10) + (val%16) );
}

// 1) Sets the date and time on the ds1307
// 2) Starts the clock
// 3) Sets hour mode to 24 hour clock
// Assumes you're passing in valid numbers
void setDateDs1307(byte second,        // 0-59
                   byte minute,        // 0-59
                   byte hour,          // 1-23
                   byte dayOfWeek,     // 1-7
                   byte dayOfMonth,    // 1-28/29/30/31
                   byte month,         // 1-12
                   byte year)          // 0-99
{
   Wire.beginTransmission(DS1307_I2C_ADDRESS);
   Wire.send(0);
   Wire.send(decToBcd(second));    // 0 to bit 7 starts the clock
   Wire.send(decToBcd(minute));
   Wire.send(decToBcd(hour));      // If you want 12 hour am/pm you need to set
                                   // bit 6 (also need to change readDateDs1307)
   Wire.send(decToBcd(dayOfWeek));
   Wire.send(decToBcd(dayOfMonth));
   Wire.send(decToBcd(month));
   Wire.send(decToBcd(year));
   Wire.endTransmission();
}

// Gets the date and time from the ds1307
void getDateDs1307(byte *second,
          byte *minute,
          byte *hour,
          byte *dayOfWeek,
          byte *dayOfMonth,
          byte *month,
          byte *year)
{
  // Reset the register pointer
  Wire.beginTransmission(DS1307_I2C_ADDRESS);
  Wire.send(0);
  Wire.endTransmission();

  Wire.requestFrom(DS1307_I2C_ADDRESS, 7);

  // A few of these need masks because certain bits are control bits
  *second     = bcdToDec(Wire.receive() & 0x7f);
  *minute     = bcdToDec(Wire.receive());
  *hour       = bcdToDec(Wire.receive() & 0x3f);  // Need to change this if 12 hour am/pm
  *dayOfWeek  = bcdToDec(Wire.receive());
  *dayOfMonth = bcdToDec(Wire.receive());
  *month      = bcdToDec(Wire.receive());
  *year       = bcdToDec(Wire.receive());
}

void setDT()
{
  byte second, minute, hour, dayOfWeek, dayOfMonth, month, year;

  // Change these values to what you want to set your clock to.
  // You probably only want to set your clock once and then remove
  // the setDateDs1307 call.
  second = 0;
  minute = 01;
  hour = 15;
  dayOfWeek = 0;
  dayOfMonth = 6;
  month = 11;
  year = 11;
  setDateDs1307(second, minute, hour, dayOfWeek, dayOfMonth, month, year);
}

////////////////////////////////////////////////////////////////////////////////

String Float2String(float value)
{
  // Convert a float to String with two decimals.
  String s;
  s = int(value);
  s += '.';
  s += int((value - int(value)) * 100);

  return s;
}

void setup()
{
  // start the Ethernet connection:
  Ethernet.begin(mac, ip);
  Wire.begin();

  Serial.begin(9600);
  // if datetime should be set:
  //setDT()
  // delay for the ethernet to get up
  delay(1000);
}

void loop()
{
  float temp_c, humidity;
  byte second, minute, hour, dayOfWeek, dayOfMonth, month, year;

  if(millis() - lastConnectionTime > postingInterval) {
    // disconnect old client
    Serial.println();
    Serial.println("disconnecting.");
    client.stop();

    delay(1000);

    // Read values from the sensor
    temp_c = sht1x.readTemperatureC();
    humidity = sht1x.readHumidity();

    Serial.print("Temperature: ");
    Serial.print(temp_c, DEC);
    Serial.print("C / ");
    Serial.print("Humidity: ");
    Serial.print(humidity);
    Serial.println("%");

    // Read data from the clock
    getDateDs1307(&second, &minute, &hour, &dayOfWeek, &dayOfMonth, &month, &year);

    // the JSON message
    txtMsg = "{ \"temperature\":";
    txtMsg += Float2String(temp_c);
    txtMsg += ", \"humidity\":";
    txtMsg += Float2String(humidity);
    txtMsg += ", \"counter\":";
    txtMsg += counter;
    txtMsg += ", \"datetime\":\'";
    txtMsg += int(year);
    txtMsg += "-";
    txtMsg += int(month);
    txtMsg += "-";
    txtMsg += int(dayOfMonth);
    txtMsg += " ";
    txtMsg += int(hour);
    txtMsg += ":";
    txtMsg += int(minute);
    txtMsg += ":";
    txtMsg += int(second);
    txtMsg += "\'";
    txtMsg += "}";

    // print for debugging
    Serial.println(txtMsg);

    sendData();
  }

  lastConnected = client.connected();
}