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