CODEWITHSUNDEEP
pythonsqloracle-database
Dan
Dan

Reputation: 46

Not closing a cursor can cause memory leaks?

We are getting some memory leak problems. I think it should be because of the cursor, which is never closed. I'm sharing the parts where i think it is the source of the problem.

def execute_sql(sql):
    logs.custom_formatter(file_name="db", class_name="execute_sql")
    try:
        mycursor = cnx.cursor()
        print("sql:",sql)
        mycursor.execute(sql)
        result = mycursor.fetchall()
        return result
    except Exception as e:
        print(e)
        logs.info("Sql Select Error: {0}".format(e))
        return []

and this

def commit_sql(sql):
    logs.custom_formatter(file_name="db", class_name="commit_sql")
    try:
        mycursor = cnx.cursor()
        mycursor.execute(sql)
        cnx.commit()
        return True
    except Exception as e:
        logs.info("Sql Commit Error: {0}".format(e))
        return False

The functions above will be called for every user to check if they are already in database if so bring the infos of them otherwise insert them to the database. So you can assume that these functions are called frequently and after a while we observe that the respond time are increasing continuous so that we need to restart the code so it can be usable again.

For now i have limited access to the code itself, but in a few days i will be given access to fix it and as far as i read from other sources i think it's probably the point, if we close the cursor, then it will be fine and memory leaks will be fixed.

I'm asking you if this can be really a problem or i'm wrong and wasting my time struggling with the cursor thing.

Upvotes: 2

Views: 794

Answers (1)

Jimmy Fraiture
Jimmy Fraiture

Reputation: 452

You have 2 possibility :

First you close the cursor for each call

def execute_sql(sql):
    logs.custom_formatter(file_name="db", class_name="execute_sql")
    mycursor = None
    result = []
    try:
        mycursor = cnx.cursor()
        print("sql:",sql)
        mycursor.execute(sql)
        result = mycursor.fetchall()
    except Exception as e:
        print(e)
        logs.info("Sql Select Error: {0}".format(e))
    finally:
        try:
            mycursor.close()
        except:
            pass
    return result

The second possibility is just to get one global cursor that you use for all your requests

Upvotes: 2

Related Questions