def getConsent(self, survey_id): conn_handler = SQLConnectionHandler() with conn_handler.get_postgres_cursor() as cur: cur.execute("""SELECT agc.participant_name, agc.participant_email, agc.parent_1_name, agc.parent_2_name, agc.is_juvenile, agc.deceased_parent, agc.ag_login_id, agc.date_signed, agc.assent_obtainer, agc.age_range, agl.survey_id FROM ag_consent agc JOIN ag_login_surveys agl USING (ag_login_id, participant_name) WHERE agl.survey_id=%s""", [survey_id]) colnames = [x[0] for x in cur.description] result = cur.fetchone() if result: result = {k: v for k, v in zip(colnames, result)} if 'date_signed' in result: result['date_signed'] = str(result['date_signed']) return result
def deleteAGParticipantSurvey(self, ag_login_id, participant_name): # Remove user using old stype DB Schema self.get_cursor().callproc("ag_delete_participant", [ag_login_id, participant_name]) self.connection.commit() # Remove user from new schema conn_handler = SQLConnectionHandler() sql = "SELECT survey_id FROM ag_login_surveys WHERE ag_login_id = " "%s AND participant_name = %s" survey_id = conn_handler.execute_fetchone(sql, (ag_login_id, participant_name))[0] with conn_handler.get_postgres_cursor() as curr: sql = "DELETE FROM survey_answers WHERE " "survey_id = %s" curr.execute(sql, [survey_id]) sql = "DELETE FROM survey_answers_other WHERE " "survey_id = %s" curr.execute(sql, [survey_id]) # Reset survey attached to barcode(s) sql = "UPDATE ag_kit_barcodes SET survey_id = NULL WHERE " "survey_id = %s" curr.execute(sql, [survey_id]) sql = "DELETE FROM promoted_survey_ids WHERE survey_id = %s" curr.execute(sql, [survey_id]) # Delete last due to foreign keys sql = "DELETE FROM ag_login_surveys WHERE " "survey_id = %s" curr.execute(sql, [survey_id]) sql = "DELETE FROM ag_consent WHERE ag_login_id = " "%s AND participant_name = %s" curr.execute(sql, [ag_login_id, participant_name])
def make_settings_table(): conn = SQLConnectionHandler() settings = AMGUT_CONFIG.get_settings() columns = [' '.join([setting[0], 'varchar']) for setting in settings] column_names = [setting[0] for setting in settings] num_values = len(settings) sql = "INSERT INTO settings ({}) VALUES ({})".format( ', '.join(column_names), ', '.join(['%s'] * num_values)) args = [str(setting[1]) for setting in settings] with conn.get_postgres_cursor() as cur: create_sql = ("CREATE TABLE ag.settings ({}, current_patch varchar " "NOT NULL DEFAULT 'unpatched')") create_sql = create_sql.format(', '.join(columns)) cur.execute(create_sql) cur.execute(sql, args)
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. """ conn = SQLConnectionHandler() current_patch = conn.execute_fetchone( "SELECT current_patch FROM settings")[0] 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 conn.get_postgres_cursor() as cur: cur.execute('SET SEARCH_PATH TO ag, barcodes, public') with open(patch_fp, 'U') as patch_file: if verbose: echo('\tApplying patch %s...' % patch_filename) cur.execute(patch_file.read()) cur.execute(patch_update_sql, [patch_filename]) conn._connection.commit()