Using python MQTT to save data to mySQL

Application Development
1

Hi, I’m currently trying to get the data out of TTN with a python script running on a server. That works, the code shows the uplink messages.

Now I want to write the data into a mySQL database within the same script. The database and columns have been created already. But somehow the code doesnt’t get to the part where the mySQL statement is formulated and just writes nothing into the database.

I’m completelynew to python, any hints/directions you can give me?

Best Regards

Marco

import time                                                                                                      
import ttn                                                                                                       
import mysql.connector                                                                                           
cnx = mysql.connector.connect(user='xxx', password='xxx',                                         
                              host='xxx',                                                
                              database='xxx')                                                          
                                                                                                                 
app_id = "ra-ma_node_01"                                                                                         
access_key = "ttn-account-v2.xxx"                                        
                                                                                                                 
def uplink_callback(msg, client):                                                                                
        print("Received uplink from ", msg.dev_id)                                                               
        print(msg)                                                                                               
        cursor = cnx.cursor()                                                                                    
        add_data = "INSERT into ttn_data_01 (payload_fields) VALUES (%s)" %msg.payload_fields                    
        print(add_data)                                                                                          
        cursor.execute (add_data)                                                                                
        cnx.commit()                                                                                             
        cursor.close()                                                                                           
                                                                                                                 
                                                                                                                 
handler = ttn.HandlerClient(app_id, access_key)                                                                  
                                                                                                                 
# using mqtt client                                                                                              
mqtt_client = handler.data()                                                                                     
mqtt_client.set_uplink_callback(uplink_callback)                                                                 
mqtt_client.connect()                                                                                            
time.sleep(60)                                                                                                   
mqtt_client.close()                                                                                              
cnx.close()                                                                                                      
                                                                                                                 
# using application manager client                                                                               
app_client =  handler.application()                                                                              
my_app = app_client.get()                                                                                        
print(my_app)                                                                                                    
my_devices = app_client.devices()                                                                                
print(my_devices)
2

Have you considered not using mysql but using splunk instead? Splunk is free for upto 1/2 GB of data per day and understands json natively. You don’t need to do anything to get it indexed and use it. I’ve been using splunk for storing lora json data in the past, it’s very nice.

There may be a little learning curve to get the most out of it though (Building dashboards for example), but for storing and being able to search and access it later it’s brilliant! It’ll also rotate all the data for you as well.

Kim Hendrikse

1 Like
3

Hi Ralf, did you ever figure how to solve this problem?

4

in your method uplink_callback there is global cnx missing.
Better you open the connection to the database inside the method…

Sven