在mysql中存储Pubnub数据

Question

概念：

通过pubnub通道发送GPS数据和邻近数据，将其存储在mysql数据库中，然后使用grafana监控数据库内容

当前情况：

我们有两个人通过名为“frameData”的pubnub频道发布数据，一个发送GPS数据，另一个发送邻近数据。echo提供了正确的参数，但是这些参数不会发布到数据库中。有人能帮忙解决这个问题吗

代码：

import os
import time
import sys
import datetime
 
import MySQLdb
 
from pubnub.enums import PNStatusCategory
from pubnub.callbacks import SubscribeCallback
from pubnub.pnconfiguration import PNConfiguration
from pubnub.pubnub import PubNub
 

# removed the pub and sub key for the purpose of asking this question
pnconfig = PNConfiguration()
pnconfig.publish_key = "pub-c-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
pnconfig.subscribe_key = "sub-c-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
pubnub = PubNub(pnconfig)
 
CHANNEL = "frameData"
 
database = MySQLdb.connect(host="localhost", user="pi", passwd="raspberry",db="IoTProject")
cursor = database.cursor()
 
FrameID = 1
SerieNummer = "000001"
Longitude = ""
Latitude = ""
isGeladen = ""
Datum=datetime.datetime.now()
 
class MySubscribeCallbackITF(SubscribeCallback):
    def message (self, pubnub, message):
 
        if type(message.message) is list:
 
            Latitude = message.message[0].lstrip("u")
            Longitude = message.message[1].lstrip("u")
            print(Latitude)
            print(Longitude)
            return(Latitude,Longitude)
 
        elif type(message.message) is not  list:
 
            isGeladen = message.message
            print(isGeladen)
            return(isGeladen)

        else:
 
            print("No data available")
 

    def presence(self, pubnub, event):
            print("[PRESENCE: {​​​​}​​​​]".format(event.event))
            print("uuid: {​​​​}​​​​, channel: {​​​​}​​​​".format(event.uuid, event.channel))
 
    def status(self, pubnub, event):
            if event.category == PNStatusCategory.PNConnectedCategory:
                    print("[STATUS: PNConnectedCategory]")
                    print("connected to channels: " + CHANNEL)
 

cursor.execute("INSERT INTO IoTProject(FrameID,SerieNummer,isGeladen,Longitude,Latitude,Datum) VALUES(%s, %s, %s, %s, %s, %s)",(FrameID,SerieNummer,isGeladen,Longitude,Latitude,Datum))
database.commit()
 
print('Listening...')
 
pubnub.add_listener(MySubscribeCallbackITF())
pubnub.subscribe().channels(CHANNEL).execute()

输出： Data that gets placed in the database so far.

Answer 1

将经纬度跟踪数据保存到数据库MySQL

以下示例修改@Daiter123原始代码示例中的帖子。代码的主要修改是在MySubscribeCallbackITF类中定义的message接收器方法内运行cursor.execute("INSERT INTO ... ")和database.commit()

import os
import time
import sys
import datetime
import MySQLdb
from pubnub.enums import PNStatusCategory
from pubnub.callbacks import SubscribeCallback
from pubnub.pnconfiguration import PNConfiguration
from pubnub.pubnub import PubNub

pnconfig = PNConfiguration()
pnconfig.publish_key = "pub-c-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
pnconfig.subscribe_key = "sub-c-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
pubnub = PubNub(pnconfig)
 
CHANNEL = "frameData"
 
database = MySQLdb.connect(host="localhost", user="pi", passwd="raspberry", db="IoTProject")
cursor = database.cursor()

FrameID = 1
SerieNummer = "000001"
Longitude = ""
Latitude = ""
isGeladen = ""
Datum=datetime.datetime.now()
 
class MySubscribeCallbackITF(SubscribeCallback):
    def message (self, pubnub, message):
        if type(message.message) is list:
            Latitude    = message.message[0].lstrip("u")
            Longitude   = message.message[1].lstrip("u")
            Datum       = datetime.datetime.now()
            FrameID     = 1
            SerieNummer = "000001"

            print(Latitude)
            print(Longitude)

            ## =-=-=-=-=-=-=-=-=-=-=-=-=-=
            ## Save Data to Database
            ## =-=-=-=-=-=-=-=-=-=-=-=-=-=
            cursor.execute(
                "INSERT INTO IoTProject(FrameID,SerieNummer,isGeladen,Longitude,Latitude,Datum) VALUES (%s, %s, %s, %s, %s, %s)",
                (FrameID,SerieNummer,isGeladen,Longitude,Latitude,Datum))
            database.commit()
 
        elif type(message.message) is not list:
            isGeladen = message.message
            print(isGeladen)

        else:
            print("No data available")
 
    def status(self, pubnub, event):
        if event.category == PNStatusCategory.PNConnectedCategory:
            print("[STATUS: PNConnectedCategory]")
            print("connected to channels: " + CHANNEL)
 
print('Listening...')
pubnub.add_listener(MySubscribeCallbackITF())
pubnub.subscribe().channels(CHANNEL).execute()