How to handle two recursive Try Except blocks in parallel (Python)

Question

I have a Python script that indefinitely connects to a SQL server and an ActiveMQ server and I am trying to build something that can handle disconnects for both separately. Whenever a connection breaks, I want to reconnect to the server. However, the ActiveMQ connection disconnects much more frequently than the SQL connection and I don't want to reconnect to the SQL server a bunch of times just because the ActiveMQ one is broken.

This is what I've got so far:

def connectSQL(host, port):
    try:
        time.sleep(5)
        connSQL = pyodbc.connect(driver='{ODBC Driver 17 for SQL Server}',
                           server=sqlserver,
                           database=sqldb,
                           uid=sqluser,pwd=sqlpassword)
        cursor = connSQL.cursor()

        def connectActiveMQ(host, port):
            try:
                time.sleep(5)           
                conn = stomp.Connection(host_and_ports = [(host, port)],heartbeats=(1000, 1000))
                conn.set_listener('', MyListener(conn))
                connect_and_subscribe(conn)
                print("Deployed ActiveMQ listener ...")
                while True:
                    time.sleep(10)
            except:
                print("ActiveMQ connection broke, redeploying listener")
                connectActiveMQ(host, port)

        connectActiveMQ(host,port)
        #Here is a ValueError representing a SQL disconnect
        raise ValueError('SQL connection broke')

    except:
        print("SQL connection broke, reconnecting to SQL")
        connectSQL(host, port)

connectSQL(host,port)

This works perfectly for reconnecting to ActiveMQ, but it doesn't work for SQL. Once it has already connected to SQL, any errors become inaccessible due to the ActiveMQ loop (the raise ValueError "SQL connection broke" becomes inaccessible in this code if both connections go through even for a moment). I need the connection to run indefinitely, but I don't know where else I can put my while:True wait statement.

How can I rewrite this so I can catch both ActiveMQ and SQL disconnects in parallel indefinitely?

I would not put these methods *inside* one another. The code that does `raise ValueError` also seems like a bug to me. — Mike Robinson, Feb 04 '20 at 17:00
Yeah I do want to avoid having them nested, but I also am not sure of any other way to have both of them working. If I have them as separate synchronous loops, the second loop will simply never be visited due to the recursion. — fume_hood_geologist, Feb 04 '20 at 19:46
Also the raise ValueError is just an example of a disconnection. I put it there to point out where I need to expect a disconnect, but my code cannot access it. — fume_hood_geologist, Feb 04 '20 at 20:24
Using recursion (especially with indefinite retries) is susceptible to a [`RecursionError`](https://docs.python.org/3/library/exceptions.html#RecursionError) (essentially a stack overflow). See more discussion [here](https://stackoverflow.com/questions/3323001/what-is-the-maximum-recursion-depth-in-python-and-how-to-increase-it). With a sleep of 5 seconds you'd hit a `RecursionError` in around 83 minutes of attempting to reconnect using the normal recursion limit of 1000. — Justin Bertram, Feb 04 '20 at 20:33
There is nothing parallel about your code. It is 100% sequential. If you really want the connection attempts to be done in parallel you should create a thread for each of them. — Justin Bertram, Feb 04 '20 at 20:42
What is the issue, exactly? This seems far too broad/vague to me. See: [ask], [help/on-topic]. — AMC, Feb 04 '20 at 23:04
Code Maverick solved my problem! Basically I just needed to introduce multithreading :D — fume_hood_geologist, Feb 04 '20 at 23:25

score 2 · Accepted Answer · answered Feb 04 '20 at 21:08

Quick fix: use threading or multiprocessing. Here is a snippet using threading.

import threading

def connectSQL(host, port):
    try:
        time.sleep(5)
        connSQL = pyodbc.connect(driver='{ODBC Driver 17 for SQL Server}',
                                server=sqlserver,
                                database=sqldb,
                                uid=sqluser,pwd=sqlpassword)
        cursor = connSQL.cursor()
        raise ValueError('SQL connection broke')
    except:
        print("SQL connection broke, reconnecting to SQL")
        connectSQL(host, port)

def connectActiveMQ(host, port):
    try:
        time.sleep(5)           
        conn = stomp.Connection(host_and_ports = [(host, port)],heartbeats=(1000, 1000))
        conn.set_listener('', MyListener(conn))
        connect_and_subscribe(conn)
        print("Deployed ActiveMQ listener ...")
        while True:
            time.sleep(10)
    except:
        print("ActiveMQ connection broke, redeploying listener")
        connectActiveMQ(host, port)


t1 = threading.Thread(target=connectActiveMQ, args=(host, port))
t2 = threading.Thread(target=connectSQL, args=(host, port))
t1.start()
t2.start()

P.S. Given the quickfix, you should definitely look into the comments above to refactor the individual functions connectSQL and connectActiveMQ. If you need to share data between the methods, have a look here.

Thank you so much!! I know nothing about multithreading, but this shows exactly what I've been trying to do very clearly. The link on thread queues was very helpful, as I needed to put my SQL connection variable into the ActiveMQ thread. I also switched my recursion to a while loop (hope that doesn't cause recursion errors). I tested out the new multithreaded approach and it works perfectly! — fume_hood_geologist, Feb 04 '20 at 23:21

How to handle two recursive Try Except blocks in parallel (Python)

1 Answers1