Python queryDB Examples

Example #1

File: dashboardPageFunctions.py Project: kevinbutryn/heroku-test

def getDashboard_calories_consumed(data):

    if data['type'] == 'total':
        if data['timePeriod'] == 'alltime':
            query = """select date_time, SUM(calories) from food where email = \'""" + data[
                'email'] + """\' and date_time <= current_date GROUP BY date_time;"""
            # query = """select * from food where email = \'"""+data['email']+"""\';"""
            records = runQuery.queryDB(query)
            return records
        else:
            query = """select date_time, SUM(calories) from food where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date GROUP BY date_time;"""
            # query = """select * from food where email = \'"""+data['email']+"""\';"""
            records = runQuery.queryDB(query)
            return records
    else:
        if data['timePeriod'] == 'alltime':
            query = """select date_time, SUM(calories) from food where email = \'""" + data[
                'email'] + """\' and date_time <= current_date and when_eaten = \'""" + data[
                    'type'] + """\' GROUP BY date_time;"""
            # query = """select * from food where email = \'"""+data['email']+"""\';"""
            records = runQuery.queryDB(query)
            return records
        else:
            query = """select date_time, SUM(calories) from food where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date and when_eaten = \'""" + data[
                        'type'] + """\' GROUP BY date_time;"""
            # query = """select * from food where email = \'"""+data['email']+"""\';"""
            records = runQuery.queryDB(query)
            return records

Example #2

File: dashboardPageFunctions.py Project: kevinbutryn/heroku-test

def getDashboard_top5exercises(data):
    if data['type'] == 'caloriesburned':
        if data['timePeriod'] == 'alltime':
            query = """select exercise_name, SUM(caloriesburned) from exercise where email = \'""" + data[
                'email'] + """\' and date_time <= current_date GROUP BY exercise_name ORDER BY sum DESC LIMIT 5;"""
            records = runQuery.queryDB(query)
            return records
        else:
            query = """select exercise_name, SUM(caloriesburned) from exercise where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date GROUP BY exercise_name ORDER BY sum DESC LIMIT 5;"""
            records = runQuery.queryDB(query)
            return records
    else:
        if data['timePeriod'] == 'alltime':
            query = """select exercise_name, SUM(cast (duration as float)) from exercise where email = \'""" + data[
                'email'] + """\' and date_time <= current_date GROUP BY exercise_name ORDER BY sum DESC LIMIT 5;"""
            records = runQuery.queryDB(query)
            return records
        else:
            query = """select exercise_name, SUM(cast (duration as float)) from exercise where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date GROUP BY exercise_name ORDER BY sum DESC LIMIT 5;"""
            records = runQuery.queryDB(query)
            return records

Example #3

File: dashboardFunc.py Project: EAkoma/individual-project-cs6440

def getDashboard_dia_bp(data):
    if data['type'] == 'total':
        if data['timePeriod'] == 'alltime':
            query = """select date_time, SUM(dia_blood) from bloodpress where email = \'""" + data[
                'email'] + """\' and date_time <= current_date GROUP BY date_time;"""
            records = runQuery.queryDB(query)
            return records
        else:
            query = """select date_time, SUM(dia_blood) from bloodpress where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date GROUP BY date_time;"""
            records = runQuery.queryDB(query)
            return records
    else:
        if data['timePeriod'] == 'alltime':
            query = """select date_time, SUM(dia_blood) from bloodpress where email = \'""" + data[
                'email'] + """\' and date_time <= current_date = \'""" + data[
                    'type'] + """\' GROUP BY date_time;"""
            records = runQuery.queryDB(query)
            return records
        else:
            query = """select date_time, SUM(dia_blood) from bloodpress where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date = \'""" + data[
                        'type'] + """\' GROUP BY date_time;"""
            records = runQuery.queryDB(query)
            return records

Example #4

File: dashboardFunc.py Project: EAkoma/individual-project-cs6440

