CODEWITHSUNDEEP
pythonmysqlmqtt
emir
emir

Reputation: 1406

How to reconnect to mysql after receiving mqtt message in python?

I am using python to subscribe to one topic, parse JSON and store them in the database. I have problems with loosing connection with MySQL because it can't be opened too long. Message that I receive is below

_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')

I managed to remove it by increasing timeout, but that is not good solution, because I can't know how long will the system need to wait for the message. Is there a possibility I could create connection only when message is received?

i tried to add the connection details into on message, and then closing it but I still have the same problem

def on_message(client, userdata, msg):

sql="""INSERT INTO data(something) VALUES (%s)""" data = ("some value") with db: try: cursor.execute(sql,data) except MySQLdb.Error: db.ping(True) cursor.execute(sql,data) except: print("error") print(cursor._last_executed)

but then that variable is not visible outside this function. What is the best practise for this.

The part of code for making connection is bellow

import paho.mqtt.client as mqtt
import MySQLdb
import json
import time
#mysql config
try:
    db = MySQLdb.connect(host="localhost",  # your host 
                     user="admin",       # username
                     passwd="somepass",     # password
                     db="mydb")   # name of the database
except:
    print("error")

So as you see, I have created one connection to mysql at the begging, and if there is no message for time longer then defined timeout my script stops working.

Upvotes: 0

Views: 1251

Answers (1)

Torbik
Torbik

Reputation: 509

Try:

cur = db.cursor()
try:
    cur.execute(query, params)
except MySQLdb.Error:
    db.ping(True)
    cur.execute(query, params)

db.ping(True) says to reconnect to DB is the connection was lost. You can also call db.ping(True) right after MySQLdb.connect. But to be on the safe side I'd better wrap execute() into try and call db.ping(True) in except block.

Upvotes: 2

Related Questions