Mercurial > repos > jjohnson > query_tabular
annotate query_tabular.py @ 3:125fc5d123b3
Remove \r\n chars from input when loading db table
| author | Jim Johnson <jj@umn.edu> |
|---|---|
| date | Wed, 04 Nov 2015 15:45:19 -0600 |
| parents | ffa5e34a55c1 |
| children |
| rev | line source |
|---|---|
| 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: | |
|
3
125fc5d123b3
Remove \r\n chars from input when loading db table
Jim Johnson <jj@umn.edu>
parents:
2
diff
changeset
|
97 fields = line.rstrip('\r\n').split('\t') |
| 0 | 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 |