Mercurial > repos > iuc > sqlite_to_tabular

comparison load_db.py @ 0:859064f07be4 draft

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
author iuc
date Tue, 18 Jul 2017 09:07:26 -0400
parents
children c1b700bc0150
comparison
equal deleted inserted replaced
-1:000000000000 0:859064f07be4
1 #!/usr/bin/env python
2
3 from __future__ import print_function
4
5 import sys
6
7 from filters import TabularReader
8
9
10 def getValueType(val):
11 if val or 0. == val:
12 try:
13 int(val)
14 return 'INTEGER'
15 except:
16 try:
17 float(val)
18 return 'REAL'
19 except:
20 return 'TEXT'
21 return None
22
23
24 def get_column_def(file_path, table_name, skip=0, comment_char='#',
25 column_names=None, max_lines=100, load_named_columns=False,
26 filters=None):
27 col_pref = ['TEXT', 'REAL', 'INTEGER', None]
28 col_types = []
29 col_idx = None
30 try:
31 tr = TabularReader(file_path, skip=skip, comment_char=comment_char,
32 col_idx=None, filters=filters)
33 for linenum, fields in enumerate(tr):
34 if linenum > max_lines:
35 break
36 try:
37 while len(col_types) < len(fields):
38 col_types.append(None)
39 for i, val in enumerate(fields):
40 colType = getValueType(val)
41 if col_pref.index(colType) < col_pref.index(col_types[i]):
42 col_types[i] = colType
43 except Exception as e:
44 print('Failed at line: %d err: %s' % (linenum, e),
45 file=sys.stderr)
46 except Exception as e:
47 print('Failed: %s' % (e), file=sys.stderr)
48 for i, col_type in enumerate(col_types):
49 if not col_type:
50 col_types[i] = 'TEXT'
51 if column_names:
52 col_names = []
53 if load_named_columns:
54 col_idx = []
55 for i, cname in enumerate(
56 [cn.strip() for cn in column_names.split(',')]):
57 if cname != '':
58 col_idx.append(i)
59 col_names.append(cname)
60 col_types = [col_types[i] for i in col_idx]
61 else:
62 col_names = ['c%d' % i for i in range(1, len(col_types) + 1)]
63 for i, cname in enumerate(
64 [cn.strip() for cn in column_names.split(',')]):
65 if cname and i < len(col_names):
66 col_names[i] = cname
67 else:
68 col_names = ['c%d' % i for i in range(1, len(col_types) + 1)]
69 col_def = []
70 for i, col_name in enumerate(col_names):
71 col_def.append('%s %s' % (col_names[i], col_types[i]))
72 return col_names, col_types, col_def, col_idx
73
74
75 def create_table(conn, file_path, table_name, skip=0, comment_char='#',
76 pkey_autoincr=None, column_names=None,
77 load_named_columns=False, filters=None,
78 unique_indexes=[], indexes=[]):
79 col_names, col_types, col_def, col_idx = \
80 get_column_def(file_path, table_name, skip=skip,
81 comment_char=comment_char, column_names=column_names,
82 load_named_columns=load_named_columns, filters=filters)
83 col_func = [float if t == 'REAL' else int
84 if t == 'INTEGER' else str for t in col_types]
85 table_def = 'CREATE TABLE %s (\n %s%s\n);' % (
86 table_name,
87 '%s INTEGER PRIMARY KEY AUTOINCREMENT,' %
88 pkey_autoincr if pkey_autoincr else '',
89 ', \n '.join(col_def))
90 # print >> sys.stdout, table_def
91 insert_stmt = 'INSERT INTO %s(%s) VALUES(%s)' % (
92 table_name, ','.join(col_names),
93 ','.join(["?" for x in col_names]))
94 # print >> sys.stdout, insert_stmt
95 data_lines = 0
96 try:
97 c = conn.cursor()
98 c.execute(table_def)
99 conn.commit()
100 c.close()
101 for i, index in enumerate(unique_indexes):
102 index_name = 'idx_uniq_%s_%d' % (table_name, i)
103 index_columns = index.split(',')
104 create_index(conn, table_name, index_name, index_columns,
105 unique=True)
106 for i, index in enumerate(indexes):
107 index_name = 'idx_%s_%d' % (table_name, i)
108 index_columns = index.split(',')
109 create_index(conn, table_name, index_name, index_columns)
110 c = conn.cursor()
111 tr = TabularReader(file_path, skip=skip, comment_char=comment_char,
112 col_idx=col_idx, filters=filters)
113 for linenum, fields in enumerate(tr):
114 data_lines += 1
115 try:
116 vals = [col_func[i](x)
117 if x else None for i, x in enumerate(fields)]
118 c.execute(insert_stmt, vals)
119 except Exception as e:
120 print('Failed at line: %d err: %s' % (linenum, e),
121 file=sys.stderr)
122 conn.commit()
123 c.close()
124 except Exception as e:
125 exit('Error: %s' % (e))
126
127
128 def create_index(conn, table_name, index_name, index_columns, unique=False):
129 index_def = "CREATE %s INDEX %s on %s(%s)" % (
130 'UNIQUE' if unique else '', index_name,
131 table_name, ','.join(index_columns))
132 c = conn.cursor()
133 c.execute(index_def)
134 conn.commit()
135 c.close()