import MySQLdb
import subprocess
from smtplib import *
from datetime import date, timedelta
class StatEntry(object):
def __init__(self, name, count):
self.name = name
self.count = count
def __str__(self):
return "%s\t%d" % (self.name, self.count)
def fetchdb(date):
conn = MySQLdb.connect(host="localhost", user="root", passwd="acd", db="cls")
cursor = conn.cursor()
cursor.execute("SELECT link, count(*) FROM sdocom WHERE DATE(logtime) = DATE('%s') GROUP BY link" % date)
rows = cursor.fetchall()
result = map(lambda x: StatEntry(*x), rows)
return result
def archive(datelist):
for date in datelist:
subprocess.call(["gzip", "cls-"+date+".log"])
def sendmail(results):
mailcontent = reduce(lambda x,y: x+str(y)+"\n", results, "")
smtp = SMTP("your.smtp.server")
smtp.login("user","passwd")
smtp.sendmail("cls-no-reply@smtp.server", ["your@mail.server"], mailcontent)
smtp.quit()
def dumpall(data):
content = reduce(lambda x,y: x+str(y)+"\n", data, "")
print content
def main():
yesterday = str(date.today()-timedelta(1))
results = fetchdb(yesterday)
#dumpall(results)
sendmail(results)
archive([yesterday])
if __name__ == '__main__':
main()
As log file was imported to database automatically by other process, the script retrieves statistical data from mysql db and sends the result to your mail box via smtp. Also, log file would be compressed by gzip to minimize storage overhead. Add it to crontab, you will get daily statistical report.
This example shows you basic usage of mysql-python lib, smtplib and how to calculate yesterday by datetime.
