query_tabular: query_tabular.py annotate

annotate query_tabular.py @ 18:b9f797bf4f38 draft

Uploaded

author	jjohnson
date	Wed, 05 Jul 2017 11:51:15 -0400
parents	5b4f6cf857cf
children	ab27c4bd14b9

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'
11 fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	67 self.func = lambda i,l: l.rstrip('\r\n') if l else None
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	68 self.src_lines = []
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	69 self.src_line_cnt = 0
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	70 if not filter_dict:
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	71 return
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	72 if filter_dict['filter'] == 'regex':
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	73 rgx = re.compile(filter_dict['pattern'])
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	74 if filter_dict['action'] == 'exclude_match':
11 fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	75 self.func = lambda i,l: l if not rgx.match(l) else None
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	76 elif filter_dict['action'] == 'include_match':
11 fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	77 self.func = lambda i,l: l if rgx.match(l) else None
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	78 elif filter_dict['action'] == 'exclude_find':
11 fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	79 self.func = lambda i,l: l if not rgx.search(l) else None
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	80 elif filter_dict['action'] == 'include_find':
11 fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	81 self.func = lambda i,l: l if rgx.search(l) else None
16 5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	82 elif filter_dict['filter'] == 'select_columns':
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	83 cols = [int(c) - 1 for c in filter_dict['columns']]
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	84 self.func = lambda i,l: self.select_columns(l,cols)
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	85 elif filter_dict['filter'] == 'replace':
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	86 p = filter_dict['pattern']
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	87 r = filter_dict['replace']
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	88 c = int(filter_dict['column']) - 1
11 fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	89 self.func = lambda i,l: '\t'.join([x if i != c else re.sub(p,r,x) for i,x in enumerate(l.split('\t'))])
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	90 elif filter_dict['filter'] == 'prepend_line_num':
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	91 self.func = lambda i,l: '%d\t%s' % (i,l)
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	92 elif filter_dict['filter'] == 'append_line_num':
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	93 self.func = lambda i,l: '%s\t%d' % (l.rstrip('\r\n'),i)
18 b9f797bf4f38 Uploaded jjohnson parents: 16 diff changeset	94 elif filter_dict['filter'] == 'prepend_text':
b9f797bf4f38 Uploaded jjohnson parents: 16 diff changeset	95 s = filter_dict['column_text']
b9f797bf4f38 Uploaded jjohnson parents: 16 diff changeset	96 self.func = lambda i,l: '%s\t%s' % (s,l)
b9f797bf4f38 Uploaded jjohnson parents: 16 diff changeset	97 elif filter_dict['filter'] == 'append_text':
b9f797bf4f38 Uploaded jjohnson parents: 16 diff changeset	98 s = filter_dict['column_text']
b9f797bf4f38 Uploaded jjohnson parents: 16 diff changeset	99 self.func = lambda i,l: '%s\t%s' % (l.rstrip('\r\n'),s)
11 fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	100 elif filter_dict['filter'] == 'skip':
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	101 cnt = filter_dict['count']
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	102 self.func = lambda i,l: l if i > cnt else None
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	103 elif filter_dict['filter'] == 'normalize':
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	104 cols = [int(c) - 1 for c in filter_dict['columns']]
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	105 sep = filter_dict['separator']
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	106 self.func = lambda i,l: self.normalize(l,cols,sep)
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	107 def __iter__(self):
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	108 return self
16 5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	109 def select_columns(self,line,cols):
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	110 fields = line.split('\t')
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	111 return '\t'.join([fields[x] for x in cols])
11 fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	112 def normalize(self,line,split_cols,sep):
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	113 lines = []
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	114 fields = line.rstrip('\r\n').split('\t')
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	115 split_fields = dict()
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	116 cnt = 0
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	117 for c in split_cols:
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	118 if c < len(fields):
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	119 split_fields[c] = fields[c].split(sep)
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	120 cnt = max(cnt, len(split_fields[c]))
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	121 if cnt == 0:
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	122 lines.append('\t'.join(fields))
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	123 else:
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	124 for n in range(0, cnt):
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	125 flds = [x if c not in split_cols else split_fields[c][n] if n < len(split_fields[c]) else '' for (c, x) in enumerate(fields)]
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	126 lines.append('\t'.join(flds))
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	127 return lines
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	128 def get_lines(self):
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	129 for i,next_line in enumerate(self.source):
11 fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	130 self.src_line_cnt += 1
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	131 line = self.func(self.src_line_cnt,next_line)
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	132 # print >> sys.stderr, 'LineFilter %s: %d %s' % (str(self.filter_dict),self.src_line_cnt,line)
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	133 if line:
11 fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	134 if isinstance(line,list):
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	135 self.src_lines.extend(line)
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	136 else:
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	137 self.src_lines.append(line)
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	138 return
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	139 def next(self):
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	140 if not self.src_lines:
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	141 self.get_lines()
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	142 if self.src_lines:
fd16243931d6 Uploaded jjohnson parents: 10 diff changeset	143 return self.src_lines.pop(0)
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	144 raise StopIteration
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	145
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	146
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	147 class TabularReader:
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	148 """
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	149 Tabular file iterator. Returns a list
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	150 """
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	151 def __init__(self, file_path, skip=0, comment_char=None, col_idx=None, filters=None):
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	152 self.skip = skip
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	153 self.comment_char = comment_char
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	154 self.col_idx = col_idx
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	155 self.filters = filters
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	156 self.tsv_file = open(file_path)
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	157 if skip and skip > 0:
9 0ed75ed20c7e Fix skip lines jjohnson parents: 8 diff changeset	158 for i in range(skip):
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	159 if not self.tsv_file.readline():
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	160 break
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	161 source = LineFilter(self.tsv_file,None)
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	162 if comment_char:
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	163 source = LineFilter(source,{"filter": "regex", "pattern": comment_char, "action": "exclude_match"})
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	164 if filters:
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	165 for f in filters:
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	166 source = LineFilter(source,f)
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	167 self.source = source
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	168 def __iter__(self):
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	169 return self
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	170 def next(self):
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	171 ''' Iteration '''
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	172 for i,line in enumerate(self.source):
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	173 fields = line.rstrip('\r\n').split('\t')
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	174 if self.col_idx:
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	175 fields = [fields[i] for i in self.col_idx]
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	176 return fields
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	177 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	178
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	179
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	180 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	181 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	182 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	183 int(val)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	184 return 'INTEGER'
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	185 except:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	186 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	187 float(val)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	188 return 'REAL'
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	189 except:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	190 return 'TEXT'
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	191 return None
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	192
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	193
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	194 def get_column_def(file_path, table_name, skip=0, comment_char='#',
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	195 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	196 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	197 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	198 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	199 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	200 try:
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	201 tr = TabularReader(file_path,skip=skip, comment_char=comment_char, col_idx=None, filters=filters)
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	202 for linenum, fields in enumerate(tr):
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	203 if linenum > max_lines:
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	204 break
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	205 try:
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	206 while len(col_types) < len(fields):
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	207 col_types.append(None)
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	208 for i, val in enumerate(fields):
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	209 colType = getValueType(val)
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	210 if col_pref.index(colType) < col_pref.index(col_types[i]):
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	211 col_types[i] = colType
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	212 except Exception, e:
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	213 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	214 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	215 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	216 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	217 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	218 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	219 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	220 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	221 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	222 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	223 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	224 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	225 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	226 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	227 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	228 else:
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty jjohnson parents: 0 diff changeset	229 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	230 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	231 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	232 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	233 else:
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty jjohnson parents: 0 diff changeset	234 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	235 col_def = []
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	236 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	237 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	238 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	239
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	240
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	241 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	242
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	243 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	244 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	245 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	246 table_def = 'CREATE TABLE %s (\n %s%s\n);' % (
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	247 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	248 '%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	249 ', \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	250 # 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	251 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	252 # 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	253 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	254 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	255 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	256 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	257 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	258 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	259 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	260 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	261 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	262 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	263 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	264 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	265 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	266 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	267 c = conn.cursor()
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	268 tr = TabularReader(file_path,skip=skip, comment_char=comment_char, col_idx=col_idx, filters=filters)
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	269 for linenum, fields in enumerate(tr):
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	270 data_lines += 1
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	271 try:
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	272 vals = [col_func[i](x) if x else None for i, x in enumerate(fields)]
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	273 c.execute(insert_stmt, vals)
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	274 except Exception, e:
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	275 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	276 conn.commit()
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	277 c.close()
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	278 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	279 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	280 exit(1)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	281
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	282
4 9d73dca48178 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9893037a046e4f4c1e7ba859e05f49378c398cb1-dirty jjohnson parents: 3 diff changeset	283 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	284 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	285 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	286 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	287 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	288 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	289
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	290
0 926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	291 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	292 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	293
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	294
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	295 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	296 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	297
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	298
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	299 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	300 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	301
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	302
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	303 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	304 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	305 if addfunctions:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	306 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	307 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	308 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	309 return conn
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	310
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	311
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	312 def __main__():
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	313 # 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	314 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	315 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	316 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	317 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	318 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	319 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	320 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	321 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	322 (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	323
16 5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	324 def run_query(query,outputFile):
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	325 conn = get_connection(options.sqlitedb, addfunctions=True)
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	326 cur = conn.cursor()
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	327 results = cur.execute(query)
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	328 if not options.no_header:
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	329 outputFile.write("#%s\n" % '\t'.join([str(col[0]) for col in cur.description]))
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	330 # yield [col[0] for col in cur.description]
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	331 for i, row in enumerate(results):
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	332 # yield [val for val in row]
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	333 outputFile.write("%s\n" % '\t'.join([str(val) if val is not None else '' for val in row]))
5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	334
0 926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	335 # 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	336 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	337 # 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	338 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	339 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	340 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	341 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	342 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	343 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	344 exit(3)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	345 else:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	346 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	347
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	348 # 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	349 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	350 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	351 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	352 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	353 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	354 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	355 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	356 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	357 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	358 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	359 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	360 # 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	361 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	362 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	363 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	364 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	365 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	366 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	367 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	368 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	369 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	370 comment_lines = table['comment_lines'] if 'comment_lines' in table else 0
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	371 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	372 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	373 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	374 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	375 else:
3e3b3c883bec planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty jjohnson parents: 0 diff changeset	376 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	377 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	378 indexes = table['index'] if 'index' in table else []
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	379 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	380 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	381 create_table(conn, path, table_name, pkey_autoincr=pkey_autoincr, column_names=column_names,
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	382 skip=comment_lines, comment_char=comment_char, load_named_columns=load_named_columns,
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	383 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	384 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	385 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	386 conn.close()
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	387
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	388 query = None
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	389 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	390 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	391 query = ''
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	392 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	393 query += line
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	394 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	395 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	396
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	397 if (query is None):
6 03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	398 tables_query = \
03842a4f71c6 Uploaded jjohnson parents: 5 diff changeset	399 "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	400 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	401 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	402 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	403 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	404 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	405 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	406 try:
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	407 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	408 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	409 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	410 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	411 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	412 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	413 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	414 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	415 exit(0)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	416 # 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	417 # 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	418 # exit(2)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	419 try:
16 5b4f6cf857cf Uploaded jjohnson parents: 11 diff changeset	420 run_query(query,outputFile)
0 926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	421 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	422 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	423 exit(1)
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	424
926c62f7fa09 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty jjohnson parents: diff changeset	425 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	426 __main__()

Mercurial > repos > jjohnson > query_tabular

annotate query_tabular.py @ 18:b9f797bf4f38 draft