is there a pythonic way to try something up to a maximum number of times?

I have a python script which is querying a MySQL server on a shared linux host. For some reason, queries to MySQL often return a “server has gone away” error:

_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')

If you try the query again immediately afterwards, it usually succeeds. So, I’d like to know if there’s a sensible way in python to try to execute a query, and if it fails, to try again, up to a fixed number of tries. Probably I’d want it to try 5 times before giving up altogether.

Here’s the kind of code I have:

conn = MySQLdb.connect(host, user, password, database)
cursor = conn.cursor()

try:
    cursor.execute(query)
    rows = cursor.fetchall()
    for row in rows:
        # do something with the data
except MySQLdb.Error, e:
    print "MySQL Error %d: %s" % (e.args[0], e.args[1])

Clearly I could do it by having another attempt in the except clause, but that’s incredibly ugly, and I have a feeling there must be a decent way to achieve this.

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

How about:

conn = MySQLdb.connect(host, user, password, database)
cursor = conn.cursor()
attempts = 0

while attempts < 3:
    try:
        cursor.execute(query)
        rows = cursor.fetchall()
        for row in rows:
            # do something with the data
        break
    except MySQLdb.Error, e:
        attempts += 1
        print "MySQL Error %d: %s" % (e.args[0], e.args[1])

Solution 2

Building on Dana’s answer, you might want to do this as a decorator:

def retry(howmany):
    def tryIt(func):
        def f():
            attempts = 0
            while attempts < howmany:
                try:
                    return func()
                except:
                    attempts += 1
        return f
    return tryIt

Then…

@retry(5)
def the_db_func():
    # [...]

Enhanced version that uses the decorator module

import decorator, time

def retry(howmany, *exception_types, **kwargs):
    timeout = kwargs.get('timeout', 0.0) # seconds
    @decorator.decorator
    def tryIt(func, *fargs, **fkwargs):
        for _ in xrange(howmany):
            try: return func(*fargs, **fkwargs)
            except exception_types or Exception:
                if timeout is not None: time.sleep(timeout)
    return tryIt

Then…

@retry(5, MySQLdb.Error, timeout=0.5)
def the_db_func():
    # [...]

To install the decorator module:

$ easy_install decorator

Solution 3

UPDATE: there is a better maintained fork of the retrying library called tenacity, which supports more features and is in general more flexible.

The API changes slightly:

@retry(stop=stop_after_attempt(7))
def stop_after_7_attempts():
    print("Stopping after 7 attempts")

@retry(wait=wait_fixed(2))
def wait_2_s():
    print("Wait 2 second between retries")

@retry(wait=wait_exponential(multiplier=1, min=4, max=10))
def wait_exponential_1000():
    print("Wait 2^x * 1000 milliseconds between each retry,")
    print("up to 10 seconds, then 10 seconds afterwards")

Yes, there is the retrying library, which has a decorator that implements several kinds of retrying logic that you can combine:

Some examples:

@retry(stop_max_attempt_number=7)
def stop_after_7_attempts():
    print("Stopping after 7 attempts")

@retry(wait_fixed=2000)
def wait_2_s():
    print("Wait 2 second between retries")

@retry(wait_exponential_multiplier=1000, wait_exponential_max=10000)
def wait_exponential_1000():
    print("Wait 2^x * 1000 milliseconds between each retry,")
    print("up to 10 seconds, then 10 seconds afterwards")

Solution 4

conn = MySQLdb.connect(host, user, password, database)
cursor = conn.cursor()

for i in range(3):
    try:
        cursor.execute(query)
        rows = cursor.fetchall()
        for row in rows:
            # do something with the data
        break
    except MySQLdb.Error, e:
        print "MySQL Error %d: %s" % (e.args[0], e.args[1])

Solution 5

I’d refactor it like so:

def callee(cursor):
    cursor.execute(query)
    rows = cursor.fetchall()
    for row in rows:
        # do something with the data

def caller(attempt_count=3, wait_interval=20):
    """:param wait_interval: In seconds."""
    conn = MySQLdb.connect(host, user, password, database)
    cursor = conn.cursor()
    for attempt_number in range(attempt_count):
        try:
            callee(cursor)
        except MySQLdb.Error, e:
            logging.warn("MySQL Error %d: %s", e.args[0], e.args[1])
            time.sleep(wait_interval)
        else:
            break

Factoring out the callee function seems to break up the functionality so that it’s easy to see the business logic without getting bogged down in the retry code.

Solution 6

Like S.Lott, I like a flag to check if we’re done:

conn = MySQLdb.connect(host, user, password, database)
cursor = conn.cursor()

success = False
attempts = 0

while attempts < 3 and not success:
    try:
        cursor.execute(query)
        rows = cursor.fetchall()
        for row in rows:
            # do something with the data
        success = True 
    except MySQLdb.Error, e:
        print "MySQL Error %d: %s" % (e.args[0], e.args[1])
        attempts += 1

Solution 7

def successful_transaction(transaction):
    try:
        transaction()
        return True
    except SQL...:
        return False

succeeded = any(successful_transaction(transaction)
                for transaction in repeat(transaction, 3))

Solution 8

1.Definition:

def try_three_times(express):
    att = 0
    while att < 3:
        try: return express()
        except: att += 1
    else: return u"FAILED"

2.Usage:

try_three_times(lambda: do_some_function_or_express())

I use it for parse html context.

Solution 9

You can use a for loop with an else clause for maximum effect:

conn = MySQLdb.connect(host, user, password, database)
cursor = conn.cursor()

for n in range(3):
    try:
        cursor.execute(query)
    except MySQLdb.Error, e:
        print "MySQL Error %d: %s" % (e.args[0], e.args[1])
    else:
        rows = cursor.fetchall()
        for row in rows:
            # do something with the data
        break
else:
    # All attempts failed, raise a real error or whatever

The key is to break out of the loop as soon as the query succeeds. The else clause will only be triggered if the loop completes without a break.

Solution 10

This is my generic solution:

class TryTimes(object):
    ''' A context-managed coroutine that returns True until a number of tries have been reached. '''

    def __init__(self, times):
        ''' times: Number of retries before failing. '''
        self.times = times
        self.count = 0

    def __next__(self):
        ''' A generator expression that counts up to times. '''
        while self.count < self.times:
            self.count += 1
        yield False

    def __call__(self, *args, **kwargs):
        ''' This allows "o() calls for "o = TryTimes(3)". '''
        return self.__next__().next()

    def __enter__(self):
        ''' Context manager entry, bound to t in "with TryTimes(3) as t" '''
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        ''' Context manager exit. '''
        return False # don't suppress exception

This allows code like the following:

with TryTimes(3) as t:
    while t():
        print "Your code to try several times"

Also possible:

t = TryTimes(3)
while t():
    print "Your code to try several times"

This can be improved by handling exceptions in a more intuitive way, I hope. Open to suggestions.

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply