5

I've an API hosted on Flask. It runs behind a Tornado server. What is happening is that sometimes changes made on the UI are not reflected in the database. Also a few of the scripts I have running gives any of the 3 following errors:

  1. pyodbc.Error: ('08S01', '[08S01] [Microsoft][ODBC SQL Server Driver]Communication link failure (0) (SQLExecDirectW)')
  2. pyodbc.Error: ('01000', '[01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()). (10054) (SQLExecDirectW)')
  3. pyodbc.Error: ('01000', '[01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()). (10054) (SQLExecDirectW)')

This is the snippet of my Flask API code:

class Type(Resource):

    def put(self):
        parser = reqparse.RequestParser()
        parser.add_argument('id', type = int)
        parser.add_argument('type', type = int)
        args = parser.parse_args()

        query = """
        UPDATE myDb SET Type = ? WHERE Id = ?
        """

        connection = pyodbc.connect(connectionString)
        cursor = connection.cursor()
        cursor.execute(query, [args['type'], args['id']])
        connection.commit()
        cursor.close()
        connection.close()

api.add_resource(Type, '/type')

Is there any retry logic I can add on the cursor.execute line? I've no idea how to deal with transient errors with python. Please help.

90abyss
  • 7,037
  • 19
  • 63
  • 94

1 Answers1

15

Per my experience, I think may be you can try to use the code below to implement the retry logic.

import time

retry_flag = True
retry_count = 0
while retry_flag and retry_count < 5:
  try:
    cursor.execute(query, [args['type'], args['id']])
    retry_flag = False
  except:
    print "Retry after 1 sec"
    retry_count = retry_count + 1
    time.sleep(1)

Hope it helps.

Peter Pan
  • 23,476
  • 4
  • 25
  • 43
  • This seems to be working as expected. Thanks a lot, Peter. :) – 90abyss Jan 05 '17 at 21:04
  • I am facing a similar issue in my python script. But, I have used my sql query like this: `import pyodbc @property def sqlquery(self): conn = pyodbc.connect("Driver={SQL Server};" "Server=server1;" "Database=db1;" "Trusted_Connection=yes;") cursor = conn.cursor() cursor.execute('SELECT TOP(10) * FROM xyz') for row in cursor: print('row = %r' % (row,)) return row conn.close()` Can you please help me how to integrate this code into the function? – Karan M Aug 02 '18 at 18:31
  • Still giving the same error. Tried increasing the retry time also. – Anurag Sharma Oct 03 '20 at 10:34