def getDashboard_sleep_hours(data):
    if data['type'] == 'total':
        if data['timePeriod'] == 'alltime':
            query = """select date_time, SUM(sleep_dur) from sleep where email = \'""" + data[
                'email'] + """\' and date_time <= current_date GROUP BY date_time;"""
            records = runQuery.queryDB(query)
            return records
        else:
            query = """select date_time, SUM(sleep_dur) from sleep where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date GROUP BY date_time;"""
            records = runQuery.queryDB(query)
            return records
    else:
        if data['timePeriod'] == 'alltime':
            query = """select date_time, SUM(sleep_dur) from sleep where email = \'""" + data[
                'email'] + """\' and date_time <= current_date and when_sleep = \'""" + data[
                    'type'] + """\' GROUP BY date_time;"""
            records = runQuery.queryDB(query)
            return records
        else:
            query = """select date_time, SUM(sleep_dur) from sleep where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date and when_sleep = \'""" + data[
                        'type'] + """\' GROUP BY date_time;"""
            records = runQuery.queryDB(query)
            return records

Example #5

File: dashboardFunc.py Project: EAkoma/individual-project-cs6440

def getDashboard_exercise_hours(data):
    if data['type'] == 'total':
        if data['timePeriod'] == 'alltime':
            query = """select date_time, SUM(cast (duration as float)) from exercise where email = \'""" + data[
                'email'] + """\' and date_time <= current_date GROUP BY date_time;"""
            records = runQuery.queryDB(query)
            return records
        else:
            query = """select date_time, SUM(cast (duration as float)) from exercise where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date GROUP BY date_time;"""
            records = runQuery.queryDB(query)
            return records
    else:
        if data['timePeriod'] == 'alltime':
            query = """select date_time, SUM(cast (duration as float)) from exercise where email = \'""" + data[
                'email'] + """\' and date_time <= current_date = \'""" + data[
                    'type'] + """\' GROUP BY date_time;"""
            records = runQuery.queryDB(query)
            return records
        else:
            query = """select date_time, SUM(cast (duration as float)) from exercise where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date = \'""" + data[
                        'type'] + """\' GROUP BY date_time;"""
            records = runQuery.queryDB(query)
            return records

Example #6

File: loginFunction.py Project: EAkoma/individual-project-cs6440

def login(user_email, user_pass):
    query = """SELECT email, user_password, is_admin FROM regularuser WHERE email=\'""" + user_email + """\';"""
    records = runQuery.queryDB(query)
    if records:
        if user_pass == records[0]["user_password"]:
            return True, records[0]["is_admin"]
    return False, False

Example #7

File: dashboardPageFunctions.py Project: kevinbutryn/heroku-test

