Python TRN.execute_fetchindex方法代码示例

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
    def test_execute_fetchindex(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)
            self.assertEqual(TRN.execute_fetchindex(), [["insert3", 3]])

            sql = """INSERT INTO qiita.test_table (str_column, int_column)
                     VALUES (%s, %s) RETURNING str_column, int_column"""
            args = [["insert4", 4], ["insert5", 5], ["insert6", 6]]
            TRN.add(sql, args, many=True)
            self.assertEqual(TRN.execute_fetchindex(3), [["insert4", 4]])

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
    def status(self):
        """The status of the prep template

        Returns
        -------
        str
            The status of the prep template

        Notes
        -----
        The status of a prep template is inferred by the status of the
        processed data generated from this prep template. If no processed
        data has been generated with this prep template; then the status
        is 'sandbox'.
        """
        with TRN:
            sql = """SELECT processed_data_status
                    FROM qiita.processed_data_status pds
                      JOIN qiita.processed_data pd
                        USING (processed_data_status_id)
                      JOIN qiita.preprocessed_processed_data ppd_pd
                        USING (processed_data_id)
                      JOIN qiita.prep_template_preprocessed_data pt_ppd
                        USING (preprocessed_data_id)
                    WHERE pt_ppd.prep_template_id=%s"""
            TRN.add(sql, [self._id])

            return infer_status(TRN.execute_fetchindex())

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
    def update(self, md_template):
        r"""Update values in the template

        Parameters
        ----------
        md_template : DataFrame
            The metadata template file contents indexed by samples Ids

        Raises
        ------
        QiitaDBError
            If md_template and db do not have the same sample ids
            If md_template and db do not have the same column headers
            If self.can_be_updated is not True
        """
        with TRN:
            # Clean and validate the metadata template given
            new_map = self._clean_validate_template(md_template, self.study_id, self.columns_restrictions)
            # Retrieving current metadata
            sql = "SELECT * FROM qiita.{0}".format(self._table_name(self.id))
            TRN.add(sql)
            current_map = self._transform_to_dict(TRN.execute_fetchindex())
            current_map = pd.DataFrame.from_dict(current_map, orient="index")

            # simple validations of sample ids and column names
            samples_diff = set(new_map.index).difference(current_map.index)
            if samples_diff:
                raise QiitaDBError(
                    "The new template differs from what is stored "
                    "in database by these samples names: %s" % ", ".join(samples_diff)
                )
            columns_diff = set(new_map.columns).difference(current_map.columns)
            if columns_diff:
                raise QiitaDBError(
                    "The new template differs from what is stored "
                    "in database by these columns names: %s" % ", ".join(columns_diff)
                )

            # here we are comparing two dataframes following:
            # http://stackoverflow.com/a/17095620/4228285
            current_map.sort(axis=0, inplace=True)
            current_map.sort(axis=1, inplace=True)
            new_map.sort(axis=0, inplace=True)
            new_map.sort(axis=1, inplace=True)
            map_diff = (current_map != new_map).stack()
            map_diff = map_diff[map_diff]
            map_diff.index.names = ["id", "column"]
            changed_cols = map_diff.index.get_level_values("column").unique()

            if not self.can_be_updated(columns=set(changed_cols)):
                raise QiitaDBError(
                    "The new template is modifying fields that cannot be "
                    "modified. Try removing the target gene fields or "
                    "deleting the processed data. You are trying to modify: %s" % ", ".join(changed_cols)
                )

            for col in changed_cols:
                self.update_category(col, new_map[col].to_dict())

            self.generate_files()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
    def _update_accession_numbers(self, column, values):
        """Update accession numbers stored in `column` with the ones in `values`

        Parameters
        ----------
        column : str
            The column name where the accession number are stored
        values : dict of {str: str}
            The accession numbers keyed by sample id

        Raises
        ------
        QiitaDBError
            If a sample in `values` already has an accession number
        QiitaDBWarning
            If `values` is not updating any accesion number
        """
        with TRN:
            sql = """SELECT sample_id, {0}
                     FROM qiita.{1}
                     WHERE {2}=%s
                        AND {0} IS NOT NULL""".format(column, self._table,
                                                      self._id_column)
            TRN.add(sql, [self.id])
            db_vals = {sample_id: accession
                       for sample_id, accession in TRN.execute_fetchindex()}
            common_samples = set(db_vals) & set(values)
            diff = [sample for sample in common_samples
                    if db_vals[sample] != values[sample]]
            if diff:
                raise QiitaDBError(
                    "The following samples already have an accession number: "
                    "%s" % ', '.join(diff))

            # Remove the common samples form the values dictionary
            values = deepcopy(values)
            for sample in common_samples:
                del values[sample]

            if values:
                sql_vals = ', '.join(["(%s, %s)"] * len(values))
                sql = """UPDATE qiita.{0} AS t
                         SET {1}=c.{1}
                         FROM (VALUES {2}) AS c(sample_id, {1})
                         WHERE c.sample_id = t.sample_id
                            AND t.{3} = %s
                         """.format(self._table, column, sql_vals,
                                    self._id_column)
                sql_vals = list(chain.from_iterable(values.items()))
                sql_vals.append(self.id)
                TRN.add(sql, sql_vals)
                TRN.execute()
            else:
                warnings.warn("No new accession numbers to update",
                              QiitaDBWarning)

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
 def raw_data(self):
     with TRN:
         sql = """SELECT raw_data_id FROM qiita.prep_template
                  WHERE prep_template_id=%s"""
         TRN.add(sql, [self.id])
         result = TRN.execute_fetchindex()
         if result:
             # If there is any result, it will be in the first row
             # and in the first element of that row, thus [0][0]
             return result[0][0]
         return None

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
    def __call__(self, searchstr, user):
        """Runs a Study query and returns matching studies and samples

        Parameters
        ----------
        searchstr : str
            Search string to use
        user : User object
            User making the search. Needed for permissions checks.

        Returns
        -------
        dict
            Found samples in format
            {study_id: [[samp_id1, meta1, meta2, ...],
                        [samp_id2, meta1, meta2, ...], ...}
        list
            metadata column names searched for

        Notes
        -----
        Metadata information for each sample is in the same order as the
        metadata columns list returned

        Metadata column names and string searches are case-sensitive
        """
        with TRN:
            study_sql, sample_sql, meta_headers = \
                self._parse_study_search_string(searchstr, True)

            # get all studies containing the metadata headers requested
            TRN.add(study_sql)
            study_ids = set(TRN.execute_fetchflatten())
            # strip to only studies user has access to
            if user.level not in {'admin', 'dev', 'superuser'}:
                study_ids = study_ids.intersection(
                    Study.get_by_status('public') | user.user_studies |
                    user.shared_studies)

            results = {}
            # run search on each study to get out the matching samples
            for sid in study_ids:
                TRN.add(sample_sql.format(sid))
                study_res = TRN.execute_fetchindex()
                if study_res:
                    # only add study to results if actually has samples
                    # in results
                    results[sid] = study_res
            self.results = results
            self.meta_headers = meta_headers
            return results, meta_headers

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
    def _to_dict(self):
        r"""Returns the categories and their values in a dictionary

        Returns
        -------
        dict of {str: str}
            A dictionary of the form {category: value}
        """
        with TRN:
            sql = "SELECT * FROM qiita.{0} WHERE sample_id=%s".format(self._dynamic_table)
            TRN.add(sql, [self._id])
            d = dict(TRN.execute_fetchindex()[0])

            # Remove the sample_id, is not part of the metadata
            del d["sample_id"]

            return d

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
    def to_dataframe(self):
        """Returns the metadata template as a dataframe

        Returns
        -------
        pandas DataFrame
            The metadata in the template,indexed on sample id
        """
        with TRN:
            cols = sorted(get_table_cols(self._table_name(self._id)))
            # Get all metadata for the template
            sql = "SELECT {0} FROM qiita.{1}".format(", ".join(cols), self._table_name(self.id))
            TRN.add(sql, [self._id])
            meta = TRN.execute_fetchindex()

            # Create the dataframe and clean it up a bit
            df = pd.DataFrame((list(x) for x in meta), columns=cols)
            df.set_index("sample_id", inplace=True, drop=True)

            return df

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
    def _get_accession_numbers(self, column):
        """Return the accession numbers stored in `column`

        Parameters
        ----------
        column : str
            The column name where the accession number is stored

        Returns
        -------
        dict of {str: str}
            The accession numbers keyed by sample id
        """
        with TRN:
            sql = """SELECT sample_id, {0}
                     FROM qiita.{1}
                     WHERE {2}=%s""".format(column, self._table,
                                            self._id_column)
            TRN.add(sql, [self.id])
            result = {sample_id: accession
                      for sample_id, accession in TRN.execute_fetchindex()}
        return result

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
    def qiime_map_fp(self):
        """The QIIME mapping filepath attached to the prep template

        Returns
        -------
        str
            The filepath of the QIIME mapping file
        """
        with TRN:
            sql = """SELECT filepath_id, filepath
                     FROM qiita.filepath
                        JOIN qiita.{0} USING (filepath_id)
                        JOIN qiita.filepath_type USING (filepath_type_id)
                     WHERE {1} = %s AND filepath_type = 'qiime_map'
                     ORDER BY filepath_id DESC""".format(self._filepath_table,
                                                         self._id_column)
            TRN.add(sql, [self._id])
            # We know that the good filepath is the one in the first row
            # because we sorted them in the SQL query
            fn = TRN.execute_fetchindex()[0][1]
            base_dir = get_mountpoint('templates')[0][1]
            return join(base_dir, fn)

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
    def get_filepaths(self):
        r"""Retrieves the list of (filepath_id, filepath)"""
        with TRN:
            try:
                sql = """SELECT filepath_id, filepath
                         FROM qiita.filepath
                         WHERE filepath_id IN (
                            SELECT filepath_id FROM qiita.{0}
                            WHERE {1}=%s)
                         ORDER BY filepath_id DESC""".format(
                    self._filepath_table, self._id_column)

                TRN.add(sql, [self.id])
                filepath_ids = TRN.execute_fetchindex()
            except Exception as e:
                LogEntry.create('Runtime', str(e),
                                info={self.__class__.__name__: self.id})
                raise e

            _, fb = get_mountpoint('templates')[0]
            base_fp = partial(join, fb)

            return [(fpid, base_fp(fp)) for fpid, fp in filepath_ids]

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
if cols_sample:
    with TRN:
        # a few notes: just getting the preps with duplicated values; ignoring
        # column 'sample_id' and tables 'study_sample', 'prep_template',
        # 'prep_template_sample'
        sql = """SELECT table_name, array_agg(column_name::text)
                    FROM information_schema.columns
                    WHERE column_name IN %s
                        AND table_name LIKE 'sample_%%'
                        AND table_name NOT IN (
                            'prep_template', 'prep_template_sample')
                    GROUP BY table_name"""
        # note that we are looking for those columns with duplicated names in
        # the headers
        TRN.add(sql, [tuple(set(cols_sample))])
        for table, columns in viewitems(dict(TRN.execute_fetchindex())):
            # [1] the format is table_# so taking the #
            st = SampleTemplate(int(table.split('_')[1]))
            # getting just the columns of interest
            st_df = st.to_dataframe()[columns]
            # converting to datetime
            for col in columns:
                st_df[col] = st_df[col].apply(transform_date)
            st.update(st_df)

