Python TRN.execute_fetchlast方法代码示例

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
    def exists(cls, sample_id, md_template):
        r"""Checks if already exists a MetadataTemplate for the provided object

        Parameters
        ----------
        sample_id : str
            The sample id
        md_template : MetadataTemplate
            The metadata template to which the sample belongs to

        Returns
        -------
        bool
            True if already exists. False otherwise.
        """
        with TRN:
            cls._check_subclass()
            sql = """SELECT EXISTS(
                        SELECT * FROM qiita.{0}
                        WHERE sample_id=%s AND {1}=%s
                    )""".format(
                cls._table, cls._id_column
            )
            TRN.add(sql, [sample_id, md_template.id])
            return TRN.execute_fetchlast()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
 def _check_id(self, id_):
     r"""Checks that the MetadataTemplate id_ exists on the database"""
     with TRN:
         sql = "SELECT EXISTS(SELECT * FROM qiita.{0} WHERE {1}=%s)".format(
             self._table, self._id_column)
         TRN.add(sql, [id_])
         return TRN.execute_fetchlast()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
    def __getitem__(self, key):
        r"""Returns the value of the metadata category `key`

        Parameters
        ----------
        key : str
            The metadata category

        Returns
        -------
        obj
            The value of the metadata category `key`

        Raises
        ------
        KeyError
            If the metadata category `key` does not exists

        See Also
        --------
        get
        """
        with TRN:
            key = key.lower()
            if key not in self._get_categories():
                # The key is not available for the sample, so raise a KeyError
                raise KeyError(
                    "Metadata category %s does not exists for sample %s"
                    " in template %d" % (key, self._id, self._md_template.id))

            sql = """SELECT {0} FROM qiita.{1}
                     WHERE sample_id=%s""".format(key, self._dynamic_table)
            TRN.add(sql, [self._id])
            return TRN.execute_fetchlast()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
    def update_category(self, category, samples_and_values):
        """Update an existing column

        Parameters
        ----------
        category : str
            The category to update
        samples_and_values : dict
            A mapping of {sample_id: value}

        Raises
        ------
        QiitaDBUnknownIDError
            If a sample_id is included in values that is not in the template
        QiitaDBColumnError
            If the column does not exist in the table. This is implicit, and
            can be thrown by the contained Samples.
        ValueError
            If one of the new values cannot be inserted in the DB due to
            different types
        """
        with TRN:
            if not set(self.keys()).issuperset(samples_and_values):
                missing = set(self.keys()) - set(samples_and_values)
                table_name = self._table_name(self._id)
                raise QiitaDBUnknownIDError(missing, table_name)

            for k, v in viewitems(samples_and_values):
                sample = self[k]
                sample.setitem(category, v)

            try:
                TRN.execute()
            except ValueError as e:
                # catching error so we can check if the error is due to
                # different column type or something else

                value_types = set(type_lookup(type(value)) for value in viewvalues(samples_and_values))

                sql = """SELECT udt_name
                         FROM information_schema.columns
                         WHERE column_name = %s
                            AND table_schema = 'qiita'
                            AND (table_name = %s OR table_name = %s)"""
                TRN.add(sql, [category, self._table, self._table_name(self._id)])
                column_type = TRN.execute_fetchlast()

                if any([column_type != vt for vt in value_types]):
                    value_str = ", ".join([str(value) for value in viewvalues(samples_and_values)])
                    value_types_str = ", ".join(value_types)

                    raise ValueError(
                        'The new values being added to column: "%s" are "%s" '
                        '(types: "%s"). However, this column in the DB is of '
                        'type "%s". Please change the values in your updated '
                        "template or reprocess your template." % (category, value_str, value_types_str, column_type)
                    )

                raise e

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
 def tester():
     self.assertEqual(TRN._contexts_entered, 1)
     with TRN:
         self.assertEqual(TRN._contexts_entered, 2)
         sql = """SELECT EXISTS(
                 SELECT * FROM qiita.test_table WHERE int_column=%s)"""
         TRN.add(sql, [2])
         self.assertTrue(TRN.execute_fetchlast())
     self.assertEqual(TRN._contexts_entered, 1)

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
    def test_execute_fetchlast(self):
        with TRN:
            sql = """INSERT INTO qiita.test_table (str_column, int_column)
                     VALUES (%s, %s) RETURNING str_column, int_column"""
            args = [["insert1", 1], ["insert2", 2], ["insert3", 3]]
            TRN.add(sql, args, many=True)

            sql = """SELECT EXISTS(
                        SELECT * FROM qiita.test_table WHERE int_column=%s)"""
            TRN.add(sql, [2])
            self.assertTrue(TRN.execute_fetchlast())

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
    def preprocessing_status(self):
        r"""Tells if the data has been preprocessed or not

        Returns
        -------
        str
            One of {'not_preprocessed', 'preprocessing', 'success', 'failed'}
        """
        with TRN:
            sql = """SELECT preprocessing_status FROM qiita.prep_template
                     WHERE {0}=%s""".format(self._id_column)
            TRN.add(sql, [self.id])
            return TRN.execute_fetchlast()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
    def study_id(self):
        """Gets the study id with which this prep template is associated

        Returns
        -------
        int
            The ID of the study with which this prep template is associated
        """
        with TRN:
            sql = """SELECT study_id FROM qiita.study_prep_template
                     WHERE prep_template_id=%s"""
            TRN.add(sql, [self.id])
            return TRN.execute_fetchlast()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def transfer_file_to_artifact(analysis_id, a_timestamp, command_id,
                              data_type_id, params, artifact_type_id,
                              filepath_id):
    """Creates a new artifact with the given filepath id

    Parameters
    ----------
    analysis_id : int
        The analysis id to attach the artifact
    a_timestamp : datetime.datetime
        The generated timestamp of the artifact
    command_id : int
        The command id of the artifact
    data_type_id : int
        The data type id of the artifact
    params : str
        The parameters of the artifact
    artifact_type_id : int
        The artifact type
    filepath_id : int
        The filepath id

    Returns
    -------
    int
        The artifact id
    """
    with TRN:
        # Add the row in the artifact table
        # Magic number 4: Visibility -> sandbox
        sql = """INSERT INTO qiita.artifact
                    (generated_timestamp, command_id, data_type_id,
                     command_parameters, visibility_id, artifact_type_id,
                     submitted_to_vamps)
                 VALUES (%s, %s, %s, %s, %s, %s, %s)
                 RETURNING artifact_id"""
        TRN.add(sql, [a_timestamp, command_id, data_type_id, params, 4,
                      artifact_type_id, False])
        artifact_id = TRN.execute_fetchlast()
        # Link the artifact with its file
        sql = """INSERT INTO qiita.artifact_filepath (artifact_id, filepath_id)
                 VALUES (%s, %s)"""
        TRN.add(sql, [artifact_id, filepath_id])
        # Link the artifact with the analysis
        sql = """INSERT INTO qiita.analysis_artifact
                    (analysis_id, artifact_id)
                 VALUES (%s, %s)"""
        TRN.add(sql, [analysis_id, artifact_id])

    return artifact_id

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
    def delete(cls, id_):
        r"""Deletes the table from the database

        Parameters
        ----------
        id_ : integer
            The object identifier

        Raises
        ------
        QiitaDBUnknownIDError
            If no sample template with id id_ exists
        QiitaDBError
            If the study that owns this sample template has raw datas
        """
        with TRN:
            cls._check_subclass()

            if not cls.exists(id_):
                raise QiitaDBUnknownIDError(id_, cls.__name__)

            # Check if there is any PrepTemplate
            sql = """SELECT EXISTS(SELECT * FROM qiita.study_prep_template
                                   WHERE study_id=%s)"""
            TRN.add(sql, [id_])
            has_prep_templates = TRN.execute_fetchlast()
            if has_prep_templates:
                raise QiitaDBError("Sample template can not be erased because "
                                   "there are prep templates associated.")

            table_name = cls._table_name(id_)

            # Delete the sample template filepaths
            sql = """DELETE FROM qiita.sample_template_filepath
                     WHERE study_id = %s"""
            args = [id_]
            TRN.add(sql, args)

            TRN.add("DROP TABLE qiita.{0}".format(table_name))

            sql = "DELETE FROM qiita.{0} WHERE {1} = %s".format(
                cls._table, cls._id_column)
            TRN.add(sql, args)

            sql = "DELETE FROM qiita.{0} WHERE {1} = %s".format(
                cls._column_table, cls._id_column)
            TRN.add(sql, args)

            TRN.execute()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
 def raw_data(self, raw_data):
     with TRN:
         sql = """SELECT (
                     SELECT raw_data_id
                     FROM qiita.prep_template
                     WHERE prep_template_id=%s)
                 IS NOT NULL"""
         TRN.add(sql, [self.id])
         exists = TRN.execute_fetchlast()
         if exists:
             raise QiitaDBError(
                 "Prep template %d already has a raw data associated"
                 % self.id)
         sql = """UPDATE qiita.prep_template
                  SET raw_data_id = %s
                  WHERE prep_template_id = %s"""
         TRN.add(sql, [raw_data.id, self.id])
         TRN.execute()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
    def __setitem__(self, column, value):
        r"""Sets the metadata value for the category `column`

        Parameters
        ----------
        column : str
            The column to update
        value : str
            The value to set. This is expected to be a str on the assumption
            that psycopg2 will cast as necessary when updating.

        Raises
        ------
        ValueError
            If the value type does not match the one in the DB
        """
        with TRN:
            self.setitem(column, value)

            try:
                TRN.execute()
            except ValueError as e:
                # catching error so we can check if the error is due to
                # different column type or something else
                value_type = type_lookup(type(value))

                sql = """SELECT udt_name
                         FROM information_schema.columns
                         WHERE column_name = %s
                            AND table_schema = 'qiita'
                            AND (table_name = %s OR table_name = %s)"""
                TRN.add(sql, [column, self._table, self._dynamic_table])
                column_type = TRN.execute_fetchlast()

                if column_type != value_type:
                    raise ValueError(
                        'The new value being added to column: "{0}" is "{1}" '
                        '(type: "{2}"). However, this column in the DB is of '
                        'type "{3}". Please change the value in your updated '
                        'template or reprocess your template.'.format(
                            column, value, value_type, column_type))

                raise e

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def create_rarefaction_job(depth, biom_artifact_id, analysis, srare_cmd_id):
    """Create a new rarefaction job

    Parameters
    ----------
    depth : int
        The rarefaction depth
    biom_artifact_id : int
        The artifact id of the input rarefaction biom table
    analysis : dict
        Dictionary with the analysis information
    srare_cmd_id : int
        The command id of the single rarefaction command

    Returns
    -------
    job_id : str
        The job id
    params : str
        The job parameters
    """
    # Add the row in the procesisng job table
    params = ('{"depth":%d,"subsample_multinomial":false,"biom_table":%s}'
              % (depth, biom_artifact_id))
    with TRN:
        # magic number 3: status -> success
        sql = """INSERT INTO qiita.processing_job
                    (email, command_id, command_parameters,
                     processing_job_status_id)
                 VALUES (%s, %s, %s, %s)
                 RETURNING processing_job_id"""
        TRN.add(sql, [analysis['email'], srare_cmd_id, params, 3])
        job_id = TRN.execute_fetchlast()
        # Step 1.2.b: Link the job with the input artifact
        sql = """INSERT INTO qiita.artifact_processing_job
                    (artifact_id, processing_job_id)
                 VALUES (%s, %s)"""
        TRN.add(sql, [biom_artifact_id, job_id])
        TRN.execute()
    return job_id, params

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
    def data_type(self, ret_id=False):
        """Returns the data_type or the data_type id

        Parameters
        ----------
        ret_id : bool, optional
            If true, return the id instead of the string, default false.

        Returns
        -------
        str or int
            string value of data_type or data_type_id if ret_id is True
        """
        with TRN:
            ret = "_id" if ret_id else ""
            sql = """SELECT d.data_type{0}
                     FROM qiita.data_type d
                        JOIN qiita.prep_template p
                            ON p.data_type_id = d.data_type_id
                     WHERE p.prep_template_id=%s""".format(ret)
            TRN.add(sql, [self.id])
            return TRN.execute_fetchlast()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchlast [as 别名]
