changeset 0:30a37dd92ccd draft

Uploaded

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlite_to_tabular.py	Sat Oct 03 09:13:55 2015 -0400
@@ -0,0 +1,64 @@
+#!/usr/bin/env python
+
+import sys
+import os.path
+import optparse
+import sqlite3 as sqlite
+
+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( '-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
+
+  if (options.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(1)
+  #if not sqlite.is_read_only_query(options.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(options.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)
+
+if __name__ == "__main__": __main__()
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sqlite_to_tabular.xml	Sat Oct 03 09:13:55 2015 -0400
@@ -0,0 +1,41 @@
+<?xml version="1.0"?>
+<tool id="sqlite_to_tabular" name="SQLite to tabular" version="0.0.1">
+  <description>for SQL query</description>
+  <stdio>
+    <exit_code range="1:" level="fatal" description="Error" />
+  </stdio>
+  <command interpreter="python">
+  sqlite_to_tabular.py 
+  --sqlitedb="$sqlitedb" 
+  --query='$sqlquery'
+  $no_header 
+  --output="$query_results"
+  </command>
+  <inputs>
+    <param name="sqlitedb" type="data" format="sqlite" label="SQLite Database"/>
+    <param name="sqlquery" type="text" area="True" size="120x20" label="SQL query">
+        <validator type="regex">^(?im)\s*SELECT\s.*\sFROM\s.*$</validator>
+    </param>
+    <param name="no_header" type="boolean" truevalue="-n" falsevalue="" checked="False" label="Omit column headers"/>
+  </inputs>
+  <outputs>
+    <data name="query_results" format="tabular" />
+  </outputs>
+  <tests>
+    <!--
+    -->
+    <test>
+      <param name="sqlitedb" ftype="sqlite" value="testdb.sqlite" />
+      <param name="sqlquery" value="SELECT first_name, last_name, age FROM contacts WHERE first_name = 'Sam'" />
+      <output name="query_results">
+        <assert_contents>
+          <has_text text="Smith" />
+          <not_has_text text="Doe" />
+        </assert_contents>
+      </output>
+    </test>
+  </tests>
+  <help>
+    Outputs the results of a query on a SQLite Database as a tabular file. 
+  </help>
+</tool>

Binary file test-data/testdb.sqlite has changed