Mercurial > repos > jjohnson > sqlite_to_tabular

annotate sqlite_to_tabular.py @ 3:f079ea3884b3 draft default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
author jjohnson
date Thu, 21 Jan 2016 09:34:45 -0500
parents bc50a7b7f246
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
1 #!/usr/bin/env python
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
2
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
3 import sys
3
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
4 import re
0
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
5 import os.path
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
6 import optparse
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
7 import sqlite3 as sqlite
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
8
3
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
9
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
10 def regex_match(expr, item):
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
11 return re.match(expr, item) is not None
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
12
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
13
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
14 def regex_search(expr, item):
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
15 return re.search(expr, item) is not None
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
16
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
17
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
18 def regex_sub(expr, replace, item):
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
19 return re.sub(expr, replace, item)
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
20
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
21
0
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
22 def __main__():
3
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
23 # Parse Command Line
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
24 parser = optparse.OptionParser()
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
25 parser.add_option('-s', '--sqlitedb', dest='sqlitedb', default=None, help='The SQLite Database')
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
26 parser.add_option('-q', '--query', dest='query', default=None, help='SQL query')
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
27 parser.add_option('-Q', '--query_file', dest='query_file', default=None, help='SQL query file')
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
28 parser.add_option('-n', '--no_header', dest='no_header', action='store_true', default=False, help='Include a column headers line')
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
29 parser.add_option('-o', '--output', dest='output', default=None, help='Output file for query results')
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
30 (options, args) = parser.parse_args()
0
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
31
3
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
32 # determine output destination
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
33 if options.output is not None:
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
34 try:
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
35 outputPath = os.path.abspath(options.output)
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
36 outputFile = open(outputPath, 'w')
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
37 except Exception, e:
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
38 print >> sys.stderr, "failed: %s" % e
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
39 exit(3)
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
40 else:
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
41 outputFile = sys.stdout
0
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
42
3
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
43 query = None
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
44 if (options.query_file is not None):
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
45 with open(options.query_file, 'r') as fh:
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
46 query = ''
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
47 for line in fh:
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
48 query += line
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
49 elif (options.query is not None):
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
50 query = options.query
2
bc50a7b7f246 Uploaded
jjohnson
parents: 0
diff changeset
51
3
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
52 if (query is None):
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
53 try:
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
54 conn = sqlite.connect(options.sqlitedb)
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
55 c = conn.cursor()
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
56 tables_query = "SELECT name,sql FROM sqlite_master WHERE type='table' ORDER BY name"
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
57 rslt = c.execute(tables_query).fetchall()
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
58 for table, sql in rslt:
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
59 print >> sys.stderr, "Table %s:" % table
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
60 try:
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
61 col_query = 'SELECT * FROM %s LIMIT 0' % table
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
62 cur = conn.cursor().execute(col_query)
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
63 cols = [col[0] for col in cur.description]
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
64 print >> sys.stderr, " Columns: %s" % cols
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
65 except Exception, exc:
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
66 print >> sys.stderr, "Error: %s" % exc
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
67 except Exception, exc:
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
68 print >> sys.stderr, "Error: %s" % exc
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
69 exit(0)
0
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
70 try:
3
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
71 conn = sqlite.connect(options.sqlitedb)
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
72 conn.create_function("re_match", 2, regex_match)
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
73 conn.create_function("re_search", 2, regex_search)
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
74 conn.create_function("re_sub", 3, regex_sub)
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
75 cur = conn.cursor()
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
76 results = cur.execute(query)
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
77 if not options.no_header:
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
78 outputFile.write("#%s\n" % '\t'.join([str(col[0]) for col in cur.description]))
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
79 for i, row in enumerate(results):
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
80 outputFile.write("%s\n" % '\t'.join([str(val) for val in row]))
0
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
81 except Exception, exc:
3
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
82 print >> sys.stderr, "Error: %s" % exc
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
83 exit(1)
0
30a37dd92ccd Uploaded
jjohnson
parents:
diff changeset
84
3
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
85 if __name__ == "__main__":
f079ea3884b3 planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
jjohnson
parents: 2
diff changeset
86 __main__()