Source code

Download source archive ↓

upstream/tools/query_tabular/query_tabular.py

1#!/usr/bin/env python
2
3from __future__ import print_function
4
5import json
6import optparse
7import os.path
8import sys
9
10from load_db import create_table
11from query_db import describe_tables, get_connection, run_query
12
13
14"""
15JSON config:
16{ tables : [
17    { file_path : '/home/galaxy/dataset_101.dat',
18            table_name : 't1',
19            column_names : ['c1','c2','c3'],
20            pkey_autoincr : 'id'
21            comment_lines : 1
22            unique: ['c1'],
23            index: ['c2', 'c3']
24    },
25    { file_path : '/home/galaxy/dataset_102.dat',
26            table_name : 'gff',
27            column_names : ['seqname',,'date','start','end']
28            comment_lines : 1
29            load_named_columns : True
30            filters : [{'filter': 'regex', 'pattern': '#peptide',
31                        'action': 'exclude_match'},
32                       {'filter': 'replace', 'column': 3,
33                        'replace': 'gi[|]', 'pattern': ''}]
34    },
35    { file_path : '/home/galaxy/dataset_103.dat',
36            table_name : 'test',
37            column_names : ['c1', 'c2', 'c3']
38    }
39    ]
40}
41"""
42
43
44def __main__():
45    # Parse Command Line
46    parser = optparse.OptionParser()
47    parser.add_option('-s', '--sqlitedb', dest='sqlitedb', default=None,
48                      help='The SQLite Database')
49    parser.add_option('-j', '--jsonfile', dest='jsonfile', default=None,
50                      help='JSON dict of table specifications')
51    parser.add_option('-q', '--query', dest='query', default=None,
52                      help='SQL query')
53    parser.add_option('-Q', '--query_file', dest='query_file', default=None,
54                      help='SQL query file')
55    parser.add_option('-n', '--no_header', dest='no_header', default=False,
56                      action='store_true',
57                      help='Include a column headers line')
58    parser.add_option('-c', '--comment_char', dest='comment_char', default='',
59                      help='comment character to prefix column header line')
60    parser.add_option('-o', '--output', dest='output', default=None,
61                      help='Output file for query results')
62    parser.add_option('-d', '--debug', dest='debug', default=False,
63                      action='store_true',
64                      help='Output info to stderr')
65    (options, args) = parser.parse_args()
66
67    # determine output destination
68    if options.output is not None:
69        try:
70            outputPath = os.path.abspath(options.output)
71            outputFile = open(outputPath, 'w')
72        except Exception as e:
73            exit('Error: %s' % (e))
74    else:
75        outputFile = sys.stdout
76
77    def _create_table(ti, table):
78        path = table['file_path']
79        table_name =\
80            table['table_name'] if 'table_name' in table else 't%d' % (ti + 1)
81        comment_lines =\
82            table['comment_lines'] if 'comment_lines' in table else 0
83        comment_char =\
84            table['comment_char'] if 'comment_char' in table else None
85        column_names =\
86            table['column_names'] if 'column_names' in table else None
87        firstlinenames =\
88            table['firstlinenames'] if 'firstlinenames' in table else False
89        if column_names:
90            load_named_columns =\
91                table['load_named_columns']\
92                if 'load_named_columns' in table else False
93        else:
94            load_named_columns = False
95        unique_indexes = table['unique'] if 'unique' in table else []
96        indexes = table['index'] if 'index' in table else []
97        filters = table['filters'] if 'filters' in table else None
98        pkey_autoincr = \
99            table['pkey_autoincr'] if 'pkey_autoincr' in table else None
100        create_table(get_connection(options.sqlitedb), path, table_name,
101                     pkey_autoincr=pkey_autoincr,
102                     firstlinenames=firstlinenames,
103                     column_names=column_names,
104                     skip=comment_lines,
105                     comment_char=comment_char,
106                     load_named_columns=load_named_columns,
107                     filters=filters,
108                     unique_indexes=unique_indexes,
109                     indexes=indexes)
110
111    if options.jsonfile:
112        try:
113            with open(options.jsonfile) as fh:
114                tdef = json.load(fh)
115                if options.debug:
116                    print('JSON: %s' % tdef, file=sys.stderr)
117                if 'tables' in tdef:
118                    for ti, table in enumerate(tdef['tables']):
119                        _create_table(ti, table)
120                if 'sql_stmts' in tdef:
121                    for si, stmt in enumerate(tdef['sql_stmts']):
122                        rowcount = run_query(get_connection(options.sqlitedb), stmt, None)
123                        if options.debug:
124                            print('\nDB modification: %s  \nrowcount: %s' %
125                                  (stmt, rowcount), file=sys.stderr)
126                if 'queries' in tdef:
127                    for qi, qstmt in enumerate(tdef['queries']):
128                        if 'header' in qstmt:
129                            no_header = False
130                            comment_char = qstmt['header']
131                        else:
132                            no_header = True
133                            comment_char = None
134                        with open(qstmt['result_file'], 'w') as fh:
135                            query = qstmt['query']
136                            rowcount = run_query(get_connection(options.sqlitedb),
137                                                 query,
138                                                 fh,
139                                                 no_header=no_header,
140                                                 comment_char=comment_char)
141                        if options.debug:
142                            print('\nSQL: %s  \nrowcount: %s' %
143                                  (query, rowcount), file=sys.stderr)
144        except Exception as e:
145            exit('Error: %s' % (e))
146
147    query = None
148    if options.query_file is not None:
149        with open(options.query_file, 'r') as fh:
150            query = ''
151            for line in fh:
152                query += line
153    elif options.query is not None:
154        query = options.query
155
156    if query is None:
157        try:
158            describe_tables(get_connection(options.sqlitedb), outputFile)
159        except Exception as e:
160            exit('Error: %s' % (e))
161    else:
162        try:
163            rowcount = run_query(get_connection(options.sqlitedb),
164                                 query, outputFile,
165                                 no_header=options.no_header,
166                                 comment_char=options.comment_char)
167            if options.debug:
168                print('\nSQL: %s  \nrowcount: %s' %
169                      (query, rowcount), file=sys.stderr)
170        except Exception as e:
171            exit('Error: %s' % (e))
172
173
174if __name__ == "__main__":
175    __main__()