can i set up a mysql db connection as a class ?

[Winfried Tilanus]

On 04/28/2006 08:35 AM, *binarystar* wrote:

Looking better at the  """ Humble Database Connection Class """: if I am
not mistaken, it seems to mix up connections and cursors.

MySQLdb has a thread safety level of '1', meaning: "Threads may share
the module, but not connections". So you have to give each thread an own
connection. Beside of that I would prefer not to share the cursor
object, although it should not really matter.

The following should work and make the cursors private to the Execute
function (untested):

class DB_Connector(object):

    """ Humble Database Connection Class """
    def __init__(self, host="localhost",
                 user="MyUser",
                 passwd="MyPassword",
                 **other_db_arguments):
        self.host   =   host
        self.user   =   user
        self.passwd =   passwd
        # Unpack Other Database Arguments Here
        self.CreateConnection()

    def CreateConnection(self):
        self.connection = MySQLdb.connect(self.host,
                                          self.user,
                                          self.passwd)

    def DestroyConnection(self):
        self.connection.close()

    def Execute(self, sql_statement):
        cursor = self.connection.cursor()
        cursor.execute(sql_statement)
        result = cursor.fetchall()
        cursor.close()
        return result


> I suppose that is possible because you are calling the one instance of a
> cursor object ... maybe you have to create a copy of the cursor object,
> rather than passing a reference to the one object? or set up the
> db_connection objects inside each of the threads? ..
> 
> Winfried Tilanus wrote:
>> On 04/28/2006 07:54 AM, *binarystar* wrote:
>>
>> Just wondering: is there any risk of two threads accessing the Execute
>> function at the same time and getting something like this on the same
>> cursor object:
>>
>> thread_1: self.cursor.Execute( sql_statement )
>> thread_2: self.cursor.Execute( sql_statement )
>> thread_1: return self.cursor.FetchAll()
>> thread_2: return self.cursor.FetchAll()
>>
>> In that case the queries would seriously be messed up. My intuition says
>> this would need some locking or a 'cursor-pool'.
>>
>> best wishes,
>>
>> Winfried
>>
>>
>>> your on the right track ... create something like this ( hope the
>>> formatting doesn't go to hay wire )
>>>
>>> class DB_Connector(object):
>>>
>>>     """ Humble Database Connection Class """
>>>         def __init__(self, host="localhost",
>>> user="MyUser",passwd="MyPassword", **other_db_arguments):
>>>                       self.host   =   host
>>>            self.user   =   user
>>>            self.passwd =   passwd
>>>                       # Unpack Other Database Arguments Here
>>>                                  self.CreateConnection()
>>>                def CreateConnection( self ):
>>>               self.cursor = MySQLdb.connect(self.host, self.user,
>>> self.passwd)
>>>            def DestroyConnection( self ):
>>>               self.cursor.close()
>>>            def Execute( self, sql_statement ):
>>>               self.cursor.Execute( sql_statement )
>>>               return self.cursor.FetchAll()
>>>        Then when you run your program create an instance of the object
>>>
>>> db_connection     = DB_Connector( 'localhost', 'administrator',
>>> 'betelgeuse99', auto_commit=1, other_keyword_arg="yes" )
>>>
>>> now when you pass the db_connection instance to other classes, a copy
>>> will be made automagically
>>>
>>> thread_1_instance    = ThreadingClass( db_connection )
>>> thread_2_instance    = ThreadingClass( db_connection )
>>> thread_3_instance    = ThreadingClass( db_connection )
>>>
>>> should work ..
>>> I hope this is useful