コード例 #1
0
def cloudsql_append_column(table, missing_column_names, all_columns,
                           inputfilename):
    columns = filter(lambda column: column['NAME'] in missing_column_names,
                     all_columns)

    alter_stmt = 'ALTER TABLE {0} ADD '.format(table)

    for idx, column in enumerate(columns):
        if column['TYPE'] == 'STRING':
            column['TYPE'] = 'VARCHAR(200)'
        if idx == 0:
            alter_stmt += '({0} {1}'.format(column['NAME'], column['TYPE'])
        else:
            alter_stmt += ',{0} {1}'.format(column['NAME'], column['TYPE'])

    alter_stmt += ');'
    # print alter_stmt
    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute(alter_stmt)
    db.commit()

    # TODO: add data from inputfile

    cursor.close()
    db.close()
コード例 #2
0
def insert_feature_defs(sql_table, study_id, name, bq_mapping, shared_map_id, type):
    insert_stmt = 'INSERT INTO {0} (study_id, feature_name, bq_map_id, shared_map_id, is_numeric) VALUES (%s,%s,%s,%s,%s);'
    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.executemany(insert_stmt, (study_id, name, bq_mapping, shared_map_id, type))
    db.commit()
    cursor.close()
    db.close()
コード例 #3
0
def insert_feature_defs_list(sql_table, data_list):
    insert_stmt = 'INSERT INTO {0} (study_id, feature_name, bq_map_id, shared_map_id, is_numeric) VALUES (%s,%s,%s,%s,%s);'.format(sql_table)
    print insert_stmt
    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.executemany(insert_stmt, data_list)
    db.commit()
    cursor.close()
    db.close()
コード例 #4
0
def insert_feature_defs(sql_table, study_id, name, bq_mapping, shared_map_id,
                        type):
    insert_stmt = 'INSERT INTO {0} (study_id, feature_name, bq_map_id, shared_map_id, is_numeric) VALUES (%s,%s,%s,%s,%s);'
    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.executemany(insert_stmt,
                       (study_id, name, bq_mapping, shared_map_id, type))
    db.commit()
    cursor.close()
    db.close()
コード例 #5
0
def insert_feature_defs_list(sql_table, data_list):
    insert_stmt = 'INSERT INTO {0} (study_id, feature_name, bq_map_id, shared_map_id, is_numeric) VALUES (%s,%s,%s,%s,%s);'.format(
        sql_table)
    print insert_stmt
    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.executemany(insert_stmt, data_list)
    db.commit()
    cursor.close()
    db.close()
コード例 #6
0
def update_molecular_metadata_samples_list(table, datatype, sample_barcodes):
    insert_stmt = 'INSERT INTO {0} (sample_barcode, has_{1}) VALUES (%s, %s) ON DUPLICATE KEY UPDATE has_{2}=1;'.format(table, datatype, datatype)
    value_list = []
    for barcode in sample_barcodes:
        value_list.append((barcode, 1))
    print insert_stmt
    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.executemany(insert_stmt, value_list)
    db.commit()
    cursor.close()
    db.close()
コード例 #7
0
def update_molecular_metadata_samples_list(table, datatype, sample_barcodes):
    insert_stmt = 'INSERT INTO {0} (sample_barcode, has_{1}) VALUES (%s, %s) ON DUPLICATE KEY UPDATE has_{2}=1;'.format(
        table, datatype, datatype)
    value_list = []
    for barcode in sample_barcodes:
        value_list.append((barcode, 1))
    print insert_stmt
    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.executemany(insert_stmt, value_list)
    db.commit()
    cursor.close()
    db.close()
コード例 #8
0
def delete_test_tables(user_id=1, project_id=1):
    db = cloudsql_connector()
    cursor = db.cursor()

    delete_stmt = 'DROP TABLE user_metadata_{0}_{1}'.format(
        user_id, project_id)
    cursor.execute(delete_stmt)
    delete_stmt = 'DROP TABLE user_metadata_samples_{0}_{1}'.format(
        user_id, project_id)
    cursor.execute(delete_stmt)
    delete_stmt = 'DROP TABLE user_feature_defs_{0}_{1}'.format(
        user_id, project_id)
    cursor.execute(delete_stmt)
コード例 #9
0
def check_update_metadata_samples(table, columns):
    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute('describe {0};'.format(table))
    column_names = [d['NAME'] for d in columns]

    # For each column in the table, remove it from the list of given column names
    for row in cursor.fetchall():
        if row['Field'] in column_names:
            column_names.remove(row['Field'])

    cursor.close()
    db.close()
    return column_names
コード例 #10
0
def check_update_metadata_samples(table, columns):
    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute('describe {0};'.format(table))
    column_names = [d['NAME'] for d in columns]

    # For each column in the table, remove it from the list of given column names
    for row in cursor.fetchall():
        if row['Field'] in column_names:
            column_names.remove(row['Field'])

    cursor.close()
    db.close()
    return column_names
コード例 #11
0
def insert_metadata_samples(data_df, table):
    columns = data_df.columns.values
    data_df = data_df.where((pd.notnull(data_df)), None)
    insert_stmt = 'INSERT INTO {0} ({1}) VALUES({2})'.format(
        table, ','.join(columns),
        ','.join(['%s' for i in range(0, len(columns))]))
    print insert_stmt
    value_list = []
    for i, j in data_df.transpose().iteritems():
        row = data_df[i:i + 1]
        value_list.append(tuple(row.values[0]))

    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.executemany(insert_stmt, value_list)
    db.commit()
    cursor.close()
    db.close()
