1 #!/usr/bin/env python3 |
|
2 """ |
|
3 loopquery |
|
4 |
|
5 Execute some queries in loop. Execute interval can be set. |
|
6 |
|
7 """ |
|
8 |
|
9 from pgtoolkit import toolbase |
|
10 |
|
11 import logging.handlers |
|
12 import time |
|
13 from datetime import datetime, timedelta |
|
14 |
|
15 |
|
16 class LoopQueryTool(toolbase.ToolBase): |
|
17 def __init__(self): |
|
18 toolbase.ToolBase.__init__(self, name='loopquery', desc='Run query in loop.') |
|
19 self.parser.add_argument('target', nargs='?', metavar='target', type=str, help='Target database') |
|
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', metavar='QUERY', 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_option('target', type=str, default=None) |
|
26 self.config.add_option('queries', type=list, default=[]) |
|
27 self.config.add_option('delay_mins', type=int, default=0) |
|
28 self.config.add_option('delay_secs', type=int, default=0) |
|
29 self.config.add_option('log_path', type=str) |
|
30 |
|
31 self.target_isolation_level = 'autocommit' |
|
32 |
|
33 self.init() |
|
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.args.delay_mins or 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 |
|
59 def main(self): |
|
60 self.reset() |
|
61 while True: |
|
62 self.wait() |
|
63 self.action() |
|
64 |
|
65 def reset(self): |
|
66 """Check current time, set next action time.""" |
|
67 dt = datetime.today() |
|
68 dt = dt.replace(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 # in case that action took too long and next planned time would |
|
83 # be in past -> reset planner |
|
84 if self.next_action_time < now: |
|
85 self.reset() |
|
86 |
|
87 def action(self): |
|
88 """Execute the queries.""" |
|
89 for q in self.queries: |
|
90 self.log.info('%s', q) |
|
91 with self.pgm.cursor('target') as curs: |
|
92 curs.execute(q) |
|
93 self.log.info('Done') |
|
94 |
|
95 |
|
96 tool = LoopQueryTool() |
|
97 tool.main() |
|
98 |
|