loopquery.py
author Radek Brich <radek.brich@devl.cz>
Sat, 13 Apr 2013 15:22:05 +0200
changeset 83 515fadd3d286
parent 78 64c62ac8f65d
child 91 fba99a54ec49
permissions -rwxr-xr-x
Add dependency on pycolib. Move common modules to pycolib. Add example table schema for meta DB.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
#!/usr/bin/env python3.2
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     2
"""
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     3
loopquery
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     4
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
     5
Execute some queries in loop. Execute interval can be set.
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     6
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     7
"""
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     8
78
64c62ac8f65d Rename config module to configparser.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
     9
from pgtoolkit import toolbase
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    10
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    11
import logging.handlers
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
import time
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    13
from datetime import datetime, timedelta
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    14
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    16
class LoopQueryTool(toolbase.ToolBase):
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
    def __init__(self):
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    18
        toolbase.ToolBase.__init__(self, name='loopquery', desc='Run query in loop.')
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    19
        self.parser.add_argument('target', nargs='?', metavar='target', type=str, help='Target database')
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    20
        self.parser.add_argument('-c', dest='config', type=str, help='Additional config file (besides pgtoolkit.conf).')
65
ea79c89e9517 Update loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 62
diff changeset
    21
        self.parser.add_argument('-q', dest='queries', metavar='QUERY', nargs='*', help='Queries to run.')
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    22
        self.parser.add_argument('--mins', dest='delay_mins', type=int, help='Delay between queries in minutes.')
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    23
        self.parser.add_argument('--secs', dest='delay_secs', type=int, help='Delay between queries in seconds.')
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    24
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    25
        self.config.add_argument('target', type=str, default=None)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    26
        self.config.add_argument('queries', type=list, default=[])
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    27
        self.config.add_argument('delay_mins', type=int, default=0)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    28
        self.config.add_argument('delay_secs', type=int, default=0)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    29
        self.config.add_argument('log_path', type=str)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    30
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    31
        self.target_isolation_level = 'autocommit'
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    32
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    33
        self.init()
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    34
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    35
    def init(self):
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    36
        toolbase.ToolBase.init(self)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    37
        if self.args.config:
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    38
            self.config.load(self.args.config)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    39
        self.queries = self.args.queries or self.config.queries
65
ea79c89e9517 Update loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 62
diff changeset
    40
        self.delay_mins = self.args.delay_mins or self.config.delay_mins
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    41
        self.delay_secs = self.args.delay_secs or self.config.delay_secs
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    42
        if self.config.log_path:
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    43
            self.init_file_logs(self.config.log_path)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    44
        self.prepare_conns(target = self.args.target or self.config.target)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    45
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    46
    def init_file_logs(self, path):
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    47
        format = logging.Formatter('%(asctime)s %(levelname)-5s %(message)s', '%y-%m-%d %H:%M:%S')
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    48
        handler = logging.handlers.TimedRotatingFileHandler(path+'/main.log', when='midnight', backupCount=5)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    49
        handler.setFormatter(format)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    50
        handler.setLevel(logging.DEBUG)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    51
        logging.getLogger('main').addHandler(handler)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    52
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    53
        format = logging.Formatter('%(asctime)s %(message)s', '%y-%m-%d %H:%M:%S')
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    54
        handler = logging.handlers.TimedRotatingFileHandler(path+'/pgnotices.log', when='midnight', backupCount=5)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    55
        handler.setFormatter(format)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    56
        handler.setLevel(logging.DEBUG)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    57
        logging.getLogger('pgmanager_notices').addHandler(handler)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    58
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    59
    def main(self):
65
ea79c89e9517 Update loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 62
diff changeset
    60
        self.reset()
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    61
        while True:
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    62
            self.wait()
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    63
            self.action()
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    64
65
ea79c89e9517 Update loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 62
diff changeset
    65
    def reset(self):
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    66
        """Check current time, set next action time."""
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    67
        dt = datetime.today()
66
3cd28308d67b Fix loopquery timing.
Radek Brich <radek.brich@devl.cz>
parents: 65
diff changeset
    68
        dt = dt.replace(microsecond = 0)
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    69
        self.next_action_time = dt + timedelta(minutes = self.delay_mins,
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    70
                                               seconds = self.delay_secs)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    71
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    72
    def wait(self):
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    73
        """Wait for action time, compute next action time."""
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    74
        now = datetime.today()
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    75
        self.log.debug('Next run %s', self.next_action_time)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    76
        if self.next_action_time > now:
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    77
            td = self.next_action_time - now
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    78
            self.log.debug('Sleep %ds', td.seconds + td.microseconds/1e6)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    79
            time.sleep(td.seconds + td.microseconds/1e6)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    80
        self.next_action_time += timedelta(minutes = self.delay_mins,
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    81
                                           seconds = self.delay_secs)
65
ea79c89e9517 Update loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 62
diff changeset
    82
        # in case that action took too long and next planned time would
ea79c89e9517 Update loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 62
diff changeset
    83
        # be in past -> reset planner
ea79c89e9517 Update loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 62
diff changeset
    84
        if self.next_action_time < now:
ea79c89e9517 Update loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 62
diff changeset
    85
            self.reset()
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    86
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    87
    def action(self):
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    88
        """Execute the queries."""
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    89
        for q in self.queries:
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    90
            self.log.info('%s', q)
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    91
            with self.pgm.cursor('target') as curs:
62
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    92
                curs.execute(q)
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    93
        self.log.info('Done')
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    94
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    95
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    96
tool = LoopQueryTool()
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    97
tool.main()
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    98