owntracks_mqtt_question4

import paho.mqtt.client as mqtt    
import sqlite3 as sql                                     #import all the necessary libraries
import json, os
from datetime import date, datetime
import time

print("Hello, Visual Studio")
  
""" callback function for connection """
def on_connect(client, userdata, flags, rc):                            #client method to connect
    if rc==0:
        client.connected_flag = True                                    #set flag
        print("connected OK Returned code=",rc)                         #let us know we connected to the broker
        client.subscribe("owntracks/KS/#")                        #we are connected, so subscribe to the topic. wildcard means any device
        print("rc")
    else:
        print("Bad connection Returned code=",rc)                #if we can't connect

  
""" callback function for messages received """
def on_message( client, userdata, msg ):                                #client method to get messages from topic  
    con=sql.connect("locations2.db")                                        #name of the database
    cur=con.cursor()                                                    #connect our Database file
    topic = msg.topic                                                   #for use when we can't decode
    try:
	  #print ("Cannot decode data on topic {0}".format(topic))         #cannot decode; print the topic for the non-decodable message
		   cur.execute("CREATE TABLE Location(longitude NUMBER(10,6), latitude NUMBER(10,6), date VARCHAR2(20), time VARCHAR2(20));") 
    except:
        data = json.loads(msg.payload.decode("utf8"))                   #decode message
        day = date.today()                                              #time functions
        clock = datetime.now()
        time = datetime.time(clock)
        cur.execute("INSER INFO Location values (?,?,?,?);",(data("lon"), data("lat"),str(day), str(time)))     #puts the latitude, longtitude, the date and time when posted into the database
		    print ("TID = {0} is currently at {1}, {2},{3},{4}".format(data['tid'], data['lat'], data['lon'], str(day), str(time)))
                                                                        #print device, latitude and longitude from the message; add time data
        print(str(data))                                                #print the entire message just for fun
        con.commit()                                                    #commit the current transaction
		    cur.close()                                                     # close the database
			  con.close()
      
client = mqtt.Client() 
client.on_connect = on_connect
client.on_message = on_message

client.username_pw_set("KS", "mqttBROKER")                        #associate authentication details with the client

#client.tls_set("mqtt.coventry.ac.uk.crt")                               #certificate for client. needs to be in the same directory as this script
client.connect("broker.hivemq.com", 1883)                             #connect to the broker on an appropriate port
      
client.loop_forever()                                                   #keep looping forever (allows realtime subscription)
print("Here we are")

#def on_connect(client, userdata, flags, rc):
  #  print("Connected with result code "+str(rc))
     # Subscribing in on_connect() means that if we lose the connection and
    # reconnect then subscriptions will be renewed.
  #  client.subscribe("$SYS/#")
    # The callback for when a PUBLISH message is received from the server.
#def on_message(client, userdata, msg):
 #   print(msg.topic+" "+str(msg.payload))
  #  client = mqtt.Client()
#client.on_connect = on_connect
#mqttBroker ="mqtt.eclipseprojects.io" 

#client = mqtt.Client("Temperature_Inside")
#client.connect(mqttBroker)
#client.on_connect = on_connect
#client.on_message = on_message
print("message")