Python TRN.execute_fetchlast方法代码示例

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
    def is_deposited_ebi(self, barcode):
        """Check if barcode is deposited to EBI

        Parameters
        ----------
        barcode : str
            Barcode to check

        Returns
        -------
        bool
            If the barcode has been deposited (True) or has not (False)

        Raises
        ------
        ValueError
            Barcode is not a registered AG barcodes
        """
        with TRN:
            sql = """SELECT EXISTS(
                     SELECT 1 FROM ag.ag_kit_barcodes WHERE barcode = %s)"""
            TRN.add(sql, [barcode])
            if not TRN.execute_fetchlast():
                raise ValueError('Barcode %s not a registered AG barcode' %
                                 barcode)

            sql = "SELECT deposited FROM ag.ag_kit_barcodes WHERE barcode = %s"
            TRN.add(sql, [barcode])
            return TRN.execute_fetchlast()

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

            sql = """SELECT sr.{0}
                     FROM {1} q
                     JOIN {2} qr
                         ON q.survey_question_id = qr.survey_question_id
                     JOIN {3} sr
                         ON qr.response = sr.{0}
                     WHERE q.survey_question_id = %s
                     ORDER BY qr.display_index
                  """.format(_LOCALE_COLUMN, self._survey_question_table,
                             self._question_response_table,
                             self._response_table)
            TRN.add(sql, [self.id])
            self.responses = TRN.execute_fetchflatten()

            if not self.responses:
                self.responses = None

            sql = """SELECT survey_response_type
                     FROM {0}
                     WHERE survey_question_id = %s
                  """.format(self._response_type_table)
            TRN.add(sql, [self.id])
            self.response_type = TRN.execute_fetchlast()

            sql = """SELECT {0}
                     FROM {1}
                     WHERE survey_question_id = %s
                  """.format(_LOCALE_COLUMN, self._survey_question_table)
            TRN.add(sql, [self.id])
            self.question = TRN.execute_fetchlast()

            sql = """SELECT american
                     FROM {0}
                     WHERE survey_question_id = %s
                  """.format(self._survey_question_table)
            TRN.add(sql, [self.id])
            self.american_question = TRN.execute_fetchlast()

            self.triggers = self._triggers()
            self.qid = '_'.join(self.group_name.split() + [str(self.id)])

            element_ids, elements = self._interface_elements()
            self.interface_elements = elements
            self.interface_element_ids = ['%s_%d' % (self.qid, i)
                                          for i in element_ids]

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
def patch_db(patches_dir=PATCHES_DIR, verbose=False):
    """Patches the database schema based on the settings table

    Pulls the current patch from the settings table and applies all subsequent
    patches found in the patches directory.
    """
    with TRN:
        TRN.add("SELECT current_patch FROM settings")
        current_patch = TRN.execute_fetchlast()
        current_patch_fp = join(patches_dir, current_patch)

        sql_glob = join(patches_dir, '*.sql')
        patch_files = natsorted(glob(sql_glob))

        if current_patch == 'unpatched':
            next_patch_index = 0
        elif current_patch_fp not in patch_files:
            raise RuntimeError("Cannot find patch file %s" % current_patch)
        else:
            next_patch_index = patch_files.index(current_patch_fp) + 1

        patch_update_sql = "UPDATE settings SET current_patch = %s"

        for patch_fp in patch_files[next_patch_index:]:
            patch_filename = split(patch_fp)[-1]

            with open(patch_fp, 'U') as patch_file:
                if verbose:
                    echo('\tApplying patch %s...' % patch_filename)

                TRN.add(patch_file.read())
                TRN.add(patch_update_sql, [patch_filename])

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
    def deleteAGParticipantSurvey(self, ag_login_id, participant_name):
        # Remove user from new schema
        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_fetchlast()

            sql = "DELETE FROM survey_answers WHERE survey_id = %s"
            TRN.add(sql, [survey_id])

            sql = "DELETE FROM survey_answers_other WHERE survey_id = %s"
            TRN.add(sql, [survey_id])

            # Reset survey attached to barcode(s)
            sql = """UPDATE ag_kit_barcodes
                     SET survey_id = NULL
                     WHERE survey_id = %s"""
            TRN.add(sql, [survey_id])

            sql = "DELETE FROM promoted_survey_ids WHERE survey_id = %s"
            TRN.add(sql, [survey_id])

            # Delete last due to foreign keys
            sql = "DELETE FROM ag_login_surveys WHERE survey_id = %s"
            TRN.add(sql, [survey_id])

            sql = """DELETE FROM ag_consent
                     WHERE ag_login_id = %s AND participant_name = %s"""
            TRN.add(sql, [ag_login_id, participant_name])

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
 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()

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
 def check_if_consent_exists(self, ag_login_id, participant_name):
     """Return True if a consent already exists"""
     with TRN:
         sql = """SELECT EXISTS(
                     SELECT 1
                     FROM ag_consent
                     WHERE ag_login_id = %s AND participant_name = %s)"""
         TRN.add(sql, [ag_login_id, participant_name])
         return TRN.execute_fetchlast()

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
 def name(self):
     """Gets the locale-specific name of the group"""
     with TRN:
         sql = """SELECT {0}
                  FROM {1}
                  WHERE group_order = %s""".format(_LOCALE_COLUMN,
                                                   self._group_table)
         TRN.add(sql, [self.id])
         return TRN.execute_fetchlast()

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
 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)

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
def patch_number():
    # Make sure the system is using the latest patch before starting up.
    with TRN:
        TRN.add('SELECT current_patch FROM ag.settings')
        system = TRN.execute_fetchlast()
        patches_dir = join(dirname(abspath(__file__)), '../db/patches')
        latest = sorted(listdir(patches_dir)).pop()
        if latest != system:
            raise EnvironmentError(("Not running latest patch. System: %s "
                                    "Latest: %s") % (system, latest))

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
    def test_execute_fetchlast(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)

            sql = """SELECT EXISTS(
                        SELECT * FROM ag.test_table WHERE int_column=%s)"""
            TRN.add(sql, [2])
            self.assertTrue(TRN.execute_fetchlast())

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
    def ag_verify_kit_password_change_code(self, email, kitid, passcode):
        """returns true if it still in the password change window

        email is the email address of the participant
        kitid is the supplied_kit_id in the ag_kit table
        passcode is the password change verification value
        """
        sql = """SELECT EXISTS(SELECT pass_reset_time
                 FROM ag.ag_kit
                 INNER JOIN ag.ag_login USING (ag_login_id)
                 WHERE pass_reset_code = %s and email = %s
                 AND supplied_kit_id = %s
                 AND NOW() < pass_reset_time)"""
        with TRN:
            TRN.add(sql, [passcode, email, kitid])
            return TRN.execute_fetchlast()

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
    def deleteSample(self, barcode, ag_login_id):
        """ Removes by either releasing barcode back for relogging or withdraw

        Parameters
        ----------
        barcode : str
            Barcode to delete
        ag_login_id : UUID4
            Login ID for the barcode

        Notes
        -----
        Strictly speaking the ag_login_id isn't needed but it makes it really
        hard to hack the function when you would need to know someone else's
        login id (a GUID) to delete something maliciously.

        If the barcode has never been scanned, assume a mis-log and wipe it so
        barcode can be logged again. If barcode has been scanned, that means we
        have recieved it and must withdraw it to delete it from the system.
        """
        with TRN:
            # Figure out if we've received the barcode or not
            sql = "SELECT scan_date FROM barcode WHERE barcode = %s"
            TRN.add(sql, [barcode])
            received = TRN.execute_fetchlast()

            if not received:
                # Not recieved, so we release the barcode back to be relogged
                set_text = """site_sampled = NULL,
                             sample_time = NULL, sample_date = NULL,
                             environment_sampled = NULL, notes = NULL,
                             survey_id = NULL"""
                sql = "UPDATE barcode SET status = NULL WHERE barcode = %s"
                TRN.add(sql, [barcode])
            else:
                # barcode already recieved, so we withdraw the barcode
                set_text = "withdrawn = 'Y'"

            sql = """UPDATE ag_kit_barcodes
                     SET {}
                     WHERE barcode IN (
                     SELECT  akb.barcode
                        FROM ag_kit_barcodes akb
                        INNER JOIN ag_kit ak USING (ag_kit_id)
                        WHERE ak.ag_login_id = %s
                        AND akb.barcode = %s)""".format(set_text)
            TRN.add(sql, [ag_login_id, barcode])

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
    def addAGLogin(self, email, name, address, city, state, zip_, country):
        """Adds a new login or returns the login_id if email already exists

        Parameters
        ----------
        email : str
            Email to register for user
        name : str
            Name to register for user
        address : str
            Street address to register for user
        city : str
            City to register for user
        state : str
            State to register for user
        zip_ : str
            Postal code to register for user
        country : str
            Country to register for user

        Returns
        -------
        ag_login_id : str
            UUID for new user, or existing user if email already in system
        """
        with TRN:
            clean_email = email.strip().lower()
            ag_login_id = self.check_login_exists(email)
            if not ag_login_id:
                # create the login
                sql = """INSERT INTO ag_login
                         (email, name, address, city, state, zip, country)
                         VALUES (%s, %s, %s, %s, %s, %s, %s)
                         RETURNING ag_login_id"""
                TRN.add(sql, [clean_email, name, address, city, state, zip_,
                              country])
                ag_login_id = TRN.execute_fetchlast()
            return ag_login_id

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
    def check_access(self, supplied_kit_id, barcode):
        """Check if the user has access to the barcode

        Parameters
        ----------
        supplied_kit_id : str
            The user's supplied kit ID
        barcode : str
            The barcode to check access for

        Returns
        -------
        boolean
            True if the user can access the barcode, False otherwise
        """
        with TRN:
            ag_login_id = self.get_user_for_kit(supplied_kit_id)
            sql = """SELECT EXISTS (
                        SELECT barcode
                        FROM ag.ag_kit
                        JOIN ag.ag_kit_barcodes USING (ag_kit_id)
                      WHERE ag_login_id = %s AND barcode = %s)"""
            TRN.add(sql, [ag_login_id, barcode])
            return TRN.execute_fetchlast()

