|
0
|
1 #!/usr/bin/env python
|
|
|
2 """
|
|
|
3 """
|
|
|
4 import sys
|
|
|
5 import os.path
|
|
|
6 import sqlite3 as sqlite
|
|
|
7 import optparse
|
|
|
8 from optparse import OptionParser
|
|
|
9
|
|
|
10 """
|
|
|
11 TODO:
|
|
|
12 - could add some transformations on tabular columns, e.g. a regex to format date/time strings
|
|
|
13 - allow multiple queries and outputs
|
|
|
14 - add a --json input for table definitions (or yaml)
|
|
|
15 JSON config:
|
|
|
16 { tables : [
|
|
|
17 { file_path : '/home/galaxy/dataset_101.dat',
|
|
|
18 table_name : 't1',
|
|
|
19 column_names : ['c1','c2','c3']
|
|
|
20 },
|
|
|
21 { file_path : '/home/galaxy/dataset_102.dat',
|
|
|
22 table_name : 't2',
|
|
|
23 column_names : ['c1','c2','c3']
|
|
|
24 }
|
|
|
25 ]
|
|
|
26 }
|
|
|
27 """
|
|
|
28
|
|
|
29 def getValueType(val):
|
|
|
30 if val or 0. == val:
|
|
|
31 try:
|
|
|
32 int(val)
|
|
|
33 return 'INTEGER'
|
|
|
34 except:
|
|
|
35 try:
|
|
|
36 float(val)
|
|
|
37 return 'REAL'
|
|
|
38 except:
|
|
|
39 return 'TEXT'
|
|
|
40 return None
|
|
|
41
|
|
|
42
|
|
|
43 def get_column_def(file_path,table_name,skip=0,comment_char='#',column_names=None,max_lines=100):
|
|
|
44 col_pref = ['TEXT','REAL','INTEGER',None]
|
|
|
45 col_types = []
|
|
|
46 data_lines = 0
|
|
|
47 try:
|
|
|
48 with open(file_path,"r") as fh:
|
|
|
49 for linenum,line in enumerate(fh):
|
|
|
50 if linenum < skip:
|
|
|
51 continue
|
|
|
52 if line.startswith(comment_char):
|
|
|
53 continue
|
|
|
54 data_lines += 1
|
|
|
55 try:
|
|
|
56 fields = line.split('\t')
|
|
|
57 while len(col_types) < len(fields):
|
|
|
58 col_types.append(None)
|
|
|
59 for i,val in enumerate(fields):
|
|
|
60 colType = getValueType(val)
|
|
|
61 if col_pref.index(colType) < col_pref.index(col_types[i]):
|
|
|
62 col_types[i] = colType
|
|
|
63 except Exception, e:
|
|
|
64 print >> sys.stderr, 'Failed at line: %d err: %s' % (linenum,e)
|
|
|
65 except Exception, e:
|
|
|
66 print >> sys.stderr, 'Failed: %s' % (e)
|
|
|
67 for i,col_type in enumerate(col_types):
|
|
|
68 if not col_type:
|
|
|
69 col_types[i] = 'TEXT'
|
|
|
70 col_names = ['c%d' % i for i in range(1,len(col_types) + 1)]
|
|
|
71 if column_names:
|
|
|
72 for i,cname in enumerate([cn.strip() for cn in column_names.split(',')]):
|
|
|
73 if cname and i < len(col_names):
|
|
|
74 col_names[i] = cname
|
|
|
75 col_def = []
|
|
|
76 for i,col_name in enumerate(col_names):
|
|
|
77 col_def.append('%s %s' % (col_names[i],col_types[i]))
|
|
|
78 return col_names,col_types,col_def
|
|
|
79
|
|
|
80 def create_table(conn,file_path,table_name,skip=0,comment_char='#',column_names=None):
|
|
|
81 col_names,col_types,col_def = get_column_def(file_path,table_name,skip=skip,comment_char=comment_char,column_names=column_names)
|
|
|
82 col_func = [float if t == 'REAL' else int if t == 'INTEGER' else str for t in col_types]
|
|
|
83 table_def = 'CREATE TABLE %s (\n %s\n);' % (table_name,',\n '.join(col_def))
|
|
|
84 # print >> sys.stdout, table_def
|
|
|
85 insert_stmt = 'INSERT INTO %s(%s) VALUES(%s)' % (table_name,','.join(col_names),','.join([ "?" for x in col_names]))
|
|
|
86 # print >> sys.stdout, insert_stmt
|
|
|
87 data_lines = 0
|
|
|
88 try:
|
|
|
89 c = conn.cursor()
|
|
|
90 c.execute(table_def)
|
|
|
91 with open(file_path,"r") as fh:
|
|
|
92 for linenum,line in enumerate(fh):
|
|
|
93 if linenum < skip or line.startswith(comment_char):
|
|
|
94 continue
|
|
|
95 data_lines += 1
|
|
|
96 try:
|
|
|
97 fields = line.split('\t')
|
|
|
98 vals = [col_func[i](x) if x else None for i,x in enumerate(fields)]
|
|
|
99 c.execute(insert_stmt,vals)
|
|
|
100 except Exception, e:
|
|
|
101 print >> sys.stderr, 'Failed at line: %d err: %s' % (linenum,e)
|
|
|
102 conn.commit()
|
|
|
103 c.close()
|
|
|
104 except Exception, e:
|
|
|
105 print >> sys.stderr, 'Failed: %s' % (e)
|
|
1
|
106 exit(1)
|
|
0
|
107
|
|
|
108 def __main__():
|
|
|
109 #Parse Command Line
|
|
|
110 parser = optparse.OptionParser()
|
|
|
111 parser.add_option( '-s', '--sqlitedb', dest='sqlitedb', default=None, help='The SQLite Database' )
|
|
|
112 parser.add_option( '-t', '--table', dest='tables', action="append", default=[], help='Tabular file: file_path[=table_name[:column_name,...]' )
|
|
|
113 parser.add_option( '-q', '--query', dest='query', default=None, help='SQL query' )
|
|
2
|
114 parser.add_option( '-Q', '--query_file', dest='query_file', default=None, help='SQL query file' )
|
|
0
|
115 parser.add_option( '-n', '--no_header', dest='no_header', action='store_true', default=False, help='Include a column headers line' )
|
|
|
116 parser.add_option( '-o', '--output', dest='output', default=None, help='Output file for query results' )
|
|
|
117 (options, args) = parser.parse_args()
|
|
|
118
|
|
|
119 # oprn sqlite connection
|
|
|
120 conn = sqlite.connect(options.sqlitedb)
|
|
|
121 # determine output destination
|
|
|
122 if options.output != None:
|
|
|
123 try:
|
|
|
124 outputPath = os.path.abspath(options.output)
|
|
|
125 outputFile = open(outputPath, 'w')
|
|
|
126 except Exception, e:
|
|
|
127 print >> sys.stderr, "failed: %s" % e
|
|
|
128 exit(3)
|
|
|
129 else:
|
|
|
130 outputFile = sys.stdout
|
|
|
131
|
|
|
132 # determine output destination
|
|
|
133 for ti,table in enumerate(options.tables):
|
|
|
134 table_name = 't%d' % (ti + 1)
|
|
|
135 column_names = None
|
|
|
136 fields = table.split('=')
|
|
|
137 path = fields[0]
|
|
|
138 if len(fields) > 1:
|
|
|
139 names = fields[1].split(':')
|
|
|
140 table_name = names[0] if names[0] else table_name
|
|
|
141 if len(names) > 1:
|
|
|
142 column_names = names[1]
|
|
|
143 # print >> sys.stdout, '%s %s' % (table_name, path)
|
|
|
144 create_table(conn,path,table_name,column_names=column_names)
|
|
|
145 conn.close()
|
|
|
146
|
|
2
|
147 query = None
|
|
|
148 if (options.query_file != None):
|
|
|
149 with open(options.query_file,'r') as fh:
|
|
|
150 query = ''
|
|
|
151 for line in fh:
|
|
|
152 query += line
|
|
|
153 elif (options.query != None):
|
|
|
154 query = options.query
|
|
|
155
|
|
|
156 if (query is None):
|
|
0
|
157 try:
|
|
|
158 conn = sqlite.connect(options.sqlitedb)
|
|
|
159 c = conn.cursor()
|
|
|
160 tables_query = "SELECT name,sql FROM sqlite_master WHERE type='table' ORDER BY name"
|
|
|
161 rslt = c.execute(tables_query).fetchall()
|
|
|
162 for table,sql in rslt:
|
|
|
163 print >> sys.stderr, "Table %s:" % table
|
|
|
164 try:
|
|
|
165 col_query = 'SELECT * FROM %s LIMIT 0' % table
|
|
|
166 cur = conn.cursor().execute(col_query)
|
|
|
167 cols = [col[0] for col in cur.description]
|
|
|
168 print >> sys.stderr, " Columns: %s" % cols
|
|
|
169 except Exception, exc:
|
|
|
170 print >> sys.stderr, "Error: %s" % exc
|
|
|
171 except Exception, exc:
|
|
|
172 print >> sys.stderr, "Error: %s" % exc
|
|
|
173 exit(0)
|
|
2
|
174 #if not sqlite.is_read_only_query(query):
|
|
0
|
175 # print >> sys.stderr, "Error: Must be a read only query"
|
|
|
176 # exit(2)
|
|
|
177 try:
|
|
|
178 conn = sqlite.connect(options.sqlitedb)
|
|
|
179 cur = conn.cursor()
|
|
2
|
180 results = cur.execute(query)
|
|
0
|
181 if not options.no_header:
|
|
|
182 outputFile.write("#%s\n" % '\t'.join([str(col[0]) for col in cur.description]))
|
|
|
183 # yield [col[0] for col in cur.description]
|
|
|
184 for i,row in enumerate(results):
|
|
|
185 # yield [val for val in row]
|
|
|
186 outputFile.write("%s\n" % '\t'.join([str(val) for val in row]))
|
|
|
187 except Exception, exc:
|
|
|
188 print >> sys.stderr, "Error: %s" % exc
|
|
1
|
189 exit(1)
|
|
0
|
190
|
|
|
191 if __name__ == "__main__": __main__()
|
|
|
192
|
|
|
193
|
