def field_value_search(self, keyword, field):
        self.validate_field_search_input(keyword, field)

        query = 'SELECT DISTINCT {search_field} FROM {table_name} WHERE {search_field} LIKE %s LIMIT %s'.format(
            table_name=self.get_table_name(),
            search_field=field
        )
        # Format the keyword for MySQL string matching
        sql_keyword = '%' + keyword + '%'
        query_args = [sql_keyword, FOUND_FEATURE_LIMIT]

        try:
            db = get_sql_connection()
            cursor = db.cursor(DictCursor)
            cursor.execute(query, tuple(query_args))
            items = []

            for row in cursor.fetchall():
                items.append(row[field])

            return items

        except MySQLError as mse:
            logger.exception(mse)
            raise BackendException('database error: ' + str(mse))
    def search(self, parameters):
        self.validate_feature_search_input(parameters)

        query = 'SELECT gene_name, probe_name, platform, relation_to_gene, relation_to_island, ' \
                       'value_field, genomic_build, internal_feature_id ' \
                'FROM {table_name} ' \
                'WHERE gene_name=%s ' \
                'AND probe_name LIKE %s ' \
                'AND platform LIKE %s ' \
                'AND relation_to_gene LIKE %s ' \
                'AND relation_to_island LIKE %s ' \
                'AND genomic_build=%s' \
                'LIMIT %s'.format(table_name=self.get_table_name()
        )

        # Fills in '' for fields that were not specified in the parameters
        input = defaultdict(lambda: '', parameters)

        # Format the keyword for MySQL string matching
        query_args = [input['gene_name'],
                      '%' + input['probe_name'] + '%',
                      '%' + input['platform'] + '%',
                      '%' + input['relation_to_gene'] + '%',
                      '%' + input['relation_to_island'] + '%',
                      input['genomic_build'],
                      FOUND_FEATURE_LIMIT]

        try:
            db = get_sql_connection()
            cursor = db.cursor(DictCursor)
            cursor.execute(query, tuple(query_args))
            items = []

            for row in cursor.fetchall():
                items.append(row)

            # Generate human readable labels
            for item in items:
                item['feature_type'] = METH_FEATURE_TYPE
                item['label'] = self.build_feature_label(item)

            return items

        except MySQLError as mse:
            logger.exception(mse)
            raise BackendException('database error')
Esempio n. 3
0
    def search(self, parameters):
        self.validate_feature_search_input(parameters)

        query = 'SELECT gene_name, genomic_build, internal_feature_id' \
                ' FROM {table_name}' \
                ' WHERE gene_name=%s' \
                ' AND genomic_build=%s' \
                ' LIMIT %s'.format(table_name=self.get_table_name()
        )

        # Fills in '' for fields that were not specified in the parameters
        input = defaultdict(lambda: '', parameters)

        # Format the keyword for MySQL string matching
        # sql_keyword = '%' + keyword + '%'
        query_args = [
            input['gene_name'], input['genomic_build'], FOUND_FEATURE_LIMIT
        ]

        try:
            db = get_sql_connection()
            cursor = db.cursor(DictCursor)
            cursor.execute(query, tuple(query_args))
            items = []

            for row in cursor.fetchall():
                items.append(row)

            # Generate human readable labels
            for item in items:
                item['feature_type'] = GEXP_FEATURE_TYPE
                item['label'] = self.build_feature_label(
                    item['gene_name'], item['genomic_build'])

            return items

        except MySQLError as mse:
            logger.exception(mse)
            raise BackendException("MySQLError: {}".format(str(mse)))