1 #!/usr/bin/env python3.2 |
1 #!/usr/bin/env python3.2 |
2 """ |
2 """ |
3 loopquery |
3 loopquery |
4 |
4 |
5 Execute some query in loop, waiting for some time interval before next run. |
5 Execute some queries in loop. Execute interval can be set. |
6 |
6 |
7 """ |
7 """ |
8 |
8 |
9 from pgtoolkit import toolbase |
9 from pgtoolkit import toolbase, config |
10 |
10 |
|
11 import logging.handlers |
11 import time |
12 import time |
|
13 from datetime import datetime, timedelta |
12 |
14 |
13 |
15 |
14 class LoopQueryTool(toolbase.SimpleTool): |
16 class LoopQueryTool(toolbase.ToolBase): |
15 def __init__(self): |
17 def __init__(self): |
16 toolbase.SimpleTool.__init__(self, name='loopquery', desc='Run query in loop.') |
18 toolbase.ToolBase.__init__(self, name='loopquery', desc='Run query in loop.') |
17 self.parser.add_argument('-q', dest='query', type=str, help='Query to run.') |
19 self.parser.add_argument('target', nargs='?', metavar='target', type=str, help='Target database') |
18 self.parser.add_argument('-i', dest='interval', type=float, help='Run interval in seconds.') |
20 self.parser.add_argument('-c', dest='config', type=str, help='Additional config file (besides pgtoolkit.conf).') |
|
21 self.parser.add_argument('-q', dest='queries', nargs='*', help='Queries to run.') |
|
22 self.parser.add_argument('--mins', dest='delay_mins', type=int, help='Delay between queries in minutes.') |
|
23 self.parser.add_argument('--secs', dest='delay_secs', type=int, help='Delay between queries in seconds.') |
|
24 |
|
25 self.config.add_argument('target', type=str, default=None) |
|
26 self.config.add_argument('queries', type=list, default=[]) |
|
27 self.config.add_argument('delay_mins', type=int, default=0) |
|
28 self.config.add_argument('delay_secs', type=int, default=0) |
|
29 self.config.add_argument('log_path', type=str) |
|
30 |
|
31 self.target_isolation_level = 'autocommit' |
|
32 |
19 self.init() |
33 self.init() |
20 |
34 |
|
35 def init(self): |
|
36 toolbase.ToolBase.init(self) |
|
37 if self.args.config: |
|
38 self.config.load(self.args.config) |
|
39 self.queries = self.args.queries or self.config.queries |
|
40 self.delay_mins = self.config.delay_mins |
|
41 self.delay_secs = self.args.delay_secs or self.config.delay_secs |
|
42 if self.config.log_path: |
|
43 self.init_file_logs(self.config.log_path) |
|
44 self.prepare_conns(target = self.args.target or self.config.target) |
|
45 |
|
46 def init_file_logs(self, path): |
|
47 format = logging.Formatter('%(asctime)s %(levelname)-5s %(message)s', '%y-%m-%d %H:%M:%S') |
|
48 handler = logging.handlers.TimedRotatingFileHandler(path+'/main.log', when='midnight', backupCount=5) |
|
49 handler.setFormatter(format) |
|
50 handler.setLevel(logging.DEBUG) |
|
51 logging.getLogger('main').addHandler(handler) |
|
52 |
|
53 format = logging.Formatter('%(asctime)s %(message)s', '%y-%m-%d %H:%M:%S') |
|
54 handler = logging.handlers.TimedRotatingFileHandler(path+'/pgnotices.log', when='midnight', backupCount=5) |
|
55 handler.setFormatter(format) |
|
56 handler.setLevel(logging.DEBUG) |
|
57 logging.getLogger('pgmanager_notices').addHandler(handler) |
|
58 |
21 def main(self): |
59 def main(self): |
|
60 self.prepare() |
22 while True: |
61 while True: |
23 self.log.info('Executing: %s', self.args.query) |
62 self.wait() |
|
63 self.action() |
|
64 |
|
65 def prepare(self): |
|
66 """Check current time, set next action time.""" |
|
67 dt = datetime.today() |
|
68 dt = dt.replace(second = 0, microsecond = 0) |
|
69 self.next_action_time = dt + timedelta(minutes = self.delay_mins, |
|
70 seconds = self.delay_secs) |
|
71 |
|
72 def wait(self): |
|
73 """Wait for action time, compute next action time.""" |
|
74 now = datetime.today() |
|
75 self.log.debug('Next run %s', self.next_action_time) |
|
76 if self.next_action_time > now: |
|
77 td = self.next_action_time - now |
|
78 self.log.debug('Sleep %ds', td.seconds + td.microseconds/1e6) |
|
79 time.sleep(td.seconds + td.microseconds/1e6) |
|
80 self.next_action_time += timedelta(minutes = self.delay_mins, |
|
81 seconds = self.delay_secs) |
|
82 |
|
83 def action(self): |
|
84 """Execute the queries.""" |
|
85 for q in self.queries: |
|
86 self.log.info('%s', q) |
24 with self.pgm.cursor('target') as curs: |
87 with self.pgm.cursor('target') as curs: |
25 curs.execute(self.args.query) |
88 curs.execute(q) |
26 self.log.info('Done.') |
89 self.log.info('Done') |
27 time.sleep(self.args.interval) |
|
28 |
90 |
29 |
91 |
30 tool = LoopQueryTool() |
92 tool = LoopQueryTool() |
31 tool.main() |
93 tool.main() |
32 |
94 |