def test_execute_fetchflatten(self): with TRN: sql = """INSERT INTO labcontrol.test_table (str_column, int_column) VALUES (%s, %s)""" args = [['insert1', 1], ['insert2', 2], ['insert3', 3]] TRN.add(sql, args, many=True) sql = "SELECT str_column, int_column FROM labcontrol.test_table" TRN.add(sql) sql = "SELECT int_column FROM labcontrol.test_table" TRN.add(sql) obs = TRN.execute_fetchflatten() self.assertEqual(obs, [1, 2, 3]) sql = "SELECT 42" TRN.add(sql) obs = TRN.execute_fetchflatten(idx=3) self.assertEqual(obs, ['insert1', 1, 'insert2', 2, 'insert3', 3])
# until prerequisite queries are completed. statements = [] # # rename kappa_hyper_plus_kit_id in library_prep_shotgun_process # sql = """SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'library_prep_shotgun_process' AND column_name = 'kappa_hyper_plus_kit_id';""" logging.debug(sql) TRN.add(sql) # because we are COUNT()ing, execute_fetchflatten() can be expected to # return a single scalar value within a list. The [0] in this case is to # access the first (and only) scalar value within the results. result = TRN.execute_fetchflatten()[0] # for now, assume result will be either 0 or 1. # if 1, then the column 'kappa_hyper_plus_kit_id' needs # to be renamed to 'kapa_hyperplus_kit_id', otherwise # it has already been renamed. if result == 1: msg = ('labcontrol.library_prep_shotgun_process contains the ' 'column name kappa_hyper_plus_kit_id. Will rename to ' 'kapa_hyperplus_kit_id...') logging.debug(msg) sql = """ALTER TABLE labcontrol.library_prep_shotgun_process RENAME COLUMN kappa_hyper_plus_kit_id to kapa_hyperplus_kit_id;""" statements.append(sql) else:
def get_samples(): with TRN: TRN.add("SELECT sample_id FROM qiita.study_sample") return TRN.execute_fetchflatten()