title: Best practices for managing connections for database polling using pyodbc?
body: <p>I need to run a stored procedure on an Azure SQL Managed Instance every 10 seconds from a Python application.  The specific call to <code>cursor.execute()</code> happens in a class that extends <code>threading.Thread</code> like so:</p>
  <pre><code>class Parser(threading.Thread):
      def __init__(self, name, event, interface, config):
          threading.Thread.__init__(self)
          self.name = name
          self.stopped = event
          self.interface = interface
          self.config = config
          self.connection_string = config['connection_string']
          self.cnxn = pyodbc.connect(self.connection_string)        
  
      def run(self):
          
          while not self.stopped.wait(10):
                  
              try:
                  cursor = self.cnxn.cursor()
                  cursor.execute(&quot;exec dbo.myStoredProcedure&quot;)
              except Exception as e:
                  logging.error(e)
  </code></pre>
  <p>My current challenge is that the above thread does not recover gracefully from interruptions to network connectivity. My goal is to have the thread continue to run and re-attempt every 10 seconds until connectivity is restored, then recover gracefully.</p>
  <ul>
  <li>Is the best practice here to delete and recreate the connection with every pass of the <code>while</code> loop?</li>
  <li>Should I be using <code>ConnectRetryCount</code> or <code>ConnectRetryInterval</code> in my connection string?</li>
  </ul>
  <p>While debugging I have found that even after connectivity is restored, pyodbc.connect() still fails with ODBC error <code>08S01 Communication link failure</code>.</p>
  <p>I have looked at the solution proposed in <a href="https://stackoverflow.com/questions/41473137/pyodbc-how-to-retry-to-recover-from-transient-errors">this post</a> but don't see how to apply that solution to a continuous polling architecture.</p>