import paho.mqtt.client as mqtt
import time
import pymysql
myConn=pymysql.connect(host='localhost',user='root',password='',db='storesensordata')
def on_connect(client,userdata,flags,rc):
    if rc==0:
        print("Connection is Ok")
    else:
        print("Bad Connection Return Code=",rc)
def on_message(client, userdata, msg):
    data=str(msg.payload.decode("utf-8"))
    temperature=int(data[0]+data[1])
    humidity=int(data[2]+data[3])
    moisture=int(data[4]+data[5])
    if moisture>40:
    	client.publish("sensor/reply","on")
    else:
        client.publish("sensor/reply","off")
    print("Humidity: ",str(humidity)+"   Temperature: ",str(temperature)+"   Moisture:",str(moisture))
    try:
        Time = time.strftime("%H:%M:%S")
        Date = time.strftime("%d/%m/%Y")
        S_cur = myConn.cursor()
        sql = "Insert into dht11 (Date,Time,Humidity,Temperature,Moisture)" \
              + "values (%s, %s, %s, %s, %s)"
        S_cur.execute(sql, (Date,Time,humidity,temperature,moisture))
        myConn.commit()
    except ValueError:
            print("Oops!  Unable to update in database...")
broker="192.168.43.63"		//Borker's IP Address
client=mqtt.Client("Python2")
client.connect(broker)
client.on_connect=on_connect
client.on_message=on_message
print("Connection to broker",broker)
client.subscribe("sensor/data")
client.loop_forever()