Monitor database and ssh connection parallel to the rest of the code

Question

I am running a python script, that is fechting data from an sql server. The queries run a long time, and sometimes the skript breaks, because the db or ssh connection is lost. In this situation, the script will freeze, because it is waiting for a result that is never comeing.

Is there a strategy to continuously moinitor if the db connection is still available, while the rest of the script is doing something else (like waiting for the query result)?

How would i even approach this problem, because the monitoring process would have to be decoupled from the rest of the script.

I know, this is a really broad question that might not have an absolute answer, but i hope for some advice for a general aproach that i can read into.

hostingutilities.com · Accepted Answer · 2019-07-25T02:19:28.053

You can do this by putting your long running code in a separate thread, and then instead of using thread.join() to detect when the thread has finished, you would periodically poll to see if the thread has finished and to see if the connection is still good. The thread.exit() function can then be used if you discover that the connection is bad, and you need to exit early.

I was going off of this for the MySQL functions, but you may have to change that out for whatever database you are using. My computer isn't setup to be able to easily connect to a database in Python, so I haven't tested any of this code, so there may be some bugs in it, but this should give you the general idea of how you can monitor a database connection while performing a query.

import threading
import MySQLdb

conn = MySQLdb.connect("localhost","testuser","test123","testdb" )

class DB_Query(threading.Thread):
    ''' Used by the run_query function to run a query in a separate thread '''
     def __init__(self, conn, query, dictionary_to_store_results):
         super(DB_Query, self).__init__()
         dictionary_to_store_results['finished'] = false
         self.conn = conn
         self.query = query
         self.result = dictionary_to_store_results

     def run(self):
         cursor = self.conn.cursor()
         try:
             cursor.execute(self.query)
         except SystemExit:
             cleanup_after_unexpected_broken_connection()
             raise
         self.result['data'] = cursor.fetchall()
         self.result['finished'] = true

    def cleanup_after_unexpected_broken_connection():
        # add cleanup code here
        pass

def check_connection_good(conn):
    # check if the connection is good here
    return True

def run_query(query):
    ''' Runs a query, while periodically checking to make sure the connection stays active throughout the process '''
    global conn
    query_result = {} # the thread will populate this dictionary with the results of executing the query
    thread = DB_Query(conn, query, query_result)
    thread.start()
    while(true):
        # check once every half second for the next 5 seconds if the thread is still running
        for i in range(5):
            time.sleep(.5)
            if not thread.is_alive():
                break
        # throw a systemExit error in the thread if the connection is broken
        if not check_connection_good(conn):
            thread.exit()
    return query_result['data']

if __name__ == "__main__":
    run_query('select * from some_table')

Monitor database and ssh connection parallel to the rest of the code

1 Answers1