Mercurial > repos > jjohnson > query_tabular

annotate query_tabular.py @ 9:0ed75ed20c7e draft

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix skip lines
author jjohnson
date Thu, 02 Mar 2017 08:26:54 -0500
parents ae27dab228b8
children e84d1c3bf4fe
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
2 """
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
3 """
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
4 import sys
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
5 import re
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
6 import os.path
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
7 import json
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
8 import sqlite3 as sqlite
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
9 import optparse
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
10 from optparse import OptionParser
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
11
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
12 """
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
13 TODO:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
14 - could read column names from comment lines, but issues with legal names
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
15 - could add some transformations on tabular columns,
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
16 filter - skip_regex
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
17 e.g. a regex to format date/time strings
4
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
18 format: {
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
19 c2 : re.sub('pat', 'sub', c2)
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
20 c3 : len(c3)
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
21 }
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
22 def format(colname,val, expr):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
23 normalize input list columns
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
24 iterate over list values creating one row per iteration
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
25 option for input line_num column
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
26 create associated table
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
27 fk, name, value # e.g. PSM table with list of proteins containing peptide
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
28 fk, name, value[, value] # if multiple columns similarly indexed, e.g. vcf
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
29 - column_defs dict of columns to create from tabular input
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
30 column_defs : { 'name1' : 'expr', 'name2' : 'expr'}
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
31 - allow multiple queries and outputs
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
32 repeat min - max with up to max conditional outputs
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
33
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
34 - add a --json input for table definitions (or yaml)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
35 JSON config:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
36 { tables : [
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
37 { 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
38 table_name : 't1',
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
39 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
40 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
41 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
42 unique: ['c1'],
19ae309ec53c planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 29288f94a382686e263623cf6ddcd235ed5f2310-dirty
jjohnson
parents: 4
diff changeset
43 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
44 },
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
45 { 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
46 table_name : 'gff',
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
47 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
48 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
49 load_named_columns : True
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
50 filters : [{'filter': 'regex', 'pattern': '#peptide', 'action': 'exclude_match'},
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
51 {'filter': 'replace', 'column': 3, '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
52 },
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
53 { 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
54 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
55 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
56 }
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
57 ]
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
58 }
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
59 """
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
60
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
61
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
62 class LineFilter( object ):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
63 def __init__(self,source,filter_dict):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
64 self.source = source
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
65 self.filter_dict = filter_dict
8
ae27dab228b8 Uploaded
jjohnson
parents: 7
diff changeset
66 # print >> sys.stderr, 'LineFilter %s' % filter_dict if filter_dict else 'NONE'
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
67 self.func = lambda l: l.rstrip('\r\n') if l else None
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
68 if not filter_dict:
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
69 return
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
70 if filter_dict['filter'] == 'regex':
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
71 rgx = re.compile(filter_dict['pattern'])
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
72 if filter_dict['action'] == 'exclude_match':
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
73 self.func = lambda l: l if not rgx.match(l) else None
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
74 elif filter_dict['action'] == 'include_match':
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
75 self.func = lambda l: l if rgx.match(l) else None
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
76 elif filter_dict['action'] == 'exclude_find':
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
77 self.func = lambda l: l if not rgx.search(l) else None
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
78 elif filter_dict['action'] == 'include_find':
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
79 self.func = lambda l: l if rgx.search(l) else None
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
80 elif filter_dict['filter'] == 'replace':
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
81 p = filter_dict['pattern']
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
82 r = filter_dict['replace']
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
83 c = int(filter_dict['column']) - 1
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
84 self.func = lambda l: '\t'.join([x if i != c else re.sub(p,r,x) for i,x in enumerate(l.split('\t'))])
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
85 def __iter__(self):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
86 return self
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
87 def next(self):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
88 for i,next_line in enumerate(self.source):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
89 line = self.func(next_line)
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
90 if line:
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
91 return line
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
92 raise StopIteration
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
93
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
94
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
95 class TabularReader:
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
96 """
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
97 Tabular file iterator. Returns a list
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
98 """
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
99 def __init__(self, file_path, skip=0, comment_char=None, col_idx=None, filters=None):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
100 self.skip = skip
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
101 self.comment_char = comment_char
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
102 self.col_idx = col_idx
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
103 self.filters = filters
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
104 self.tsv_file = open(file_path)
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
105 if skip and skip > 0:
9
0ed75ed20c7e Fix skip lines
jjohnson
parents: 8
diff changeset
106 for i in range(skip):
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
107 if not self.tsv_file.readline():
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
108 break
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
109 source = LineFilter(self.tsv_file,None)
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
110 if comment_char:
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
111 source = LineFilter(source,{"filter": "regex", "pattern": comment_char, "action": "exclude_match"})
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
112 if filters:
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
113 for f in filters:
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
114 source = LineFilter(source,f)
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
115 self.source = source
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
116 def __iter__(self):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
117 return self
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
118 def next(self):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
119 ''' Iteration '''
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
120 for i,line in enumerate(self.source):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
121 fields = line.rstrip('\r\n').split('\t')
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
122 if self.col_idx:
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
123 fields = [fields[i] for i in self.col_idx]
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
124 return fields
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
125 raise StopIteration
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
126
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
127
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
128 def getValueType(val):
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
129 if val or 0. == val:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
130 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
131 int(val)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
132 return 'INTEGER'
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
133 except:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
134 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
135 float(val)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
136 return 'REAL'
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
137 except:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
138 return 'TEXT'
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
139 return None
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
140
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
141
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
142 def get_column_def(file_path, table_name, skip=0, comment_char='#',
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
143 column_names=None, max_lines=100,load_named_columns=False,filters=None):
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
144 col_pref = ['TEXT', 'REAL', 'INTEGER', None]
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
145 col_types = []
3
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
146 col_idx = None
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
147 data_lines = 0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
148 try:
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
149 tr = TabularReader(file_path,skip=skip, comment_char=comment_char, col_idx=None, filters=filters)
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
150 for linenum, fields in enumerate(tr):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
151 if linenum > max_lines:
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
152 break
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
153 try:
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
154 while len(col_types) < len(fields):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
155 col_types.append(None)
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
156 for i, val in enumerate(fields):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
157 colType = getValueType(val)
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
158 if col_pref.index(colType) < col_pref.index(col_types[i]):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
159 col_types[i] = colType
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
160 except Exception, e:
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
161 print >> sys.stderr, 'Failed at line: %d err: %s' % (linenum, e)
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
162 except Exception, e:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
163 print >> sys.stderr, 'Failed: %s' % (e)
3
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
164 for i,col_type in enumerate(col_types):
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
165 if not col_type:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
166 col_types[i] = 'TEXT'
3
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
167 if column_names:
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
168 col_names = []
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
169 if load_named_columns:
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
170 col_idx = []
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
171 for i, cname in enumerate([cn.strip() for cn in column_names.split(',')]):
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
172 if cname != '':
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
173 col_idx.append(i)
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
174 col_names.append(cname)
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
175 col_types = [col_types[i] for i in col_idx]
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
176 else:
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
177 col_names = ['c%d' % i for i in range(1, len(col_types) + 1)]
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
178 for i, cname in enumerate([cn.strip() for cn in column_names.split(',')]):
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
179 if cname and i < len(col_names):
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
180 col_names[i] = cname
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
181 else:
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
182 col_names = ['c%d' % i for i in range(1, len(col_types) + 1)]
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
183 col_def = []
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
184 for i, col_name in enumerate(col_names):
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
185 col_def.append('%s %s' % (col_names[i], col_types[i]))
3
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
186 return col_names, col_types, col_def, col_idx
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
187
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
188
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
189 def create_table(conn, file_path, table_name, skip=0, comment_char='#', pkey_autoincr=None, column_names=None,load_named_columns=False,filters=None,unique_indexes=[],indexes=[]):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
190
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
191 col_names, col_types, col_def, col_idx = get_column_def(file_path, table_name, skip=skip, comment_char=comment_char,
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
192 column_names=column_names,load_named_columns=load_named_columns,filters=filters)
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
193 col_func = [float if t == 'REAL' else int if t == 'INTEGER' else str for t in col_types]
5
19ae309ec53c planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 29288f94a382686e263623cf6ddcd235ed5f2310-dirty
jjohnson
parents: 4
diff changeset
194 table_def = 'CREATE TABLE %s (\n %s%s\n);' % (
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
195 table_name,
5
19ae309ec53c planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 29288f94a382686e263623cf6ddcd235ed5f2310-dirty
jjohnson
parents: 4
diff changeset
196 '%s INTEGER PRIMARY KEY AUTOINCREMENT,' % pkey_autoincr if pkey_autoincr else '',
19ae309ec53c planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 29288f94a382686e263623cf6ddcd235ed5f2310-dirty
jjohnson
parents: 4
diff changeset
197 ', \n '.join(col_def))
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
198 # print >> sys.stdout, table_def
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
199 insert_stmt = 'INSERT INTO %s(%s) VALUES(%s)' % (table_name, ','.join(col_names), ','.join(["?" for x in col_names]))
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
200 # print >> sys.stdout, insert_stmt
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
201 data_lines = 0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
202 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
203 c = conn.cursor()
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
204 c.execute(table_def)
4
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
205 conn.commit()
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
206 c.close()
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
207 for i,index in enumerate(unique_indexes):
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
208 index_name='idx_uniq_%s_%d' % (table_name,i)
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
209 index_columns = index.split(',')
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
210 create_index(conn, table_name, index_name, index_columns,unique=True)
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
211 for i,index in enumerate(indexes):
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
212 index_name='idx_%s_%d' % (table_name,i)
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
213 index_columns = index.split(',')
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
214 create_index(conn, table_name, index_name, index_columns)
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
215 c = conn.cursor()
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
216 tr = TabularReader(file_path,skip=skip, comment_char=comment_char, col_idx=col_idx, filters=filters)
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
217 for linenum, fields in enumerate(tr):
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
218 data_lines += 1
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
219 try:
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
220 vals = [col_func[i](x) if x else None for i, x in enumerate(fields)]
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
221 c.execute(insert_stmt, vals)
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
222 except Exception, e:
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
223 print >> sys.stderr, 'Failed at line: %d err: %s' % (linenum, e)
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
224 conn.commit()
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
225 c.close()
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
226 except Exception, e:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
227 print >> sys.stderr, 'Failed: %s' % (e)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
228 exit(1)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
229
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
230
4
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
231 def create_index(conn, table_name, index_name, index_columns, unique=False):
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
232 index_def = "CREATE %s INDEX %s on %s(%s)" % ('UNIQUE' if unique else '', index_name, table_name, ','.join(index_columns))
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
233 c = conn.cursor()
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
234 c.execute(index_def)
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
235 conn.commit()
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
236 c.close()
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
237
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
238
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
239 def regex_match(expr, item):
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
240 return re.match(expr, item) 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
241
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
242
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
243 def regex_search(expr, item):
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
244 return re.search(expr, item) 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
245
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
246
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
247 def regex_sub(expr, replace, item):
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
248 return re.sub(expr, replace, item)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
249
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
250
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
251 def get_connection(sqlitedb_path, addfunctions=False):
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
252 conn = sqlite.connect(sqlitedb_path)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
253 if addfunctions:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
254 conn.create_function("re_match", 2, regex_match)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
255 conn.create_function("re_search", 2, regex_search)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
256 conn.create_function("re_sub", 3, regex_sub)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
257 return conn
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
258
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
259
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
260 def __main__():
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
261 # 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
262 parser = optparse.OptionParser()
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
263 parser.add_option('-s', '--sqlitedb', dest='sqlitedb', default=None, help='The SQLite Database')
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
264 parser.add_option('-t', '--table', dest='tables', action="append", default=[], help='Tabular file: file_path[=table_name[:column_name, ...]')
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
265 parser.add_option('-j', '--jsonfile', dest='jsonfile', default=None, help='Tabular file: file_path[=table_name[:column_name, ...]')
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
266 parser.add_option('-q', '--query', dest='query', default=None, help='SQL query')
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
267 parser.add_option('-Q', '--query_file', dest='query_file', default=None, help='SQL query file')
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
268 parser.add_option('-n', '--no_header', dest='no_header', action='store_true', default=False, help='Include a column headers line')
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
269 parser.add_option('-o', '--output', dest='output', default=None, help='Output file for query results')
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
270 (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
271
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
272 # open sqlite connection
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
273 conn = get_connection(options.sqlitedb)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
274 # 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
275 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
276 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
277 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
278 outputFile = open(outputPath, 'w')
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
279 except Exception, e:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
280 print >> sys.stderr, "failed: %s" % e
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
281 exit(3)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
282 else:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
283 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
284
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
285 # get table defs
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
286 if options.tables:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
287 for ti, table in enumerate(options.tables):
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
288 table_name = 't%d' % (ti + 1)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
289 column_names = None
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
290 fields = table.split('=')
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
291 path = fields[0]
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
292 if len(fields) > 1:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
293 names = fields[1].split(':')
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
294 table_name = names[0] if names[0] else table_name
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
295 if len(names) > 1:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
296 column_names = names[1]
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
297 # print >> sys.stdout, '%s %s' % (table_name, path)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
298 create_table(conn, path, table_name, column_names=column_names)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
299 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
300 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
301 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
302 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
303 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
304 for ti, table in enumerate(tdef['tables']):
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
305 path = table['file_path']
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
306 table_name = table['table_name'] if 'table_name' in table else 't%d' % (ti + 1)
3
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
307 comment_lines = table['comment_lines'] if 'comment_lines' in table else 0
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
308 comment_char = table['comment_char'] if 'comment_char' in table else None
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
309 column_names = table['column_names'] if 'column_names' in table else None
3
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
310 if column_names:
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
311 load_named_columns = table['load_named_columns'] if 'load_named_columns' in table else False
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
312 else:
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 0
diff changeset
313 load_named_columns = False
4
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
314 unique_indexes = table['unique'] if 'unique' in table else []
9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty
jjohnson
parents: 3
diff changeset
315 indexes = table['index'] if 'index' in table else []
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
316 filters = table['filters'] if 'filters' in table else None
5
19ae309ec53c planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 29288f94a382686e263623cf6ddcd235ed5f2310-dirty
jjohnson
parents: 4
diff changeset
317 pkey_autoincr = table['pkey_autoincr'] if 'pkey_autoincr' in table else None
19ae309ec53c planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 29288f94a382686e263623cf6ddcd235ed5f2310-dirty
jjohnson
parents: 4
diff changeset
318 create_table(conn, path, table_name, pkey_autoincr=pkey_autoincr, column_names=column_names,
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
319 skip=comment_lines, comment_char=comment_char, load_named_columns=load_named_columns,
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
320 filters=filters,unique_indexes=unique_indexes, indexes=indexes)
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
321 except Exception, exc:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
322 print >> sys.stderr, "Error: %s" % exc
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
323 conn.close()
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
324
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
325 query = None
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
326 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
327 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
328 query = ''
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
329 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
330 query += line
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
331 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
332 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
333
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
334 if (query is None):
6
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
335 tables_query = \
03842a4f71c6 Uploaded
jjohnson
parents: 5
diff changeset
336 "SELECT name, sql FROM sqlite_master WHERE type='table' ORDER BY name"
0
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
337 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
338 conn = get_connection(options.sqlitedb)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
339 c = conn.cursor()
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
340 rslt = c.execute(tables_query).fetchall()
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
341 for table, sql in rslt:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
342 print >> sys.stderr, "Table %s:" % table
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
343 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
344 col_query = 'SELECT * FROM %s LIMIT 0' % table
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
345 cur = conn.cursor().execute(col_query)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
346 cols = [col[0] for col in cur.description]
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
347 print >> sys.stderr, " Columns: %s" % cols
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
348 except Exception, exc:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
349 print >> sys.stderr, "Error: %s" % exc
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
350 except Exception, exc:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
351 print >> sys.stderr, "Error: %s" % exc
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
352 exit(0)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
353 # if not sqlite.is_read_only_query(query):
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
354 # print >> sys.stderr, "Error: Must be a read only query"
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
355 # exit(2)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
356 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
357 conn = get_connection(options.sqlitedb, addfunctions=True)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
358 cur = conn.cursor()
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
359 results = cur.execute(query)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
360 if not options.no_header:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
361 outputFile.write("#%s\n" % '\t'.join([str(col[0]) for col in cur.description]))
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
362 # yield [col[0] for col in cur.description]
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
363 for i, row in enumerate(results):
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
364 # yield [val for val in row]
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
365 outputFile.write("%s\n" % '\t'.join([str(val) for val in row]))
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
366 except Exception, exc:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
367 print >> sys.stderr, "Error: %s" % exc
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
368 exit(1)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
369
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents:
diff changeset
370 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
371 __main__()