Python sql_connection.TRN类代码示例

    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]

    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 ()

    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])

    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

    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)

    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))

    def test_context_manager_multiple_2(self):
        self.assertEqual(TRN._contexts_entered, 0)

        def tester():
            self.assertEqual(TRN._contexts_entered, 1)
            with TRN:
                self.assertEqual(TRN._contexts_entered, 2)
                sql = """SELECT EXISTS(
                        SELECT * FROM ag.test_table WHERE int_column=%s)"""
                TRN.add(sql, [2])
                self.assertTrue(TRN.execute_fetchlast())
            self.assertEqual(TRN._contexts_entered, 1)

        with TRN:
            self.assertEqual(TRN._contexts_entered, 1)
            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)
            tester()
            self.assertEqual(TRN._contexts_entered, 1)
            self._assert_sql_equal([])

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

 def verifyKit(self, supplied_kit_id):
     """Set the KIT_VERIFIED for the supplied_kit_id to 'y'"""
     sql = """UPDATE AG_KIT
              SET kit_verified='y'
              WHERE supplied_kit_id=%s"""
     with TRN:
         TRN.add(sql, [supplied_kit_id])

    def test_context_manager_multiple(self):
        self.assertEqual(TRN._contexts_entered, 0)

        with TRN:
            self.assertEqual(TRN._contexts_entered, 1)

            TRN.add("SELECT 42")
            with TRN:
                self.assertEqual(TRN._contexts_entered, 2)
                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)

            # We exited the second context, nothing should have been executed
            self.assertEqual(TRN._contexts_entered, 1)
            self.assertEqual(
                TRN._connection.get_transaction_status(),
                TRANSACTION_STATUS_IDLE)
            self._assert_sql_equal([])

        # We have exited the first context, everything should have been
        # executed and committed
        self.assertEqual(TRN._contexts_entered, 0)
        self._assert_sql_equal([('insert1', True, 1), ('insert2', True, 2),
                                ('insert3', True, 3)])
        self.assertEqual(
            TRN._connection.get_transaction_status(),
            TRANSACTION_STATUS_IDLE)

    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]

    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]

 def american_name(self):
     """Gets the locale-specific name of the group"""
     with TRN:
         sql = """SELECT american
                  FROM {0}
                  WHERE group_order = %s""".format(self._group_table)
         TRN.add(sql, [self.id])
         return TRN.execute_fetchlast()

 def _get_unverified_kits(self):
     """Gets list of unverified kit IDs, Helper function for tests"""
     sql = """SELECT supplied_kit_id
              FROM AG_KIT
              WHERE NOT kit_verified = 'y'"""
     with TRN:
         TRN.add(sql)
         return TRN.execute_fetchflatten()

    def test_post_commit_funcs_error(self):
        def func():
            raise ValueError()

        with self.assertRaises(RuntimeError):
            with TRN:
                TRN.add("SELECT 42")
                TRN.add_post_commit_func(func)

 def tester():
     self.assertEqual(TRN._contexts_entered, 1)
     with TRN:
         self.assertEqual(TRN._contexts_entered, 2)
         sql = """SELECT EXISTS(
                 SELECT * FROM ag.test_table WHERE int_column=%s)"""
         TRN.add(sql, [2])
         self.assertTrue(TRN.execute_fetchlast())
     self.assertEqual(TRN._contexts_entered, 1)