fetch MySQL slow-log from table

slow_log.py

#!/usr/bin/env python

import collections
import getpass
import MySQLdb
import optparse
import os
import sys

parser = optparse.OptionParser("usage %prog [OPTIONS]", add_help_option=False)
parser.add_option('-P', '--port', type='int', default=3306, dest='port', help='The TCP/IP port number to use for the connection.')
parser.add_option('-h', '--host', type='str', default='localhost', dest='host', help='Connect to the MySQL server on the given host.')
parser.add_option('-u', '--user', type='str', default=os.getenv('USER'), dest='user', help='The MySQL user name to use when connecting to the server.')
parser.add_option('-p', '--password', type='str', dest='passwd', help='The password to use when connecting to the server.')
(options, args) = parser.parse_args(sys.argv)

if not options.passwd:
    options.passwd = getpass.getpass('Password: ')

attrs = ["start_time", "user_host", "query_time", "lock_time", "rows_sent", "rows_examined", "db", "last_insert_id", "insert_id", "server_id", "sql_text"]
slow_log = collections.namedtuple('slow_log', attrs)

db = MySQLdb.connect(user=options.user, passwd=options.passwd, host=options.host, port=options.port)
cur = db.cursor()
cur.execute("SELECT %s FROM mysql.slow_log;" % (", ".join(attrs)))

for log in [ slow_log(*r) for r in cur.fetchall() ]:
    print("""# Time: %s
# User@Host: %s
# Query_time: %s Lock_time: %s Rows_sent: %s Rows_examined: %s
%s
    """ % (log.start_time, log.user_host, log.query_time.seconds, log.lock_time.seconds, log.rows_sent, log.rows_examined, log.sql_text))

# vim:set ft=python :