def get(self, table_id, sample, gene): """Returns the value for a given gene and sample. If no sample is given returns all values for that gene """ if not BARUtils.is_arabidopsis_gene_valid(gene): return BARUtils.success_exit('Invalid gene ID'), 400 else: key = request.headers.get('X-Api-Key') if SummarizationGeneExpressionUtils.decrement_uses(key): con = db.get_engine(bind='summarization') tbl = SummarizationGeneExpressionUtils.get_table_object( table_id) if sample == '': values = {} try: rows = con.execute( tbl.select(tbl.c.Value).where(tbl.c.Gene == gene)) except SQLAlchemyError: return BARUtils.error_exit( 'Internal server error'), 500 for row in rows: values.update({row.Sample: row.Value}) else: values = [] try: rows = con.execute( tbl.select(tbl.c.Value).where( tbl.c.Sample == sample).where( tbl.c.Gene == gene)) except SQLAlchemyError: return BARUtils.error_exit( 'Internal server error'), 500 [values.append(row.Value) for row in rows] return BARUtils.success_exit(values) else: return BARUtils.error_exit('Invalid API key')
def wrapper(*args, **kwargs): target_config = kwargs.get('target_config') if target_config: engine = get_engine(target_config) session = sessionmaker(bind=engine)() kwargs['session'] = session return f(*args, **kwargs)
def get(self, table_id=''): """Checks if a given table exists """ con = db.get_engine(bind='summarization') if con.dialect.has_table(con, table_id): return BARUtils.success_exit(True) else: return BARUtils.success_exit(False)
def get_table_object(table_name): metadata = db.MetaData() table_object = db.Table( table_name, metadata, autoload=True, autoload_with=db.get_engine(bind='summarization')) return table_object
def get(self, table_id=''): """Returns the list of samples in the table with the given ID """ con = db.get_engine(bind='summarization') tbl = SummarizationGeneExpressionUtils.get_table_object(table_id) values = [] try: rows = con.execute(db.select([tbl.c.Sample]).distinct()) except SQLAlchemyError: return BARUtils.error_exit('Internal server error'), 500 [values.append(row.Sample) for row in rows] return BARUtils.success_exit(values)
def get(self, table_id='', user_string=''): """Returns all genes that contain a given string as part of their name """ con = db.get_engine(bind='summarization') tbl = SummarizationGeneExpressionUtils.get_table_object(table_id) values = [] try: rows = con.execute( db.select([tbl.c.Gene]).where( tbl.c.Gene.contains(user_string)).distinct()) except SQLAlchemyError: return BARUtils.error_exit('Internal server error'), 500 [values.append(row.Gene) for row in rows] return BARUtils.success_exit(values)
def post(self, *args, **kwargs): json_input = request.get_json() if not json_input: return {"message": "No input data provided"}, 400 nr_num = json_input['nameRequest'] current_app.logger.debug('attempting to load: {}'.format(nr_num)) if not validNRFormat(nr_num): return {"message": "Valid NR format required - 'NR 9999999'"}, 400 if Request.find_by_nr(nr_num): return { "message": "{nr} already exists in namex, unable to create a duplicate". format(nr=nr_num) }, 409 conn = db.get_engine(bind='nro') nr_header = get_nr_header(conn, nr_num) current_app.logger.debug('nr_header: {}'.format(nr_header)) if not nr_header: return { "message": "{nr} not found, unable to complete extraction to new system". format(nr=nr_num) }, 404 nr_submitter = get_nr_submitter(conn, nr_header['request_id']) nr_applicant = get_nr_requester(conn, nr_header['request_id']) nr_ex_comments = get_exam_comments(conn, nr_header['request_id']) nr_nwpat = get_nwpta(conn, nr_header['request_id']) nr_names = get_names(conn, nr_header['request_id']) user = User.find_by_username(current_app.config['NRO_SERVICE_ACCOUNT']) #Create NR new_nr = Request() add_nr_header(new_nr, nr_header, nr_submitter, user) add_applicant(new_nr, nr_applicant) add_comments(new_nr, nr_ex_comments) add_nwpta(new_nr, nr_nwpat) add_names(new_nr, nr_names) new_nr.save_to_db() return { "message": "{nr} has been successfully copied".format(nr=nr_num) }, 200
def teardown_db(executor_config=None, target_config=None): engine = get_engine(executor_config) db_name = target_config['DB_NAME'] db_user = target_config['DB_USER'] with engine.connect() as conn: # terminate all connections to be able to drop database conn.execute(""" SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '%s' AND pid <> pg_backend_pid();""" % db_name) conn.execute("DROP DATABASE IF EXISTS %s" % db_name) conn.execute("DROP ROLE IF EXISTS %s" % db_user)
def setup_db(executor_config=None, target_config=None): engine = get_engine(executor_config) db_name = target_config['DB_NAME'] db_user = target_config['DB_USER'] db_pass = target_config['DB_PASS'] with engine.connect() as conn: teardown_db(executor_config=executor_config, target_config=target_config) conn.execute("CREATE USER %s WITH PASSWORD '%s'" % (db_user, db_pass)) conn.execute("CREATE DATABASE %s" % db_name) conn.execute("GRANT ALL PRIVILEGES ON DATABASE %s TO %s" % (db_name, db_user))
def get(self, table_id=''): """Returns the list of genes in the table with the given ID """ key = request.headers.get('x-api-key') if SummarizationGeneExpressionUtils.decrement_uses(key): con = db.get_engine(bind='summarization') tbl = SummarizationGeneExpressionUtils.get_table_object(table_id) values = [] try: rows = con.execute(db.select([tbl.c.Gene]).distinct()) except SQLAlchemyError: return BARUtils.error_exit('Internal server error'), 500 [values.append(row.Gene) for row in rows] return BARUtils.success_exit(values) else: return BARUtils.error_exit('Invalid API key')
def create_sample_data(target_config=None): engine = get_engine(target_config) session = sessionmaker(bind=engine)() session.add( Users(username='******', email='*****@*****.**', password_hash=generate_password_hash('adam'))) session.add( Users(username='******', email='*****@*****.**', password_hash=generate_password_hash('user2'))) session.add( Users(username='******', email='*****@*****.**', password_hash=generate_password_hash('user3'))) session.commit()
def get_values(logger, tumor_type, attribute_name): #tumor_type = request.form.get('tumor_type_id') #attribute_name = request.form.get('regions') table_name = ClinicalDatum.__tablename__ tumor_type_id = tumor_type_reverse_dict[tumor_type] print("tumor_type_id =>", tumor_type_id) query = "SELECT " + attribute_name + ", count(*) from " + table_name + " where tumor_type_id_wgs=" + str( tumor_type_id) + " OR tumor_type_id_wxs=" + str( tumor_type_id) + " group by " + attribute_name + ";" if not tumor_type or not attribute_name: logger.error("Missing tumor_type_id or attribute_name.") abort(400) values = [] try: connection = db.get_engine().connect() # session.execute("set enable_seqscan=false") result = connection.execute(text(query)) print(result) values_count = 0 for value in result: if not (value[0] is None): values.append({"value": value[0], "count": value[1]}) values_count = values_count + value[1] result.close() except Exception as e: logger.error("Error or table does not exist", e) abort(500) answer = { "tumor_type": tumor_type, "attribute": attribute_name, "values_count": values_count, "values": values } return json.dumps(answer)
def post(self): if request.method == 'POST': response_json = request.get_json() df = pandas.DataFrame.from_records([response_json]) con = db.get_engine(bind='summarization') try: reqs = Requests() users = Users() row_req = reqs.query.filter_by(email=df.email[0]).first() row_users = users.query.filter_by(email=df.email[0]).first() if row_req is None and row_users is None: df.to_sql('requests', con, if_exists='append', index=False) else: return BARUtils.error_exit('E-mail already in use'), 409 except SQLAlchemyError: return BARUtils.error_exit('Internal server error'), 500
def decrement_uses(key): """Subtracts 1 from the uses_left column of the user whose key matches the given string :param key: The user's API key """ if SummarizationGeneExpressionUtils.validate_api_key(key): tbl = SummarizationGeneExpressionUtils.get_table_object('users') con = db.get_engine(bind='summarization') try: con.execute( db.update(tbl).where(tbl.c.api_key == key).values( uses_left=(tbl.c.uses_left - 1))) db.session.commit() except SQLAlchemyError as e: error = str(e.__dict__['orig']) return error return True else: return False
def get_donors(tumor_type, filter_json): tumor_type_id = tumor_type_reverse_dict[tumor_type] correct_id = str(tumor_type_dict[tumor_type_id][6]) print("id " + str(tumor_type_id) + " correct_id " + correct_id) query = get_query(correct_id, filter_json) donors = [] try: result = db.get_engine().connect().execute(text(query)) for value in result: donors.append(value[0]) return donors except: abort(500)
def validate_api_key(key): """Checks if a given API key is in the Users database :param key: The API key to be checked """ tbl = SummarizationGeneExpressionUtils.get_table_object('users') con = db.get_engine(bind='summarization') try: row = con.execute( db.select([tbl.c.uses_left ]).where(tbl.c.api_key == key)).first() except SQLAlchemyError as e: error = str(e.__dict__['orig']) return error if row is None: return False else: if row.uses_left > 0: return True else: return False
def post(self): """This function adds a CSV's data to the database. This is only called by the Cromwell server after receiving the user's file. """ if request.remote_addr != '127.0.0.1': return BARUtils.error_exit('Forbidden'), 403 if request.method == 'POST': key = request.headers.get('X-Api-Key') if SummarizationGeneExpressionUtils.decrement_uses(key): csv = request.get_json()['csv'] db_id = request.get_json()['uid'] df = pandas.read_csv(csv) db_id = db_id.split('.')[0] df = df.melt(id_vars=['Gene'], var_name='Sample', value_name='Value') db_id = db_id.split('/')[len(db_id.split('/')) - 1] con = db.get_engine(bind='summarization') df.to_sql(db_id, con, if_exists='append', index=True) return BARUtils.success_exit('Success') else: return BARUtils.error_exit('Invalid API key')
def post(self): """Approve a request from the database and add it to the Users table """ if request.method == 'POST': response_json = request.get_json() email = response_json['email'] password = response_json['password'] if ApiManagerUtils.check_admin_pass(password): table = Requests() values = [] try: rows = table.query.filter_by(email=email).all() except SQLAlchemyError: return BARUtils.error_exit('Internal server error'), 500 key = uuid.uuid4().hex [ values.append({ 'first_name': row.first_name, 'last_name': row.last_name, 'email': row.email, 'telephone': row.telephone, 'contact_type': row.contact_type, 'date_added': datetime.now(), 'status': 'user', 'api_key': key, 'uses_left': 25 }) for row in rows ] df = pandas.DataFrame.from_records([values[0]]) con = db.get_engine(bind='summarization') try: df.to_sql('users', con, if_exists='append', index=False) el = table.query.filter_by(email=email).one() db.session.delete(el) except SQLAlchemyError: return BARUtils.error_exit('Internal server error'), 500 return BARUtils.success_exit(key) else: return BARUtils.error_exit('Forbidden'), 403
def create_tables(target_config=None): engine = get_engine(target_config) meta = MetaData() meta.create_all(bind=engine, tables=[Users.__table__, Message.__table__])
def drop_tables(target_config=None): engine = get_engine(target_config) Base.metadata.drop_all(engine) meta = MetaData() meta.drop_all(bind=engine, tables=[Users.__table__, Message.__table__])
import pytest from typing import Any, Callable, List, Optional from asyncpg.pool import PoolConnectionProxy from api.__main__ import init_app from api.config import TestConfig from api.db import get_engine from api.db.schema import metadata, users config = TestConfig.load_config() engine = get_engine(config) @pytest.fixture async def client(aiohttp_client: Callable) -> Any: """ Fixture of application's instance. :param aiohttp_client: Wrapper of application :type aiohttp_client: Callable :return: Wrapped application instance :rtype: Any """ app = await init_app(config) client = await aiohttp_client(app) return client