MQTT + MySQL: Bridge message example

bridge-mqtt-mysql.py

#!/usr/bin/python
import paho.mqtt.client as mqttClient
import MySQLdb as mdb
import time

#broker_address= "m11.cloudmqtt.com"  #Broker address
broker_address = "localhost"
broker_port = 1883                           #Broker broker_port
#broker_user = "yourUser"                    #Connection username
#broker_password = "yourPassword"            #Connection password

db_hostname = "localhost"  # MySQL host ip address or name
db_database = "mqtt"       # MySQL database name
db_username = "mqttuser"   # MySQL database user name
db_password = "mqttpass"   # MySQL database password

#Connected = False   #global variable for the state of the connection

def on_connect(client, userdata, flags, rc):
    if rc == 0:
        print("Connected to broker")
        #global Connected                #Use global variable
        #Connected = True                #Signal connection 
        #Subscribing in on_connect() means that if we lose the connection and reconnect then subscriptions will be renewed.
        client.subscribe([("#",0),("/#",0),("$SYS/broker/log/#",0)]) #subscribe all 
    else:
        print("Connection failed")
 
def on_message(client, userdata, msg):
   print "MQTT subscribed |",msg.topic,"|",str(msg.qos),"|",str(msg.payload),"|"
   db_insert(msg)


def db_insert(msg):
    with con:
        cur = con.cursor()
        cur.execute("INSERT INTO messages (topic , qos, message) VALUES (%s, %s, %s)", (msg.topic, msg.qos, msg.payload))
        print "MySQL INSERT INTO messages (topic_id , qos, message_id) VALUES (",str(msg.topic),", ",str(msg.qos),", ",str(msg.payload),")"


def main(): 
    global con
    #Try connect databank
    db_connected = 0
    while db_connected == 0:
        try:
            con = mdb.connect(db_hostname, db_username, db_password, db_database)
            db_connected = 1
            print "Connected to database"
        except:
            print "Warning: No database (connection) found. Retry in one minute."
            time.sleep(60)
        pass

    client = mqttClient.Client("PythonMQTT-MySQL")               #create new instance
    #client.username_pw_set(broker_user, password=broker_password)    #set username and password
    client.on_connect= on_connect                      #attach function to callback
    client.on_message= on_message                      #attach function to callback
     
    rc = 1
    while rc == 1:
        try:
            client.connect(broker_address, port=broker_port)
            rc = 0
        except:
            print "Warning: No broker found. Retry in one minute."
            time.sleep(60)
            pass

    while rc == 0:
        try:
            rc = client.loop()
        except:
            rc = 1

    print("Warning: Connection error - Restarting.")

    #client.connect(broker_address, port=broker_port)          #connect to broker
     
    #client.loop_start()        #start the loop
     
    #while Connected != True:    #Wait for connection
    #    time.sleep(0.1)
     
    

    

if __name__ == "__main__":
    try:
        while True:
            main()
            #time.sleep(1)
     
    except KeyboardInterrupt:
        print "exiting"
        client.disconnect()
        client.loop_stop()
        quit()
    
 

initMqttDatabank.sql

CREATE DATABASE mqtt;
USE mqtt;

/*Apada tabela se ela existe*/
DROP TABLE IF EXISTS messages;
CREATE TABLE messages (
  timestamp   timestamp DEFAULT CURRENT_TIMESTAMP,
  /*NOT NULL obriga que esse dado seja preenchido*/
  topic       text NOT NULL,
  qos         tinyint(1) NOT NULL,
  message     text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS settings;
CREATE TABLE settings (
  /*
  Faz com que não possam haver campos iguais a este na tabela
  */
  setting     varchar(8) NOT NULL PRIMARY KEY,
  state       tinyint(1) NOT NULL,
  timestamp   timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;