python mysql connection auto connect on error

Question

I have a problem with my python mysql connection which I need help with. My setup is two Pi's running servers on each one. One Pi (SolartPi) has Mysql database collecting data. The other pi (OfficePi) is connecting to the solarPi database to retrieve and update data over a network connection.

My main script works all ok until I have to reboot the SolarPi for a maintenance or power problem and the connection to the OfficePi is lost. The python script on the officePi then goes into a fault loop "2006, MYSQL Server has gone away" Below is a sample of this script.

import MySQLdb

connSolar = MySQLdb.connect("192.xxx.x.x", "external", "xxxxx", "xxxxx") 
#eternal connection to solar pi database
cursSolar = connSolar.cursor()

while 1:

   try:
       cursSolar.execute("SELECT * FROM dashboard")
       connSolar.commit()
       for reading in cursSolar.fetchall():
           heatingDemand = reading[2] #get heating demand from dB
           print heatingDemand
   except (MySQLdb.Error, MySQLdb.Warning) as e:
       print (e)    

connSolar.close()

So I tried rewriting this with the script from stackoverflow and a web site as shown below, but this now terminates the program when SolarPi is rebooted with the following error _mysql_exceptions.OperationalError: (2003, 'Can\'t connect to MySQL server on \'192.xxx.x.x' (111 "Connection refused")')

import MySQLdb

class DB:
con = None

def connect(self):
    self.conn = MySQLdb.connect("192.xxx.x.x", "xxxxx", "xxxxxx", "house") #eternal connection to solar pi database

def query(self, sql):
    try:
        cursor = self.conn.cursor()
        cursor.execute(sql)
    except (AttributeError, MySQLdb.OperationalError):
        self.connect()
        cursor = self.conn.cursor()
        cursor.execute(sql)
    return cursor   

while 1:
    db = DB()
    sql = "SELECT * FROM dashboard"
    cur = db.query(sql)
    for reading in cur.fetchall():
        heatingDemand = reading[2] #get heating demand from dB
        print heatingDemand

Is there a way for the OfficePi to keep trying to connect to SolarPi mysql database when it has shut down.

Ashfaqur Rahaman · Accepted Answer · 2018-04-17 11:49:03Z

2

Change your code to check a valid connection each loop otherwise pass:

import MySQLdb

class DB:

    def connect(self):
        try:
            self.conn = MySQLdb.connect("192.xxx.x.x", "xxxxx", "xxxxxx", "house")

        except (MySQLdb.Error, MySQLdb.Warning) as e:
            print (e)
            self.conn = None

    return self.conn

    def query(self, sql):
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
        except (AttributeError, MySQLdb.OperationalError):
            self.connect()
            cursor = self.conn.cursor()
            cursor.execute(sql)
        return cursor   

while 1:
    db = DB()

    conn = db.connect()

    if conn:
        sql = "SELECT * FROM dashboard"
        cur = db.query(sql)
        for reading in cur.fetchall():
            heatingDemand = reading[2] #get heating demand from dB
            print heatingDemand

answered Apr 17, 2018 at 11:49

Ashfaqur Rahaman

7581 gold badge5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2669997 Over a year ago

Thank you this works, i assumed i was suppose to put all below class DB: indented to this class. Anyway thank you again for the quick reply I will now implement this into my main program :)

Collectives™ on Stack Overflow

python mysql connection auto connect on error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related