owntracks-subscribe_database.py

import paho.mqtt.client as mqtt                                         #necessary imports
import sqlite3 as sql
import json, os
from datetime import date, datetime
import time
import sys

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. You might want to change it. 
    cur = con.cursor()                                                   #Connect or Create DB File 
    try:
        cur.execute("CREATE TABLE Location(longitude NUMBER(10,6), latitude NUMBER(10,6), date VARCHAR2(20), time VARCHAR2(20));") 
    except:
        pass
        data = json.loads(msg.payload.decode("utf8"))                   #decode message
        day = date.today()                                              #time functions
        clock = datetime.now()
        time = datetime.time(clock)
        cur.execute("INSERT INTO Location values(?,?,?,?);", (data["lon"], data["lat"], str(day), str(time)))
                                                                        #puts the latitude, longitude from the posted message as well as the date and time when it was 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 (same as db)
        print(str(data))                                                                #puts the latitude, longitude from the posted message as well as the date and time when it was posted into the database
    
        
        con.commit()                                                        #This method commits the current transaction.
    cur.close()
    con.close()                                                         #close the database
     
    print("Here we are")

    print("message")


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.connect("broker.hivemq.com", 1883)                             #connect to the broker on an appropriate port
      
client.loop_forever()                                                   #keep looping forever (allows realtime subscription)