Python TRN.execute_fetchindex方法代码示例

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def test_sources(self):
        with TRN:
            sql = """SELECT barcode
                     FROM source_barcodes_surveys
                     JOIN ag.ag_login_surveys USING (survey_id)
                     WHERE ag_login_id = %s AND survey_id = %s"""

            exp_barcodes = ['000063476', '000063477', '000063478',
                            '000063479', '000063480', '000063481',
                            '000063482', '000063483', '000063484',
                            '000063487']
            TRN.add(sql, ['0097e665-ea1d-483d-b248-402bcf6abf2a',
                          '5c7106b35dda787d'])
            obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
            self.assertEqual(set(exp_barcodes), set(obs_barcodes))
            TRN.add(sql, ['0097e665-ea1d-483d-b248-402bcf6abf2a',
                          'fb6d5a66ef0dd8c7'])
            obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
            self.assertEqual(set(exp_barcodes), set(obs_barcodes))

            exp_barcodes = ['000046215']
            TRN.add(sql, ['0073af72-39e5-4bc8-9908-eff6c4ce2d6c',
                          'db2e324b45e34b97'])
            obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
            self.assertEqual(set(exp_barcodes), set(obs_barcodes))
            TRN.add(sql, ['0073af72-39e5-4bc8-9908-eff6c4ce2d6c',
                          'eea585c6eb5dd4b5'])
            obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
            self.assertEqual(set(exp_barcodes), set(obs_barcodes))
            TRN.add(sql, ['0073af72-39e5-4bc8-9908-eff6c4ce2d6c',
                          'fb420871cdcf5adb'])
            obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
            self.assertEqual(set(exp_barcodes), set(obs_barcodes))

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def test_execute_fetchindex(self):
        with TRN:
            sql = """INSERT INTO ag.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 ag.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 amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def _triggers(self):
        """What other question-response combinations this question can trigger

        Returns
        -------
        tuple
            (other_question_id, [triggering indices to that question])
        """
        with TRN:
            sql = """SELECT triggered_question, display_index
                     FROM {0} sst
                     JOIN {1} sqr
                        ON sst.survey_question_id=sqr.survey_question_id
                        AND sqr.response=sst.triggering_response
                     WHERE sst.survey_question_id = %s
                     ORDER BY triggered_question
                 """.format(self._supplemental_survey_table,
                            self._question_response_table)
            TRN.add(sql, [self.id])
            trigger_list = TRN.execute_fetchindex()

            results = defaultdict(list)
            for question, index in trigger_list:
                results[question].append(index)

            if results:
                return results
            else:
                return ()

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def get_survey_ids(self, ag_login_id, participant_name):
        """Return the survey IDs associated with a participant or None

        Parameters
        ----------
        ag_login_id : str
            A valid login ID, that should be a test as a valid UUID
        participant_name : str
            A participant name

        Returns
        -------
        dict or None
            The survey IDs keyed to the survey id,
            or None if a survey ID cannot be found.

        Raises
        ------
        ValueError
            Unknown ag_login_id or participant_name passed
        """
        with TRN:
            sql = """SELECT DISTINCT s.survey_id, als.survey_id
                     FROM ag.ag_login_surveys als
                     LEFT JOIN ag.survey_answers sa USING (survey_id)
                     LEFT JOIN ag.group_questions gq USING (survey_question_id)
                     LEFT JOIN ag.surveys s USING (survey_group)
                     WHERE ag_login_id=%s AND participant_name=%s"""
            TRN.add(sql, [ag_login_id, participant_name])
            survey_id = TRN.execute_fetchindex()
            if not survey_id:
                raise ValueError("No survey ID found!")
            return dict(i for i in survey_id)

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def logParticipantSample(self, ag_login_id, barcode, sample_site,
                             environment_sampled, sample_date, sample_time,
                             participant_name, notes):
        with TRN:
            if sample_site is not None:
                # Get survey id
                sql = """SELECT survey_id
                         FROM ag_login_surveys
                         WHERE ag_login_id = %s AND participant_name = %s"""

                TRN.add(sql, (ag_login_id, participant_name))
                survey_id = TRN.execute_fetchindex()
                if not survey_id:
                    raise ValueError("No survey ID for ag_login_id %s and "
                                     "participant name %s" %
                                     (ag_login_id, participant_name))
                survey_id = survey_id[0][0]
            else:
                # otherwise, it is an environmental sample
                survey_id = None

            # Add barcode info
            sql = """UPDATE ag_kit_barcodes
                     SET site_sampled = %s, environment_sampled = %s,
                         sample_date = %s, sample_time = %s,
                         participant_name = %s, notes = %s, survey_id = %s
                     WHERE barcode = %s"""
            TRN.add(sql, [sample_site, environment_sampled, sample_date,
                          sample_time, participant_name, notes, survey_id,
                          barcode])

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def get_login_info(self, ag_login_id):
        """Get kit registration information

        Parameters
        ----------
        ag_login_id : str
            A valid login ID, that should be a test as a valid UUID

        Returns
        -------
        list of dict
            A list of registration information associated with a common login
            ID.

        Raises
        ------
        ValueError
            Unknown ag_login_id passed
        """
        with TRN:
            sql = """SELECT ag_login_id, email, name, address, city, state,
                            zip, country
                     FROM ag_login
                     WHERE ag_login_id = %s"""
            TRN.add(sql, [ag_login_id])
            info = TRN.execute_fetchindex()
            if not info:
                raise ValueError('ag_login_id not in database: %s' %
                                 ag_login_id)
            return [dict(row) for row in info]

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def get_survey_id(self, ag_login_id, participant_name):
        """Return the survey ID associated with a participant or None

        Parameters
        ----------
        ag_login_id : str
            A valid login ID, that should be a test as a valid UUID
        participant_name : str
            A participant name

        Returns
        -------
        str or None
            The survey ID, or None if a survey ID cannot be found.

        Raises
        ------
        ValueError
            Unknown ag_login_id or participant_name passed
        """
        with TRN:
            sql = """SELECT survey_id
                     FROM ag_login_surveys
                     WHERE ag_login_id=%s AND participant_name=%s"""
            TRN.add(sql, [ag_login_id, participant_name])
            survey_id = TRN.execute_fetchindex()
            if not survey_id:
                raise ValueError("No survey ID found!")
            return survey_id[0][0]

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def get_vioscreen_status(self, survey_id):
        """Retrieves the vioscreen status for a survey_id

        Parameters
        ----------
        survey_id : str
            The survey to get status for

        Returns
        -------
        int
            Vioscreen status

        Raises
        ------
        ValueError
            survey_id passed is not in the database
        """
        with TRN:
            sql = """SELECT vioscreen_status
                     FROM ag.ag_login_surveys
                     WHERE survey_id = %s"""
            TRN.add(sql, [survey_id])
            status = TRN.execute_fetchindex()
            if not status:
                raise ValueError("Survey ID %s not in database" % survey_id)
            return status[0][0]

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def authenticateWebAppUser(self, username, password):
        """ Attempts to validate authenticate the supplied username/password

        Attempt to authenticate the user against the list of users in
        web_app_user table. If successful, a dict with user innformation is
        returned. If not, the function returns False.
        """
        with TRN:
            sql = """SELECT  cast(ag_login_id as varchar(100)) as ag_login_id,
                      email, name, address, city,
                      state, zip, country,kit_password
                    FROM ag_login
                INNER JOIN ag_kit USING (ag_login_id)
                WHERE supplied_kit_id = %s"""
            TRN.add(sql, [username])
            row = TRN.execute_fetchindex()
            if not row:
                return False

            results = dict(row[0])

            if not bcrypt.verify(password, results['kit_password']):
                return False
            results['ag_login_id'] = str(results['ag_login_id'])

            return results

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def __init__(self, ID):
        with TRN:
            self.id = ID
            n = self.american_name

            sql = """SELECT gq.survey_question_id
                     FROM {0} sg
                     JOIN {1} gq ON sg.group_order = gq.survey_group
                     LEFT JOIN {2} sq USING (survey_question_id)
                     WHERE sg.group_order = %s AND sq.retired = FALSE
                     ORDER BY gq.display_index
                  """.format(self._group_table, self._group_questions_table,
                             self._questions_table)
            TRN.add(sql, [self.id])
            results = TRN.execute_fetchindex()
            qs = [Question.factory(x[0], n) for x in results]

            self.id_to_eid = {q.id: q.interface_element_ids for q in qs}

            self.question_lookup = {q.id: q for q in qs}
            self.questions = qs

            self.supplemental_eids = set()
            for q in qs:
                for id_ in q.triggers:
                    triggered = self.question_lookup[id_]
                    triggered_eids = triggered.interface_element_ids
                    self.supplemental_eids.update(set(triggered_eids))

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def fetch_survey(self, survey_id):
        """Return {element_id: answer}

        The answer is in the form of ["display_index"] or ["text"] depending on
        if the answer has a foreign key or not. These data are serialized for
        input into a WTForm.
        """
        with TRN:
            sql = """SELECT survey_question_id, display_index,
                            survey_response_type
                     FROM {0}
                     JOIN {1} USING (response, survey_question_id)
                     JOIN {2} USING (survey_question_id)
                     LEFT JOIN {3} USING (survey_question_id)
                     WHERE survey_id = %s AND retired = FALSE""".format(
                self._survey_answers_table,
                self._survey_question_response_table,
                self._survey_question_response_type_table,
                self._questions_table)
            TRN.add(sql, [survey_id])
            answers = TRN.execute_fetchindex()

            TRN.add("""SELECT survey_question_id, response
                       FROM {0}
                       LEFT JOIN {1} using (survey_question_id)
                       WHERE survey_id = %s AND retired = FALSE""".format(
                self._survey_answers_other_table, self._questions_table),
                [survey_id])
            answers_other = TRN.execute_fetchindex()

            survey = defaultdict(list)
            for qid, idx, qtype in answers:
                eid = self.questions[qid].interface_element_ids[0]
                if qtype == 'SINGLE':
                    survey[eid] = idx
                else:
                    survey[eid].append(idx)

            for qid, data in answers_other:
                eid = self.questions[qid].interface_element_ids[0]
                data = data.strip(' []"')
                survey[eid] = data

            if len(survey) == 0:
                raise ValueError("Survey answers do not exist in DB: %s" %
                                 survey_id)
            return survey

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def handoutCheck(self, username, password):
        with TRN:
            sql = "SELECT password FROM ag.ag_handout_kits WHERE kit_id = %s"
            TRN.add(sql, [username])
            to_check = TRN.execute_fetchindex()

            if not to_check:
                return False
            else:
                return bcrypt.verify(password, to_check[0][0])

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
 def getParticipantSamples(self, ag_login_id, participant_name):
     sql = """SELECT  barcode, site_sampled, sample_date, sample_time,
                 notes, status
              FROM ag_kit_barcodes akb
              INNER JOIN barcode USING (barcode)
              INNER JOIN ag_kit ak USING (ag_kit_id)
              WHERE (site_sampled IS NOT NULL AND site_sampled::text <> '')
              AND ag_login_id = %s AND participant_name = %s"""
     with TRN:
         TRN.add(sql, [ag_login_id, participant_name])
         rows = TRN.execute_fetchindex()
         return [dict(row) for row in rows]

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
 def get_user_for_kit(self, supplied_kit_id):
     with TRN:
         sql = """SELECT ag_login_id
                  FROM ag.ag_kit
                  JOIN ag_login USING (ag_login_id)
                  WHERE supplied_kit_id = %s"""
         TRN.add(sql, [supplied_kit_id])
         results = TRN.execute_fetchindex()
         if results:
             return results[0][0]
         else:
             raise ValueError("No user ID for kit %s" % supplied_kit_id)

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchindex [as 别名]
    def test_oldtables(self):
        # check that we have 13512 distinct "sources" in ag_login_surveys,
        # which are of tuple ag_login_id, participant_name
        with TRN:
            sql = """SELECT COUNT(*)
                     FROM (SELECT DISTINCT ag_login_id, participant_name
                           FROM ag.ag_login_surveys) AS foo"""
            TRN.add(sql, [])
            num_sources = TRN.execute_fetchindex()[0][0]
        self.assertEqual(num_sources, 13512)

        with TRN:
            sql = """SELECT COUNT(*)
                     FROM (SELECT DISTINCT ag_login_id, participant_name
                           FROM ag.ag_login_surveys
                           WHERE vioscreen_status IS NOT NULL) AS foo"""
            TRN.add(sql, [])
            num_vio_sources = TRN.execute_fetchindex()[0][0]
        self.assertEqual(num_vio_sources, 1692)

        # check number of unique surveys
        with TRN:
            sql = """SELECT COUNT(DISTINCT survey_id)
                     FROM ag.ag_login_surveys"""
            TRN.add(sql, [])
            num_surveys = TRN.execute_fetchindex()[0][0]
        self.assertEqual(num_surveys, 13850)

        # check number of sources with more than one survey
        with TRN:
            sql = """SELECT COUNT(*)
                     FROM (SELECT ag_login_id,
                                  participant_name,
                                  count(survey_id) AS cs
                           FROM ag.ag_login_surveys
                           GROUP BY ag_login_id, participant_name) AS foo
                           WHERE cs > 1"""
            TRN.add(sql, [])
            num_multi_sources = TRN.execute_fetchindex()[0][0]
        self.assertEqual(num_multi_sources, 314)

        # check number of consents
        with TRN:
            sql = """SELECT COUNT(*)
                     FROM (SELECT DISTINCT ag_login_id, participant_name
                           FROM ag.ag_consent) AS foo"""
            TRN.add(sql, [])
            num_consent = TRN.execute_fetchindex()[0][0]
        self.assertEqual(num_consent, 13514)
        # why do we have two more consents than sources (both for ag_login_id
        # e1934ceb-6e92-c36a-e040-8a80115d2d64)??

        # check number of barcodes
        with TRN:
            sql = """SELECT COUNT(*)
                     FROM (SELECT DISTINCT barcode
                           FROM ag.ag_kit_barcodes) AS foo"""
            TRN.add(sql, [])
            num_barcodes = TRN.execute_fetchindex()[0][0]
        self.assertEqual(num_barcodes, 28865)