runquery.py
author Radek Brich <radek.brich@devl.cz>
Tue, 04 Feb 2014 16:10:04 +0100
changeset 94 a10f553e6f6a
parent 93 b72591087495
child 99 245646538743
permissions -rwxr-xr-x
PgDiff: Add patch support for SQL functions.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
93
b72591087495 Change python3.2 to python3 in scripts.
Radek Brich <brich.radek@ifortuna.cz>
parents: 92
diff changeset
     1
#!/usr/bin/env python3
51
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
92
Radek Brich <radek.brich@devl.cz>
parents: 91
diff changeset
    15
from psycopg2 import ProgrammingError
Radek Brich <radek.brich@devl.cz>
parents: 91
diff changeset
    16
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
67
78478a49c0bd Add runquery tool. Update setup.py.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
    18
class RunQueryTool(toolbase.ToolBase):
91
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    19
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    20
    def __init__(self):
67
78478a49c0bd Add runquery tool. Update setup.py.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
    21
        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
    22
        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
    23
        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
    24
        self.parser.add_argument('-q', dest='queries', metavar='QUERY', nargs='*', help='Queries to run.')
90
43e5d2cf4ddb Update runquery: Add parameter to read queries from file.
Radek Brich <radek.brich@devl.cz>
parents: 86
diff changeset
    25
        self.parser.add_argument('-f', dest='file', metavar='FILE', help='Read query from file.')
91
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    26
        self.parser.add_argument('-p', '--parameter', dest='parameters', metavar='PARAM=VALUE', nargs='*',
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    27
            help="If query should be used as format template, these parameters will be substituted.")
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    28
        self.parser.add_argument('--output-file', dest='output_file', metavar='OUTPUT_FILE', help='Write query result in specified file.')
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    29
        self.parser.add_argument('--format', dest='format', metavar='FORMAT', help='Format string for each line in output file (using Python\'s format()).')
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
    30
86
b61b54aa9f96 Fix: ConfigParser add_argument -> add_option.
Radek Brich <brich.radek@ifortuna.cz>
parents: 78
diff changeset
    31
        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
    32
        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
    33
        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
    34
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.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
    36
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    37
        self.init()
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    38
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
    39
    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
    40
        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
    41
        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
    42
            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
    43
        self.queries = self.args.queries or self.config.queries
90
43e5d2cf4ddb Update runquery: Add parameter to read queries from file.
Radek Brich <radek.brich@devl.cz>
parents: 86
diff changeset
    44
        # read query from file
43e5d2cf4ddb Update runquery: Add parameter to read queries from file.
Radek Brich <radek.brich@devl.cz>
parents: 86
diff changeset
    45
        if self.args.file:
43e5d2cf4ddb Update runquery: Add parameter to read queries from file.
Radek Brich <radek.brich@devl.cz>
parents: 86
diff changeset
    46
            with open(self.args.file, 'r', encoding='utf8') as f:
43e5d2cf4ddb Update runquery: Add parameter to read queries from file.
Radek Brich <radek.brich@devl.cz>
parents: 86
diff changeset
    47
                data = f.read()
43e5d2cf4ddb Update runquery: Add parameter to read queries from file.
Radek Brich <radek.brich@devl.cz>
parents: 86
diff changeset
    48
                self.queries.insert(0, data)
91
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    49
        # prepare parameters
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    50
        self._prepare_parameters(self.args.parameters)
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
    51
        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
    52
            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
    53
        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
    54
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
    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
    56
        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
    57
        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
    58
        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
    59
        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
    60
        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
    61
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
        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
    63
        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
    64
        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
    65
        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
    66
        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
    67
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    68
    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
    69
        """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
    70
        for q in self.queries:
92
Radek Brich <radek.brich@devl.cz>
parents: 91
diff changeset
    71
            if self.parameters:
Radek Brich <radek.brich@devl.cz>
parents: 91
diff changeset
    72
                q = q.format(**self.parameters)
90
43e5d2cf4ddb Update runquery: Add parameter to read queries from file.
Radek Brich <radek.brich@devl.cz>
parents: 86
diff changeset
    73
            self.log.info('%s', q if len(q) < 100 else q[:100]+'...')
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    74
            with self.pgm.cursor('target') as curs:
91
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    75
                curs.execute(q)
67
78478a49c0bd Add runquery tool. Update setup.py.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
    76
                self.log.info('Rows affected: %d', curs.rowcount)
92
Radek Brich <radek.brich@devl.cz>
parents: 91
diff changeset
    77
                try:
Radek Brich <radek.brich@devl.cz>
parents: 91
diff changeset
    78
                    rows = curs.fetchall_dict()
Radek Brich <radek.brich@devl.cz>
parents: 91
diff changeset
    79
                    self._write_output_file(rows)
Radek Brich <radek.brich@devl.cz>
parents: 91
diff changeset
    80
                except ProgrammingError:
Radek Brich <radek.brich@devl.cz>
parents: 91
diff changeset
    81
                    pass
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
    82
        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
    83
91
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    84
    def _write_output_file(self, rows):
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    85
        if not self.args.output_file:
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    86
            return
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    87
        with open(self.args.output_file, 'w', encoding='utf8') as f:
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    88
            for row in rows:
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    89
                print(self.args.format.format(row), file=f)
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    90
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    91
    def _prepare_parameters(self, parameters):
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    92
        self.parameters = {}
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    93
        for parameter in parameters or ():
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    94
            name, value = parameter.split('=', 1)
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    95
            self.parameters[name] = value
fba99a54ec49 Add to runquery tool: Parameters for query, output file and format for output file. Fix loopquery tool.
Radek Brich <radek.brich@devl.cz>
parents: 90
diff changeset
    96
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    97
67
78478a49c0bd Add runquery tool. Update setup.py.
Radek Brich <radek.brich@devl.cz>
parents: 66
diff changeset
    98
tool = RunQueryTool()
51
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    99
tool.main()
bdc44f96cb0b Add loopquery tool. Log notices to console from all tools (toolbase).
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   100