Permalink
Cannot retrieve contributors at this time
Name already in use
A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
Week2_Tutorial/owntracks-subscribe_database.py
Go to fileThis commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.
58 lines (44 sloc)
3.1 KB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) | |