COMP/JOBROBOT/cleanMySQL.py

#!/usr/bin/env python

import sys, os, getopt, MySQLdb, time

def main(argv) :
    """

    cleanMySQL

    clean MySQL DB of Boss for entries for a specific day which are still not retrieved and not running

    required parameters
    --year <year>        : year in format YYYY
    --month <day>        : month in format MM
    --day <day>          : day in format DD

    optional parameters  :
    --help (-h)          : help
    --debug (-d)         : debug statements

    """

    # defaults
    year = 0
    month = 0
    day = 0
    debug = 0

    try:
        opts, args = getopt.getopt(argv, "", ["help", "debug", "year=", "month=", "day="])
    except getopt.GetoptError:
        print main.__doc__
        sys.exit(2)

    # check command line parameter
    for opt, arg in opts :
        if opt == "--help" :
            print main.__doc__
            sys.exit()
        elif opt == "--debug" :
            debug = 1
        elif opt == "--year" :
            try: year = int(arg)
            except:
                print main.__doc__
                sys.exit()
        elif opt == "--month" :
            try: month = int(arg)
            except:
                print main.__doc__
                sys.exit()
        elif opt == "--day" :
            try: day = int(arg)
            except:
                print main.__doc__
                sys.exit()

    if day == 0 or month == 0 or year == 0:
        print main.__doc__
        sys.exit()        

    if debug :
        print 'year:',year,'month:',month,'day:',day

    # connect db
    db = MySQLdb.connect(host="localhost", user="boss", passwd="boss",db="jobrobot")
    # create a cursor
    cursor = db.cursor()

    # TASK_ID,CHAIN_ID list
    ids = []

    # query for TASK_ID of day
    query = 'SELECT ID FROM TASK WHERE DECL_TIME>=' + str(time.mktime([year,month,day,0,0,0,0,0,1])) + \
            ' AND DECL_TIME<=' + str(time.mktime([year,month,day,0,0,0,0,0,1]) + 86400)
    if debug :
        print 'Query:',query
    cursor.execute(query)
    tasks = cursor.fetchall()

    if debug :
        print 'tasks:',tasks

    for task in tasks:
        # query for jobs declared but not submitted
        query = 'SELECT TASK_ID,CHAIN_ID FROM JOB WHERE SUB_T=\'\' AND TASK_ID=' + str(task[0]) + ' ORDER BY TASK_ID,CHAIN_ID'
        if debug :
            print 'Query:',query
        cursor.execute(query)
        ids += cursor.fetchall()

    if debug :
        print 'ids:',ids

    # query for list of tables
    cursor.execute("show tables")
    tables = cursor.fetchall()

    if debug:
        print 'tables:',tables

    # query each table and print content
    for id in ids :
        for table in tables :
            tablename = str(table[0])
            if tablename.find('ENDED') == -1 :
                try:
                    if tablename.find('CHAIN') != -1 :
                        query = 'DELETE FROM ' + tablename + ' WHERE TASK_ID=' + str(id[0]) + ' AND ID=' + str(id[1])
                    else :
                        query = 'DELETE FROM ' + tablename + ' WHERE TASK_ID=' + str(id[0]) + ' AND CHAIN_ID=' + str(id[1])
                    if debug :
                        print 'Query:',query
                    if cursor.execute(query) > 0:
                        print 'Delete entry for TASK_ID:',id[0],'CHAIN_ID:',id[1],'in table:',tablename
                except:
                    pass

        
if __name__ == '__main__' :
    main(sys.argv[1:])
Revision:	1.1
Committed:	Sun Oct 22 23:22:12 2006 UTC (18 years, 6 months ago) by gutsche
Content type:	text/x-python
Branch:	MAIN
CVS Tags:	HEAD
Log Message:	clean Boss MySQL DB from projects created but not submitted
#	User	Rev	Content
1	gutsche	1.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:])