def getDashboard_calories_burned(data):
    if data['type'] == 'total':
        if data['timePeriod'] == 'alltime':
            query = """select date_time, SUM(caloriesburned) from exercise where email = \'""" + data[
                'email'] + """\' and date_time <= current_date GROUP BY date_time;"""
            # query = """select * from food where email = \'"""+data['email']+"""\';"""
            records = runQuery.queryDB(query)
            return records
        else:
            query = """select date_time, SUM(caloriesburned) from exercise where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date GROUP BY date_time;"""
            # query = """select * from food where email = \'"""+data['email']+"""\';"""
            records = runQuery.queryDB(query)
            return records
    elif data['type'] == 'duration':
        if data['timePeriod'] == 'alltime':
            query = """select date_time, SUM(cast (duration as float))  from exercise where email = \'""" + data[
                'email'] + """\' and date_time <= current_date GROUP BY date_time;"""
            # query = """select * from food where email = \'"""+data['email']+"""\';"""
            records = runQuery.queryDB(query)
            return records
        else:
            query = """select date_time, SUM(cast (duration as float))  from exercise where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date GROUP BY date_time;"""
            # query = """select * from food where email = \'"""+data['email']+"""\';"""
            records = runQuery.queryDB(query)
            return records
    else:
        if data['timePeriod'] == 'alltime':
            # query = """select date_time, SUM(caloriesburned) from exercise where email = \'"""+data['email']+"""\' and date_time <= current_date GROUP BY date_time;"""
            query = """SELECT CASE WHEN a.date_time IS NULL THEN b.date_time ELSE a.date_time END AS date_time, COALESCE(caloriesconsumed,0)-COALESCE(calorieburned,0) as energy FROM (select date_time, SUM(caloriesburned) as calorieBurned from exercise where email = \'""" + data[
                'email'] + """\' and date_time <= current_date GROUP BY date_time) a FULL OUTER JOIN (select date_time, SUM(calories) as caloriesconsumed from food where email = \'""" + data[
                    'email'] + """\' and date_time <= current_date GROUP BY date_time) b ON a.date_time=b.date_time;"""
            records = runQuery.queryDB(query)
            return records
        else:
            # query = """select date_time, SUM(caloriesburned) from exercise where email = \'"""+data['email']+"""\' and date_time >= (current_date-INTERVAL \'"""+data['timePeriod']+""" days\') and date_time <= current_date GROUP BY date_time;"""
            query = """SELECT CASE WHEN a.date_time IS NULL THEN b.date_time ELSE a.date_time END AS date_time, COALESCE(caloriesconsumed,0)-COALESCE(calorieburned,0) as energy FROM (select date_time, SUM(caloriesburned) as calorieBurned from exercise where email = \'""" + data[
                'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                    'timePeriod'] + """ days\') and date_time <= current_date GROUP BY date_time) a FULL OUTER JOIN (select date_time, SUM(calories) as caloriesconsumed from food where email = \'""" + data[
                        'email'] + """\' and date_time >= (current_date-INTERVAL \'""" + data[
                            'timePeriod'] + """ days\') and date_time <= current_date GROUP BY date_time) b ON a.date_time=b.date_time;"""
            records = runQuery.queryDB(query)
            return records

Example #8

File: displayFunc.py Project: EAkoma/individual-project-cs6440

def getSleep_all(data):
    query = """select * from sleep where email = \'""" + data[
        'email'] + """\';"""
    records = runQuery.queryDB(query)
    return records

Example #9

File: displayFunc.py Project: EAkoma/individual-project-cs6440

def getExercise_all(data):
    query = """select * from exercise where email = \'""" + data[
        'email'] + """\';"""
    records = runQuery.queryDB(query)
    return records

Example #10

File: displayFunc.py Project: EAkoma/individual-project-cs6440

def getBloodp_all(data):
    query = """select * from bloodpress where email = \'""" + data[
        'email'] + """\';"""
    records = runQuery.queryDB(query)
    return records

Example #11

File: exerciseFunction.py Project: kevinbutryn/heroku-test

def getcaloriesBurned_all_homePage(data):
    query = """select date_time, SUM(caloriesburned) from exercise where email = \'""" + data[
        'email'] + """\' and date_time >= (current_date-INTERVAL \'7 days\') and date_time <= current_date GROUP BY date_time;"""
    # query = """select * from food where email = \'"""+data['email']+"""\';"""
    records = runQuery.queryDB(query)
    return records

Example #12

def getExerciseCalories():
    query = """select * from caloriesperexercise; """
    records = runQuery.queryDB(query)
    return records

Example #13

File: foodFunction.py Project: kevinbutryn/heroku-test

def get_articles(data):
    query = """select * FROM articles"""
    # query = """select * from food where email = \'"""+data['email']+"""\';"""
    records = runQuery.queryDB(query)
    return records

Example #14

File: appointmentsFunction.py Project: kevinbutryn/heroku-test

def getAllHomePage(data):
    # print(data)
    query = """select * from appointments where email = \'"""+data['email']+"""\' AND date_time < (current_date+INTERVAL \'7 days\') and date_time > current_date;"""
    records = runQuery.queryDB(query)
    return records

Example #15

File: appointmentsFunction.py Project: kevinbutryn/heroku-test

def getAll(data):
    print(data)
    query = """select * from appointments where email = \'"""+data['email']+"""\';"""
    records = runQuery.queryDB(query)
    return records