Mercurial > repos > jjohnson > query_tabular

annotate query_tabular.py @ 7:aa2409ae9dc0 draft

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
author jjohnson
date Tue, 16 Feb 2016 09:33:46 -0500
parents afdbc7198353
children 6d9c91071884
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
1 #!/usr/bin/env python
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
2 """
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
3 """
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
4 import sys
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
5 import re
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
6 import os.path
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
7 import json
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
8 import sqlite3 as sqlite
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
9 import optparse
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
10 from optparse import OptionParser
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
11
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
12 """
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
13 TODO:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
14 - could read column names from comment lines, but issues with legal names
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
15 - could add some transformations on tabular columns,
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
16 e.g. a regex to format date/time strings
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
17 c2 : re.sub('pat', 'sub', c2)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
18 c3 :
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
19 - column_defs dict of columns to create from tabular input
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
20 column_defs : { 'name1' : 'expr', 'name2' : 'expr'}
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
21 - allow multiple queries and outputs
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
22 - add a --json input for table definitions (or yaml)
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
23 JSON config:
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
24 { tables : [
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
25 { file_path : '/home/galaxy/dataset_101.dat',
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
26 table_name : 't1',
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
27 column_names : ['c1', 'c2', 'c3'],
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
28 comment_lines : 1
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
29 },
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
30 { file_path : '/home/galaxy/dataset_102.dat',
7
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
31 table_name : 'gff',
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
32 column_names : ['seqname',,,'start','end']
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
33 comment_lines : 1
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
34 load_named_columns : True
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
35 },
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
36 { file_path : '/home/galaxy/dataset_103.dat',
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
37 table_name : 'test',
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
38 column_names : ['c1', 'c2', 'c3']
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
39 }
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
40 ]
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
41 }
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
42 """
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
43
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
44 tables_query = \
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
45 "SELECT name, sql FROM sqlite_master WHERE type='table' ORDER BY name"
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
46
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
47
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
48 def getValueType(val):
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
49 if val or 0. == val:
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
50 try:
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
51 int(val)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
52 return 'INTEGER'
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
53 except:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
54 try:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
55 float(val)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
56 return 'REAL'
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
57 except:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
58 return 'TEXT'
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
59 return None
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
60
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
61
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
62 def get_column_def(file_path, table_name, skip=0, comment_char='#',
7
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
63 column_names=None, max_lines=100,load_named_columns=False):
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
64 col_pref = ['TEXT', 'REAL', 'INTEGER', None]
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
65 col_types = []
7
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
66 col_idx = None
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
67 data_lines = 0
7
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
68
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
69 try:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
70 with open(file_path, "r") as fh:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
71 for linenum, line in enumerate(fh):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
72 if linenum < skip:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
73 continue
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
74 if line.startswith(comment_char):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
75 continue
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
76 data_lines += 1
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
77 try:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
78 fields = line.split('\t')
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
79 while len(col_types) < len(fields):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
80 col_types.append(None)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
81 for i, val in enumerate(fields):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
82 colType = getValueType(val)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
83 if col_pref.index(colType) < col_pref.index(col_types[i]):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
84 col_types[i] = colType
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
85 except Exception, e:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
86 print >> sys.stderr, 'Failed at line: %d err: %s' % (linenum, e)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
87 except Exception, e:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
88 print >> sys.stderr, 'Failed: %s' % (e)
7
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
89 for i,col_type in enumerate(col_types):
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
90 if not col_type:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
91 col_types[i] = 'TEXT'
7
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
92 if column_names:
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
93 col_names = []
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
94 if load_named_columns:
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
95 col_idx = []
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
96 for i, cname in enumerate([cn.strip() for cn in column_names.split(',')]):
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
97 if cname != '':
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
98 col_idx.append(i)
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
99 col_names.append(cname)
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
100 col_types = [col_types[i] for i in col_idx]
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
101 else:
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
102 col_names = ['c%d' % i for i in range(1, len(col_types) + 1)]
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
103 for i, cname in enumerate([cn.strip() for cn in column_names.split(',')]):
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
104 if cname and i < len(col_names):
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
105 col_names[i] = cname
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
106 else:
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
107 col_names = ['c%d' % i for i in range(1, len(col_types) + 1)]
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
108 col_def = []
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
109 for i, col_name in enumerate(col_names):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
110 col_def.append('%s %s' % (col_names[i], col_types[i]))
7
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
111 return col_names, col_types, col_def, col_idx
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
112
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
113
7
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
114 def create_table(conn, file_path, table_name, skip=0, comment_char='#', column_names=None,load_named_columns=False):
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
115 col_names, col_types, col_def, col_idx = get_column_def(file_path, table_name, skip=skip, comment_char=comment_char, column_names=column_names,load_named_columns=load_named_columns)
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
116 col_func = [float if t == 'REAL' else int if t == 'INTEGER' else str for t in col_types]
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
117 table_def = 'CREATE TABLE %s (\n %s\n);' % (table_name, ', \n '.join(col_def))
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
118 # print >> sys.stdout, table_def
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
119 insert_stmt = 'INSERT INTO %s(%s) VALUES(%s)' % (table_name, ','.join(col_names), ','.join(["?" for x in col_names]))
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
120 # print >> sys.stdout, insert_stmt
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
121 data_lines = 0
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
122 try:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
123 c = conn.cursor()
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
124 c.execute(table_def)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
125 with open(file_path, "r") as fh:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
126 for linenum, line in enumerate(fh):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
127 if linenum < skip or line.startswith(comment_char):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
128 continue
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
129 data_lines += 1
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
130 try:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
131 fields = line.rstrip('\r\n').split('\t')
7
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
132 if col_idx:
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
133 fields = [fields[i] for i in col_idx]
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
134 vals = [col_func[i](x) if x else None for i, x in enumerate(fields)]
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
135 c.execute(insert_stmt, vals)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
136 except Exception, e:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
137 print >> sys.stderr, 'Failed at line: %d err: %s' % (linenum, e)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
138 conn.commit()
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
139 c.close()
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
140 except Exception, e:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
141 print >> sys.stderr, 'Failed: %s' % (e)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
142 exit(1)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
143
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
144
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
145 def regex_match(expr, item):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
146 return re.match(expr, item) is not None
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
147
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
148
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
149 def regex_search(expr, item):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
150 return re.search(expr, item) is not None
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
151
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
152
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
153 def regex_sub(expr, replace, item):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
154 return re.sub(expr, replace, item)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
155
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
156
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
157 def get_connection(sqlitedb_path, addfunctions=False):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
158 conn = sqlite.connect(sqlitedb_path)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
159 if addfunctions:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
160 conn.create_function("re_match", 2, regex_match)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
161 conn.create_function("re_search", 2, regex_search)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
162 conn.create_function("re_sub", 3, regex_sub)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
163 return conn
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
164
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
165
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
166 def __main__():
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
167 # Parse Command Line
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
168 parser = optparse.OptionParser()
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
169 parser.add_option('-s', '--sqlitedb', dest='sqlitedb', default=None, help='The SQLite Database')
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
170 parser.add_option('-t', '--table', dest='tables', action="append", default=[], help='Tabular file: file_path[=table_name[:column_name, ...]')
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
171 parser.add_option('-j', '--jsonfile', dest='jsonfile', default=None, help='Tabular file: file_path[=table_name[:column_name, ...]')
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
172 parser.add_option('-q', '--query', dest='query', default=None, help='SQL query')
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
173 parser.add_option('-Q', '--query_file', dest='query_file', default=None, help='SQL query file')
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
174 parser.add_option('-n', '--no_header', dest='no_header', action='store_true', default=False, help='Include a column headers line')
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
175 parser.add_option('-o', '--output', dest='output', default=None, help='Output file for query results')
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
176 (options, args) = parser.parse_args()
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
177
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
178 # open sqlite connection
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
179 conn = get_connection(options.sqlitedb)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
180 # determine output destination
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
181 if options.output is not None:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
182 try:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
183 outputPath = os.path.abspath(options.output)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
184 outputFile = open(outputPath, 'w')
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
185 except Exception, e:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
186 print >> sys.stderr, "failed: %s" % e
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
187 exit(3)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
188 else:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
189 outputFile = sys.stdout
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
190
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
191 # get table defs
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
192 if options.tables:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
193 for ti, table in enumerate(options.tables):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
194 table_name = 't%d' % (ti + 1)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
195 column_names = None
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
196 fields = table.split('=')
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
197 path = fields[0]
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
198 if len(fields) > 1:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
199 names = fields[1].split(':')
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
200 table_name = names[0] if names[0] else table_name
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
201 if len(names) > 1:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
202 column_names = names[1]
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
203 # print >> sys.stdout, '%s %s' % (table_name, path)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
204 create_table(conn, path, table_name, column_names=column_names)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
205 if options.jsonfile:
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
206 try:
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
207 fh = open(options.jsonfile)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
208 tdef = json.load(fh)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
209 if 'tables' in tdef:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
210 for ti, table in enumerate(tdef['tables']):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
211 path = table['file_path']
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
212 table_name = table['table_name'] if 'table_name' in table else 't%d' % (ti + 1)
7
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
213 comment_lines = table['comment_lines'] if 'comment_lines' in table else 0
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
214 column_names = table['column_names'] if 'column_names' in table else None
7
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
215 if column_names:
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
216 load_named_columns = table['load_named_columns'] if 'load_named_columns' in table else False
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
217 else:
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
218 load_named_columns = False
aa2409ae9dc0 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty
jjohnson
parents: 4
diff changeset
219 create_table(conn, path, table_name, column_names=column_names, skip=comment_lines,load_named_columns=load_named_columns)
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
220 except Exception, exc:
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
221 print >> sys.stderr, "Error: %s" % exc
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
222 conn.close()
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
223
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
224 query = None
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
225 if (options.query_file is not None):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
226 with open(options.query_file, 'r') as fh:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
227 query = ''
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
228 for line in fh:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
229 query += line
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
230 elif (options.query is not None):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
231 query = options.query
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
232
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
233 if (query is None):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
234 try:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
235 conn = get_connection(options.sqlitedb)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
236 c = conn.cursor()
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
237 rslt = c.execute(tables_query).fetchall()
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
238 for table, sql in rslt:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
239 print >> sys.stderr, "Table %s:" % table
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
240 try:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
241 col_query = 'SELECT * FROM %s LIMIT 0' % table
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
242 cur = conn.cursor().execute(col_query)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
243 cols = [col[0] for col in cur.description]
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
244 print >> sys.stderr, " Columns: %s" % cols
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
245 except Exception, exc:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
246 print >> sys.stderr, "Error: %s" % exc
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
247 except Exception, exc:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
248 print >> sys.stderr, "Error: %s" % exc
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
249 exit(0)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
250 # if not sqlite.is_read_only_query(query):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
251 # print >> sys.stderr, "Error: Must be a read only query"
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
252 # exit(2)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
253 try:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
254 conn = get_connection(options.sqlitedb, addfunctions=True)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
255 cur = conn.cursor()
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
256 results = cur.execute(query)
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
257 if not options.no_header:
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
258 outputFile.write("#%s\n" % '\t'.join([str(col[0]) for col in cur.description]))
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
259 # yield [col[0] for col in cur.description]
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
260 for i, row in enumerate(results):
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
261 # yield [val for val in row]
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
262 outputFile.write("%s\n" % '\t'.join([str(val) for val in row]))
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
263 except Exception, exc:
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
264 print >> sys.stderr, "Error: %s" % exc
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
265 exit(1)
0
763c4f968dc6 Uploaded
jjohnson
parents:
diff changeset
266
4
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
267 if __name__ == "__main__":
afdbc7198353 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 9ae87502ea7c3da33ecc453872c4eb2f41ecea4a-dirty
jjohnson
parents: 2
diff changeset
268 __main__()