Python TRN.execute方法代码示例

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
    def test_delete_study(self):
        # as samples have been submitted to EBI, this will fail
        job = self._create_job('delete_study', {'study': 1})
        private_task(job.id)
        self.assertEqual(job.status, 'error')
        self.assertIn("Cannot delete artifact 2: it has been "
                      "submitted to EBI", job.log.msg)

        # delete everything from the EBI submissions and the processing job so
        # we can try again: test success (with tags)
        with TRN:
            sql = """DELETE FROM qiita.ebi_run_accession"""
            TRN.add(sql)
            sql = """DELETE FROM qiita.artifact_processing_job"""
            TRN.add(sql)
            TRN.execute()

            # adding tags
            Study(1).update_tags(self.user, ['my new tag!'])

            job = self._create_job('delete_study', {'study': 1})
            private_task(job.id)

            self.assertEqual(job.status, 'success')
            with self.assertRaises(QiitaDBUnknownIDError):
                Study(1)

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
    def _common_creation_steps(cls, md_template, obj_id):
        r"""Executes the common creation steps

        Parameters
        ----------
        md_template : DataFrame
            The metadata template file contents indexed by sample ids
        obj_id : int
            The id of the object being created
        """
        with TRN:
            cls._check_subclass()

            # Get some useful information from the metadata template
            sample_ids = md_template.index.tolist()
            headers = sorted(md_template.keys().tolist())

            # Insert values on template_sample table
            values = [[obj_id, s_id] for s_id in sample_ids]
            sql = """INSERT INTO qiita.{0} ({1}, sample_id)
                     VALUES (%s, %s)""".format(cls._table, cls._id_column)
            TRN.add(sql, values, many=True)

            # Insert rows on *_columns table
            datatypes = get_datatypes(md_template.ix[:, headers])
            # psycopg2 requires a list of tuples, in which each tuple is a set
            # of values to use in the string formatting of the query. We have
            # all the values in different lists (but in the same order) so use
            # zip to create the list of tuples that psycopg2 requires.
            values = [[obj_id, h, d] for h, d in zip(headers, datatypes)]
            sql = """INSERT INTO qiita.{0} ({1}, column_name, column_type)
                     VALUES (%s, %s, %s)""".format(cls._column_table,
                                                   cls._id_column)
            TRN.add(sql, values, many=True)

            # Create table with custom columns
            table_name = cls._table_name(obj_id)
            column_datatype = ["%s %s" % (col, dtype)
                               for col, dtype in zip(headers, datatypes)]
            sql = """CREATE TABLE qiita.{0} (
                        sample_id varchar NOT NULL, {1},
                        CONSTRAINT fk_{0} FOREIGN KEY (sample_id)
                            REFERENCES qiita.study_sample (sample_id)
                            ON UPDATE CASCADE
                     )""".format(table_name, ', '.join(column_datatype))
            TRN.add(sql)

            # Insert values on custom table
            values = as_python_types(md_template, headers)
            values.insert(0, sample_ids)
            values = [list(v) for v in zip(*values)]
            sql = """INSERT INTO qiita.{0} (sample_id, {1})
                     VALUES (%s, {2})""".format(
                table_name, ", ".join(headers),
                ', '.join(["%s"] * len(headers)))
            TRN.add(sql, values, many=True)

            # Execute all the steps
            TRN.execute()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [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 [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 [as 别名]
    def test_context_manager_no_commit(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)

            TRN.execute()
            self._assert_sql_equal([])

        self._assert_sql_equal([("insert1", True, 1), ("insert2", True, 2), ("insert3", True, 3)])
        self.assertEqual(TRN._connection.get_transaction_status(), TRANSACTION_STATUS_IDLE)

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
    def test_context_manager_rollback(self):
        try:
            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)

                TRN.execute()
                raise ValueError("Force exiting the context manager")
        except ValueError:
            pass
        self._assert_sql_equal([])
        self.assertEqual(TRN._connection.get_transaction_status(), TRANSACTION_STATUS_IDLE)

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [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 [as 别名]
    def add_filepath(self, filepath, fp_id=None):
        r"""Populates the DB tables for storing the filepath and connects the
        `self` objects with this filepath"""
        with TRN:
            fp_id = self._fp_id if fp_id is None else fp_id

            try:
                fpp_id = insert_filepaths([(filepath, fp_id)], None, "templates", "filepath", move_files=False)[0]
                sql = """INSERT INTO qiita.{0} ({1}, filepath_id)
                         VALUES (%s, %s)""".format(
                    self._filepath_table, self._id_column
                )
                TRN.add(sql, [self._id, fpp_id])
                TRN.execute()
            except Exception as e:
                LogEntry.create("Runtime", str(e), info={self.__class__.__name__: self.id})
                raise e

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [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 [as 别名]
    def test_context_manager_checker(self):
        with self.assertRaises(RuntimeError):
            TRN.add("SELECT 42")

        with self.assertRaises(RuntimeError):
            TRN.execute()

        with self.assertRaises(RuntimeError):
            TRN.commit()

        with self.assertRaises(RuntimeError):
            TRN.rollback()

        with TRN:
            TRN.add("SELECT 42")

        with self.assertRaises(RuntimeError):
            TRN.execute()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
    def test_index(self):
        with TRN:
            self.assertEqual(TRN.index, 0)

            TRN.add("SELECT 42")
            self.assertEqual(TRN.index, 1)

            sql = "INSERT INTO qiita.test_table (int_column) VALUES (%s)"
            args = [[1], [2], [3]]
            TRN.add(sql, args, many=True)
            self.assertEqual(TRN.index, 4)

            TRN.execute()
            self.assertEqual(TRN.index, 4)

            TRN.add(sql, args, many=True)
            self.assertEqual(TRN.index, 7)

        self.assertEqual(TRN.index, 0)

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
 def test_execute_return(self):
     with TRN:
         sql = """INSERT INTO qiita.test_table (str_column, int_column)
                  VALUES (%s, %s) RETURNING str_column, int_column"""
         TRN.add(sql, ["test_insert", 2])
         sql = """UPDATE qiita.test_table SET bool_column = %s
                  WHERE str_column = %s RETURNING int_column"""
         TRN.add(sql, [False, "test_insert"])
         obs = TRN.execute()
         self.assertEqual(obs, [[["test_insert", 2]], [[2]]])

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [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 [as 别名]
    def preprocessing_status(self, state):
        r"""Update the preprocessing status

        Parameters
        ----------
        state : str, {'not_preprocessed', 'preprocessing', 'success', 'failed'}
            The current status of preprocessing

        Raises
        ------
        ValueError
            If the state is not known.
        """
        if (state not in ('not_preprocessed', 'preprocessing', 'success') and
                not state.startswith('failed:')):
            raise ValueError('Unknown state: %s' % state)
        with TRN:
            sql = """UPDATE qiita.prep_template SET preprocessing_status = %s
                     WHERE {0} = %s""".format(self._id_column)
            TRN.add(sql, [state, self.id])
            TRN.execute()

# 需要导入模块: from qiita_db.sql_connection import TRN [as 别名]
# 或者: from qiita_db.sql_connection.TRN import execute [as 别名]
    def investigation_type(self, investigation_type):
        r"""Update the investigation type

        Parameters
        ----------
        investigation_type : str
            The investigation type to set, should be part of the ENA ontology

        Raises
        ------
        QiitaDBColumnError
            If the investigation type is not a valid ENA ontology
        """
        with TRN:
            if investigation_type is not None:
                self.validate_investigation_type(investigation_type)

            sql = """UPDATE qiita.prep_template SET investigation_type = %s
                     WHERE {0} = %s""".format(self._id_column)
            TRN.add(sql, [investigation_type, self.id])
            TRN.execute()