Is opening a new MySQL cursor for each query slow?

Question

I've seen some answers around here that open a new MySQL cursor before each query, then close it.

Is that slow? Shouldn't I be recycling a cursor, by passing it in as a parameter?

I have a program that does an infinite loop, so eventually the connection will time out after the default 8 hours.

Edit:

As requested, this is the relevant code that handles the SQL query:

def fetch_data(query):
    try:
        cursor = db.Cursor()
        cursor.execute(query)
        return cursor.fetchall()
    except OperationalError as e:
        db = fetchDb()
        db.autocommit(True)
        print 'reconnecting and trying again...'
        return fetch_data(query)

You should identify the current functionality as a bottleneck with profiling. There's also this question which seems similar to your own: http://stackoverflow.com/questions/8099902/should-i-reuse-the-cursor-in-the-python-mysqldb-module — Selali Adobor, Jul 24 '14 at 01:54
@hd1 Who says the basic functionality is not done? It is done, I'm just fixing a bug that occurs after the first 10k-20k iterations, and it relies on this question. — User, Jul 24 '14 at 01:59
Because I have no idea what SQLAlchemy is and this code I can understand I think can do the job, no? — User, Jul 24 '14 at 02:56

score 1 · Accepted Answer · answered Jul 24 '14 at 05:59

Of course, re-connecting a connection for thousands of times will take much more time. You'd better set it as a property of your class, like this:

class yourClass():
    self.db = ...
    self.cursor = self.con.Cursor()

    # do something
    def fetch_data(self, query):
        try:
            if self.cursor:
                self.cursor.execute(query)
            else:
                raise OperationalError
            return self.cursor.fetchall()
        except OperationalError as e:
            self.db = fetchDb()
            self.db.autocommit(True)
            print 'reconnecting and trying again...'
            return fetch_data(query)

Is opening a new MySQL cursor for each query slow?

1 Answers1