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?

Ruslan Osipov · Accepted Answer · 2012-11-28 16:28:48Z

3

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.

edited Nov 28, 2012 at 16:28

answered Nov 28, 2012 at 16:19

Ruslan Osipov

5,8734 gold badges31 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

RHPT Over a year ago

But where would I put that in the class?

Martijn Pieters Over a year ago

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

Ruslan Osipov Over a year ago

@RHPT: Expanded answer a bit.

RHPT Over a year ago

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

Ruslan Osipov Over a year ago

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

|

Navid · Accepted Answer · 2012-12-01 09:25:49Z

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.

Collectives™ on Stack Overflow

Closing cursor and connection with Python and MySQLdb

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related