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

[nephish]

hey there,
i have a huge app that connects to MySQL. There are three threads that
are continually connecting and disconnecting to the db. The problem is,
if there is an error, it faults out sometimes without closing the
connection. i connect like this.
db = MySQLdb.connect (host="localhos t", user="MyUser",
passwd="MyPassw ord", db="Stuff")
cursor=db.curso r()

then i use the cursor.execute( "SELECT yadda yadda

my question is, is there a way i can set up a global connection so that
when the program loads, it connects once, then stays connected ? maybe
i could assign instances of the cursor ?

please someone let me know if you have any good ideas

sk

[*binarystar*]

that's definitely the way to go ..

-create a database_object
-initialise at start up
-then pass the database object to other classes as needed ...

If you want to get really fancy have a look at some ORM's ... I think
there is a Python one called SQLObject?

ne*****@xit.net wrote:

hey there,
i have a huge app that connects to MySQL. There are three threads that
are continually connecting and disconnecting to the db. The problem is,
if there is an error, it faults out sometimes without closing the
connection. i connect like this.
db = MySQLdb.connect (host="localhos t", user="MyUser",
passwd="MyPassw ord", db="Stuff")
cursor=db.curso r()

then i use the cursor.execute( "SELECT yadda yadda

my question is, is there a way i can set up a global connection so that
when the program loads, it connects once, then stays connected ? maybe
i could assign instances of the cursor ?

please someone let me know if you have any good ideas

sk

[nephish]

This is great !

ok, i dont really have a lot of time to get into the ORMS (before your
post, this is the first i have heard of it) and my stuff is due on
Monday. he he.

but, if i am able to make a global db connection, and multiple cursors
pointing to the same connection object, how do i pull that off without
making new db connections?

something like
class db(self):
def __init__(self):
db = MySQLdb.connect (host="localhos t", user="MyUser",
passwd="MyPassw ord",
db="Stuff")
def cursor(self):
cursor = db.cursor()
return cursor
then have in my threads that need to connect

cursor = db.cursor()
cursor2 = db.cursor()

and so on ? i may be way outta whack here. i am still new at classes,
methods, and modules.
i do have Essential Reference on the way from Amazon though ! :)

thanks again

[*binarystar*]

your on the right track ... create something like this ( hope the formatting doesn't go to hay wire )

class DB_Connector(ob ject):

""" Humble Database Connection Class """

def __init__(self, host="localhost ", user="MyUser",p asswd="MyPasswo rd", **other_db_argu ments):

self.host = host
self.user = user
self.passwd = passwd

# Unpack Other Database Arguments Here
self.CreateConn ection()

def CreateConnectio n( self ):

self.cursor = MySQLdb.connect (self.host, self.user, self.passwd)

def DestroyConnecti on( self ):

self.cursor.clo se()

def Execute( self, sql_statement ):

self.cursor.Exe cute( sql_statement )

return self.cursor.Fet chAll()

Then when you run your program create an instance of the object

db_connection = DB_Connector( 'localhost', 'administrator' , 'betelgeuse99', auto_commit=1, other_keyword_a rg="yes" )

now when you pass the db_connection instance to other classes, a copy will be made automagically

thread_1_instan ce = ThreadingClass( db_connection )
thread_2_instan ce = ThreadingClass( db_connection )
thread_3_instan ce = ThreadingClass( db_connection )

should work ..

I hope this is useful

ne*****@xit.net wrote:

This is great !

ok, i dont really have a lot of time to get into the ORMS (before your
post, this is the first i have heard of it) and my stuff is due on
Monday. he he.

but, if i am able to make a global db connection, and multiple cursors
pointing to the same connection object, how do i pull that off without
making new db connections?

something like
class db(self):
def __init__(self):
db = MySQLdb.connect (host="localhos t", user="MyUser",
passwd="MyPassw ord",
db="Stuff")
def cursor(self):
cursor = db.cursor()
return cursor
then have in my threads that need to connect

cursor = db.cursor()
cursor2 = db.cursor()

and so on ? i may be way outta whack here. i am still new at classes,
methods, and modules.
i do have Essential Reference on the way from Amazon though ! :)

thanks again

[*binarystar*]

Oops .. slight edit
now when you pass the db_connection instance to other classes, a reference will be passed automagically

[Winfried Tilanus]

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.Exe cute( sql_statement )
thread_2: self.cursor.Exe cute( sql_statement )
thread_1: return self.cursor.Fet chAll()
thread_2: return self.cursor.Fet chAll()

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(ob ject):

""" Humble Database Connection Class """
def __init__(self, host="localhost ",
user="MyUser",p asswd="MyPasswo rd", **other_db_argu ments):
self.host = host
self.user = user
self.passwd = passwd
# Unpack Other Database Arguments Here
self.CreateConn ection()
def CreateConnectio n( self ):
self.cursor = MySQLdb.connect (self.host, self.user,
self.passwd)
def DestroyConnecti on( self ):
self.cursor.clo se()
def Execute( self, sql_statement ):
self.cursor.Exe cute( sql_statement )
return self.cursor.Fet chAll()
Then when you run your program create an instance of the object

db_connection = DB_Connector( 'localhost', 'administrator' ,
'betelgeuse99', auto_commit=1, other_keyword_a rg="yes" )

now when you pass the db_connection instance to other classes, a copy
will be made automagically

thread_1_instan ce = ThreadingClass( db_connection )
thread_2_instan ce = ThreadingClass( db_connection )
thread_3_instan ce = ThreadingClass( db_connection )

should work ..
I hope this is useful

[*binarystar*]

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.Exe cute( sql_statement )
thread_2: self.cursor.Exe cute( sql_statement )
thread_1: return self.cursor.Fet chAll()
thread_2: return self.cursor.Fet chAll()

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(ob ject):

""" Humble Database Connection Class """
def __init__(self, host="localhost ",
user="MyUser",p asswd="MyPasswo rd", **other_db_argu ments):
self.host = host
self.user = user
self.passwd = passwd
# Unpack Other Database Arguments Here
self.CreateConn ection()
def CreateConnectio n( self ):
self.cursor = MySQLdb.connect (self.host, self.user,
self.passwd)
def DestroyConnecti on( self ):
self.cursor.clo se()
def Execute( self, sql_statement ):
self.cursor.Exe cute( sql_statement )
return self.cursor.Fet chAll()
Then when you run your program create an instance of the object

db_connection = DB_Connector( 'localhost', 'administrator' ,
'betelgeuse99', auto_commit=1, other_keyword_a rg="yes" )

now when you pass the db_connection instance to other classes, a copy
will be made automagically

thread_1_instan ce = ThreadingClass( db_connection )
thread_2_instan ce = ThreadingClass( db_connection )
thread_3_instan ce = ThreadingClass( db_connection )

should work ..
I hope this is useful

[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(ob ject):

""" Humble Database Connection Class """
def __init__(self, host="localhost ",
user="MyUser",
passwd="MyPassw ord",
**other_db_argu ments):
self.host = host
self.user = user
self.passwd = passwd
# Unpack Other Database Arguments Here
self.CreateConn ection()

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

def DestroyConnecti on(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.Exe cute( sql_statement )
thread_2: self.cursor.Exe cute( sql_statement )
thread_1: return self.cursor.Fet chAll()
thread_2: return self.cursor.Fet chAll()

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(ob ject):

""" Humble Database Connection Class """
def __init__(self, host="localhost ",
user="MyUser",p asswd="MyPasswo rd", **other_db_argu ments):
self.host = host
self.user = user
self.passwd = passwd
# Unpack Other Database Arguments Here
self.CreateConn ection()
def CreateConnectio n( self ):
self.cursor = MySQLdb.connect (self.host, self.user,
self.passwd)
def DestroyConnecti on( self ):
self.cursor.clo se()
def Execute( self, sql_statement ):
self.cursor.Exe cute( sql_statement )
return self.cursor.Fet chAll()
Then when you run your program create an instance of the object

db_connection = DB_Connector( 'localhost', 'administrator' ,
'betelgeuse99', auto_commit=1, other_keyword_a rg="yes" )

now when you pass the db_connection instance to other classes, a copy
will be made automagically

thread_1_instan ce = ThreadingClass( db_connection )
thread_2_instan ce = ThreadingClass( db_connection )
thread_3_instan ce = ThreadingClass( db_connection )

should work ..
I hope this is useful

[nephish]

So this opens and closes the connection every time i run the query?
thats cool. i think that would fit in well. so when i need to run the
query, i pass something like

query = "SELECT * FROM `Table` WHERE `foo` = 'bar'"

result = DB_Connector.Ex ecute(query)

and the result would be the same as if i ran the query right there in
the thread?

thanks for all your help gents, this is helping me a lot.

[nephish]

way cool, i think that this will work.

thanks very much

-sk