Mercurial > repos > jjohnson > query_tabular
annotate query_tabular.py @ 23:5bb148ee644a draft default tip
Deleted selected files
author | jjohnson |
---|---|
date | Mon, 17 Jul 2017 15:22:38 -0400 |
parents | bed5018e7ae3 |
children |
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: |
22 | 69 exit('Error: %s' % (e)) |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
70 else: |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
71 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
|
72 |
20 | 73 def _create_table(ti, table): |
74 path = table['file_path'] | |
75 table_name =\ | |
76 table['table_name'] if 'table_name' in table else 't%d' % (ti + 1) | |
77 comment_lines =\ | |
78 table['comment_lines'] if 'comment_lines' in table else 0 | |
79 comment_char =\ | |
80 table['comment_char'] if 'comment_char' in table else None | |
81 column_names =\ | |
82 table['column_names'] if 'column_names' in table else None | |
83 if column_names: | |
84 load_named_columns =\ | |
85 table['load_named_columns']\ | |
86 if 'load_named_columns' in table else False | |
87 else: | |
88 load_named_columns = False | |
89 unique_indexes = table['unique'] if 'unique' in table else [] | |
90 indexes = table['index'] if 'index' in table else [] | |
91 filters = table['filters'] if 'filters' in table else None | |
92 pkey_autoincr = \ | |
93 table['pkey_autoincr'] if 'pkey_autoincr' in table else None | |
94 create_table(get_connection(options.sqlitedb), path, table_name, | |
95 pkey_autoincr=pkey_autoincr, | |
96 column_names=column_names, | |
97 skip=comment_lines, | |
98 comment_char=comment_char, | |
99 load_named_columns=load_named_columns, | |
100 filters=filters, | |
101 unique_indexes=unique_indexes, | |
102 indexes=indexes) | |
103 | |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
104 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
|
105 try: |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
106 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
|
107 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
|
108 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
|
109 for ti, table in enumerate(tdef['tables']): |
20 | 110 _create_table(ti, table) |
22 | 111 except Exception as e: |
112 exit('Error: %s' % (e)) | |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
113 |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
114 query = None |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
115 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
|
116 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
|
117 query = '' |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
118 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
|
119 query += line |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
120 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
|
121 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
|
122 |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
123 if (query is None): |
20 | 124 try: |
125 describe_tables(get_connection(options.sqlitedb), outputFile) | |
22 | 126 except Exception as e: |
127 exit('Error: %s' % (e)) | |
20 | 128 else: |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
129 try: |
20 | 130 run_query(get_connection(options.sqlitedb), query, outputFile, |
131 no_header=options.no_header) | |
22 | 132 except Exception as e: |
133 exit('Error: %s' % (e)) | |
20 | 134 |
0
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
135 |
926c62f7fa09
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff
changeset
|
136 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
|
137 __main__() |