Mercurial > repos > jjohnson > query_tabular
changeset 10:98bd1e29d669 draft default tip
planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 29288f94a382686e263623cf6ddcd235ed5f2310-dirty
| author | jjohnson |
|---|---|
| date | Wed, 20 Apr 2016 15:44:33 -0400 |
| parents | b7f149b4792f |
| children | |
| files | query_tabular.py query_tabular.xml |
| diffstat | 2 files changed, 34 insertions(+), 23 deletions(-) [+] |
line wrap: on
line diff
--- a/query_tabular.py Fri Apr 15 16:48:50 2016 -0400 +++ b/query_tabular.py Wed Apr 20 15:44:33 2016 -0400 @@ -14,16 +14,11 @@ - could read column names from comment lines, but issues with legal names - could add some transformations on tabular columns, e.g. a regex to format date/time strings - index: ['c2','c4,c5'] - unique: ['c1'] format: { c2 : re.sub('pat', 'sub', c2) c3 : len(c3) } def format(colname,val, expr): - -- allow optional autoincrement id column - user supplied name? - autoincrement : 'id' - column_defs dict of columns to create from tabular input column_defs : { 'name1' : 'expr', 'name2' : 'expr'} - allow multiple queries and outputs @@ -33,7 +28,10 @@ { file_path : '/home/galaxy/dataset_101.dat', table_name : 't1', column_names : ['c1', 'c2', 'c3'], + pkey_autoincr : 'id' comment_lines : 1 + unique: ['c1'], + index: ['c2','c3'] }, { file_path : '/home/galaxy/dataset_102.dat', table_name : 'gff', @@ -119,10 +117,13 @@ return col_names, col_types, col_def, col_idx -def create_table(conn, file_path, table_name, skip=0, comment_char='#', column_names=None,load_named_columns=False,unique_indexes=[],indexes=[]): +def create_table(conn, file_path, table_name, skip=0, comment_char='#', pkey_autoincr=None, column_names=None,load_named_columns=False,unique_indexes=[],indexes=[]): col_names, col_types, col_def, col_idx = get_column_def(file_path, table_name, skip=skip, comment_char=comment_char, column_names=column_names,load_named_columns=load_named_columns) col_func = [float if t == 'REAL' else int if t == 'INTEGER' else str for t in col_types] - table_def = 'CREATE TABLE %s (\n %s\n);' % (table_name, ', \n '.join(col_def)) + table_def = 'CREATE TABLE %s (\n %s%s\n);' % ( + table_name, + '%s INTEGER PRIMARY KEY AUTOINCREMENT,' % pkey_autoincr if pkey_autoincr else '', + ', \n '.join(col_def)) # print >> sys.stdout, table_def insert_stmt = 'INSERT INTO %s(%s) VALUES(%s)' % (table_name, ','.join(col_names), ','.join(["?" for x in col_names])) # print >> sys.stdout, insert_stmt @@ -243,7 +244,8 @@ load_named_columns = False unique_indexes = table['unique'] if 'unique' in table else [] indexes = table['index'] if 'index' in table else [] - create_table(conn, path, table_name, column_names=column_names, + pkey_autoincr = table['pkey_autoincr'] if 'pkey_autoincr' in table else None + create_table(conn, path, table_name, pkey_autoincr=pkey_autoincr, column_names=column_names, skip=comment_lines, load_named_columns=load_named_columns, unique_indexes=unique_indexes, indexes=indexes) except Exception, exc:
--- a/query_tabular.xml Fri Apr 15 16:48:50 2016 -0400 +++ b/query_tabular.xml Wed Apr 20 15:44:33 2016 -0400 @@ -1,4 +1,4 @@ -<tool id="query_tabular" name="Query Tabular" version="0.1.2"> +<tool id="query_tabular" name="Query Tabular" version="0.1.3"> <description>using sqlite sql</description> <requirements> @@ -41,6 +41,9 @@ ## #if $tbl.tbl_opts.sel_cols: ## #set $jtbl['sel_cols'] = $tbl.tbl_opts.sel_cols el_cols ## #end if + #if $tbl.tbl_opts.pkey_autoincr: + #set $jtbl['pkey_autoincr'] = str($tbl.tbl_opts.pkey_autoincr) + #end if #if $tbl.tbl_opts.col_names: #set $col_names = str($tbl.tbl_opts.col_names) #if $tbl.tbl_opts.load_named_columns: @@ -77,21 +80,25 @@ </configfiles> <inputs> <param name="workdb" type="hidden" value="workdb.sqlite" label=""/> - <repeat name="tables" title="Add tables" min="1"> - <param name="table" type="data" format="tabular" label="Dataset"/> + <repeat name="tables" title="Database Table" min="1"> + <param name="table" type="data" format="tabular" label="Tabular Dataset for Table"/> <section name="tbl_opts" expanded="false" title="Table Options"> - <param name="table_name" type="text" value="" optional="true" label="Table name"> - <help>By default, tables will be named: t1,t2,...,tn</help> + <param name="table_name" type="text" value="" optional="true" label="Specify Name for Table"> + <help>By default, tables will be named: t1,t2,...,tn (table names must be unique)</help> <validator type="regex" message="Table name should start with a letter and may contain additional letters, digits, and underscores">^[A-Za-z]\w*$</validator> </param> - <param name="col_names" type="text" value="" optional="true" label="Column names"> - <help>By default, table columns will be named: c1,c2,c3,...,cn</help> + <param name="col_names" type="text" value="" optional="true" label="Specify Column Names"> + <help>By default, table columns will be named: c1,c2,c3,...,cn (column names for a table must be unique)</help> <sanitizer sanitize="False"/> <validator type="regex" message="A List of names separated by commas: Column names should start with a letter and may contain additional letters, digits, and underscores. Otherwise, the name must be eclosed in: double quotes, back quotes, or square brackets.">^([A-Za-z]\w*|"\S+[^,"]*"|`\S+[^,`]*`|[[]\S+[^,"]*[]])?(,([A-Za-z]\w*|"\S+.*"|`\S+[^,`]*`|[[]\S+[^,"]*[]])?)*$</validator> </param> - <param name="load_named_columns" type="boolean" truevalue="load_named_columns" falsevalue="" checked="false" label="Only load named columns into database"/> - <param name="skip_lines" type="integer" value="" min="0" optional="true" label="Skip lines" help="Leave blank to use the datatype comment lines metadata" /> - <repeat name="indexes"> + <param name="load_named_columns" type="boolean" truevalue="load_named_columns" falsevalue="" checked="false" label="Only load the columns you have named into database"/> + <param name="pkey_autoincr" type="text" value="" optional="true" label="Add an auto increment primary key column with this name" + help="Only creates this additional column when a name is entered. (This can not be the same name as any of the other columns in this table.)"> + <validator type="regex" message="Column name">^([A-Za-z]\w*)?$</validator> + </param> + <param name="skip_lines" type="integer" value="" min="0" optional="true" label="Skip lines" help="Leave blank to use the comment lines metadata for this dataset" /> + <repeat name="indexes" title="Table Index"> <param name="unique" type="boolean" truevalue="yes" falsevalue="no" checked="False" label="This is a unique index"/> <param name="index_columns" type="text" value="" label="Index on Columns"> <help>Create an index on the column names: e,g, c1 or c2,c4</help> @@ -100,14 +107,13 @@ </repeat> </section> </repeat> - <param name="sqlquery" type="text" area="true" size="10x80" value="" optional="true" label="SQL Query"> - <help>By default, tables will be named: t1,t2,...,tn</help> + <param name="save_db" type="boolean" truevalue="yes" falsevalue="no" checked="false" label="Save the sqlite database in your history"/> + <param name="sqlquery" type="text" area="true" size="10x80" value="" optional="true" label="SQL Query to generate tabular output"> + <help>By default: tables are named: t1,t2,...,tn and columns in each table: c1,c2,...,cn</help> <sanitizer sanitize="False"/> <validator type="regex" message="">^(?ims)\s*select\s+.*\s+from\s+.*$</validator> </param> - <param name="no_header" type="boolean" truevalue="-n" falsevalue="" checked="False" label="Omit column headers"/> - - <param name="save_db" type="boolean" truevalue="yes" falsevalue="no" checked="false" label="Save the sqlite database"/> + <param name="no_header" type="boolean" truevalue="-n" falsevalue="" checked="False" label="Omit column headers from tabular output"/> </inputs> <outputs> <data format="sqlite" name="sqlitedb" label="sqlite db of ${on_string}"> @@ -186,6 +192,9 @@ The SQLite_ data base can also be saved and output as a dataset in the history. + *(The* **SQLite to tabular** *tool can run additional queries on this database.)* + + For help in using SQLite_ see: http://www.sqlite.org/docs.html