batchquery.py
author Radek Brich <radek.brich@devl.cz>
Thu, 28 Feb 2013 12:55:25 +0100
changeset 69 1bf7d2f5e00c
parent 39 0cef3540b69f
child 83 515fadd3d286
permissions -rwxr-xr-x
Add doc for PgManager.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
29
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
#!/usr/bin/env python3.2
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     2
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     3
from pgtoolkit import toolbase
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     4
from pgtoolkit.highlight import highlight
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     5
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     6
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     7
class BatchQueryTool(toolbase.SimpleTool):
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     8
    def __init__(self):
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     9
        toolbase.SimpleTool.__init__(self, name='batchquery', desc='Run a query using columns from CSV file as arguments.')
39
Radek Brich <radek.brich@devl.cz>
parents: 30
diff changeset
    10
        self.parser.add_argument('--query', dest='query', type=str, help='Query to run. Use %%s for arguments, or %%(name)s for named arguments (see --header).')
29
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    11
        self.parser.add_argument('--file', dest='file', type=str, help='CSV file with data to use as arguments.')
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
        self.parser.add_argument('--init', dest='init', type=str, help='Query which initialize database session (eg. temporary function).')
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    13
        self.parser.add_argument('--output', dest='output', type=str, help='File name for results.')
30
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    14
        self.parser.add_argument('--outputfunc', dest='outputfunc', type=str, help='Python function which will format results (format_row(args, rows)).')
29
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
        self.parser.add_argument('--header', dest='header', action='store_true', help='First line of CSV is header with names for columns. These name can be used in query.')
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
        self.init()
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    18
    def _split_line(self, line):
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    19
        return [x.strip() for x in line.split(',')]
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    20
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    21
    def main(self):
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    22
        results = []
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    23
        # load query from file
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    24
        with open(self.args.query, 'r', encoding='utf8') as f:
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    25
            query = f.read()
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    26
        # connect DB
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    27
        with self.pgm.cursor('target') as curs:
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    28
            # run init query
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    29
            if self.args.init:
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    30
                with open(self.args.init, 'r', encoding='utf8') as f:
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    31
                    curs.execute(f.read(), [])
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    32
            # read CSV file
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    33
            with open(self.args.file, 'r', encoding='utf8') as f:
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    34
                # read header
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    35
                names = None
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    36
                if self.args.header:
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    37
                    line = f.readline()
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    38
                    names = self._split_line(line)
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    39
                # read and process lines
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    40
                for line in f:
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    41
                    args = self._split_line(line)
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    42
                    if names:
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    43
                        args = dict(zip(names, args))
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    44
                    curs.execute(query, args)
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    45
                    rows = curs.fetchall()
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    46
                    results.append((args, rows))
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    47
            curs.connection.commit()
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    48
        # write results to output file
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    49
        if self.args.output:
30
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    50
            format_row = None
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    51
            if self.args.outputfunc:
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    52
                with open(self.args.outputfunc, 'r', encoding='utf8') as f:
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    53
                    d = dict()
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    54
                    exec(f.read(), d)
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    55
                    format_row = d['format_row']
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    56
            
29
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    57
            with open(self.args.output, 'w', encoding='utf8') as f:
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    58
                for args, rows in results:
30
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    59
                    if format_row:
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    60
                        f.write(format_row(args, rows))
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    61
                    else:
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    62
                        f.write(repr(args))
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    63
                        f.write(' -> ')
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 29
diff changeset
    64
                        f.write(repr(rows))
29
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    65
                    f.write('\n')
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    66
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    67
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    68
tool = BatchQueryTool()
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    69
tool.main()
8636719a30f6 Add batchquery tool.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    70