Mercurial > repos > jjohnson > sqlite_to_tabular
changeset 0:cf32fc695e72 draft
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/sqlite_to_tabular commit 64a950cafd655311c99a54f96a27b49f0bdf2731-dirty
| author | jjohnson |
|---|---|
| date | Thu, 21 Jan 2016 09:35:35 -0500 |
| parents | |
| children | 78e9570fbe08 |
| files | sqlite_to_tabular.py sqlite_to_tabular.xml test-data/testdb.sqlite |
| diffstat | 3 files changed, 188 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlite_to_tabular.py Thu Jan 21 09:35:35 2016 -0500 @@ -0,0 +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() + + # 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 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): + 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) + 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(1) + +if __name__ == "__main__": + __main__()
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlite_to_tabular.xml Thu Jan 21 09:35:35 2016 -0500 @@ -0,0 +1,102 @@ +<?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"><![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> + <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><![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 John.Smith@yahoo.com 1968-02-04 626 222-2222 + 2 Steven Goldfish goldfish@fishhere.net 1974-04-04 323 455-4545 + 3 Paula Brown pb@herowndomain.org 1978-05-24 416 323-3232 + 4 James Smith jim@supergig.co.uk 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>