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

Question

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.

Trifle

That's a good point. I'd probably put a sleep in for a few seconds. I don't know what's wrong with the MySQL installation on the server, but it does seem that it fails one second, and the next it works. – Trifle

@Yuval A: It is a common task. I suspect it is even builtin in Erlang. – Pointing

Just to mention that maybe nothing is wrong, Mysql has a wait_timeout variable to configure mysql to drop inactive connections. – Amiamiable

@MadPhysicist I pointed the duplicate closure the other way, because this version of the question seems clearly better to me by every metric I can think of. – Voyles

Answer 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])

Aldus

Or for attempt_number in range(3) – Leyla

Well, I kinda like mine because it makes it explicit that the attempts are only increased in the event of an exception. – Aldus

Yeah, I guess I'm more paranoid about infinite while loops creeping in than most people. – Leyla

-1: Don't like break. Like "while not done and attempts < 3:" better. – Gangrel

I like the break, but not the while. This more like C-ish than pythonic. for i in range is better imho. – Standstill

I like Kiv's use of while <condition> and not success:--with success = True at the end of the try clause--as an alternative to break as a minor improvement on this answer. – Gerfen

how do I try to re-connect? I mean what about the error was happen on the MySQLdb.connect ? (oh, sorry, I'm so stupid. I think I found the way ...) – Whenever

using the try/except/else and putting the break inside the else block may be cleaner in this case – Frankhouse

This is wrong and terrible. Won't throw any errors after 3rd attempt. – Enosis

Answer 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

Jocund

The decorator should probably take an exception class as well, so you don't have to use a bare except; i.e. @retry(5, MySQLdb.Error) – Leyla

Nifty! I never think to use decorators :P – Aldus

That should be "return func() in the try block, not just "func()". – Illtimed

Did you actually try running this? It doesn't work. The problem is that the func() call in the tryIt function gets executed as soon as you decorate the function, and not when you actually call the decorated function. You need another nested function. – Elihu

I get ImportError: No module named decorator in 2.6 and 3.0 – Singlehandedly

J.F.Sebastian has edited (as in completely rewritten) the above code snippet. Doesn't work here on 2.6.1 either. You might want to look in the edits, start with a previous version and then fix things as seen in comments. – Jocund

I'm sorry It doesn't occur to me that decorator is not in stdlib. pypi.python.org/pypi/decorator I will add version without it. – Pointing

I've added fixed original @dwc's version. – Pointing

timeout = kwargs.get('timeout', 0.0) # seconds /// I think it should be: /// timeout = kwargs.get('timeout', None) # seconds /// since later you do: /// if timeout is not None – Kho

Did that mean def the_db_function()? I can't execute it on python 2.7.1 even without using decorator... It gave me a SyntaxError... – Unbelievable

would it be possible to give an explanation of whats going on in this answer? I don't really understand why there are so many def functions or whats really going on and I don't want to just copy and paste :) – Breastsummer

Answer 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")

Jugurtha

The retrying library has been superseded by the tenacity library. – Twine

Answer 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])

Incensory

You could add an else at the bottom: else: raise TooManyRetriesCustomException – Closeknit

Answer 5

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

Nasho

Answer 6

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.

Leyla

-1: else and break... icky. Prefer a clearer "while not done and count != attempt_count" than break. – Gangrel

Really? I thought it made more sense this way -- if the exception doesn't occur, break out of the loop. I may be overly afraid of infinite while loops. – Leyla

+1: I hate flag variables when the language includes the code structures to do it for you. For bonus points, put an else on the for to deal with failing all the attempts. – Paddock

Answer 7

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.

Hydrograph

Answer 8

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

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

Adamsen

Answer 9

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.

Dyane

Answer 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.

Meroblastic