Getting Update Times On The DB Server - MySQL4

MySQL    Python    November Post-A-Day    2009-11-06

Here's the MySQL4 version of the script I posted yesterday. On the MySQL4 server, we don't have that handy INFORMATION_SCHEMA db for reference - instead, we have to get table information using 

SHOW TABLE STATUS
. The biggest difference between the two scripts is that this one requires a lot of extra calls to the server - at the beginning, to generate a list of dbs, then individual 
SHOW TABLE STATUS
requests as I loop over those database names.

#!/usr/bin/python

# usage: ./bluesky_provams4.py

import os, os.path, sys, MySQLdb as Database, smtplib

emailto = ['barbara.shaurette@gmail.com']
allupdatetimes = []

def list_dbs():
    """generate a list of dbs"""
    cursor = db.cursor()
    sql = """SHOW DATABASES"""
    cursor.execute(sql)
    dbnames = cursor.fetchall()
    cursor.close()
    return dbnames

def report_update_times(dbname):
    """get the update_time value for a specific table in each db"""
    update_times = ''
    cursor = db.cursor()
    sql = """SHOW TABLE STATUS IN %s LIKE 'user_accounts'""" %(dbname)
    cursor.execute(sql)
    rows_status = cursor.fetchone()
    if rows_status:
        # rows_status[11] corresponds to update_time in the list of fields returned by 'SHOW TABLE STATUS'
        # for InnoDB, this will come back as NULL
        update_times = update_times + str(dbname) + ', ' + str(rows_status[11]) + '\n'
    cursor_two.close()
    return update_times

def send_mail(text):
    fromaddr = "Barbara Shaurette "
    toaddrs  = emailto
    msg = ("From: %s\r\nTo: %s\r\n\r\n" % (fromaddr, ", ".join(toaddrs)))
    for item in text:        line = item
        msg = msg + line

    server = smtplib.SMTP('localhost')
    server.sendmail(fromaddr, toaddrs, msg)
    server.quit()

db = Database.connect(host="host", port="port", user="username", passwd="password", db="")
dblist = list_dbs()
# we have a few dbs on the 4 server that we don't particularly care about
excludelist = ['someolddb', 'anotherolddb', 'onemoreolddb']
for dbname in dblist:
    if dbname[0] not in excludelist:
        updates = report_update_times(dbname[0])
        allupdatetimes.append(updates)
db.close()
send_mail(allupdatetimes)


That returns another list like this:


db1, 2009-07-11 00:17:19
db2, 2007-02-12 11:46:20
db3, 2007-05-25 13:38:31
db4, 2007-10-01 13:08:53
db5, 2009-10-07 15:17:43
db6, 2009-10-06 09:48:22
db7, 2007-02-12 11:48:52