Handling MySQL connection

Josh JoshuaACohen at gmail.com
Mon Jan 31 15:10:41 EST 2005


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()

Finally in another Function I would like to be able to close the
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. 


More information about the Python-list mailing list