使用Python制作一个每日归档的crontab job。
#!/usr/bin/env python #encoding: utf-8 from dmconfig import DmConf #from dmdb import Dmdb import redis import MySQLdb import dawnutils import time from datetime import datetime, timedelta, date conf = DmConf().loadConf() db = MySQLdb.connect(host=conf["DbHost"],user=conf['DbAccount'],passwd=conf['DbPassword'],\ db=conf['DbName'],charset=conf['DbCharset']) cache = redis.Redis(host=conf['RedisHost'], port=conf['RedisPort'], db=conf['Redisdbid'], password=conf['RedisPassword']) #cursor = db.cursor() def try_reconnect(conn): try: conn.ping() except: conn = MySQLdb.connect(host=conf["DbHost"],user=conf['DbAccount'],passwd=conf['DbPassword'],\ db=conf['DbName'],charset=conf['DbCharset']) def zip_task(device, start, stop): #cursor = db.cursor() format = "%Y%m%d%H%M%S" begin = time.strftime("%Y-%m-%d %H:%M:%S",time.strptime(start,format)) end = time.strftime("%Y-%m-%d %H:%M:%S",time.strptime(stop,format)) print "%s:%s,%s"%(device, begin, end) sql = "SELECT * from `period` WHERE `snrCode` = \"%s\" AND `time` > \"%s\" AND `time` < \"%s\" ORDER BY `recId` DESC"%(device, begin, end) print sql cursor = db.cursor() try_reconnect(db) t1 = time.time() try: cursor.execute(sql) results = cursor.fetchall() except MySQLdb.Error,e: print "Error %s"%(e) print ("SQL takes %f seconds"%(time.time()-t1)) print ("len of reconds, %d"%len(results)) #for row in results: #print row def dispatcher(devSet, start, stop): print "size of set: %d"%len(devSet) print devSet for dev in devSet: zip_task(dev, start, stop) def archive_task_queue(): today = datetime.now() oneday = timedelta(days=1) yesterday = today - oneday format = "%Y%m%d%H%M%S" begin = time.strftime(format, yesterday.timetuple())[:8] + '120000' end = time.strftime(format, today.timetuple())[:8] + '120000' sql = "SELECT * from `logbook` WHERE `login` > \"%s\" AND `login` < \"%s\" AND `logout` > \"%s\" AND `logout` < \"%s\""%(begin, end, begin, end) print sql cursor = db.cursor() reclist = [] try: cursor.execute(sql) results = cursor.fetchall() for row in results: #print row reclist.append(row[1]) except MySQLdb.Error,e: print "Error %s"%(e) if len(reclist): dispatcher(set(reclist), begin, end) db.close() if __name__ == '__main__': archive_task_queue()
运行结果比较古怪。
SELECT * from `logbook` WHERE `login` > "20160720120000" AND `login` < "20160721120000" AND `logout` > "20160720120000" AND `logout` < "20160721120000" size of set: 4 set([u'B1H700001', u'B1H700002', u'A1E500018', u'A2H300001']) B1H700001:2016-07-20 12:00:00,2016-07-21 12:00:00 SELECT * from `period` WHERE `snrCode` = "B1H700001" AND `time` > "2016-07-20 12:00:00" AND `time` < "2016-07-21 12:00:00" ORDER BY `recId` DESC SQL takes 0.018648 seconds len of reconds, 597 B1H700002:2016-07-20 12:00:00,2016-07-21 12:00:00 SELECT * from `period` WHERE `snrCode` = "B1H700002" AND `time` > "2016-07-20 12:00:00" AND `time` < "2016-07-21 12:00:00" ORDER BY `recId` DESC SQL takes 0.566339 seconds len of reconds, 4642 A1E500018:2016-07-20 12:00:00,2016-07-21 12:00:00 SELECT * from `period` WHERE `snrCode` = "A1E500018" AND `time` > "2016-07-20 12:00:00" AND `time` < "2016-07-21 12:00:00" ORDER BY `recId` DESC SQL takes 0.236042 seconds len of reconds, 0 A2H300001:2016-07-20 12:00:00,2016-07-21 12:00:00 SELECT * from `period` WHERE `snrCode` = "A2H300001" AND `time` > "2016-07-20 12:00:00" AND `time` < "2016-07-21 12:00:00" ORDER BY `recId` DESC SQL takes 65.640211 seconds len of reconds, 5772
B1H70002的查询4642组数据,A2H300001为5772组数据,为何SQL耗时分别为0.56和65秒?差100倍?
一次查询结果并不能完全断定该语句的问题,你可以回顾下是否在改次查询中有其他的资源使用数据库或者网路资源。
ps:可以将sql改为参数传递,方便sql执行时候优化器的处理,加速执行效率。