changeset 2:bc50a7b7f246 draft

Uploaded

--- a/sqlite_to_tabular.py	Sun Oct 04 09:50:54 2015 -0400
+++ b/sqlite_to_tabular.py	Sun Oct 04 10:51:12 2015 -0400
@@ -10,6 +10,7 @@
   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()
@@ -25,7 +26,16 @@
   else:
     outputFile = sys.stdout
 
-  if (options.query is None):
+  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
+
+  if (query is None):
     try:
       conn = sqlite.connect(options.sqlitedb)
       c = conn.cursor()
@@ -42,14 +52,14 @@
           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):
+    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(options.query)
+    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]

--- a/sqlite_to_tabular.xml	Sun Oct 04 09:50:54 2015 -0400
+++ b/sqlite_to_tabular.xml	Sun Oct 04 10:51:12 2015 -0400
@@ -1,42 +1,46 @@
 <?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>
-        <sanitizer sanitize="False"/>
-    </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>
+    <description>for SQL query</description>
+    <stdio>
+        <exit_code range="1:" level="fatal" description="Error" />
+    </stdio>
+    <configfiles>
+        <configfile name="query_file">
+$sqlquery
+        </configfile>
+    </configfiles>
+
+    <command interpreter="python">
+    sqlite_to_tabular.py 
+    --sqlitedb="$sqlitedb" 
+    --query_file="$query_file"
+    $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>
+            <sanitizer sanitize="False"/>
+        </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>