1 #!/usr/bin/env python3

     2 """

     3 runquery

     4 

     5 Execute configured queries once.

     6 

     7 """

     8 

     9 from pgtoolkit import toolbase

    10 

    11 import logging.handlers

    12 import time

    13 from datetime import datetime, timedelta

    14 

    15 from psycopg2 import ProgrammingError

    16 

    17 

    18 class RunQueryTool(toolbase.ToolBase):

    19 

    20     def __init__(self):

    21         toolbase.ToolBase.__init__(self, name='runquery', desc='Run configured queries.')

    22         self.parser.add_argument('target', nargs='?', metavar='target', type=str, help='Target database')

    23         self.parser.add_argument('-c', dest='config', type=str, help='Additional config file (besides pgtoolkit.conf).')

    24         self.parser.add_argument('-q', dest='queries', metavar='QUERY', nargs='*', help='Queries to run.')

    25         self.parser.add_argument('-f', dest='file', metavar='FILE', help='Read query from file.')

    26         self.parser.add_argument('--one-query-per-line', action='store_true', help='When reading queries from file, consider each line as separate query.')

    27         self.parser.add_argument('-p', '--parameter', dest='parameters', metavar='PARAM=VALUE', nargs='*',

    28             help="If query should be used as format template, these parameters will be substituted.")

    29         self.parser.add_argument('--output-file', dest='output_file', metavar='OUTPUT_FILE', help='Write query result in specified file.')

    30         self.parser.add_argument('--format', dest='format', metavar='FORMAT', help='Format string for each line in output file (using Python\'s format()).')

    31 

    32         self.config.add_option('target', type=str, default=None)

    33         self.config.add_option('queries', type=list, default=[])

    34         self.config.add_option('log_path', type=str)

    35 

    36         self.target_isolation_level = 'autocommit'

    37 

    38         self.init()

    39 

    40     def init(self):

    41         toolbase.ToolBase.init(self)

    42         if self.args.config:

    43             self.config.load(self.args.config)

    44         self.queries = self.args.queries or self.config.queries

    45         # read query from file

    46         if self.args.file:

    47             with open(self.args.file, 'r', encoding='utf8') as f:

    48                 data = f.read()

    49             if self.args.one_query_per_line:

    50                 file_queries = [ln for ln in data.splitlines() if not ln.lstrip().startswith('--')]

    51                 self.queries = file_queries + self.queries

    52             else:

    53                 self.queries.insert(0, data)

    54         # prepare parameters

    55         self._prepare_parameters(self.args.parameters)

    56         if self.config.log_path:

    57             self.init_file_logs(self.config.log_path)

    58         self.prepare_conns(target = self.args.target or self.config.target)

    59 

    60     def init_file_logs(self, path):

    61         format = logging.Formatter('%(asctime)s %(levelname)-5s %(message)s', '%y-%m-%d %H:%M:%S')

    62         handler = logging.handlers.TimedRotatingFileHandler(path+'/main.log', when='midnight', backupCount=5)

    63         handler.setFormatter(format)

    64         handler.setLevel(logging.DEBUG)

    65         logging.getLogger('main').addHandler(handler)

    66 

    67         format = logging.Formatter('%(asctime)s %(message)s', '%y-%m-%d %H:%M:%S')

    68         handler = logging.handlers.TimedRotatingFileHandler(path+'/pgnotices.log', when='midnight', backupCount=5)

    69         handler.setFormatter(format)

    70         handler.setLevel(logging.DEBUG)

    71         logging.getLogger('pgmanager_notices').addHandler(handler)

    72 

    73     def main(self):

    74         """Execute the queries."""

    75         for q in self.queries:

    76             if self.parameters:

    77                 q = q.format(**self.parameters)

    78             self.log.info('%s', q if len(q) < 100 else q[:100]+'...')

    79             with self.pgm.cursor('target') as curs:

    80                 curs.execute(q)

    81                 self.log.info('Rows affected: %d', curs.rowcount)

    82                 try:

    83                     rows = curs.fetchall_dict()

    84                     self._write_output_file(rows)

    85                 except ProgrammingError:

    86                     pass

    87         self.log.info('Done')

    88 

    89     def _write_output_file(self, rows):

    90         if not self.args.output_file:

    91             return

    92         with open(self.args.output_file, 'w', encoding='utf8') as f:

    93             for row in rows:

    94                 print(self.args.format.format(row), file=f)

    95 

    96     def _prepare_parameters(self, parameters):

    97         self.parameters = {}

    98         for parameter in parameters or ():

    99             name, value = parameter.split('=', 1)

   100             self.parameters[name] = value

   101 

   102 

   103 tool = RunQueryTool()

   104 tool.main()

   105