Mercurial > repos > jjohnson > query_tabular
annotate query_tabular.py @ 20:ab27c4bd14b9 draft
Uploaded
author | jjohnson |
---|---|
date | Fri, 14 Jul 2017 11:39:27 -0400 |
parents | b9f797bf4f38 |
children | bed5018e7ae3 |
rev | line source |
---|---|
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
1 #!/usr/bin/env python |
20 | 2 |
3 from __future__ import print_function | |
4 | |
5 import json | |
6 import optparse | |
7 import os.path | |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
8 import sys |
20 | 9 |
10 from load_db import create_table | |
11 | |
12 from query_db import describe_tables, get_connection, run_query | |
13 | |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
14 |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
15 """ |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
16 JSON config: |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
17 { tables : [ |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
18 { file_path : '/home/galaxy/dataset_101.dat', |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
19 table_name : 't1', |
20 | 20 column_names : ['c1','c2','c3'], |
5
19ae309ec53c
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 29288f94a382686e263623cf6ddcd235ed5f2310-dirty
jjohnson
parents:
4
diff
changeset
|
21 pkey_autoincr : 'id' |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
22 comment_lines : 1 |
5
19ae309ec53c
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 29288f94a382686e263623cf6ddcd235ed5f2310-dirty
jjohnson
parents:
4
diff
changeset
|
23 unique: ['c1'], |
20 | 24 index: ['c2', 'c3'] |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
25 }, |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
26 { file_path : '/home/galaxy/dataset_102.dat', |
3
3e3b3c883bec
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents:
0
diff
changeset
|
27 table_name : 'gff', |
6 | 28 column_names : ['seqname',,'date','start','end'] |
3
3e3b3c883bec
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents:
0
diff
changeset
|
29 comment_lines : 1 |
3e3b3c883bec
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents:
0
diff
changeset
|
30 load_named_columns : True |
20 | 31 filters : [{'filter': 'regex', 'pattern': '#peptide', |
32 'action': 'exclude_match'}, | |
33 {'filter': 'replace', 'column': 3, | |
34 'replace': 'gi[|]', 'pattern': ''}] | |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
35 }, |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
36 { file_path : '/home/galaxy/dataset_103.dat', |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
37 table_name : 'test', |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
38 column_names : ['c1', 'c2', 'c3'] |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
39 } |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
40 ] |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
41 } |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
42 """ |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
43 |
6 | 44 |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
45 def __main__(): |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
46 # Parse Command Line |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
47 parser = optparse.OptionParser() |
20 | 48 parser.add_option('-s', '--sqlitedb', dest='sqlitedb', default=None, |
49 help='The SQLite Database') | |
50 parser.add_option('-j', '--jsonfile', dest='jsonfile', default=None, | |
51 help='JSON dict of table specifications') | |
52 parser.add_option('-q', '--query', dest='query', default=None, | |
53 help='SQL query') | |
54 parser.add_option('-Q', '--query_file', dest='query_file', default=None, | |
55 help='SQL query file') | |
56 parser.add_option('-n', '--no_header', dest='no_header', default=False, | |
57 action='store_true', | |
58 help='Include a column headers line') | |
59 parser.add_option('-o', '--output', dest='output', default=None, | |
60 help='Output file for query results') | |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
61 (options, args) = parser.parse_args() |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
62 |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
63 # determine output destination |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
64 if options.output is not None: |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
65 try: |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
66 outputPath = os.path.abspath(options.output) |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
67 outputFile = open(outputPath, 'w') |
20 | 68 except Exception as e: |
69 print("failed: %s" % e, file=sys.stderr) | |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
70 exit(3) |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
71 else: |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
72 outputFile = sys.stdout |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
73 |
20 | 74 def _create_table(ti, table): |
75 path = table['file_path'] | |
76 table_name =\ | |
77 table['table_name'] if 'table_name' in table else 't%d' % (ti + 1) | |
78 comment_lines =\ | |
79 table['comment_lines'] if 'comment_lines' in table else 0 | |
80 comment_char =\ | |
81 table['comment_char'] if 'comment_char' in table else None | |
82 column_names =\ | |
83 table['column_names'] if 'column_names' in table else None | |
84 if column_names: | |
85 load_named_columns =\ | |
86 table['load_named_columns']\ | |
87 if 'load_named_columns' in table else False | |
88 else: | |
89 load_named_columns = False | |
90 unique_indexes = table['unique'] if 'unique' in table else [] | |
91 indexes = table['index'] if 'index' in table else [] | |
92 filters = table['filters'] if 'filters' in table else None | |
93 pkey_autoincr = \ | |
94 table['pkey_autoincr'] if 'pkey_autoincr' in table else None | |
95 create_table(get_connection(options.sqlitedb), path, table_name, | |
96 pkey_autoincr=pkey_autoincr, | |
97 column_names=column_names, | |
98 skip=comment_lines, | |
99 comment_char=comment_char, | |
100 load_named_columns=load_named_columns, | |
101 filters=filters, | |
102 unique_indexes=unique_indexes, | |
103 indexes=indexes) | |
104 | |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
105 if options.jsonfile: |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
106 try: |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
107 fh = open(options.jsonfile) |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
108 tdef = json.load(fh) |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
109 if 'tables' in tdef: |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
110 for ti, table in enumerate(tdef['tables']): |
20 | 111 _create_table(ti, table) |
112 except Exception as exc: | |
113 print("Error: %s" % exc, file=sys.stderr) | |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
114 |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
115 query = None |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
116 if (options.query_file is not None): |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
117 with open(options.query_file, 'r') as fh: |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
118 query = '' |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
119 for line in fh: |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
120 query += line |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
121 elif (options.query is not None): |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
122 query = options.query |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
123 |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
124 if (query is None): |
20 | 125 try: |
126 describe_tables(get_connection(options.sqlitedb), outputFile) | |
127 except Exception as exc: | |
128 print("Error: %s" % exc, file=sys.stderr) | |
129 else: | |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
130 try: |
20 | 131 run_query(get_connection(options.sqlitedb), query, outputFile, |
132 no_header=options.no_header) | |
133 except Exception as exc: | |
134 print("Error: %s" % exc, file=sys.stderr) | |
135 exit(1) | |
136 | |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
137 |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
138 if __name__ == "__main__": |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
139 __main__() |