#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    3
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    3
    Rep Power
    0

    Is this possible?


    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.
  4. #3
  5. Mini me.
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2003
    Location
    Cambridge, UK
    Posts
    783
    Rep Power
    14
    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:

    Code:
    conn.query("OPTIMIZE TABLE "+mystr+";")
    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
  6. #4
  7. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    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.

    Code:
    try:
        conn.query("OPTIMIZE TABLE %s;" % mystr)
    except TheErrorsName:
        pass
    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.
    programming language development: www.netytan.com Hula

  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    3
    Rep Power
    0

    Cool I think I solved it


    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 .

    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.store_result() # Prevents optimizing errors
    	conn.query("OPTIMIZE TABLE %s;" % mystr)
  10. #6
  11. Mini me.
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2003
    Location
    Cambridge, UK
    Posts
    783
    Rep Power
    14
    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
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2004
    Posts
    3
    Rep Power
    0
    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.

IMN logo majestic logo threadwatch logo seochat tools logo