def create_non_rarefied_biom_artifact(analysis, biom_data, rarefied_table):
    """Creates the initial non-rarefied BIOM artifact of the analysis

    Parameters
    ----------
    analysis : dict
        Dictionary with the analysis information
    biom_data : dict
        Dictionary with the biom file information
    rarefied_table : biom.Table
        The rarefied BIOM table

    Returns
    -------
    int
        The id of the new artifact
    """
    # The non rarefied biom artifact is the initial biom table of the analysis.
    # This table does not currently exist anywhere, so we need to actually
    # create the BIOM file. To create this BIOM file we need: (1) the samples
    # and artifacts they come from and (2) whether the samples where
    # renamed or not. (1) is on the database, but we need to inferr (2) from
    # the existing rarefied BIOM table. Fun, fun...

    with TRN:
        # Get the samples included in the BIOM table grouped by artifact id
        # Note that the analysis contains a BIOM table per data type included
        # in it, and the table analysis_sample does not differentiate between
        # datatypes, so we need to check the data type in the artifact table
        sql = """SELECT artifact_id, array_agg(sample_id)
                 FROM qiita.analysis_sample
                    JOIN qiita.artifact USING (artifact_id)
                 WHERE analysis_id = %s AND data_type_id = %s
                 GROUP BY artifact_id"""
        TRN.add(sql, [analysis['analysis_id'], biom_data['data_type_id']])
        samples_by_artifact = TRN.execute_fetchindex()

        # Create an empty BIOM table to be the new master table
        new_table = Table([], [], [])
        ids_map = {}
        for a_id, samples in samples_by_artifact:
            # Get the filepath of the BIOM table from the artifact
            artifact = Artifact(a_id)
            biom_fp = None
            for _, fp, fp_type in artifact.filepaths:
                if fp_type == 'biom':
                    biom_fp = fp
            # Note that we are sure that the biom table exists for sure, so
            # no need to check if biom_fp is undefined
            biom_table = load_table(biom_fp)
            samples = set(samples).intersection(biom_table.ids())
            biom_table.filter(samples, axis='sample', inplace=True)
            # we need to check if the table has samples left before merging
            if biom_table.shape[0] != 0 and biom_table.shape[1] != 0:
                new_table = new_table.merge(biom_table)
                ids_map.update({sid: "%d.%s" % (a_id, sid)
                                for sid in biom_table.ids()})

        # Check if we need to rename the sample ids in the biom table
        new_table_ids = set(new_table.ids())
        if not new_table_ids.issuperset(rarefied_table.ids()):
            # We need to rename the sample ids
            new_table.update_ids(ids_map, 'sample', True, True)

        sql = """INSERT INTO qiita.artifact
                    (generated_timestamp, data_type_id, visibility_id,
                     artifact_type_id, submitted_to_vamps)
            VALUES (%s, %s, %s, %s, %s)
            RETURNING artifact_id"""
        # Magic number 4 -> visibility sandbox
        # Magix number 7 -> biom artifact type
        TRN.add(sql, [analysis['timestamp'], biom_data['data_type_id'],
                      4, 7, False])
        artifact_id = TRN.execute_fetchlast()

        # Associate the artifact with the analysis
        sql = """INSERT INTO qiita.analysis_artifact
                    (analysis_id, artifact_id)
                 VALUES (%s, %s)"""
        TRN.add(sql, [analysis['analysis_id'], artifact_id])
        # Link the artifact with its file
        dd_id, mp = get_mountpoint('BIOM')[0]
        dir_fp = join(get_db_files_base_dir(), mp, str(artifact_id))
        if not exists(dir_fp):
            makedirs(dir_fp)
        new_table_fp = join(dir_fp, "biom_table.biom")
        with biom_open(new_table_fp, 'w') as f:
            new_table.to_hdf5(f, "Generated by Qiita")

        sql = """INSERT INTO qiita.filepath
                    (filepath, filepath_type_id, checksum,
                     checksum_algorithm_id, data_directory_id)
                 VALUES (%s, %s, %s, %s, %s)
                 RETURNING filepath_id"""
        # Magic number 7 -> filepath_type_id = 'biom'
        # Magic number 1 -> the checksum algorithm id
        TRN.add(sql, [basename(new_table_fp), 7,
                      compute_checksum(new_table_fp), 1, dd_id])
        fp_id = TRN.execute_fetchlast()
        sql = """INSERT INTO qiita.artifact_filepath
#.........这里部分代码省略.........