Handling MySQL connection

[Josh]

Hi,

First off, I am a newbie to Python and so far I must say that we're
getting along pretty well. My schooling was is C/C++, but have been
writing code in, dare I say it, VB, for the past 4 years.

Here is the problem I have run across. I am using SPE w/WXGlade to
setup the GUI, so it handled the initial formatting of my python code
as two seperate classes, one for the APP and one for the Frame. I'll be
the first to admit I haven't gotten a good grasp on how things should
flow within a python program. Anyway, what I have done is created a few
functions using the DEF statement to handle the action of the widgets.
Part of the program is accessing a MySQL database. Once a button is
pushed, part of it's function is to open the database, check to see if
a value exists, and then close the connection. Running this locally on
my PC, which is also where MySQL resides, returns results instantly.
The flow is
a) Open a connection to the database
b) Execute a simple Select statement to verify the field
c) Close the connection.
All done within a single DEF

This works fine and dandy on my Windows 2000 PC that is also acting as
the MySQL server, but when I transfer the program to a Linux box
running Fedora, there is a very noticable lag when this action takes
place. So my thought is rather than connecting to the database,
fetching the data, then closing out the connection every time, it may
be a better idea to connect to the database upon starting the program,
leaving the connection open, making the transactions as needed, and
then closing the connection upon leaving the program. Doing this,
however, has proved to be a little more difficult than I thought, and
I'm sure it's just because of my lack of knowledge of Python.

So I put the question out, how do I accomplish this?

Do I need some sort of Global(such an evil word) object to work off of?

In the Def __init__ I would like to open the connection
eg: connection = MySQLdb.connect(host="localhost", user="root",
passwd="blah", db="data")

In a Sepeate Function I would like to make the transactions on it.
eg: cursor = connection.cursor()
cursor.execute( "SELECT * FROM tags WHERE tags.tag_no = %s"
%temp_tag )
self.data = cursor.fetchall()
cursor.close()

Finally in another Function I would like to be able to close the
connection
eg: connection.close()

All of these Def's would fall under the same Frame class. Am I going
about this incorrectly?

Thanks for your help and patience.

Josh

 

[Dennis Lee Bieber]

Do I need some sort of Global(such an evil word) object to work off of?

In the Def __init__ I would like to open the connection
eg: connection = MySQLdb.connect(host="localhost", user="root",
passwd="blah", db="data")

In a Sepeate Function I would like to make the transactions on it.
eg: cursor = connection.cursor()
cursor.execute( "SELECT * FROM tags WHERE tags.tag_no = %s"
%temp_tag )
self.data = cursor.fetchall()
cursor.close()

Finally in another Function I would like to be able to close the
connection
eg: connection.close()

All of these Def's would fall under the same Frame class. Am I going
about this incorrectly?

If they are part of a class instance, you should be using
instance objects...


class AClass(object):
def __init__(self, ...):
self.connection = MySQLdb(...)
self.cursor = self.connection.cursor()
...
def Retrieve(self, temp_tag, ...):
self.cursor.execute(
"select * from tags where tags.tag_no = %s",
tmp_tag)
# NOTE: need to verify that MySQLdb uses %s, and not %?
# or other method.
# ADVICE: db-api compliant modules don't need the
# "string..." % arg
# format; recommend to supply as
# "string...", arg # or , (arg, arg, ...)
# and let the module do the proper quoting and escaping

self.data = self.cursor.fetchall()
...
# I notice you realized the need for self.data, but didn't
# use it for the database connection objects...

def CloseIt(self, ...):
self.cursor.close()
self.connection.close()
...

# somewhere

MyObject = AClass(...)

MyObject.Retrieve(aTag)

MyObject.CloseIt()

--

 

[Josh]

Thanks for the help! I was able to work through it based on you
example. It was lack of knowledge of the object class that was throwing
me off.

Josh