Need advices for mysqldb connection best practice

[Romaric DEFAUX]

Hi all,


I've a problem with a mysql connection. I run a client/server 
application. Every hour, clients contact the server, send datas, and the 
server updates the database. Everything works perfectly.
But after a while, I get in trouble with my db connection. I've got the 
impression my server application "mix" the cursors...
I've got this kind of strange errors in my log :
Traceback (most recent call last):
   File "/usr/local/lib/audaxis/system_serverbox.py", line 519, in 
__update_in_db
     old_serverbox.load_from_db(cursor, self.__id)
   File "/usr/local/lib/audaxis/system_serverbox.py", line 131, in 
load_from_db
     self.__uuid = row['uuid']
TypeError: 'NoneType' object is unsubscriptable

Traceback (most recent call last):
   File "/usr/local/lib/audaxis/system_server.py", line 168, in process_data
     result += my_serverbox.save_website_list(cursor)
   File "/usr/local/lib/audaxis/system_serverbox.py", line 1197, in 
save_website_list
     old_serverbox.load_from_db(cursor, self.__id)
   File "/usr/local/lib/audaxis/system_serverbox.py", line 143, in 
load_from_db
     self.__load_disk_list_from_db(cursor)
   File "/usr/local/lib/audaxis/system_serverbox.py", line 203, in 
__load_disk_list_from_db
     my_disk.load_from_db(cursor, disk_id)
   File "/usr/local/lib/audaxis/system_disk.py", line 54, in load_from_db
     self.__fs = row['fs']
KeyError: 'fs'

Traceback (most recent call last):
   File "/usr/local/lib/audaxis/system_serverbox.py", line 521, in 
__update_in_db
     __uuid_host = self.is_virtual(cursor)
   File "/usr/local/lib/audaxis/system_serverbox.py", line 339, in 
is_virtual
     result = row['uuid']
KeyError: 'uuid'

and a lot of KeyError (at every update)

The requests that produce these errors were working perfectly. And if I 
restart my server, everything is working fine again !

Here's part of my code (I removed some parts):
I create a db connection like this in my object server:
def connect():
     con = MySQLdb.connect (host = "localhost", user = "myuser", passwd 
= "good_password", db = "my_db", cursorclass=DictCursor)
     con.ping(True) <- this normally activate auto-reconnection (or I 
did a mistake ?)
     con.cursor().execute('SET AUTOCOMMIT=1')
     return con

self.__db_connection = connect()

Then, each time a client connects :
cursor = self.__db_connection.cursor()
...process the datas...
cursor.close()

So , I thought about some solutions :
- restarting the server every sometimes (but it's the worst solution in 
my mind)
- creating a connection (not only cursor) at each client connection (but 
I'm afraid it overloads the mysql server)
- trying to find where I did a mistake, and correct the bug (that why 
I'm doing by writing this list :), or send me a link that could help me 
(before writing I googled for one hour and found nothing interresting in 
my case...)

Thanks in advance for your advices !

Romaric