Python SQLConnectionHandler.add_to_queue方法代码示例

# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import add_to_queue [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
        """
        cls._check_subclass()

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

        raw_datas = [str(rd) for rd in Study(cls(id_).study_id).raw_data()]
        if raw_datas:
            raise QiitaDBError("Sample template can not be erased because "
                               "there are raw datas (%s) associated." %
                               ', '.join(raw_datas))

        table_name = cls._table_name(id_)
        conn_handler = SQLConnectionHandler()

        # Delete the sample template filepaths
        queue = "delete_sample_template_%d" % id_
        conn_handler.create_queue(queue)

        conn_handler.add_to_queue(
            queue,
            "DELETE FROM qiita.sample_template_filepath WHERE study_id = %s",
            (id_, ))

        conn_handler.add_to_queue(
            queue,
            "DROP TABLE qiita.{0}".format(table_name))

        conn_handler.add_to_queue(
            queue,
            "DELETE FROM qiita.{0} where {1} = %s".format(cls._table,
                                                          cls._id_column),
            (id_,))

        conn_handler.add_to_queue(
            queue,
            "DELETE FROM qiita.{0} where {1} = %s".format(cls._column_table,
                                                          cls._id_column),
            (id_,))

        conn_handler.execute_queue(queue)

# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import add_to_queue [as 别名]
                 WHERE raw_data_id = %s"""
move_files = []
for rd_id in rd_ids:
    rd = RawData(rd_id)
    filepaths = rd.get_filepaths()
    studies = [s[0] for s in conn_handler.execute_fetchall(sql_studies,
                                                           (rd_id,))]
    if filepaths:
        # we need to move the files to a study. We chose the one with lower
        # study id. Currently there is no case in the live database in which a
        # RawData with no prep templates is attached to more than one study,
        # but I think it is better to normalize this just in case
        move_files.append((min(studies), filepaths))

    # To delete the RawData we first need to unlink all the files
    conn_handler.add_to_queue(queue, sql_unlink, (rd_id,))

    # Then, remove the raw data from all the studies
    for st_id in studies:
        conn_handler.add_to_queue(queue, sql_detach, (rd_id, st_id))

    conn_handler.add_to_queue(queue, sql_delete, (rd_id,))

# We can now perform all changes in the DB. Although these changes can be
# done in an SQL patch, they are done here because we need to execute the
# previous clean up in the database before we can actually execute the SQL
# patch.
sql = """CREATE TABLE qiita.study_prep_template (
    study_id             bigint  NOT NULL,
    prep_template_id     bigint  NOT NULL,
    CONSTRAINT idx_study_prep_template

# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import add_to_queue [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
        """
        # 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)

        # Get a connection handler
        conn_handler = SQLConnectionHandler()
        queue_name = "CREATE_PREP_TEMPLATE_%d_%d" % (study.id, id(md_template))
        conn_handler.create_queue(queue_name)

        # 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
        # We need the prep_id for multiple calls below, which currently is not
        # supported by the queue system. Thus, executing this outside the queue
        prep_id = conn_handler.execute_fetchone(
            "INSERT INTO qiita.prep_template "
            "(data_type_id, investigation_type) "
            "VALUES (%s, %s) RETURNING prep_template_id",
            (data_type_id, investigation_type))[0]

        cls._add_common_creation_steps_to_queue(md_template, prep_id,
                                                conn_handler, queue_name)

        # Link the prep template with the study
        sql = ("INSERT INTO qiita.study_prep_template "
               "(study_id, prep_template_id) VALUES (%s, %s)")
        conn_handler.add_to_queue(queue_name, sql, (study.id, prep_id))

        try:
            conn_handler.execute_queue(queue_name)
        except Exception:
            # Clean up row from qiita.prep_template
            conn_handler.execute(
                "DELETE FROM qiita.prep_template where "
                "{0} = %s".format(cls._id_column), (prep_id,))

            # 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
            prep_samples = set(md_template.index.values)
            unknown_samples = prep_samples.difference(
                s[0] for s in conn_handler.execute_fetchall(sql, [study.id]))
            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

        pt = cls(prep_id)
        pt.generate_files()

        return pt

# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import add_to_queue [as 别名]
class TestConnHandler(TestCase):
    def test_create_queue(self):
        self.conn_handler.create_queue("toy_queue")
        self.assertEqual(self.conn_handler.list_queues(), ["toy_queue"])

    def test_close(self):
        self.assertEqual(self.conn_handler._user_conn.closed, 0)
        self.conn_handler.close()
        self.assertNotEqual(self.conn_handler._user_conn.closed, 0)

    def test_run_queue(self):
        self.conn_handler.create_queue("toy_queue")
        self.conn_handler.add_to_queue(
            "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password,"
            "phone) VALUES (%s, %s, %s, %s)",
            ['[email protected]', 'Toy', 'pass', '111-111-11112'])
        self.conn_handler.add_to_queue(
            "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1, "
            "phone = '222-222-2221' WHERE email = %s",
            ['[email protected]'])
        obs = self.conn_handler.execute_queue("toy_queue")
        self.assertEqual(obs, [])
        obs = self.conn_handler.execute_fetchall(
            "SELECT * from qiita.qiita_user WHERE email = %s",
            ['[email protected]'])
        exp = [['[email protected]', 1, 'pass', 'Toy', None, None, '222-222-2221',
                None, None, None]]
        self.assertEqual(obs, exp)

    def test_run_queue_many(self):
        sql = ("INSERT INTO qiita.qiita_user (email, name, password,"
               "phone) VALUES (%s, %s, %s, %s)")
        sql_args = [
            ('[email protected]', 'p1', 'pass1', '111-111'),
            ('[email protected]', 'p2', 'pass2', '111-222')
            ]
        self.conn_handler.create_queue("toy_queue")
        self.conn_handler.add_to_queue(
            "toy_queue", sql, sql_args, many=True)
        self.conn_handler.execute_queue('toy_queue')

        # make sure both users added
        obs = self.conn_handler.execute_fetchall(
            "SELECT * from qiita.qiita_user WHERE email = %s",
            ['[email protected]'])
        exp = [['[email protected]', 5, 'pass1', 'p1', None, None, '111-111',
                None, None, None]]
        self.assertEqual(obs, exp)
        obs = self.conn_handler.execute_fetchall(
            "SELECT * from qiita.qiita_user WHERE email = %s",
            ['[email protected]'])
        exp = [['[email protected]', 5, 'pass2', 'p2', None, None, '111-222',
                None, None, None]]
        self.assertEqual(obs, exp)

    def test_run_queue_last_return(self):
        self.conn_handler.create_queue("toy_queue")
        self.conn_handler.add_to_queue(
            "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password,"
            "phone) VALUES (%s, %s, %s, %s)",
            ['[email protected]', 'Toy', 'pass', '111-111-11112'])
        self.conn_handler.add_to_queue(
            "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1, "
            "phone = '222-222-2221' WHERE email = %s RETURNING phone",
            ['[email protected]'])
        obs = self.conn_handler.execute_queue("toy_queue")
        self.assertEqual(obs, ['222-222-2221'])

    def test_run_queue_placeholders(self):
        self.conn_handler.create_queue("toy_queue")
        self.conn_handler.add_to_queue(
            "toy_queue", "INSERT INTO qiita.qiita_user (email, name, password,"
            "phone) VALUES (%s, %s, %s, %s) RETURNING email, password",
            ['[email protected]', 'Toy', 'pass', '111-111-11112'])
        self.conn_handler.add_to_queue(
            "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1, "
            "phone = '222-222-2221' WHERE email = %s AND password = %s",
            ['{0}', '{1}'])
        obs = self.conn_handler.execute_queue("toy_queue")
        self.assertEqual(obs, [])
        obs = self.conn_handler.execute_fetchall(
            "SELECT * from qiita.qiita_user WHERE email = %s",
            ['[email protected]'])
        exp = [['[email protected]', 1, 'pass', 'Toy', None, None, '222-222-2221',
                None, None, None]]
        self.assertEqual(obs, exp)

    def test_queue_fail(self):
        """Fail if no results data exists for substitution"""
        self.conn_handler = SQLConnectionHandler()
        self.conn_handler.create_queue("toy_queue")
        self.conn_handler.add_to_queue(
            "toy_queue",
            "INSERT INTO qiita.qiita_user (email, name, password) VALUES "
            "(%s, %s, %s)", ['[email protected]', 'Toy', 'pass'])
        self.conn_handler.add_to_queue(
            "toy_queue", "UPDATE qiita.qiita_user SET user_level_id = 1 "
            "WHERE email = %s and password = %s", [{0}, {1}])

        with self.assertRaises(QiitaDBExecutionError):
#.........这里部分代码省略.........

# 需要导入模块: from qiita_db.sql_connection import SQLConnectionHandler [as 别名]
# 或者: from qiita_db.sql_connection.SQLConnectionHandler import add_to_queue [as 别名]
    table = 'prep_template_filepath'
    column = 'prep_template_id'

    # unlink all the qiime mapping files for this prep template object
    for mf in mapping_files:

        # (1) get the ids that we are going to delete.
        # because of the FK restriction, we cannot just delete the ids
        ids = conn_handler.execute_fetchall(
            'SELECT filepath_id FROM qiita.{0} WHERE '
            '{1}=%s and filepath_id=%s'.format(table, column), (pt.id, mf[0]))
        ids = flatten(ids)

        # (2) delete the entries from the prep_template_filepath table
        conn_handler.add_to_queue(
            q_name, "DELETE FROM qiita.{0} "
            "WHERE {1}=%s and filepath_id=%s;".format(table, column),
            (pt.id, mf[0]))

        # (3) delete the entries from the filepath table
        conn_handler.add_to_queue(
            q_name,
            "DELETE FROM qiita.filepath WHERE "
            "filepath_id IN ({0});".format(', '.join(map(str, ids))))

try:
    conn_handler.execute_queue(q_name)
except Exception as e:
    raise

# create correct versions of the mapping files
for prep_template_id in all_ids: