Closing cursor and connection with Python and MySQLdb

Question

I have a simple web.py-based app that uses MySQLdb. I have a class that handles database operations like so:

class db():
   def __init__(self):
       db = MySQLdb.connect(host='mysql.server', user='user', passwd='pass', db='app')
       self.cur = db.cursor()

   def get_data(self):
       sql = "SELECT * FROM foobar"
       self.cur.execute(sql)
       rs = self.cur
       r.fetchall()
       return rs

I instantiate the class like so DB = db(). Then, in another class, I will refer to it.

class bleh()
   def blarg():
      DB.get_data()

With something like this, where would I close the cursor and connection? Or am I approaching this completely wrong?

Solution 1

db.close() for connection and cur.close() for cursor.

http://mysql-python.sourceforge.net/MySQLdb.html

EDIT:

But if it give it a bit thought - you won't need to close cursor. Python closes the cursor once the variable is destroyed, so when the instance of your class does not exist anymore -- cursor will be closed.

Solution 2

First of all use different names for class-name and variable as you have used same name ('db') for class-name and connection as well.

Next, you need to define conn (in your question db line no 3) as self.conn.

import MySQLdb

class db():

def __init__(self):
    self.conn = MySQLdb.connect(host='mysql.server', user='user', passwd='pass', db='app')
    self.cur = self.conn.cursor()

def get_data(self):
    sql = "SELECT * FROM test"
    self.cur.execute(sql)
    rs = self.cur
    rs.fetchall()
    return rs

class bleh()
    def blarg():
        data = DB.get_data()
        DB.cur.close()
        DB.conn.close()

Note: If you have multiple functions in class bleh to get data from database make sure that you close cursor and connection in function, which is to called in last. Or you may have a seperate function, which closes cursor and connection.

Comments

[RHPT]

But where would I put that in the class?

[Martijn Pieters]

@RHPT: When do you no longer need the database connection or the cursor?

[Ruslan Osipov]

@RHPT: Expanded answer a bit.

[RHPT]

I added more explanation to the question. Hoepfully, it will clear it up better.

[Ruslan Osipov]

@RHPT: Destroy DB if you need new connection, use same cursor for the same connection.

[RHPT]

Where in the code should I destory db? Outside the class? Inside the method? In my example, DB is a global var.

[Ruslan Osipov]

@RHPT You should destroy var when you do not need a connection anymore.