From 3710dc777a1990a2868d3d187d9dd18efaa1f90a Mon Sep 17 00:00:00 2001
From: Roman Bazalevsky <rvb@rvb.name>
Date: Mon, 7 Nov 2016 15:18:25 +0300
Subject: [PATCH 1/1] =?utf8?q?=D0=94=D0=BE=D0=B1=D0=B0=D0=B2=D0=BB=D0=B5?=
 =?utf8?q?=D0=BD=20=D1=80=D0=B5=D1=82=D1=80=D0=B0=D0=BD=D1=81=D0=BB=D1=8F?=
 =?utf8?q?=D1=82=D0=BE=D1=80=20MQTT-MYSQL?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 mqtt.conf       | 10 ++++++
 weathermon-mqtt | 82 +++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 92 insertions(+)
 create mode 100644 mqtt.conf
 create mode 100755 weathermon-mqtt

diff --git a/mqtt.conf b/mqtt.conf
new file mode 100644
index 0000000..028cba4
--- /dev/null
+++ b/mqtt.conf
@@ -0,0 +1,10 @@
+[mqtt]
+server=localhost
+port=1883
+username=readonly
+password=mypassword
+[mysql]
+server=dbhost
+username=meteo
+password=mydbpasswd
+db=meteo
diff --git a/weathermon-mqtt b/weathermon-mqtt
new file mode 100755
index 0000000..6c5fd37
--- /dev/null
+++ b/weathermon-mqtt
@@ -0,0 +1,82 @@
+#!/usr/bin/python
+
+import paho.mqtt.client as paho
+import sys
+from ConfigParser import ConfigParser
+import MySQLdb
+from pprint import pprint
+
+def on_message(mosq, obj, msg):
+  topic=msg.topic
+  payload=msg.payload
+  try:
+    c = database.cursor()
+    c.execute('CALL meteo.submit_mqtt(%s,%s,NULL)', (topic,payload))
+    database.commit()
+    print topic,payload
+  except:
+    print "Failed to submit data"  
+
+def Topics():
+
+  c = database.cursor()
+  c.execute(
+    '''
+    select topic from mqtt_topics
+    '''
+  )
+
+  topics=c.fetchall()
+  return topics
+  
+  
+def Init():
+
+  global client,database
+
+  conffile = sys.argv[1]
+
+  config = ConfigParser()
+  config.add_section('mqtt')
+  # set defaults for anonymous auth
+  config.set('mqtt', 'username', '')
+  config.set('mqtt', 'password', '')
+  config.set('mqtt', 'port', '1883')
+  config.read(conffile)
+
+  mqtt_server = config.get('mqtt', 'server')
+  mqtt_port = config.getint('mqtt', 'port')
+  mqtt_username = config.get('mqtt', 'username')
+  mqtt_password = config.get('mqtt', 'password')
+
+  mysql_server = config.get('mysql', 'server')
+  mysql_username = config.get('mysql','username')
+  mysql_password = config.get('mysql','password')
+  mysql_db = config.get('mysql','db')  
+  
+  client = paho.Client('weather')
+  client.username_pw_set(mqtt_username, mqtt_password)
+  client.on_message=on_message
+  client.connect(mqtt_server, port=mqtt_port)
+
+  database = MySQLdb.connect(host=mysql_server,user=mysql_username,passwd=mysql_password,db=mysql_db,use_unicode=True,connect_timeout=10)
+  database.set_character_set('utf8')
+  c = database.cursor()
+  c.execute('SET NAMES utf8;')
+
+  topics=[]
+  for topic in Topics():
+    topics.append((topic[0].encode('UTF-8'),1))
+  
+  client.subscribe(topics)
+
+Init()
+
+try:
+  while True:
+    try:
+      client.loop()
+    except:
+      break
+finally:
+  exit()   
-- 
2.34.1