CODEWITHSUNDEEP
pythonmysql
User
User

Reputation: 24731

Is opening a new MySQL cursor for each query slow?

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)

Upvotes: 0

Views: 1022

Answers (1)

Stephen Lin
Stephen Lin

Reputation: 4912

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)

Upvotes: 1

Related Questions