CODEWITHSUNDEEP
pythonmysql
user2669997
user2669997

Reputation:

python mysql connection auto connect on error

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.

Upvotes: 0

Views: 1120

Answers (1)

Ashfaqur Rahaman
Ashfaqur Rahaman

Reputation: 758

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

Upvotes: 2

Related Questions