changeset 3:f079ea3884b3 draft default tip

planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty

--- 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__()

--- 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 @@
     <stdio>
         <exit_code range="1:" level="fatal" description="Error" />
     </stdio>
+    <command interpreter="python"><![CDATA[
+    sqlite_to_tabular.py 
+    --sqlitedb="$sqlitedb" 
+    --query_file="$query_file"
+    $no_header 
+    --output="$query_results"
+    ]]></command>
     <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">
@@ -40,7 +39,64 @@
             </output>
         </test>
     </tests>
-    <help>
-        Outputs the results of a query on a SQLite Database as a tabular file. 
-    </help>
+    <help><![CDATA[
+Outputs the results of a query on a SQLite_ Database as a tabular file. 
+
+In addition to the standard SQLite_functions_ regular_expression_ functions are included.
+
+For example, with SQLite table "customers":
+
+    =========== ========== ========== ===================== ========== ============
+    #CustomerID FirstName  LastName   Email                 BirthDate  Phone
+    =========== ========== ========== ===================== ========== ============
+    1           John       Smith      [e-mail]  1968-02-04 626 222-2222
+    2           Steven     Goldfish   [e-mail] 1974-04-04 323 455-4545
+    3           Paula      Brown      [e-mail]   1978-05-24 416 323-3232
+    4           James      Smith      [e-mail]    1980-10-20 416 323-8888
+    =========== ========== ========== ===================== ========== ============
+
+  ::
+
+    matching:      re_match('pattern',column) 
+
+    SELECT FirstName, LastName
+    FROM customers
+    WHERE re_match('^.*\.(net|org)$',Email)
+
+  Results:
+
+    =========== ==========
+    #FirstName  LastName
+    =========== ==========
+    Steven      Goldfish
+    Paula       Brown
+    =========== ==========
+
+
+  ::
+
+    searching:     re_search('pattern',column)
+    substituting:  re_sub('pattern','replacement,column)
+
+    SELECT FirstName, LastName, re_sub('^\d{2}(\d{2})-(\d\d)-(\d\d)','\3/\2/\1',BirthDate) as "DOB"
+    FROM customers
+    WHERE re_search('[hp]er',Email)
+
+  Results:
+
+    =========== ========== ==========
+    #FirstName  LastName   DOB
+    =========== ========== ==========
+    Steven      Goldfish   04/04/74
+    Paula       Brown      24/05/78
+    James       Smith      20/10/80
+    =========== ========== ==========
+
+
+
+.. _Regular_expression: https://docs.python.org/release/2.7/library/re.html
+.. _SQLite: http://www.sqlite.org/index.html
+.. _SQLite_functions: http://www.sqlite.org/docs.html
+
+    ]]></help>
 </tool>