# 需要导入模块: from amgut.lib.data_access.sql_connection import TRN [as 别名]
# 或者: from amgut.lib.data_access.sql_connection.TRN import execute_fetchlast [as 别名]
    def registerHandoutKit(self, ag_login_id, supplied_kit_id):
        """Registeres a handout kit to a user

        Parameters
        ----------
        ag_login_id : str
            UUID4 formatted string of login ID to associate with kit
        supplied_kit_id : str
            kit ID for the handout kit

        Returns
        -------
        bool
            True:  success
            False: insert failed due to IntegrityError

        Raises
        ------
        ValueError
            Non-UUID4 value sent as ag_login_id
        """
        with TRN:
            # make sure properly formatted UUID passed in
            UUID(ag_login_id, version=4)

            printresults = self.checkPrintResults(supplied_kit_id)
            # make sure login_id and skid exists
            sql = """SELECT EXISTS(SELECT *
                                   FROM ag.ag_login
                                   WHERE ag_login_id = %s)"""
            TRN.add(sql, [ag_login_id])
            exists = TRN.execute_fetchlast()
            if not exists:
                return False
            sql = """SELECT EXISTS(SELECT *
                                   FROM ag.ag_handout_kits
                                   WHERE kit_id = %s)"""
            TRN.add(sql, [supplied_kit_id])
            if not TRN.execute_fetchlast():
                return False

            sql = """
                DO $do$
                DECLARE
                    k_id uuid;
                    bc varchar;
                BEGIN
                    INSERT INTO ag_kit
                    (ag_login_id, supplied_kit_id, kit_password, swabs_per_kit,
                     kit_verification_code, print_results)
                    SELECT '{0}', kit_id, password, swabs_per_kit,
                        verification_code, '{1}'
                        FROM ag_handout_kits WHERE kit_id = %s LIMIT 1
                    RETURNING ag_kit_id INTO k_id;
                    FOR bc IN
                        SELECT barcode
                        FROM ag_handout_barcodes
                        WHERE kit_id = %s
                    LOOP
                        INSERT  INTO ag_kit_barcodes
                            (ag_kit_id, barcode, sample_barcode_file)
                            VALUES (k_id, bc, bc || '.jpg');
                    END LOOP;
                    DELETE FROM ag_handout_barcodes WHERE kit_id = %s;
                    DELETE FROM ag_handout_kits WHERE kit_id = %s;
                END $do$;
                """.format(ag_login_id, printresults)
            TRN.add(sql, [supplied_kit_id] * 4)
            try:
                TRN.execute()
            except psycopg2.IntegrityError:
                logging.exception('Error on skid %s:' % ag_login_id)
                return False
            return True