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