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 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))
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 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 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 ut_get_ag_login_id_from_barcode(self, barcode): """ Returns ag_login_id for a given barcode. For unit testing only! Parameters ---------- barcode : str The barcode for which the ag_login_id should be retrieved. Returns ------- str: ag_login_id Example: 'd8592c74-9694-2135-e040-8a80115d6401' Raises ------ ValueError If the given barcode can not be found in the DB. """ with TRN: sql = """SELECT ag.ag_kit.ag_login_id FROM ag.ag_kit_barcodes JOIN ag.ag_kit USING (ag_kit_id) WHERE ag.ag_kit_barcodes.barcode = %s""" TRN.add(sql, [barcode]) info = TRN.execute_fetchindex() if not info: raise ValueError('Barcode "%s" not in DB' % barcode) return info[0][0]
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]) password = password.encode('utf-8') if not bcrypt.checkpw(password, results['kit_password']): return False results['ag_login_id'] = str(results['ag_login_id']) return results
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 ut_get_arbitrary_barcode(self, deposited=True): """ Returns arbitrarily chosen barcode. For unit testing only! Parameters ---------- deposited : boolean If true, pick a deposited barcode. Default = True Returns ------- str: barcode Example: '000032951' Raises ------ ValueError If no barcodes can be found in the DB.""" with TRN: sql = """SELECT barcode FROM ag.ag_kit_barcodes WHERE deposited=%s LIMIT 1""" TRN.add(sql, [deposited]) info = TRN.execute_fetchindex() if not info: raise ValueError('No barcodes found.') return info[0][0]
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()
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 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_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 ut_get_supplied_kit_id(self, ag_login_id): """ Returns supplied_kit_id for a given ag_login_id. For unit testing only! Parameters ---------- ag_login_id : str Existing ag_login_id. Returns ------- str The supplied_kit_id for the given ag_login_id. Example: 'DokBF' Raises ------ ValueError If ag_login_id is not in DB. """ with TRN: sql = """SELECT supplied_kit_id FROM ag.ag_kit 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 info[0][0]
def ut_get_email_from_ag_login_id(self, ag_login_id): """ Returns email for a given ag_login_id. For unit testing only! Parameters ---------- ag_login_id : str Existing ag_login_id. Returns ------- str: email Example: 'xX/tEv7O+T@6Ri7C.)LO' Raises ------ ValueError If ag_login_id is not in DB. """ with TRN: sql = """SELECT email FROM ag.ag_login WHERE ag_login_id=%s""" TRN.add(sql, [ag_login_id]) info = TRN.execute_fetchindex() if not info: raise ValueError('No emails found.') return info[0][0]
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 __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 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 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 populate_test_db(): command = [ 'pg_restore', '-d', AMGUT_CONFIG.database, '--no-privileges', '--no-owner', '--role=%s' % AMGUT_CONFIG.user, POPULATE_FP ] proc = Popen(command, stdin=PIPE, stdout=PIPE) retcode = proc.wait() if retcode != 0: raise RuntimeError("Could not populate test database %s: retcode %d" % (AMGUT_CONFIG.database, retcode)) # Adds a new labadmin user names 'master' to the database and grants # highest privileges to this user. Password is 'password'. with TRN: username = '******' # create new labadmin user 'master' sql = """INSERT INTO ag.labadmin_users (email, password) VALUES (%s, %s)""" TRN.add(sql, [ username, ('$2a$10$2.6Y9HmBqUFmSvKCjWmBte70WF.' 'zd3h4VqbhLMQK1xP67Aj3rei86') ]) # granting new user highest privileges sql = """INSERT INTO ag.labadmin_users_access (access_id, email) VALUES (%s, %s)""" TRN.add(sql, [7, username]) TRN.execute()
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_nonconsented_scanned_barcodes(self, kit_id): """Returns list of barcodes that have been scanned but not consented Parameters ---------- kit_id : str The supplied kit identifier to check for barcodes. Returns ------- list of str The barcodes, if any, that have been scanned but not consented """ sql = """SELECT barcode FROM ag_kit_barcodes INNER JOIN ag_kit USING (ag_kit_id) RIGHT JOIN ag_login USING (ag_login_id) LEFT JOIN barcode USING (barcode) FULL JOIN ag.source_barcodes_surveys USING (barcode) WHERE ag.source_barcodes_surveys.survey_id IS NULL AND scan_date IS NOT NULL AND ag_login_id = %s""" with TRN: user = self.get_user_for_kit(kit_id) TRN.add(sql, [user]) return TRN.execute_fetchflatten()
def ut_get_participant_names_from_ag_login_id(self, ag_login_id): """ Returns all participant_name(s) for a given ag_login_id. For unit testing only! Parameters ---------- ag_login_id : str Existing ag_login_id. Returns ------- [[str]] Example: ["Name - z\xc3\x96DOZ8(Z~'", "Name - z\xc3\x96DOZ8(Z~'", 'Name - QpeY\xc3\xb8u#0\xc3\xa5<', 'Name - S)#@G]xOdL', 'Name - Y5"^&sGQiW', 'Name - L\xc3\xa7+c\r\xc3\xa5?\r\xc2\xbf!', 'Name - (~|w:S\xc3\x85#L\xc3\x84'] Raises ------ ValueError If ag_login_id is not in DB. """ with TRN: sql = """SELECT participant_name FROM ag.ag_login_surveys 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 [n[0] for n in info]
def ut_get_arbitrary_supplied_kit_id_scanned_unconsented(self): """ Returns arbitrarily chosen supplied_kit_id and barcode which has been scanned but is without consent. For unit testing only! Returns ------- list of str: [supplied_kit_id, barcode] example: ['fNIYa', '000001053'] Raises ------ ValueError If no kits can be found in the DB that have been scanned and are without consent.""" with TRN: sql = """SELECT supplied_kit_id, barcode FROM barcodes.barcode JOIN ag.ag_kit_barcodes USING (barcode) JOIN ag.ag_kit USING (ag_kit_id) LEFT JOIN ag.source_barcodes_surveys USING (barcode) WHERE barcodes.barcode.scan_date IS NOT NULL AND ag.source_barcodes_surveys.survey_id IS NULL LIMIT 1""" TRN.add(sql, []) info = TRN.execute_fetchindex() if not info: raise ValueError('No kits found.') return info[0]
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 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 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)
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()
def getAvailableBarcodes(self, ag_login_id): sql = """SELECT barcode FROM ag_kit_barcodes INNER JOIN ag_kit USING (ag_kit_id) WHERE coalesce(sample_date::text, '') = '' AND kit_verified = 'y' AND ag_login_id = %s""" with TRN: TRN.add(sql, [ag_login_id]) return TRN.execute_fetchflatten()
def getAnimalParticipants(self, ag_login_id): sql = """SELECT DISTINCT participant_name from ag.ag_login_surveys JOIN ag.survey_answers USING (survey_id) JOIN ag.group_questions gq USING (survey_question_id) JOIN ag.surveys ags USING (survey_group) WHERE ag_login_id = %s AND ags.survey_id = %s""" with TRN: TRN.add(sql, [ag_login_id, 2]) return TRN.execute_fetchflatten()
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()
def getAnimalParticipants(self, ag_login_id): sql = """SELECT participant_name from ag.ag_login_surveys JOIN ag.survey_answers USING (survey_id) JOIN ag.group_questions gq USING (survey_question_id) JOIN ag.surveys ags USING (survey_group) WHERE ag_login_id = %s AND ags.survey_id = %s""" with TRN: TRN.add(sql, [ag_login_id, 2]) return TRN.execute_fetchflatten()
def test_execute_return(self): with TRN: sql = """INSERT INTO ag.test_table (str_column, int_column) VALUES (%s, %s) RETURNING str_column, int_column""" TRN.add(sql, ['test_insert', 2]) sql = """UPDATE ag.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]]])
def test_add_many(self): with TRN: self.assertEqual(TRN._queries, []) sql = "INSERT INTO ag.test_table (int_column) VALUES (%s)" args = [[1], [2], [3]] TRN.add(sql, args, many=True) exp = [(sql, [1]), (sql, [2]), (sql, [3])] self.assertEqual(TRN._queries, exp)
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])
def getHumanParticipants(self, ag_login_id): # get people from new survey setup sql = """SELECT DISTINCT participant_name from ag.ag_login_surveys LEFT JOIN ag.survey_answers USING (survey_id) JOIN ag.group_questions gq USING (survey_question_id) JOIN ag.surveys ags USING (survey_group) WHERE ag_login_id = %s AND ags.survey_id = %s""" with TRN: TRN.add(sql, [ag_login_id, 1]) return TRN.execute_fetchflatten()
def get_countries(self): """ Returns ------- list of str All country names in database""" with TRN: sql = 'SELECT country FROM ag.iso_country_lookup ORDER BY country' TRN.add(sql) return TRN.execute_fetchflatten()
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))
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())
def getBarcodesByKit(self, kitid): """Returns a list of barcodes in a kit kitid is the supplied_kit_id from the ag_kit table """ sql = """SELECT barcode FROM ag_kit_barcodes INNER JOIN ag_kit USING (ag_kit_id) WHERE supplied_kit_id = %s""" with TRN: TRN.add(sql, [kitid]) return TRN.execute_fetchflatten()
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)