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__()