comparison query_tabular.py @ 8:6d9c91071884 draft

planemo upload for repository https://github.com/jj-umn/galaxytools/tree/master/query_tabular commit 60d1a49c09f87c1c1ec6fecbe54aa226bdc695a7-dirty

7:aa2409ae9dc0

"""
TODO:
- 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
    c2 : re.sub('pat', 'sub', c2)
    c3 :
- column_defs dict of columns to create from tabular input
    column_defs : { 'name1' : 'expr', 'name2' : 'expr'}
- allow multiple queries and outputs
- add a --json input for table definitions (or yaml)
JSON config:

    for i, col_name in enumerate(col_names):
        col_def.append('%s %s' % (col_names[i], col_types[i]))
    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):
    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))
    # 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
    data_lines = 0
    try:
        c = conn.cursor()
        c.execute(table_def)
        with open(file_path, "r") as fh:
            for linenum, line in enumerate(fh):
                if linenum < skip or line.startswith(comment_char):
                    continue
                data_lines += 1

        c.close()
    except Exception, e:
        print >> sys.stderr, 'Failed: %s' % (e)
        exit(1)


def regex_match(expr, item):
    return re.match(expr, item) is not None

                    column_names = table['column_names'] if 'column_names' in table else None
                    if column_names:
                        load_named_columns = table['load_named_columns'] if 'load_named_columns' in table else False
                    else:   
                        load_named_columns = False
                    create_table(conn, path, table_name, column_names=column_names, skip=comment_lines,load_named_columns=load_named_columns)
        except Exception, exc:
            print >> sys.stderr, "Error: %s" % exc
    conn.close()

    query = None

8:6d9c91071884

"""
TODO:
- 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
- add a --json input for table definitions (or yaml)
JSON config:

    for i, col_name in enumerate(col_names):
        col_def.append('%s %s' % (col_names[i], col_types[i]))
    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=[]):
    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))
    # 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
    data_lines = 0
    try:
        c = conn.cursor()
        c.execute(table_def)
        conn.commit()
        c.close()
        for i,index in enumerate(unique_indexes):
            index_name='idx_uniq_%s_%d' % (table_name,i)
            index_columns = index.split(',')
            create_index(conn, table_name, index_name, index_columns,unique=True)
        for i,index in enumerate(indexes):
            index_name='idx_%s_%d' % (table_name,i)
            index_columns = index.split(',')
            create_index(conn, table_name, index_name, index_columns)
        c = conn.cursor()
        with open(file_path, "r") as fh:
            for linenum, line in enumerate(fh):
                if linenum < skip or line.startswith(comment_char):
                    continue
                data_lines += 1

        c.close()
    except Exception, e:
        print >> sys.stderr, 'Failed: %s' % (e)
        exit(1)

def create_index(conn, table_name, index_name, index_columns, unique=False):
    index_def = "CREATE %s INDEX %s on %s(%s)" % ('UNIQUE' if unique else '', index_name, table_name, ','.join(index_columns))
    c = conn.cursor()
    c.execute(index_def)
    conn.commit()
    c.close()

def regex_match(expr, item):
    return re.match(expr, item) is not None

                    column_names = table['column_names'] if 'column_names' in table else None
                    if column_names:
                        load_named_columns = table['load_named_columns'] if 'load_named_columns' in table else False
                    else:   
                        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, 
                                 skip=comment_lines, load_named_columns=load_named_columns, 
                                 unique_indexes=unique_indexes, indexes=indexes)
        except Exception, exc:
            print >> sys.stderr, "Error: %s" % exc
    conn.close()

    query = None