| 1 |
#!/usr/bin/env python
|
| 2 |
|
| 3 |
import sys, os, getopt, MySQLdb, time
|
| 4 |
|
| 5 |
def main(argv) :
|
| 6 |
"""
|
| 7 |
|
| 8 |
cleanMySQL
|
| 9 |
|
| 10 |
clean MySQL DB of Boss for entries for a specific day which are still not retrieved and not running
|
| 11 |
|
| 12 |
required parameters
|
| 13 |
--year <year> : year in format YYYY
|
| 14 |
--month <day> : month in format MM
|
| 15 |
--day <day> : day in format DD
|
| 16 |
|
| 17 |
optional parameters :
|
| 18 |
--help (-h) : help
|
| 19 |
--debug (-d) : debug statements
|
| 20 |
|
| 21 |
"""
|
| 22 |
|
| 23 |
# defaults
|
| 24 |
year = 0
|
| 25 |
month = 0
|
| 26 |
day = 0
|
| 27 |
debug = 0
|
| 28 |
|
| 29 |
try:
|
| 30 |
opts, args = getopt.getopt(argv, "", ["help", "debug", "year=", "month=", "day="])
|
| 31 |
except getopt.GetoptError:
|
| 32 |
print main.__doc__
|
| 33 |
sys.exit(2)
|
| 34 |
|
| 35 |
# check command line parameter
|
| 36 |
for opt, arg in opts :
|
| 37 |
if opt == "--help" :
|
| 38 |
print main.__doc__
|
| 39 |
sys.exit()
|
| 40 |
elif opt == "--debug" :
|
| 41 |
debug = 1
|
| 42 |
elif opt == "--year" :
|
| 43 |
try: year = int(arg)
|
| 44 |
except:
|
| 45 |
print main.__doc__
|
| 46 |
sys.exit()
|
| 47 |
elif opt == "--month" :
|
| 48 |
try: month = int(arg)
|
| 49 |
except:
|
| 50 |
print main.__doc__
|
| 51 |
sys.exit()
|
| 52 |
elif opt == "--day" :
|
| 53 |
try: day = int(arg)
|
| 54 |
except:
|
| 55 |
print main.__doc__
|
| 56 |
sys.exit()
|
| 57 |
|
| 58 |
if day == 0 or month == 0 or year == 0:
|
| 59 |
print main.__doc__
|
| 60 |
sys.exit()
|
| 61 |
|
| 62 |
if debug :
|
| 63 |
print 'year:',year,'month:',month,'day:',day
|
| 64 |
|
| 65 |
# connect db
|
| 66 |
db = MySQLdb.connect(host="localhost", user="boss", passwd="boss",db="jobrobot")
|
| 67 |
# create a cursor
|
| 68 |
cursor = db.cursor()
|
| 69 |
|
| 70 |
# TASK_ID,CHAIN_ID list
|
| 71 |
ids = []
|
| 72 |
|
| 73 |
# query for TASK_ID of day
|
| 74 |
query = 'SELECT ID FROM TASK WHERE DECL_TIME>=' + str(time.mktime([year,month,day,0,0,0,0,0,1])) + \
|
| 75 |
' AND DECL_TIME<=' + str(time.mktime([year,month,day,0,0,0,0,0,1]) + 86400)
|
| 76 |
if debug :
|
| 77 |
print 'Query:',query
|
| 78 |
cursor.execute(query)
|
| 79 |
tasks = cursor.fetchall()
|
| 80 |
|
| 81 |
if debug :
|
| 82 |
print 'tasks:',tasks
|
| 83 |
|
| 84 |
for task in tasks:
|
| 85 |
# query for jobs declared but not submitted
|
| 86 |
query = 'SELECT TASK_ID,CHAIN_ID FROM JOB WHERE SUB_T=\'\' AND TASK_ID=' + str(task[0]) + ' ORDER BY TASK_ID,CHAIN_ID'
|
| 87 |
if debug :
|
| 88 |
print 'Query:',query
|
| 89 |
cursor.execute(query)
|
| 90 |
ids += cursor.fetchall()
|
| 91 |
|
| 92 |
if debug :
|
| 93 |
print 'ids:',ids
|
| 94 |
|
| 95 |
# query for list of tables
|
| 96 |
cursor.execute("show tables")
|
| 97 |
tables = cursor.fetchall()
|
| 98 |
|
| 99 |
if debug:
|
| 100 |
print 'tables:',tables
|
| 101 |
|
| 102 |
# query each table and print content
|
| 103 |
for id in ids :
|
| 104 |
for table in tables :
|
| 105 |
tablename = str(table[0])
|
| 106 |
if tablename.find('ENDED') == -1 :
|
| 107 |
try:
|
| 108 |
if tablename.find('CHAIN') != -1 :
|
| 109 |
query = 'DELETE FROM ' + tablename + ' WHERE TASK_ID=' + str(id[0]) + ' AND ID=' + str(id[1])
|
| 110 |
else :
|
| 111 |
query = 'DELETE FROM ' + tablename + ' WHERE TASK_ID=' + str(id[0]) + ' AND CHAIN_ID=' + str(id[1])
|
| 112 |
if debug :
|
| 113 |
print 'Query:',query
|
| 114 |
if cursor.execute(query) > 0:
|
| 115 |
print 'Delete entry for TASK_ID:',id[0],'CHAIN_ID:',id[1],'in table:',tablename
|
| 116 |
except:
|
| 117 |
pass
|
| 118 |
|
| 119 |
|
| 120 |
|
| 121 |
|
| 122 |
|
| 123 |
|
| 124 |
if __name__ == '__main__' :
|
| 125 |
main(sys.argv[1:])
|
