Python to optimize MySQL tables

Hey,

I was wondering if anyone would have a solution for me to a problem I am trying to solve with Python. I'm trying to write a python script that I can run that would automatically optimize any table in a specific MySQL database that has overhead. I have got as far as grabbing all the tables but run into an issue as soon as the script runs into a table that doesn't have overhead and I'm not sure how to have it ignore the error and carry on, here's my code so far: Thanks for any help that you can offer.

BEGIN CODE

import MySQLdb
import string

# Connection Settings
datahost="host"
username="user"
password="pass"
database="db"

# Connection syntax: host, username, password, database
conn = MySQLdb.connect(datahost, username, password, database)

mycursor = conn.cursor()

mycursor.execute("Show tables")
result = mycursor.fetchall()

for mytable in result:
mystr=repr(mytable)
mystr=string.replace(mystr,"(","")
mystr=string.replace(mystr,")","")
mystr=string.replace(mystr,",","")
mystr=string.replace(mystr,"'","")
conn.query("OPTIMIZE TABLE "+mystr+";")
#print "OPTIMIZE TABLE "+mystr+";"

END CODE

Is it safe to assume no one has tried this, or that people think it's impossible? Just seems to be taking a while to get even a response let alone a possible solution.

I haven't used MySQLdb but the code looks simple enough.

Looking at your code I guess the problem is a malformd mystr string, or are you suggesting that if a table is already optimized an error is raised? What do you mean by overhead (processing or unwanted characters or ...)?

A sample error report would help

BTW code tags display like this:


spelt as
[CODE ]
conn.query("OPTIMIZE TABLE "+mystr+";")
[/CODE ]
without the spaces in the CODE tags. It's easy enough to use the # button that is the top of the reply dialog.
grim

If a Python program raises and error then you can use a try-except block to ignore it, by using the pass keyword i.e.



You should check out the Python docs for more on exception handling. You can also find a nicly written article covering the subject on devshed:

http://www.devshed.com/c/a/Python/Python-101-part-8-An-Exceptionally-Clever-Snake/1/

Mark.

Thanks very much for your input guys.

Netytan thanks for the advice about exceptions, I tried them but it appears the issue could not simply be solved by them. The actual error I got was:
_mysql_expections.ProgrammingError: (2014, "Commands out of sync; You can't run this command now")

I tried defining the exception as ProgrammingError but it didn't like that -- I guess I need more practice with exception writing.

Grim Archon, thanks for the notice about code tags . By overhead I'm referring to the table overhead column that gets listed in phpmyadmin when you view a database. Essentially it's based on activity -- such as sessions stored in the database (like hard drive fragmentation).

The final code I'm using is below (obviously the login info is bogus..) My problem was described in http://www.devshed.com/c/a/MySQL/Troubleshooting-Problems-with-MySQL-Programs/5/ section A.2.13.

I had enough problems getting the exact function name. A tricky problem for sure after freeing the result everything worked . Of course, it would be more optimal if I could figure a way to ensure the table needs optimization before running it but hey you have to start somewhere..let me know if you got any ideas on ensuring optimization is needed on the table before running it. Thanks again for your help guys you helped me figure it out .

If I understand right, I think what you need the query :

SHOW TABLE STATUS

which will provide a list of tables and their status - specifically the "Data_free" field will correspond to the phpMyAdmin Overhead value.

I hope that helps

grim

result = mycursor.fetchall()
for mytable in result:
mystr=repr(mytable)
mystr=string.replace(mystr,"(","")
mystr=string.replace(mystr,")","")
mystr=string.replace(mystr,",","")
mystr=string.replace(mystr,"'","")
conn.store_result() # Prevents optimizing errors
conn.query("OPTIMIZE TABLE %s;" % mystr)

Your problem is that result is not a list, but is a tuple of tuples something like ((table1,),(table2,),(table3,)...) and therefore mytable is a single element tuple (table1,) etc.

You can simplify your code:

result = mycursor.fetchall()
for mytable in result:

conn.store_result()
conn.query("OPTIMIZE TABLE %s;" % mytable[0])

Hope that helps.