def getHumanParticipants(self, ag_login_id):
conn_handler = SQLConnectionHandler()
# get people from new survey setup
return_res = []
new_survey_sql = ("SELECT participant_name FROM ag_login_surveys "
"WHERE ag_login_id = %s")
results = conn_handler.execute_fetchall(new_survey_sql, [ag_login_id])
return_res.extend(row[0] for row in results)
# get people from old surveys
old_survey_sql = ("SELECT participant_name FROM ag_human_survey where "
"ag_login_id = %s")
results = conn_handler.execute_fetchall(new_survey_sql, [ag_login_id])
return_res.extend(row[0] for row in results)
return return_res
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"""
conn_handler = SQLConnectionHandler()
return [row[0] for row in conn_handler.execute_fetchall(sql, [ag_login_id, 2])]
def get_countries(self):
"""
Returns
-------
list of str
All country names in database"""
conn_handler = SQLConnectionHandler()
return [x[0] for x in conn_handler.execute_fetchall(
'SELECT country FROM ag.iso_country_lookup ORDER BY country')]
def barcodes_correct():
# For patch 0011 & 0012
# Needed because barcodes are added as last barcode in system + 1
# and system testing was using these larger barcodes. Now use 0-1000 range
conn_handler = SQLConnectionHandler()
sql = "SELECT barcode FROM barcodes.barcode WHERE barcode::integer >= 800000000"
bcs = conn_handler.execute_fetchall(sql)
if bcs:
raise EnvironmentError("Invalid barcodes found: %s" % ", ".join([x[0] for x in bcs]))
def search_participants(self, term):
sql = """ select cast(ag_login_id as varchar(100)) as ag_login_id
from ag_consent
where lower(participant_name) like %s or
lower(participant_email) like %s"""
conn_handler = SQLConnectionHandler()
liketerm = '%%' + term + '%%'
return [x[0] for x in conn_handler.execute_fetchall(
sql, [liketerm, liketerm])]
def getHumanParticipants(self, ag_login_id):
conn_handler = SQLConnectionHandler()
# get people from new survey setup
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"""
results = conn_handler.execute_fetchall(sql, [ag_login_id, 1])
return [row[0] for row in results]
def getParticipantSamples(self, ag_login_id, participant_name):
sql = """SELECT barcode, site_sampled, sample_date, sample_time,
notes, status
FROM ag_kit_barcodes akb
INNER JOIN barcode USING (barcode)
INNER JOIN ag_kit ak USING (ag_kit_id)
WHERE (site_sampled IS NOT NULL AND site_sampled::text <> '')
AND ag_login_id = %s AND participant_name = %s"""
conn_handler = SQLConnectionHandler()
rows = conn_handler.execute_fetchall(sql, [ag_login_id, participant_name])
barcodes = [dict(row) for row in rows]
return barcodes
class AGDataAccess(object):
"""Data Access implementation for all the American Gut web portal
"""
# arbitrary, unique ID and value
human_sites = ['Stool',
'Mouth',
'Right hand',
'Left hand',
'Forehead',
'Nares',
'Hair',
'Tears',
'Nasal mucus',
'Ear wax',
'Vaginal mucus']
animal_sites = ['Stool',
'Mouth',
'Nares',
'Ears',
'Skin',
'Fur']
general_sites = ['Animal Habitat',
'Biofilm',
'Dust',
'Food',
'Fermented Food',
'Indoor Surface',
'Outdoor Surface',
'Plant habitat',
'Soil',
'Sole of shoe',
'Water']
def __init__(self, con=None):
self.connection = None
if con is None:
self._open_connection()
else:
self.connection = con
cur = self.get_cursor()
cur.execute('set search_path TO ag, barcodes, public')
self._sql = SQLConnectionHandler(con)
def __del__(self):
self.connection.close()
def get_cursor(self):
if self.connection.closed:
self._open_connection()
return self.connection.cursor()
def _open_connection(self):
self.connection = psycopg2.connect(
user=AMGUT_CONFIG.user, password=AMGUT_CONFIG.password,
database=AMGUT_CONFIG.database, host=AMGUT_CONFIG.host,
port=AMGUT_CONFIG.port)
#####################################
# Helper Functions
#####################################
def _get_col_names_from_cursor(self, cur):
if cur.description:
return [x[0] for x in cur.description]
else:
return []
#####################################
# Users
#####################################
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.
"""
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"""
row = self._sql.execute_fetchone(sql, [username])
if row:
results = dict(row)
if not bcrypt.verify(password, results['kit_password']):
return False
results['ag_login_id'] = str(results['ag_login_id'])
return results
else:
return False
def addAGLogin(self, email, name, address, city, state, zip_, country):
clean_email = email.strip().lower()
sql = "select ag_login_id from ag_login WHERE LOWER(email) = %s"
cur = self.get_cursor()
cur.execute(sql, [clean_email])
ag_login_id = cur.fetchone()
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")
cur.execute(sql, [clean_email, name, address, city,
state, zip_, country])
ag_login_id = cur.fetchone()
self.connection.commit()
return ag_login_id[0]
def getAGBarcodeDetails(self, barcode):
sql = """SELECT email,
cast(ag_kit_barcode_id as varchar(100)),
cast(ag_kit_id as varchar(100)),
barcode, site_sampled, environment_sampled, sample_date,
sample_time, participant_name, notes, refunded, withdrawn,
moldy, other, other_text, date_of_last_email ,overloaded,
name, status
FROM ag_kit_barcodes
INNER JOIN ag_kit USING (ag_kit_id)
INNER JOIN ag_login USING (ag_login_id)
INNER JOIN barcode USING (barcode)
WHERE barcode = %s"""
row = self._sql.execute_fetchone(sql, [barcode])
row_dict = {}
if row:
row_dict = dict(row)
return row_dict
def getAGKitDetails(self, supplied_kit_id):
sql = """SELECT cast(ag_kit_id as varchar(100)),
supplied_kit_id, kit_password, swabs_per_kit, kit_verified,
kit_verification_code, verification_email_sent
FROM ag_kit
WHERE supplied_kit_id = %s"""
row = self._sql.execute_fetchone(sql, [supplied_kit_id])
kit_details = {}
if row:
kit_details = dict(row)
return kit_details
def registerHandoutKit(self, ag_login_id, supplied_kit_id):
"""
Returns
-------
bool
True: success
False: insert failed due to IntegrityError
Notes
-----
Whatever is passed as kit_password will be added AS IS. This means you
must hash the password before passing, if desired.
"""
printresults = self.checkPrintResults(supplied_kit_id)
if printresults is None:
printresults = 'n'
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_kits WHERE kit_id = %s;
END $do$;
""".format(ag_login_id, printresults)
conn_handler = SQLConnectionHandler()
try:
conn_handler.execute(sql, [supplied_kit_id] * 3)
except psycopg2.IntegrityError:
logging.exception('Error on skid %s:' % ag_login_id)
return False
return True
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 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 logParticipantSample(self, ag_login_id, barcode, sample_site,
environment_sampled, sample_date, sample_time,
participant_name, notes):
conn_handler = SQLConnectionHandler()
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")
survey_id = conn_handler.execute_fetchone(
sql, (ag_login_id, participant_name))
if survey_id:
# remove the list encapulation
survey_id = survey_id[0]
else:
raise RuntimeError("No survey ID for ag_login_id %s and "
"participant name %s" % (ag_login_id,
participant_name))
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"""
conn_handler.execute(sql, [
sample_site, environment_sampled, sample_date, sample_time,
participant_name, notes, survey_id, barcode])
self.connection.commit()
def deleteSample(self, barcode, ag_login_id):
"""
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
"""
self.get_cursor().callproc('ag_delete_sample',
[barcode, ag_login_id])
self.connection.commit()
def getHumanParticipants(self, ag_login_id):
conn_handler = SQLConnectionHandler()
# get people from new survey setup
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"""
results = conn_handler.execute_fetchall(sql, [ag_login_id, 1])
return [row[0] for row in results]
def is_old_survey(self, survey_id):
conn_handler = SQLConnectionHandler()
# check survey exists
survey_answers = conn_handler.execute_fetchone(
"SELECT exists(SELECT * FROM survey_answers WHERE survey_id = %s)",
[survey_id])[0]
survey_answers_other = conn_handler.execute_fetchone(
"SELECT exists(SELECT * FROM survey_answers_other WHERE "
"survey_id = %s)", [survey_id])[0]
return all((survey_answers is False, survey_answers_other is False))
def updateVioscreenStatus(self, survey_id, status):
conn_handler = SQLConnectionHandler()
sql = ("UPDATE ag_login_surveys SET vioscreen_status = %s WHERE "
"survey_id = %s")
conn_handler.execute(sql, (status, survey_id))
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"""
conn_handler = SQLConnectionHandler()
return [row[0] for row in
conn_handler.execute_fetchall(
sql, [ag_login_id, 2])]
def getParticipantSamples(self, ag_login_id, participant_name):
sql = """SELECT barcode, site_sampled, sample_date, sample_time,
notes, status
FROM ag_kit_barcodes akb
INNER JOIN barcode USING (barcode)
INNER JOIN ag_kit ak USING (ag_kit_id)
WHERE (site_sampled IS NOT NULL AND site_sampled::text <> '')
AND ag_login_id = %s AND participant_name = %s"""
conn_handler = SQLConnectionHandler()
rows = conn_handler.execute_fetchall(
sql, [ag_login_id, participant_name])
barcodes = [dict(row) for row in rows]
return barcodes
def getEnvironmentalSamples(self, ag_login_id):
sql = """SELECT barcode, site_sampled, sample_date, sample_time,
notes, status
FROM ag_kit_barcodes
INNER JOIN barcode USING (barcode)
INNER JOIN ag_kit USING(ag_kit_id)
WHERE (environment_sampled IS NOT NULL AND
environment_sampled::text <> '')
AND ag_login_id = %s"""
rows = self._sql.execute_fetchall(sql, [ag_login_id])
barcodes = [dict(row) for row in rows]
return barcodes
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"""
results = self._sql.execute_fetchall(sql, [ag_login_id])
return [row[0] for row in results]
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"""
self._sql.execute(sql, [supplied_kit_id])
def getMapMarkers(self):
cur_completed = self.get_cursor()
cur_ver = self.get_cursor()
cur_ll = self.get_cursor()
# fetch all latitide/longitude by kit id
cur_ll.execute("""SELECT ak.supplied_kit_id, al.latitude, al.longitude
FROM ag_login al
INNER JOIN ag_kit ak
ON ak.ag_login_id=al.ag_login_id
WHERE al.latitude IS NOT NULL AND
al.longitude IS NOT NULL""")
ll = {res[0]: (res[1], res[2]) for res in cur_ll.fetchall()}
# determine all completed kits
cur_completed.execute("""SELECT ak.supplied_kit_id
FROM ag_kit ak
WHERE (
SELECT count(*)
FROM ag_kit_barcodes akb
WHERE akb.ag_kit_id = ak.ag_kit_id
) =
(
SELECT count(*)
FROM ag_kit_barcodes akb
WHERE akb.ag_kit_id = ak.ag_kit_id AND
akb.site_sampled IS NOT NULL
)""")
completed = (res[0] for res in cur_completed.fetchall())
# determine what kit are not verified
cur_ver.execute("""SELECT supplied_kit_id, kit_verified
FROM ag_kit""")
notverified = (res[0] for res in cur_ver.fetchall() if res[1] == 'n')
# set green for completed kits
res = {ll[kid]: '00FF00' for kid in completed if kid in ll}
# set blue for unverified kits
res.update({ll[kid]: '00B2FF' for kid in notverified if kid in ll})
# set yellow for all others
res.update({v: 'FFFF00' for k, v in ll.items() if v not in res})
return [[lat, lng, c] for ((lat, lng), c) in res.items()]
def handoutCheck(self, username, password):
cursor = self.get_cursor()
cursor.execute("""SELECT password
FROM ag.ag_handout_kits
WHERE kit_id=%s""", [username])
to_check = cursor.fetchone()
if not to_check:
return False
else:
return bcrypt.verify(password, to_check[0])
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
"""
ag_login_id = self.get_user_for_kit(supplied_kit_id)
cursor = self.get_cursor()
cursor.execute("""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)""", [ag_login_id, barcode])
return cursor.fetchone()[0]
def getAGKitIDsByEmail(self, email):
"""Returns a list of kitids based on email
email is email address of login
returns a list of kit_id's associated with the email or an empty list
"""
sql = """SELECT supplied_kit_id
FROM ag_kit
INNER JOIN ag_login USING (ag_login_id)
WHERE email = %s"""
return [row[0] for row in self._sql.execute_fetchall(
sql, [email.lower()])]
def ag_set_pass_change_code(self, email, kitid, pass_code):
"""updates ag_kit table with the supplied pass_code
email is email address of participant
kitid is supplied_kit_id in the ag_kit table
pass_code is the password change verfication value
"""
sql = """UPDATE ag_kit
SET pass_reset_code = %s,
pass_reset_time = clock_timestamp() + interval '2' hour
WHERE supplied_kit_id = %s AND ag_login_id in
(SELECT ag_login_id FROM ag_login WHERE email = %s)"""
self._sql.execute(sql, [pass_code, kitid, email])
def ag_update_kit_password(self, kit_id, password):
"""updates ag_kit table with password
kit_id is supplied_kit_id in the ag_kit table
password is the new password
"""
password = bcrypt.encrypt(password)
sql = """UPDATE AG_KIT
SET kit_password = %s, pass_reset_code = NULL
WHERE supplied_kit_id = %s"""
self.connection.commit(sql, [password, kit_id])
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)"""
return self._sql.execute_fetchone(sql, [passcode, email, kitid])[0]
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"""
results = self._sql.execute_fetchall(sql, [kitid])
return [row[0] for row in results]
def checkPrintResults(self, kit_id):
sql = "SELECT print_results FROM ag_handout_kits WHERE kit_id = %s"
results = self._sql.execute_fetchone(sql, [kit_id])
if results is None:
return None
else:
return results[0].strip()
def get_user_for_kit(self, supplied_kit_id):
sql = ("select AK.ag_login_id from ag_kit AK "
"join ag_login AL on AK.ag_login_id = AL.ag_login_id "
"where AK.supplied_kit_id = %s")
cursor = self.get_cursor()
cursor.execute(sql, [supplied_kit_id])
results = cursor.fetchone()
if results:
return results[0]
else:
raise RuntimeError("No user ID for kit %s" % supplied_kit_id)
def get_menu_items(self, supplied_kit_id):
"""Returns information required to populate the menu of the website"""
ag_login_id = self.get_user_for_kit(supplied_kit_id)
info = self.getAGKitDetails(supplied_kit_id)
kit_verified = False
if info['kit_verified'] == 'y':
kit_verified = True
human_samples = {hs: self.getParticipantSamples(ag_login_id, hs)
for hs in self.getHumanParticipants(ag_login_id)}
animal_samples = {ans: self.getParticipantSamples(ag_login_id, ans)
for ans in self.getAnimalParticipants(ag_login_id)}
environmental_samples = self.getEnvironmentalSamples(ag_login_id)
return (human_samples, animal_samples, environmental_samples,
kit_verified)
def check_if_consent_exists(self, ag_login_id, participant_name):
"""Return True if a consent already exists"""
sql = """select exists(
select 1
from ag_consent
where ag_login_id=%s and
participant_name=%s)"""
cursor = self.get_cursor()
cursor.execute(sql, (ag_login_id, participant_name))
return cursor.fetchone()[0]
def get_user_info(self, supplied_kit_id):
sql = """SELECT cast(agl.ag_login_id as varchar(100)) as ag_login_id,
agl.email, agl.name, agl.address, agl.city,
agl.state, agl.zip, agl.country
from ag_login agl
inner join ag_kit agk
on agl.ag_login_id = agk.ag_login_id
where agk.supplied_kit_id = %s"""
cursor = self.get_cursor()
cursor.execute(sql, [supplied_kit_id])
row = cursor.fetchone()
col_names = self._get_col_names_from_cursor(cursor)
user_data = {}
if row:
user_data = dict(zip(col_names, row))
user_data['ag_login_id'] = str(user_data['ag_login_id'])
return user_data
def get_person_info(self, survey_id):
# get question responses
info = {'birth_month': 'Unspecified', 'birth_year': 'Unspecified', 'gender': 'Unspecified'}
sql = ("SELECT q.american, sa.response FROM ag.survey_answers_other "
" sa JOIN ag.ag_login_surveys ls ON sa.survey_id = ls.survey_id "
"JOIN ag.survey_question q ON q.survey_question_id = sa.survey_question_id "
"WHERE sa.survey_id = %s AND q.american IN ('Birth month:','Birth year:','Gender:')")
cursor = self.get_cursor()
cursor.execute(sql, [survey_id])
rows = cursor.fetchall()
for res in rows:
value = json.loads(res[1])[0]
if res[0] == 'Birth month:':
info['birth_month'] = value
elif res[0] == 'Birth year:':
info['birth_year'] = value
elif res[0] == 'Gender:':
info['gender'] = value
# get name from consent form
sql = ("SELECT c.participant_name FROM ag.ag_consent c JOIN "
"ag.ag_login_surveys ls ON c.ag_login_id = ls.ag_login_id WHERE "
"ls.survey_id = %s")
cursor.execute(sql, [survey_id])
info["name"] = cursor.fetchone()[0]
return info
def get_barcode_results(self, supplied_kit_id):
"""Get the results associated with the login ID of the kit"""
ag_login_id = self.get_user_for_kit(supplied_kit_id)
cursor = self.get_cursor()
sql = """SELECT akb.barcode, akb.participant_name
FROM ag_kit_barcodes akb
INNER JOIN ag_kit agk USING(ag_kit_id)
WHERE agk.ag_login_id = %s AND akb.results_ready = 'Y'"""
cursor.execute(sql, [ag_login_id])
results = cursor.fetchall()
col_names = self._get_col_names_from_cursor(cursor)
return [dict(zip(col_names, row)) for row in results]
def get_login_info(self, ag_login_id):
sql = """select ag_login_id, email, name, address, city, state, zip,
country
from ag_login
where ag_login_id = %s"""
cursor = self.get_cursor()
cursor.execute(sql, [ag_login_id])
col_names = [x[0] for x in cursor.description]
results = [dict(zip(col_names, row)) for row in cursor.fetchall()]
cursor.close()
return results
#################################################
### GENERAL DATA ACCESS #######################
################################################
# not sure where these should end up
def get_survey_id(self, ag_login_id, participant_name):
"""Return the survey ID associated with a participant or None"""
sql = """select survey_id
from ag_login_surveys
where ag_login_id=%s and participant_name=%s"""
cursor = self.get_cursor()
cursor.execute(sql, [ag_login_id, participant_name])
id_ = cursor.fetchone()
return id_[0] if id_ else None
def get_countries(self):
"""
Returns
-------
list of str
All country names in database"""
conn_handler = SQLConnectionHandler()
return [x[0] for x in conn_handler.execute_fetchall(
'SELECT country FROM ag.iso_country_lookup ORDER BY country')]