runquery.py
author Radek Brich <radek.brich@devl.cz>
Tue, 24 Sep 2013 13:34:15 +0200
changeset 87 3ef717ee9253
parent 86 b61b54aa9f96
child 90 43e5d2cf4ddb
permissions -rwxr-xr-x
PgDiff: Add support for indexes.
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
"""
67
78478a49c0bd Add runquery tool. Update setup.py.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
     3
runquery
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     4
67
78478a49c0bd Add runquery tool. Update setup.py.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
     5
Execute configured queries once.
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: 67
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
67
78478a49c0bd Add runquery tool. Update setup.py.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
    16
class RunQueryTool(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):
67
78478a49c0bd Add runquery tool. Update setup.py.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
    18
        toolbase.ToolBase.__init__(self, name='runquery', desc='Run configured queries.')
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
    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
86
b61b54aa9f96 Fix: ConfigParser add_argument -> add_option.
Radek Brich <brich.radek@ifortuna.cz>
parents: 78
diff changeset
    23
        self.config.add_option('target', type=str, default=None)
b61b54aa9f96 Fix: ConfigParser add_argument -> add_option.
Radek Brich <brich.radek@ifortuna.cz>
parents: 78
diff changeset
    24
        self.config.add_option('queries', type=list, default=[])
b61b54aa9f96 Fix: ConfigParser add_argument -> add_option.
Radek Brich <brich.radek@ifortuna.cz>
parents: 78
diff changeset
    25
        self.config.add_option('log_path', type=str)
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
    26
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.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
    28
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    29
        self.init()
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    30
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
    31
    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
    32
        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
    33
        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
    34
            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
    35
        self.queries = self.args.queries or self.config.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
    36
        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
    37
            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
    38
        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
    39
af637235ca81 Update loopquery: allow any number of queries, support reading parameters from config file.
Radek Brich <radek.brich@devl.cz>
parents: 51
diff changeset
    40
    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
    41
        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
    42
        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
    43
        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
    44
        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
    45
        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
    46
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 %(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+'/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
    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('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
    52
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    53
    def main(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
    54
        """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
    55
        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
    56
            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
    57
            with self.pgm.cursor('target') as curs:
67
78478a49c0bd Add runquery tool. Update setup.py.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
    58
                rows = curs.execute(q)
78478a49c0bd Add runquery tool. Update setup.py.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
    59
                self.log.info('Rows affected: %d', curs.rowcount)
78478a49c0bd Add runquery tool. Update setup.py.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
    60
                self.log.info('Result: %r', rows)
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
    61
        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
    62
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    63
67
78478a49c0bd Add runquery tool. Update setup.py.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
    64
tool = RunQueryTool()
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    65
tool.main()
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    66