# HG changeset patch
# User jjohnson
# Date 1453386885 18000
# Node ID f079ea3884b3e9ac2c4bda96f87760fa45137091
# Parent bc50a7b7f246cedc5d781e8f5ba06a95dfac577d
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
diff -r bc50a7b7f246 -r f079ea3884b3 sqlite_to_tabular.py
--- a/sqlite_to_tabular.py Sun Oct 04 10:51:12 2015 -0400
+++ b/sqlite_to_tabular.py Thu Jan 21 09:34:45 2016 -0500
@@ -1,74 +1,86 @@
#!/usr/bin/env python
import sys
+import re
import os.path
import optparse
import sqlite3 as sqlite
+
+def regex_match(expr, item):
+ return re.match(expr, item) is not None
+
+
+def regex_search(expr, item):
+ return re.search(expr, item) is not None
+
+
+def regex_sub(expr, replace, item):
+ return re.sub(expr, replace, item)
+
+
def __main__():
- #Parse Command Line
- parser = optparse.OptionParser()
- parser.add_option( '-s', '--sqlitedb', dest='sqlitedb', default=None, help='The SQLite Database' )
- parser.add_option( '-q', '--query', dest='query', default=None, help='SQL query' )
- parser.add_option( '-Q', '--query_file', dest='query_file', default=None, help='SQL query file' )
- parser.add_option( '-n', '--no_header', dest='no_header', action='store_true', default=False, help='Include a column headers line' )
- parser.add_option( '-o', '--output', dest='output', default=None, help='Output file for query results' )
- (options, args) = parser.parse_args()
+ # Parse Command Line
+ parser = optparse.OptionParser()
+ parser.add_option('-s', '--sqlitedb', dest='sqlitedb', default=None, help='The SQLite Database')
+ parser.add_option('-q', '--query', dest='query', default=None, help='SQL query')
+ parser.add_option('-Q', '--query_file', dest='query_file', default=None, help='SQL query file')
+ parser.add_option('-n', '--no_header', dest='no_header', action='store_true', default=False, help='Include a column headers line')
+ parser.add_option('-o', '--output', dest='output', default=None, help='Output file for query results')
+ (options, args) = parser.parse_args()
- # determine output destination
- if options.output != None:
- try:
- outputPath = os.path.abspath(options.output)
- outputFile = open(outputPath, 'w')
- except Exception, e:
- print >> sys.stderr, "failed: %s" % e
- exit(3)
- else:
- outputFile = sys.stdout
+ # determine output destination
+ if options.output is not None:
+ try:
+ outputPath = os.path.abspath(options.output)
+ outputFile = open(outputPath, 'w')
+ except Exception, e:
+ print >> sys.stderr, "failed: %s" % e
+ exit(3)
+ else:
+ outputFile = sys.stdout
- query = None
- if (options.query_file != None):
- with open(options.query_file,'r') as fh:
- query = ''
- for line in fh:
- query += line
- elif (options.query != None):
- query = options.query
+ query = None
+ if (options.query_file is not None):
+ with open(options.query_file, 'r') as fh:
+ query = ''
+ for line in fh:
+ query += line
+ elif (options.query is not None):
+ query = options.query
- if (query is None):
+ if (query is None):
+ try:
+ conn = sqlite.connect(options.sqlitedb)
+ c = conn.cursor()
+ tables_query = "SELECT name,sql FROM sqlite_master WHERE type='table' ORDER BY name"
+ rslt = c.execute(tables_query).fetchall()
+ for table, sql in rslt:
+ print >> sys.stderr, "Table %s:" % table
+ try:
+ col_query = 'SELECT * FROM %s LIMIT 0' % table
+ cur = conn.cursor().execute(col_query)
+ cols = [col[0] for col in cur.description]
+ print >> sys.stderr, " Columns: %s" % cols
+ except Exception, exc:
+ print >> sys.stderr, "Error: %s" % exc
+ except Exception, exc:
+ print >> sys.stderr, "Error: %s" % exc
+ exit(0)
try:
- conn = sqlite.connect(options.sqlitedb)
- c = conn.cursor()
- tables_query = "SELECT name,sql FROM sqlite_master WHERE type='table' ORDER BY name"
- rslt = c.execute(tables_query).fetchall()
- for table,sql in rslt:
- print >> sys.stderr, "Table %s:" % table
- try:
- col_query = 'SELECT * FROM %s LIMIT 0' % table
- cur = conn.cursor().execute(col_query)
- cols = [col[0] for col in cur.description]
- print >> sys.stderr, " Columns: %s" % cols
- except Exception, exc:
- print >> sys.stderr, "Error: %s" % exc
+ conn = sqlite.connect(options.sqlitedb)
+ conn.create_function("re_match", 2, regex_match)
+ conn.create_function("re_search", 2, regex_search)
+ conn.create_function("re_sub", 3, regex_sub)
+ cur = conn.cursor()
+ results = cur.execute(query)
+ if not options.no_header:
+ outputFile.write("#%s\n" % '\t'.join([str(col[0]) for col in cur.description]))
+ for i, row in enumerate(results):
+ outputFile.write("%s\n" % '\t'.join([str(val) for val in row]))
except Exception, exc:
- print >> sys.stderr, "Error: %s" % exc
- exit(0)
- #if not sqlite.is_read_only_query(query):
- # print >> sys.stderr, "Error: Must be a read only query"
- # exit(2)
- try:
- conn = sqlite.connect(options.sqlitedb)
- cur = conn.cursor()
- results = cur.execute(query)
- if not options.no_header:
- outputFile.write("#%s\n" % '\t'.join([str(col[0]) for col in cur.description]))
- # yield [col[0] for col in cur.description]
- for i,row in enumerate(results):
- # yield [val for val in row]
- outputFile.write("%s\n" % '\t'.join([str(val) for val in row]))
- except Exception, exc:
- print >> sys.stderr, "Error: %s" % exc
- exit(1)
+ print >> sys.stderr, "Error: %s" % exc
+ exit(1)
-if __name__ == "__main__": __main__()
-
+if __name__ == "__main__":
+ __main__()
diff -r bc50a7b7f246 -r f079ea3884b3 sqlite_to_tabular.xml
--- a/sqlite_to_tabular.xml Sun Oct 04 10:51:12 2015 -0400
+++ b/sqlite_to_tabular.xml Thu Jan 21 09:34:45 2016 -0500
@@ -4,19 +4,18 @@
+
$sqlquery
-
-
- sqlite_to_tabular.py
- --sqlitedb="$sqlitedb"
- --query_file="$query_file"
- $no_header
- --output="$query_results"
-
@@ -40,7 +39,64 @@
-
- Outputs the results of a query on a SQLite Database as a tabular file.
-
+