Mercurial > repos > jjohnson > sqlite_to_tabular

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