annotate load_db.py @ 10:dfe38b7c34ed draft

"planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
author iuc
date Thu, 24 Sep 2020 11:27:11 +0000
parents 4678715f7147
children c4d18aa4ec4a
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
1 #!/usr/bin/env python
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
2
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
3 from __future__ import print_function
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
4
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
5 import re
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
6 import sys
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
7
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
8 from filters import TabularReader
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
9
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
10
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
11 SQLITE_KEYWORDS = [
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
12 'ABORT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
13 'ACTION',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
14 'ADD',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
15 'AFTER',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
16 'ALL',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
17 'ALTER',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
18 'ANALYZE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
19 'AND',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
20 'AS',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
21 'ASC',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
22 'ATTACH',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
23 'AUTOINCREMENT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
24 'BEFORE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
25 'BEGIN',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
26 'BETWEEN',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
27 'BY',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
28 'CASCADE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
29 'CASE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
30 'CAST',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
31 'CHECK',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
32 'COLLATE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
33 'COLUMN',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
34 'COMMIT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
35 'CONFLICT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
36 'CONSTRAINT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
37 'CREATE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
38 'CROSS',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
39 'CURRENT_DATE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
40 'CURRENT_TIME',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
41 'CURRENT_TIMESTAMP',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
42 'DATABASE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
43 'DEFAULT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
44 'DEFERRABLE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
45 'DEFERRED',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
46 'DELETE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
47 'DESC',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
48 'DETACH',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
49 'DISTINCT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
50 'DROP',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
51 'EACH',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
52 'ELSE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
53 'END',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
54 'ESCAPE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
55 'EXCEPT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
56 'EXCLUSIVE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
57 'EXISTS',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
58 'EXPLAIN',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
59 'FAIL',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
60 'FOR',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
61 'FOREIGN',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
62 'FROM',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
63 'FULL',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
64 'GLOB',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
65 'GROUP',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
66 'HAVING',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
67 'IF',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
68 'IGNORE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
69 'IMMEDIATE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
70 'IN',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
71 'INDEX',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
72 'INDEXED',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
73 'INITIALLY',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
74 'INNER',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
75 'INSERT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
76 'INSTEAD',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
77 'INTERSECT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
78 'INTO',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
79 'IS',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
80 'ISNULL',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
81 'JOIN',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
82 'KEY',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
83 'LEFT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
84 'LIKE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
85 'LIMIT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
86 'MATCH',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
87 'NATURAL',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
88 'NO',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
89 'NOT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
90 'NOTNULL',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
91 'NULL',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
92 'OF',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
93 'OFFSET',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
94 'ON',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
95 'OR',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
96 'ORDER',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
97 'OUTER',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
98 'PLAN',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
99 'PRAGMA',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
100 'PRIMARY',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
101 'QUERY',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
102 'RAISE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
103 'RECURSIVE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
104 'REFERENCES',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
105 'REGEXP',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
106 'REINDEX',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
107 'RELEASE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
108 'RENAME',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
109 'REPLACE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
110 'RESTRICT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
111 'RIGHT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
112 'ROLLBACK',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
113 'ROW',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
114 'SAVEPOINT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
115 'SELECT',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
116 'SET',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
117 'TABLE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
118 'TEMP',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
119 'TEMPORARY',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
120 'THEN',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
121 'TO',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
122 'TRANSACTION',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
123 'TRIGGER',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
124 'UNION',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
125 'UNIQUE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
126 'UPDATE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
127 'USING',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
128 'VACUUM',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
129 'VALUES',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
130 'VIEW',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
131 'VIRTUAL',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
132 'WHEN',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
133 'WHERE',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
134 'WITH',
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
135 'WITHOUT'
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
136 ]
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
137
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
138
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
139 def get_valid_column_name(name):
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
140 valid_name = name
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
141 if not name or not name.strip():
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
142 return None
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
143 elif name.upper() in SQLITE_KEYWORDS:
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
144 valid_name = '"%s"' % name
7
b722161a845a planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit a4b0969b33a68a0ea9ba12291f6694aec24f13ed
iuc
parents: 2
diff changeset
145 elif re.match(r'^[a-zA-Z]\w*$', name):
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
146 pass
7
b722161a845a planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit a4b0969b33a68a0ea9ba12291f6694aec24f13ed
iuc
parents: 2
diff changeset
147 elif re.match(r'^"[^"]+"$', name):
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
148 pass
7
b722161a845a planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit a4b0969b33a68a0ea9ba12291f6694aec24f13ed
iuc
parents: 2
diff changeset
149 elif re.match(r'^\[[^\[\]]*\]$', name):
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
150 pass
7
b722161a845a planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit a4b0969b33a68a0ea9ba12291f6694aec24f13ed
iuc
parents: 2
diff changeset
151 elif re.match(r"^`[^`]+`$", name):
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
152 pass
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
153 elif name.find('"') < 0:
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
154 valid_name = '"%s"' % name
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
155 elif name.find('[') < 0 and name.find(']') < 0:
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
156 valid_name = '[%s]' % name
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
157 elif name.find('`') < 0:
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
158 valid_name = '`%s`' % name
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
159 elif name.find("'") < 0:
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
160 valid_name = "'%s'" % name
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
161 return valid_name
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
162
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
163
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
164 def getValueType(val):
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
165 if val or 0. == val:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
166 try:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
167 int(val)
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
168 return 'INTEGER'
2
150765965caa planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 4a3c9f195ba5d899b1a1ce5e80281cdf230f456a
iuc
parents: 1
diff changeset
169 except ValueError:
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
170 try:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
171 float(val)
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
172 return 'REAL'
2
150765965caa planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 4a3c9f195ba5d899b1a1ce5e80281cdf230f456a
iuc
parents: 1
diff changeset
173 except ValueError:
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
174 return 'TEXT'
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
175 return None
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
176
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
177
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
178 def get_column_def(file_path, table_name, skip=0, comment_char='#',
9
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
179 column_names=None, max_lines=1000, load_named_columns=False,
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
180 firstlinenames=False, filters=None):
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
181 col_pref = ['TEXT', 'REAL', 'INTEGER', None]
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
182 col_types = []
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
183 col_idx = None
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
184 col_names = []
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
185 try:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
186 tr = TabularReader(file_path, skip=skip, comment_char=comment_char,
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
187 col_idx=None, filters=filters)
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
188 for linenum, fields in enumerate(tr):
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
189 if linenum == 0 and firstlinenames:
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
190 col_names = [get_valid_column_name(name) or 'c%d' % (i + 1)
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
191 for i, name in enumerate(fields)]
10
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
192 # guarantee col_types in case of empty data
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
193 while len(col_types) < len(fields):
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
194 col_types.append(None)
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
195 continue
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
196 if linenum > max_lines:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
197 break
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
198 try:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
199 while len(col_types) < len(fields):
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
200 col_types.append(None)
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
201 for i, val in enumerate(fields):
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
202 colType = getValueType(val)
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
203 if col_pref.index(colType) < col_pref.index(col_types[i]):
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
204 col_types[i] = colType
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
205 except Exception as e:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
206 print('Failed at line: %d err: %s' % (linenum, e),
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
207 file=sys.stderr)
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
208 except Exception as e:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
209 print('Failed: %s' % (e), file=sys.stderr)
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
210 for i, col_type in enumerate(col_types):
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
211 if not col_type:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
212 col_types[i] = 'TEXT'
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
213 if not col_names:
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
214 col_names = ['c%d' % i for i in range(1, len(col_types) + 1)]
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
215 if column_names:
10
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
216 cnames = [cn.strip() for cn in column_names.split(',')]
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
217 if load_named_columns:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
218 col_idx = []
10
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
219 colnames = []
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
220 for i, cname in enumerate(cnames):
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
221 # guarantee col_types in case of empty data
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
222 if i >= len(col_types):
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
223 col_types.append('TEXT')
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
224 if cname != '':
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
225 col_idx.append(i)
10
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
226 colnames.append(cname)
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
227 col_types = [col_types[i] for i in col_idx]
10
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
228 col_names = colnames
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
229 else:
10
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
230 if col_names:
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
231 for i, cname in enumerate(cnames):
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
232 if cname and i < len(col_names):
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
233 col_names[i] = cname
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
234 else:
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
235 col_names = [x if x else 'c%d' % (i) for i, x in enumerate(cnames)]
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
236 col_def = []
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
237 for i, col_name in enumerate(col_names):
10
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
238 if i >= len(col_types):
dfe38b7c34ed "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 67db58361546009a77b2cbd140967fbc634d425b"
iuc
parents: 9
diff changeset
239 col_types.append('TEXT')
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
240 col_def.append('%s %s' % (col_names[i], col_types[i]))
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
241 return col_names, col_types, col_def, col_idx
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
242
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
243
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
244 def create_table(conn, file_path, table_name, skip=0, comment_char='#',
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
245 pkey_autoincr=None, column_names=None,
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
246 load_named_columns=False, firstlinenames=False,
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
247 filters=None, unique_indexes=[], indexes=[]):
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
248 col_names, col_types, col_def, col_idx = \
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
249 get_column_def(file_path, table_name, skip=skip,
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
250 comment_char=comment_char, column_names=column_names,
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
251 load_named_columns=load_named_columns,
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
252 firstlinenames=firstlinenames,
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
253 filters=filters)
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
254 col_func = [float if t == 'REAL' else int
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
255 if t == 'INTEGER' else str for t in col_types]
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
256 table_def = 'CREATE TABLE %s (\n %s%s\n);' % (
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
257 table_name,
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
258 '%s INTEGER PRIMARY KEY AUTOINCREMENT,' %
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
259 pkey_autoincr if pkey_autoincr else '',
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
260 ', \n '.join(col_def))
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
261 # print >> sys.stdout, table_def
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
262 insert_stmt = 'INSERT INTO %s(%s) VALUES(%s)' % (
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
263 table_name, ','.join(col_names),
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
264 ','.join(["?" for x in col_names]))
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
265 # print >> sys.stdout, insert_stmt
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
266 data_lines = 0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
267 try:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
268 c = conn.cursor()
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
269 c.execute(table_def)
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
270 conn.commit()
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
271 c.close()
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
272
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
273 c = conn.cursor()
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
274 tr = TabularReader(file_path, skip=skip, comment_char=comment_char,
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
275 col_idx=col_idx, filters=filters)
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
276 for linenum, fields in enumerate(tr):
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
277 if linenum == 0 and firstlinenames:
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
278 continue
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
279 data_lines += 1
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
280 try:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
281 vals = [col_func[i](x)
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
282 if x else None for i, x in enumerate(fields)]
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
283 c.execute(insert_stmt, vals)
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
284 except Exception as e:
9
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
285 print('Load %s Failed line: %d err: %s' % (file_path, linenum, e),
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
286 file=sys.stderr)
9
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
287 for i, val in enumerate(fields):
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
288 try:
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
289 col_func[i](val)
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
290 except Exception:
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
291 colType = getValueType(val)
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
292 col_func[i] = float if colType == 'REAL' else int if colType == 'INTEGER' else str
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
293 print('Changing %s from %s to %s' % (col_names[i], col_types[i], colType),
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
294 file=sys.stderr)
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
295 col_types[i] = colType
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
296 vals = [col_func[i](x)
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
297 if x else None for i, x in enumerate(fields)]
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
298 print('%s %s' % (insert_stmt, vals),
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
299 file=sys.stderr)
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
300 try:
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
301 c.execute(insert_stmt, vals)
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
302 except Exception as e:
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
303 print('Insert %s line: %d Failed err: %s' % (file_path, linenum, e),
4678715f7147 "planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit daa9af57fe07ee83a45ddc9f855716f9d14a8e12"
iuc
parents: 7
diff changeset
304 file=sys.stderr)
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
305 conn.commit()
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
306 c.close()
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
307 for i, index in enumerate(unique_indexes):
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
308 index_name = 'idx_uniq_%s_%d' % (table_name, i)
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
309 index_columns = index.split(',')
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
310 create_index(conn, table_name, index_name, index_columns,
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
311 unique=True)
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
312 for i, index in enumerate(indexes):
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
313 index_name = 'idx_%s_%d' % (table_name, i)
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
314 index_columns = index.split(',')
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
315 create_index(conn, table_name, index_name, index_columns)
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
316 except Exception as e:
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
317 exit('Error: %s' % (e))
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
318
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
319
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
320 def create_index(conn, table_name, index_name, index_columns, unique=False):
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
321 index_def = 'CREATE %s INDEX %s on %s(%s)' % (
0
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
322 'UNIQUE' if unique else '', index_name,
859064f07be4 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 74915fc9cee746bbce1c4b507e13231259de177d
iuc
parents:
diff changeset
323 table_name, ','.join(index_columns))
1
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
324 try:
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
325 c = conn.cursor()
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
326 c.execute(index_def)
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
327 conn.commit()
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
328 c.close()
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
329 except Exception as e:
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
330 print('Failed: %s err: %s' % (index_def, e), file=sys.stderr)
c1b700bc0150 planemo upload for repository https://github.com/galaxyproject/tools-iuc/tree/master/tools/query_tabular commit 81f69ad5f39223059c40501e55ac777d3feca845
iuc
parents: 0
diff changeset
331 raise(e)