コード例 #12
0
def insert_metadata_samples(data_df, table):
    columns = data_df.columns.values
    data_df = data_df.where((pd.notnull(data_df)), None)
    insert_stmt = 'INSERT INTO {0} ({1}) VALUES({2})'.format(table,
                                                             ','.join(columns),
                                                             ','.join(['%s' for i in range(0, len(columns))]))
    print insert_stmt
    value_list = []
    for i, j in data_df.transpose().iteritems():
        row = data_df[i:i+1]
        value_list.append(tuple(row.values[0]))

    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.executemany(insert_stmt, value_list)
    db.commit()
    cursor.close()
    db.close()
コード例 #13
0
def update_metadata_data(table, metadata):
    metadata_schema = user_metadata()
    column_titles = [d['column_name'] for d in metadata_schema]
    value_list = []
    for idx, title in enumerate(column_titles):
        if idx == 0:
            value_list.append((metadata[title]))
        else:
            value_list.append('\'' + str(metadata[title])+ '\'')

    insert_stmt = 'INSERT INTO {0} ({1}) VALUES ({2});'.format(table, ','.join(column_titles), ','.join(value_list))
    # print insert_stmt
    # print value_list

    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute(insert_stmt)
    db.commit()
    cursor.close()
    db.close()
コード例 #14
0
def create_test_tables(user_id=1, project_id=1):
    db = cloudsql_connector()

    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    create_col_template = '%s %s\n\t'

    # Create user_metdata
    user_metadata_schema = user_metadata()
    column_definitions = ','.join([
        create_col_template % (column['column_name'], column['type'])
        for column in user_metadata_schema
    ])
    create_stmt = 'CREATE TABLE IF NOT EXISTS user_metadata_{0}_{1} ({2});'.format(
        user_id, project_id, column_definitions)
    # print create_stmt
    cursor.execute(create_stmt)

    # Create user_metadata_sample
    user_metadata_sample_schema = user_metadata_sample()
    column_definitions = ','.join([
        create_col_template % (column['column_name'], column['type'])
        for column in user_metadata_sample_schema
    ])
    create_stmt = 'CREATE TABLE IF NOT EXISTS user_metadata_samples_{0}_{1} ({2});'.format(
        user_id, project_id, column_definitions)
    # print create_stmt
    cursor.execute(create_stmt)

    # Create user_feature_def
    user_feature_def_schema = user_feature_def()
    column_definitions = ','.join([
        create_col_template % (column['column_name'], column['type'])
        for column in user_feature_def_schema
    ])
    create_stmt = 'CREATE TABLE IF NOT EXISTS user_feature_defs_{0}_{1} ({2});'.format(
        user_id, project_id, column_definitions)
    # print create_stmt
    cursor.execute(create_stmt)

    cursor.close()
    db.close()
コード例 #15
0
def update_metadata_data(table, metadata):
    metadata_schema = user_metadata()
    column_titles = [d['column_name'] for d in metadata_schema]
    value_list = []
    for idx, title in enumerate(column_titles):
        if idx == 0:
            value_list.append((metadata[title]))
        else:
            value_list.append('\'' + str(metadata[title]) + '\'')

    insert_stmt = 'INSERT INTO {0} ({1}) VALUES ({2});'.format(
        table, ','.join(column_titles), ','.join(value_list))
    # print insert_stmt
    # print value_list

    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute(insert_stmt)
    db.commit()
    cursor.close()
    db.close()
コード例 #16
0
def update_metadata_data_list(table, metadata):
    metadata_schema = user_metadata()
    column_titles = [d['column_name'] for d in metadata_schema]
    insert_stmt = 'INSERT INTO {0} ({1}) VALUES ({2});'.format(table, ','.join(column_titles), ','.join(['%s' for i in range(0,len(column_titles))]))
    value_list = []

    # Generate a tuple for each row in the metadata. Only collect data from columns in the table
    for row in metadata:
        value_tuple = ()
        for idx, title in enumerate(column_titles):
            if idx == 0:
                value_tuple += ((row[title]),)
            else:
                value_tuple += ((row[title]),)
        value_list.append(value_tuple)

    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.executemany(insert_stmt, value_list)
    db.commit()
    cursor.close()
    db.close()
コード例 #17
0
def update_metadata_data_list(table, metadata):
    metadata_schema = user_metadata()
    column_titles = [d['column_name'] for d in metadata_schema]
    insert_stmt = 'INSERT INTO {0} ({1}) VALUES ({2});'.format(
        table, ','.join(column_titles),
        ','.join(['%s' for i in range(0, len(column_titles))]))
    value_list = []

    # Generate a tuple for each row in the metadata. Only collect data from columns in the table
    for row in metadata:
        value_tuple = ()
        for idx, title in enumerate(column_titles):
            if idx == 0:
                value_tuple += ((row[title]), )
            else:
                value_tuple += ((row[title]), )
        value_list.append(value_tuple)

    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.executemany(insert_stmt, value_list)
    db.commit()
    cursor.close()
    db.close()
コード例 #18
0
def cloudsql_append_column(table, missing_column_names, all_columns, inputfilename):
    columns = filter(lambda column: column['NAME'] in missing_column_names, all_columns)

    alter_stmt = 'ALTER TABLE {0} ADD '.format(table)

    for idx, column in enumerate(columns):
        if column['TYPE'] == 'STRING':
            column['TYPE'] = 'VARCHAR(200)'
        if idx == 0:
            alter_stmt += '({0} {1}'.format(column['NAME'], column['TYPE'])
        else:
            alter_stmt += ',{0} {1}'.format(column['NAME'], column['TYPE'])

    alter_stmt += ');'
    # print alter_stmt
    db = cloudsql_connector()
    cursor = db.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute(alter_stmt)
    db.commit()

    # TODO: add data from inputfile

    cursor.close()
    db.close()