Python TRN.execute_fetchflatten方法代码示例

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [as 别名]
    def test_create_templates_from_qiime_mapping_file_reverse_linker(self):
        with TRN:
            TRN.add("SELECT last_value FROM "
                    "qiita.prep_template_prep_template_id_seq")
            curr_id = TRN.execute_fetchflatten()[0]
        obs_st, obs_pt = create_templates_from_qiime_mapping_file(
            StringIO(QIIME_MAP_WITH_REVERSE_LINKER_PRIMER),
            self.new_study, "16S")

        # Be green: clean the environment
        for template in [obs_st, obs_pt]:
            for _, fp in template.get_filepaths():
                self._clean_up_files.append(fp)

        self.assertEqual(obs_st.id, self.new_study.id)
        self.assertEqual(obs_pt.id, curr_id + 1)

        # Check that each template has the correct columns
        exp = {"physical_specimen_location", "physical_specimen_remaining",
               "dna_extracted", "sample_type", "host_subject_id", "latitude",
               "longitude", "taxon_id", "scientific_name",
               "collection_timestamp", "description"}
        self.assertEqual(set(obs_st.categories()), exp)

        exp = {"barcode", "primer", "center_name", "run_prefix", "platform",
               "library_construction_protocol", "instrument_model",
               "experiment_design_description", "reverselinkerprimer"}
        self.assertEqual(set(obs_pt.categories()), exp)

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [as 别名]
 def preprocessed_data(self):
     with TRN:
         sql = """SELECT preprocessed_data_id
                  FROM qiita.prep_template_preprocessed_data
                  WHERE prep_template_id=%s"""
         TRN.add(sql, [self.id])
         return TRN.execute_fetchflatten()

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

            sql = "SELECT str_column, int_column FROM qiita.test_table"
            TRN.add(sql)

            sql = "SELECT int_column FROM qiita.test_table"
            TRN.add(sql)
            obs = TRN.execute_fetchflatten()
            self.assertEqual(obs, [1, 2, 3])

            sql = "SELECT 42"
            TRN.add(sql)
            obs = TRN.execute_fetchflatten(idx=3)
            self.assertEqual(obs, ["insert1", 1, "insert2", 2, "insert3", 3])

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [as 别名]
    def _get_sample_ids(self):
        r"""Returns all the available samples for the metadata template

        Returns
        -------
        set of str
            The set of all available sample ids
        """
        with TRN:
            sql = "SELECT sample_id FROM qiita.{0} WHERE {1}=%s".format(self._table, self._id_column)
            TRN.add(sql, [self._id])
            return set(TRN.execute_fetchflatten())

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [as 别名]
    def metadata_headers():
        """Returns metadata headers available

        Returns
        -------
        list
            Alphabetical list of all metadata headers available
        """
        with TRN:
            sql = """SELECT DISTINCT column_name
                     FROM qiita.study_sample_columns ORDER BY column_name"""
            TRN.add(sql)
            return TRN.execute_fetchflatten()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [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_fetchflatten [as 别名]
# The full license is in the file LICENSE, distributed with this software.
# -----------------------------------------------------------------------------

from qiita_db.sql_connection import TRN


# Due to the size of these changes we will
with TRN:
    # select all table and column names from all sample template
    sql = """SELECT DISTINCT table_name FROM information_schema.columns
                WHERE (table_name LIKE 'sample_%'
                       OR table_name LIKE 'prep_%')
                    AND table_name NOT LIKE '%template%'"""
    TRN.add(sql)

    all_tables = TRN.execute_fetchflatten()

for table in all_tables:
    with TRN:
        sql = """SELECT column_name FROM information_schema.columns
                    WHERE table_name = %s
                    ORDER BY column_name"""
        TRN.add(sql, [table])

        for column in TRN.execute_fetchflatten():
            sql = "ALTER TABLE qiita.%s ALTER COLUMN %s TYPE VARCHAR" % (
                table, column)
            TRN.add(sql)

        TRN.execute()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [as 别名]
 def postgres_test(**kwargs):
     """Open a connection and query postgres"""
     from qiita_db.sql_connection import TRN
     with TRN:
         TRN.add("SELECT 42")
         return TRN.execute_fetchflatten()[0]

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [as 别名]
# make the RawData to be effectively just a container for the raw files,
# which is how it was acting previously.

from qiita_db.sql_connection import TRN
from qiita_db.data import RawData
from qiita_db.util import move_filepaths_to_upload_folder

with TRN:
    # the system may contain raw data with no prep template associated to it.
    # Retrieve all those raw data ids
    sql = """SELECT raw_data_id
             FROM qiita.raw_data
             WHERE raw_data_id NOT IN (
                SELECT DISTINCT raw_data_id FROM qiita.prep_template);"""
    TRN.add(sql)
    rd_ids = TRN.execute_fetchflatten()

    # We will delete those RawData. However, if they have files attached, we
    # should move them to the uploads folder of the study
    sql_detach = """DELETE FROM qiita.study_raw_data
                    WHERE raw_data_id = %s AND study_id = %s"""
    sql_unlink = "DELETE FROM qiita.raw_filepath WHERE raw_data_id = %s"
    sql_delete = "DELETE FROM qiita.raw_data WHERE raw_data_id = %s"
    sql_studies = """SELECT study_id FROM qiita.study_raw_data
                     WHERE raw_data_id = %s"""
    move_files = []
    for rd_id in rd_ids:
        rd = RawData(rd_id)
        filepaths = rd.get_filepaths()
        TRN.add(sql_studies, [rd_id])
        studies = TRN.execute_fetchflatten()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [as 别名]
# Feberuary 7, 2015
# This patch recreates all the QIIME mapping files to avoid lower/upper case
# problems. See https://github.com/biocore/qiita/issues/799
#
# heavily based on 7.py

from os.path import basename

from qiita_db.sql_connection import TRN
from qiita_db.metadata_template import PrepTemplate

with TRN:
    sql = "SELECT prep_template_id FROM qiita.prep_template"
    TRN.add(sql)
    all_ids = TRN.execute_fetchflatten()

    # remove all the bad mapping files
    for prep_template_id in all_ids:
        pt = PrepTemplate(prep_template_id)
        fps = pt.get_filepaths()

        # get the QIIME mapping file, note that the way to figure out what is
        # and what's not a qiime mapping file is to check for the existance of
        # the word qiime in the basename of the file path, hacky but that's
        # the way it is being done in qiita_pet/uimodules/raw_data_tab.py
        mapping_files = [f for f in fps if '_qiime_' in basename(f[1])]

        table = 'prep_template_filepath'
        column = 'prep_template_id'

        # unlink all the qiime mapping files for this prep template object

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [as 别名]
    def create(cls, md_template, study, data_type, investigation_type=None):
        r"""Creates the metadata template in the database

        Parameters
        ----------
        md_template : DataFrame
            The metadata template file contents indexed by samples Ids
        study : Study
            The study to which the prep template belongs to.
        data_type : str or int
            The data_type of the prep template
        investigation_type : str, optional
            The investigation type, if relevant

        Returns
        -------
        A new instance of `cls` to access to the PrepTemplate stored in the DB

        Raises
        ------
        QiitaDBColumnError
            If the investigation_type is not valid
            If a required column is missing in md_template
        """
        with TRN:
            # If the investigation_type is supplied, make sure it is one of
            # the recognized investigation types
            if investigation_type is not None:
                cls.validate_investigation_type(investigation_type)

            # Check if the data_type is the id or the string
            if isinstance(data_type, (int, long)):
                data_type_id = data_type
                data_type_str = convert_from_id(data_type, "data_type")
            else:
                data_type_id = convert_to_id(data_type, "data_type")
                data_type_str = data_type

            pt_cols = PREP_TEMPLATE_COLUMNS
            if data_type_str in TARGET_GENE_DATA_TYPES:
                pt_cols = deepcopy(PREP_TEMPLATE_COLUMNS)
                pt_cols.update(PREP_TEMPLATE_COLUMNS_TARGET_GENE)

            md_template = cls._clean_validate_template(md_template, study.id,
                                                       pt_cols)

            # Insert the metadata template
            sql = """INSERT INTO qiita.prep_template
                        (data_type_id, investigation_type)
                     VALUES (%s, %s)
                     RETURNING prep_template_id"""
            TRN.add(sql, [data_type_id, investigation_type])
            prep_id = TRN.execute_fetchlast()

            try:
                cls._common_creation_steps(md_template, prep_id)
            except Exception:
                # Check if sample IDs present here but not in sample template
                sql = """SELECT sample_id from qiita.study_sample
                         WHERE study_id = %s"""
                # Get list of study sample IDs, prep template study IDs,
                # and their intersection
                TRN.add(sql, [study.id])
                prep_samples = set(md_template.index.values)
                unknown_samples = prep_samples.difference(
                    TRN.execute_fetchflatten())
                if unknown_samples:
                    raise QiitaDBExecutionError(
                        'Samples found in prep template but not sample '
                        'template: %s' % ', '.join(unknown_samples))

                # some other error we haven't seen before so raise it
                raise

            # Link the prep template with the study
            sql = """INSERT INTO qiita.study_prep_template
                        (study_id, prep_template_id)
                     VALUES (%s, %s)"""
            TRN.add(sql, [study.id, prep_id])

            TRN.execute()

            pt = cls(prep_id)
            pt.generate_files()

            return pt

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [as 别名]
# 23 Nov, 2014
# This patch creates all the qiime mapping files for the existing
# prep templates

from qiita_db.util import get_mountpoint
from qiita_db.sql_connection import TRN
from qiita_db.metadata_template import PrepTemplate

with TRN:
    _id, fp_base = get_mountpoint('templates')[0]

    TRN.add("SELECT prep_template_id FROM qiita.prep_template")
    for prep_template_id in TRN.execute_fetchflatten():
        pt = PrepTemplate(prep_template_id)
        study_id = pt.study_id

        for _, fpt in pt.get_filepaths():
            pt.create_qiime_mapping_file(fpt)

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [as 别名]
nans = tuple(NA_VALUES)
false_vals = tuple(FALSE_VALUES)
true_vals = tuple(TRUE_VALUES)

st_update = set()
pr_update = set()

with TRN:
    sql = """SELECT table_name
             FROM information_schema.tables
             WHERE table_schema='qiita'
                AND (table_name SIMILAR TO 'sample\_[0-9]+'
                     OR table_name SIMILAR TO 'prep\_[0-9]+')"""
    TRN.add(sql)
    tables = TRN.execute_fetchflatten()

    cols_sql = """SELECT column_name
                  FROM information_schema.columns
                  WHERE table_name = %s
                  AND data_type = 'character varying'"""
    alter_sql = """ALTER TABLE qiita.{0}
                   ALTER COLUMN {1} TYPE bool
                   USING CASE
                       WHEN {1} IN %s THEN FALSE
                       WHEN {1} IN %s THEN TRUE
                   END"""
    null_sql = "UPDATE qiita.{0} SET {1} = NULL WHERE {1} IN %s"
    ssc_update_sql = """UPDATE qiita.study_sample_columns
                        SET column_type = 'bool'
                        WHERE study_id = %s AND column_name = %s"""

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [as 别名]
# Mar 27, 2015
# Need to re-generate the files, given that some headers have changed

from qiita_db.sql_connection import TRN
from qiita_db.metadata_template import SampleTemplate, PrepTemplate

with TRN:
    # Get all the sample templates
    TRN.add("SELECT DISTINCT study_id from qiita.study_sample")
    study_ids = TRN.execute_fetchflatten()

    for s_id in study_ids:
        SampleTemplate(s_id).generate_files()

    # Get all the prep templates
    TRN.add("SELECT DISTINCT prep_template_id from qiita.prep_template")
    prep_ids = TRN.execute_fetchflatten()
    for prep_id in prep_ids:
        PrepTemplate(prep_id).generate_files()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute_fetchflatten [as 别名]
def calculate(finfo):
    try:
        size = getsize(finfo['fullpath'])
    except (FileNotFoundError, PermissionError):
        return finfo, None, None

    checksum = compute_checksum(finfo['fullpath'])

    return finfo['filepath_id'], checksum, size


# get all filepaths and their filepath information; takes ~10 min
with TRN:
    TRN.add("SELECT filepath_id FROM qiita.filepath")
    files = []
    for fid in TRN.execute_fetchflatten():
        files.append(get_filepath_information(fid))


# just get the filepath ids that haven't been processed, the file format
# of this file is filepath_id[tab]checksum[tab]filesize
fpath = join(dirname(abspath(__file__)), '74.py.cache.tsv')
processed = []
if exists(fpath):
    with open(fpath, 'r') as f:
        processed = [int(line.split('\t')[0])
                     for line in f.read().split('\n') if line != '']
files_curr = [f for f in files if f['filepath_id'] not in processed]

# let's use 20 processor and in each iteration use 120 files
fids = 120