if cols_prep:
    with TRN:
        # a few notes: just getting the preps with duplicated values; ignoring
        # column 'sample_id' and tables 'study_sample', 'prep_template',
        # 'prep_template_sample'
        sql = """SELECT table_name, array_agg(column_name::text)

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
    pc_update_sql = """UPDATE qiita.prep_columns
                        SET column_type = 'bool'
                        WHERE prep_template_id = %s AND column_name = %s"""

    for table in tables:
        table_id = table.split("_")[1]
        # Change NaN values to NULL in database
        TRN.add(cols_sql, [table])
        cols = TRN.execute_fetchflatten()
        for col in cols:
            TRN.add(null_sql.format(table, col), [nans])
        TRN.execute()

        # Update now boolean columns to bool in database
        TRN.add("SELECT {0} FROM qiita.{1}".format(",".join(cols), table))
        col_vals = zip(*TRN.execute_fetchindex())
        for col, vals in zip(cols, col_vals):
            if set(vals) == {None}:
                # Ignore columns that are all NULL
                continue
            if all([v in bool_vals for v in vals]):
                # Every value in the column should be bool, so do it
                TRN.add(alter_sql.format(table, col), [false_vals, true_vals])
                if "sample" in table:
                    st_update.add(table_id)
                    TRN.add(ssc_update_sql, [table_id, col])
                else:
                    pr_update.add(table_id)
                    TRN.add(pc_update_sql, [table_id, col])

    TRN.execute()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
        TRN.add(sql, [client_id, client_secret])

        sql = """INSERT INTO qiita.oauth_software (software_id, client_id)
                 VALUES (%s, %s)"""
        TRN.add(sql, [i, client_id])
        TRN.execute()

    #
    # Generating compressed files for picking failures -- artifact_type = BIOM
    #
    sql = """SELECT artifact_id FROM qiita.artifact
                JOIN qiita.artifact_type USING (artifact_type_id)
                WHERE artifact_type = 'BIOM'"""
    TRN.add(sql)

    for r in TRN.execute_fetchindex():
        to_tgz = None
        a = Artifact(r[0])
        for _, fp, fp_type in a.filepaths:
            if fp_type == 'directory':
                # removing / from the path if it exists
                to_tgz = fp[:-1] if fp[-1] == '/' else fp
                break

        if to_tgz is None:
            continue

        tgz = to_tgz + '.tgz'
        if not exists(tgz):
            with taropen(tgz, "w:gz") as tar:
                tar.add(to_tgz, arcname=basename(to_tgz))

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchindex [as 别名]
    def create_qiime_mapping_file(self):
        """This creates the QIIME mapping file and links it in the db.

        Returns
        -------
        filepath : str
            The filepath of the created QIIME mapping file

        Raises
        ------
        ValueError
            If the prep template is not a subset of the sample template
        QiitaDBWarning
            If the QIIME-required columns are not present in the template

        Notes
        -----
        We cannot ensure that the QIIME-required columns are present in the
        metadata map. However, we have to generate a QIIME-compliant mapping
        file. Since the user may need a QIIME mapping file, but not these
        QIIME-required columns, we are going to create them and
        populate them with the value XXQIITAXX.
        """
        with TRN:
            rename_cols = {
                'barcode': 'BarcodeSequence',
                'primer': 'LinkerPrimerSequence',
                'description': 'Description',
            }

            if 'reverselinkerprimer' in self.categories():
                rename_cols['reverselinkerprimer'] = 'ReverseLinkerPrimer'
                new_cols = ['BarcodeSequence', 'LinkerPrimerSequence',
                            'ReverseLinkerPrimer']
            else:
                new_cols = ['BarcodeSequence', 'LinkerPrimerSequence']

            # getting the latest sample template
            sql = """SELECT filepath_id, filepath
                     FROM qiita.filepath
                        JOIN qiita.sample_template_filepath
                        USING (filepath_id)
                     WHERE study_id=%s
                     ORDER BY filepath_id DESC"""
            TRN.add(sql, [self.study_id])
            # We know that the good filepath is the one in the first row
            # because we sorted them in the SQL query
            sample_template_fname = TRN.execute_fetchindex()[0][1]
            _, fp = get_mountpoint('templates')[0]
            sample_template_fp = join(fp, sample_template_fname)

            # reading files via pandas
            st = load_template_to_dataframe(sample_template_fp)
            pt = self.to_dataframe()

            st_sample_names = set(st.index)
            pt_sample_names = set(pt.index)

            if not pt_sample_names.issubset(st_sample_names):
                raise ValueError(
                    "Prep template is not a sub set of the sample template, "
                    "file: %s - samples: %s"
                    % (sample_template_fp,
                       ', '.join(pt_sample_names-st_sample_names)))

            mapping = pt.join(st, lsuffix="_prep")
            mapping.rename(columns=rename_cols, inplace=True)

            # Pre-populate the QIIME-required columns with the value XXQIITAXX
            index = mapping.index
            placeholder = ['XXQIITAXX'] * len(index)
            missing = []
            for val in viewvalues(rename_cols):
                if val not in mapping:
                    missing.append(val)
                    mapping[val] = pd.Series(placeholder, index=index)

            if missing:
                warnings.warn(
                    "Some columns required to generate a QIIME-compliant "
                    "mapping file are not present in the template. A "
                    "placeholder value (XXQIITAXX) has been used to populate "
                    "these columns. Missing columns: %s" % ', '.join(missing),
                    QiitaDBWarning)

            # Gets the orginal mapping columns and readjust the order to comply
            # with QIIME requirements
            cols = mapping.columns.values.tolist()
            cols.remove('BarcodeSequence')
            cols.remove('LinkerPrimerSequence')
            cols.remove('Description')
            new_cols.extend(cols)
            new_cols.append('Description')
            mapping = mapping[new_cols]

            # figuring out the filepath for the QIIME map file
            _id, fp = get_mountpoint('templates')[0]
            filepath = join(fp, '%d_prep_%d_qiime_%s.txt' % (self.study_id,
                            self.id, strftime("%Y%m%d-%H%M%S")))

#.........这里部分代码省略.........