python mysql custom re-connect class does not hold server connection open

Question

I have a custom class written in python to re-establish a mysql connection, if the current connection is dead. The only problem is, the current one always seems to be dead and is always entering into the except block of my try-except.

class DB:
    conn = None
    def connect( self ):
            try:
               self.conn = MySQLdb.connect("localhost", "xxxx", "xxxxxx", "Systems")
            except ( AttributeError, Mysqldb.OperationalError ), e:
               raise e

    def query( self, sql, params ):
            try:
               cursor = self.conn.cursor()
               cursor.execute( sql, ( params ) )
            except ( AttributeError, MySQLdb.OperationalError ), e:
               print 'exception generated during sql connection: ', e
               self.connect()
               cursor = self.conn.cursor()
               cursor.execute( sql, ( params ) )
            cursor.close()
            return cursor

    def close( self ):
            try:
               if self.conn:
                  self.conn.close()
                  print '...Closed Database Connection: ' + self.conn
               else:
                  print '...No Database Connection to Close.'
            except ( AttributeError, MySQLdb.OperationalError ), e:
               raise e

db = DB()
db.query( sql, myParams )

The problem is two-fold. 1) When I call db.close() I get ...No Database Connection output. 2) Each time I execut db.query( sql, myParams ) it enters the except clause and outputs exception generated during sql connection:

It seems like my db connection is closing itself after each query call. But I don't know why this would be happening. Any help on this issue would be greatly appreciated.

unutbu · Accepted Answer · 2011-12-31 15:39:43Z

2

The first time you make a call to db.query, self.conn is equal to None. So indeed, you will always get a exception generated during sql connection message the first time you call db.query. However, subsequent calls to db.query will not produce that message.

If you add
```
def __init__(self):
    self.connect()
```
then self.conn will be initialzed when you create db = DB(). Then you will not get the error message. Or, if you only want to create a connect on-demand, then just take out the error message.
You can remove conn = None from the DB class. The code will still work, in basically the same way, by raising an AttributeError when self.conn is not set.
Note also that, if your sql is a SELECT statement, you should not call cursor.close() in DB.query, since a subsequent call to cursor.fetchall() will no longer work.

Here is some example code to show that, with the changes mentioned above, the exception generated during sql connection message is only printed once, and the db.close() prints ...Closed Database Connection: as it should.

If your code is behaving otherwise, please modify the code below to demonstrate the behavior.

import MySQLdb
import config

class DB:
    def connect(self):
        try:
            self.conn = MySQLdb.connect("localhost",
                                        config.USER, config.PASS, config.MYDB)
        except (AttributeError, MySQLdb.OperationalError), e:
            raise e

    def query(self, sql, params = ()):
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql, params)
        except (AttributeError, MySQLdb.OperationalError) as e:
            print 'exception generated during sql connection: ', e
            self.connect()
            cursor = self.conn.cursor()
            cursor.execute(sql, params)
        return cursor

    def close(self):
        try:
            if self.conn:
                self.conn.close()
                print '...Closed Database Connection: ' + str(self.conn)
            else:
                print '...No Database Connection to Close.'
        except (AttributeError, MySQLdb.OperationalError) as e:
            raise e

db = DB()
sql = '''DROP TABLE IF EXISTS foo'''
db.query(sql)

sql = '''CREATE TABLE foo (bar INT(11))'''
db.query(sql)

db.close()

# exception generated during sql connection:  DB instance has no attribute 'conn'
# ...Closed Database Connection: <_mysql.connection closed at 8769f8c>

config.py:

USER = 'myusername'
PASS = 'mypasswd'
HOST = 'localhost'
MYDB = 'dbname'

edited Dec 31, 2011 at 15:39

answered Dec 28, 2011 at 18:46

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

8 Comments

sadmicrowave Over a year ago

I agree that my first call to db.query() will generate the exception but my issue is that each and every subsequent call to db.query is generating the error also.

unutbu Over a year ago

I've added some code to demonstrate what I mean. I'm not seeing an exception with every call to db.query, just on the first call (as expected). Please modify the code to demonstrate the error.

sadmicrowave Over a year ago

sorry for the ignorance but could you explain (or provide doc reference) for the config method you are using to pass username, password, and database to the connect command?

unutbu Over a year ago

No problem; I use config.py to define private variables. It's just a file with a few variables defined (see above). You can import it into other python scripts and access the variables with config.whatever.

sadmicrowave Over a year ago

Also, I'm not seeing where you have done anything substantially different from my code. I see you removed the conn = None and the cursor.close() and I understand the rationale behind both. But I don't see how your recommendation will change how my code executes in order to make it work. You started talking about adding an __init__ method inside my class but then did not include it in the example you posted...

|

Collectives™ on Stack Overflow

python mysql custom re-connect class does not hold server connection open

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related