Esempio n. 1
0
def getDist(coordinate1, coordinate2):
    """
    Get the distance between the newest incoming point and the most recent previously recorded
    point, distance is reported as meters by default by ST_DistanceSphere.

    Parameters
    ----------
    coordinate:1 String
        WKT representation of the most recently recorded GPS point coordinates.
    coordinate2: String
        WKT representation of the incoming GPS point coordinates.

    Returns
    -------
    dist : Float
        Distance in meters between the newest and most recent recorded points.

    """
    session = Session()
    # Geoalchemy ORM expression
    res = session.query(
        sqlfunc.ST_DistanceSphere(sqlfunc.ST_GeomFromText(coordinate1),
                                  sqlfunc.ST_GeomFromText(coordinate2)))
    # coordinate1 = coordinate1
    # res = db.session.query(sqlfunc.ST_DistanceSphere(coordinate1,coordinate2))
    dist = None
    for i in res:
        # Make sure the data comes through as a float, not a ORM object
        dist = float(i[0])
        # Round off number, don't need high precision
        dist = round(dist, 1)
    session.close()
    return dist
def checkmd5(hash, pdfDate):
    """
    Checks if the downloaded PDF's MD5 hash is already in Postgres and returns result.

    :param hash: String
        New MD5 hash
    :param pdfDate: String
        New PDF Date
    :return: String
        "Exists" - Hash is already in Postgres
        "New" - Hash is not in Postgres and no other hashes exist for the PDF result week
        "Update" - Hash is not in Postgres but other hashes exist for the PDF result week
    """
    # Query Postgres with pdfDate of newly downloaded PDF
    session = Session()
    application.logger.debug(
        f"Querying water quality MD5 hashes with the date {pdfDate}")
    query = session.query(waterQualityMD5).filter(
        waterQualityMD5.pdfdate == pdfDate).all()
    hashList = []
    application.logger.debug(f"Iterating ")
    for i in query:
        hashList.append(i.md5)
    session.close()
    if hash in hashList:
        return "Exists"
    elif len(hashList) == 0:
        return "New"
    else:
        return "Update"
Esempio n. 3
0
def getroles(username):
    """
    Queries PostgreSQL database for user roles using username supplied by Flask HTTP Basic Auth.

    Parses roles into a list.

    Parameters
    ----------
    username : String
        Username as provided by user in the auth prompt.

    Returns
    -------
    res : List
        List of strings containing user roles.
        All roles need to be returned.
    None if empty results, user not in database.
    """
    session = Session()
    query = session.query(Roles.roles).filter(Roles.user == username).all()
    #     res += row
    res = query[0]
    if len(res) == 0:
        res = None
    # Roles are stored as comma seperated strings
    # Convert result tuple into a list of strings split by commas
    else:
        res = res[0].split(",")
    session.close()
    return res
def getBeachWaterQual():
    """
    Queries Postgres AWS RDS to return the most recent water quality report data for each beach that is tested in SB
    County.

    Data are spread across tables with mapped relationships.

    This query joins the relevant tables and uses "distinct" on the waterQuality beach ID field, selecting only one
    record per beach, then "order_by" is used on the joined MD5 table to grab only the most recent record per beach.

    :return: List:
        Nested lists containing SQL Alchemy query results:
            3 query result objects:
                waterQuality, waterqualityMD5 beaches
            1 string:
                geometry type of associated beach
            2 floats:
                x and y coordinates of the associated beach
    """
    session = Session()
    records = session.query(waterQuality, waterQualityMD5, beaches, sqlfunc.ST_GeometryType(beaches.geom),
                               sqlfunc.st_x(beaches.geom), sqlfunc.st_y(beaches.geom)) \
        .join(waterQualityMD5) \
        .join(beaches) \
        .distinct(waterQuality.beach_id) \
        .order_by(waterQuality.beach_id, waterQualityMD5.insdate.desc()).all()

    # Can't close session here because these results are used in another function
    # session.close()
    return records
Esempio n. 5
0
def getHashPass(username):
    """
    Get the hashed password from PostgreSQL database using the username supplied by HTTP Basic Auth.

    Parameters
    ----------
    username : String
        Username as provided by user in the auth prompt.

    Returns
    -------
    res_dict : dictionary
        keys:
            username (string, parameter)
         value:
            hashed password (string)
            None (if no matching user in table)

    A dictionary is returned to maintain connection between username supplied and password.
    """
    session = Session()
    query = session.query(User.hashpass).filter(User.user == username).all()
    res_dict = {}
    for row in query:
        res_dict[username] = row.hashpass
    session.close()
    if len(res_dict) == 0:
        return None
    else:
        return res_dict
Esempio n. 6
0
def getActiveSubID():
    """
    Gets the active Strava webhook subscription ID from the database.
    @return: Int. Active subscription ID
    """
    session = Session()
    query = session.query(webhook_subs).filter(
        webhook_subs.activesub == "Yes").first()
    session.close()
    if query:
        return int(query.sub_id)
Esempio n. 7
0
def checkathleteID(athID):
    """
    Checks if the provided actID is already within the database.

    @param actID: Int. Strava Activity ID
    @return: String. "True" or "False" depending if record exists in databse
    """
    # Open session
    session = Session()
    # Query database
    record = session.query(athletes.athlete_id == athID).first()
    session.close()
    return record
Esempio n. 8
0
def insertSubUpdate(content):
    """

    Inserts Strava webhook subscription data into Postgres database. This information will be used to get full activity
    information from another query.

    Parameters
    ----------
    content. Subscription Update object of Strava webhook update generated by Stravalib

    Returns
    -------
    Nothing. Updates database.
    """
    # Verify is activity title is in update data, if not set to None. Some activities may have empty titles.
    if "title" in content.updates.keys():
        title = content.updates['title']
        application.logger.debug(f"Title of new activity is {title}")
    else:
        title = None
    session = Session()
    insert = sub_update(aspect=content.aspect_type,
                        event_time=datetime.fromtimestamp(
                            content.event_time.timestamp),
                        object_id=content.object_id,
                        object_type=content.object_type,
                        owner_id=content.owner_id,
                        subscription_id=content.subscription_id,
                        update_title=title)
    session.add(insert)
    session.commit()
    session.close()
    application.logger.debug(f"New webhook update has been added to Postgres!")
def insertWaterQual(beachDict, md5_fk):
    """
    Inserts water quality results into water quality database table with md5 foreign key relationship.

    Parameters
    ----------
    beachDict: Dictionary. Dictionary containing values to be inserted into database.
    md5_fk: String. Foreign key from md5 table.

    Returns
    -------
    Print statement.
    """
    session = Session()
    inslist = []
    # Iterate over beaches in dictionary creating waterQuality objects for each beach key
    for key in beachDict.keys():
        inslist.append(
            waterQuality(
                beach_id=beachDict[key]['fk'],
                TotColi=beachDict[key]['Total Coliform Results (MPN*)'],
                FecColi=beachDict[key]["Fecal Coliform Results (MPN*)"],
                Entero=beachDict[key]['Enterococcus Results (MPN*)'],
                ExceedsRatio=beachDict[key]['Exceeds FC:TC ratio standard **'],
                BeachStatus=beachDict[key]['Beach Status'],
                resample=beachDict[key]['resample'],
                md5_id=int(md5_fk)))
    # Add list of objects to session
    session.add_all(inslist)
    session.commit()
    session.close()
    application.logger.debug("Data added to water quality table!")
Esempio n. 10
0
def insertOriginalAct(actDict):
    """
    Inserts new activity into database, POSTed by Strava webhook update or by manually triggering process activity
    event route.

    Parameters
    ----------
    actDict. Dict. Generated by StravaWebHook.handle_sub_update() or by getStravaActivities.processActs().

    Returns
    -------
    Nothing. Data are inserted into Postgres/PostGIS.
    """
    insert = strava_activities(
        actID=actDict['actId'],
        upload_id=actDict['upload_id'],
        name=actDict['name'],
        distance=actDict['distance'],
        moving_time=actDict['moving_time'],
        elapsed_time=actDict['elapsed_time'],
        total_elevation_gain=actDict['total_elevation_gain'],
        elev_high=actDict['elev_high'],
        elev_low=actDict['elev_low'],
        type=actDict['type'],
        start_date=actDict['start_date'],
        start_date_local=actDict['start_date_local'],
        timezone=actDict['timezone'],
        utc_offset=actDict['utc_offset'],
        start_latlng=actDict['start_latlng'],
        end_latlng=actDict['end_latlng'],
        start_latitude=actDict['start_latitude'],
        start_longitude=actDict['start_longitude'],
        achievement_count=actDict['achievement_count'],
        pr_count=actDict['pr_count'],
        private=actDict['private'],
        gear_id=actDict['gear_id'],
        average_speed=actDict['average_speed'],
        max_speed=actDict['max_speed'],
        average_watts=actDict['average_watts'],
        kilojoules=actDict['kilojoules'],
        description=actDict['description'],
        workout_type=actDict['workout_type'],
        calories=actDict['calories'],
        device_name=actDict['device_name'],
        manual=actDict['manual'],
        athlete_id=actDict['athlete_id'],
        type_extended=actDict['type_extended'],
        avgtemp=actDict['average_temp'],
        has_heartrate=actDict['has_heartrate'],
        average_cadence=actDict["average_cadence"],
        average_heartrate=actDict['average_heartrate'],
        max_heartrate=actDict['max_heartrate'],
        geom=actDict['geom_wkt'])
    session = Session()
    session.add(insert)
    session.commit()
    session.close()
    application.logger.debug(
        f"New webhook update for activity {actDict['actId']} has been added to Postgres!"
    )
def handleTrackerPOST(data):
    """

    Parameters
    ----------
    data

    Returns
    -------

    """
    session = Session()
    newObjDict = {}
    # Create instance of a gps track
    trackRecord = gpsTrackObj(data)
    # Check if there has been movement, if so add to new object dictionary, otherwise no entry will be made
    if trackRecord["activity"] == "Yes":
        newObjDict["track"] = trackRecord["model"]
    # Add new gps record object to new objects dictionary
    newObjDict["gpspoint"] = newGPSRecord(data, trackRecord["activity"])
    # Iterate over new objdict, can allow building out so many things can be committed to db
    # This allows for empty models to be skipped
    newObjs = []
    for obj in newObjDict.keys():
        newObjs.append(newObjDict[obj])
    # Add new objects to session and commit them
    session.add_all(newObjs)
    session.commit()
    session.close()
def insmd5(MD5, pdfDate, pdfName):
    """
    Add water quality md5 and other information to postgres database. After committing, call on the primary key, id,
    to get the persisted, auto-incremented, id. The record must be committed before this value is assigned.
    :param MD5: String of MD5 hash.
    :param pdfDate: String of pdfDate.
    :param pdfName: String of PDF name, without file location

    :return:
    Int, primary key of new MD5 entry
    """
    session = Session()
    # application.logger.debug(f"Inserting new md5 hash using the following details: md5:{MD5}, pdfdate:{pdfDate}",
    #                          f" pdfname:{pdfName}, insdate:{datetime.now()}")
    application.logger.debug(
        f"Inserting new md5 hash using the following details:")
    application.logger.debug(MD5)
    application.logger.debug(pdfDate)
    application.logger.debug(pdfName)
    application.logger.debug(datetime.now())
    # Get datetime in PST since records are from this time zone
    newrec = waterQualityMD5(md5=MD5,
                             pdfdate=pdfDate,
                             pdfName=pdfName,
                             insdate=datetime.now(
                                 pytz.timezone("America/Los_Angeles")))
    session.add(newrec)
    session.commit()
    newId = newrec.id
    session.close()
    application.logger.debug("Data added to MD5 table!")
    application.logger.debug(f"New water quality md5 hash id is {newrec.id}")
    return newId
Esempio n. 13
0
def checkAthleteAndSub(athID, subID):
    """
    Checks if provided athlete and subscription ID are in the database with an active subscription status
    @param athID: Int. Strava athlete ID
    @param subID: Int. Strava Webhook Subscription ID
    @return: Object Instance. Instance of Athletes Model with results
    """
    # Open session
    session = Session()
    # Query database
    record = session.query(athletes). \
        join(webhook_subs). \
        filter(athletes.athlete_id == athID, webhook_subs.sub_id == subID, webhook_subs.activesub == "Yes").first()
    session.close()
    return record
Esempio n. 14
0
def checkVerificationToken(token):
    """
    Verifies that the provided verification token is in the database. Used as part Strava Webhook subscription callback
    verification and setup process. Only needed on setup, further POST requests won't contain the token.
    @param token: String. Strava verify token generated in script
    @return: Instance of webhook sub model if exists, None otherwise
    """
    # Open session
    session = Session()
    # Query database, get most recent record in case the token is in the database multiple times
    record = session.query(webhook_subs.verify_token == token).order_by(
        webhook_subs.id.desc()).first()
    session.close()
    if record:
        return record
Esempio n. 15
0
def getSubIdList():
    """
    Gets list of subscription webhook IDs from database.
    Check if used, likely can delete
    Returns
    -------
    List. Subscription webhook IDs (Int) stored in database.
    """
    session = Session()
    query = session.query(athletes).all()
    subIdList = []
    for i in query:
        subIdList.append(i.sub_id)
    session.close()
    return subIdList
Esempio n. 16
0
def getAthleteList():
    """
    Gets list of athlete IDs from database.

    Returns
    -------
    List. Athlete IDs (int) stored in database.
    """
    session = Session()
    query = session.query(athletes).all()
    athleteList = []
    for i in query:
        athleteList.append(i.athlete_id)
    session.close()
    return athleteList
def getStandards():
    """
    Get the state health standards for ocean water quality tests.

    Returns
    -------
    recDict : Dictionary
        Dict of State health standards, with the standard name as the keys and values as values.

    """
    session = Session()
    records = session.query(stateStandards).all()
    recDict = {}
    for i in records:
        recDict[i.Name] = i.StandardMPN
    session.close()
    return recDict
Esempio n. 18
0
def create(name, url):
    '''新增'''
    job = Job()
    job.name = name
    job.url = url
    job.create_time = datetime.now()

    session = Session()
    session.add(job)
    session.commit()
    session.close()
Esempio n. 19
0
def getAuth():
    """
    Loads Strava client authentication details from Postgres and creates a authorized client instance.
    Checks if access token is expired, if so it is refreshed and updated.

    Returns
    -------
    client. Stravalib model client instance. Contains access token to Strava API for the athlete, ID is hard coded for now.
    """
    # Build empty stravalib client instance
    client = Client()
    # create db session
    session = Session()
    # Hard coded athlete id
    athleteID = 7170058
    authDict = {}
    # Load tokens and expiration time from Postgres
    query = session.query(athletes).filter(athletes.athlete_id == athleteID)
    for i in query:
        authDict["Access_Token"] = i.access_token
        authDict["Expiration"] = i.access_token_exp
        authDict["Refresh_Token"] = i.refresh_token
    application.logger.debug(f"Auth token details are: {authDict}")
    # Check if access token has expired, if so request a new one and update Postgres
    if time.time() > authDict["Expiration"]:
        application.logger.debug("Access token has expired, refreshing")
        refresh_response = client.refresh_access_token(
            client_id=int(os.environ.get('STRAVA_CLIENT_ID')),
            client_secret=os.environ.get('STRAVA_CLIENT_SECRET'),
            refresh_token=authDict["Refresh_Token"])
        # Update access token and expiration date
        session.query(athletes).filter(athletes.athlete_id == athleteID). \
            update({athletes.access_token: refresh_response['access_token'],
                    athletes.access_token_exp: refresh_response['expires_at']})
        # Commit update
        session.commit()
        # Set Strava auth details
        client.access_token = refresh_response['access_token']
        client.refresh_token = authDict["Refresh_Token"]
        client.token_expires_at = refresh_response['expires_at']
    else:
        application.logger.debug("Access token is fresh, no refresh required")
        # Access token is up-to-date, set client details
        client.access_token = authDict["Access_Token"]
        client.refresh_token = authDict["Refresh_Token"]
        client.token_expires_at = authDict["Expiration"]
    # Close out session
    session.close()
    return client
Esempio n. 20
0
def update(job_id, name, url):
    '''修改'''
    session = Session()
    job = session.query(Job).get(job_id)
    job.name = name
    job.url = url
    session.commit()
    session.close()
Esempio n. 21
0
def deleteVerifyTokenRecord(token):
    """
    Deletes script generated Strava webhook verification token from database, called when an existing webhook is removed
    @param token: String. Strava verify token generated in script
    @return: Nothing
    """
    # Open session
    session = Session()
    session.query(webhook_subs).filter(
        webhook_subs.verify_token == token).delete()
    session.commit()
    session.close()
Esempio n. 22
0
def removeActivityFromDB(actID):
    """
    Removes a activity from the original and public activities database tables.

    @param actID: Int. Strava Activity ID
    @return: Nothing.
    """
    # Open session
    session = Session()
    # Delete from masked table
    session.query(strava_activities_masked).filter(
        strava_activities_masked.actID == actID).delete()
    # Delete from original DB table
    session.query(strava_activities).filter(
        strava_activities.actID == actID).delete()
    # Commit changes
    session.commit()
    # Close session
    session.close()
Esempio n. 23
0
def insertVerifyToken(token):
    """
    Inserts the provided generated token into database.
    @param token: String. Strava verify token generated in script.
    @return: Nothing
    """
    # Open session
    session = Session()
    # Create new record
    newRec = webhook_subs(verify_token=token)
    # Add new record to session
    session.add(newRec)
    session.commit()
    session.close()
def getNullBeaches(pdfDate):
    """
    Returns list of beaches with null values for the given PDF test week. Only called when a update/re-sample PDF is
    downloaded.

    :param pdfDate: String
        Date of new weekly PDF results
    :return: List[Strings,]
        Names of beaches with null test results
    """
    session = Session()
    query = session.query(waterQuality) \
        .join(waterQualityMD5) \
        .join(beaches) \
        .filter(waterQualityMD5.pdfdate == pdfDate) \
        .filter(or_(waterQuality.FecColi == None, waterQuality.Entero == None, waterQuality.TotColi == None)) \
        .all()
    nullbeaches = []
    for i in query:
        nullbeaches.append(i.beach_rel.BeachName)
    session.close()
    return nullbeaches
Esempio n. 25
0
def setWebhookInactive(subID):
    """
    Sets provided subscription to inactive status
    @param subID: Int.
    @return: Nothing
    """
    # Open session
    session = Session()
    # Set active status to No
    session.query(webhook_subs).filter(webhook_subs.sub_id == subID).update(
        {webhook_subs.activesub: "No"})
    # Commit changes
    session.commit()
    # Close out session
    session.close()
def getBeachResults(beach):
    """

    @param beach:
    @return:
    """
    session = Session()
    records = session.query(waterQuality, waterQualityMD5) \
        .join(waterQualityMD5) \
        .join(beaches) \
        .filter(beaches.BeachName == beach) \
        .order_by(waterQuality.id.desc()) \
        .limit(10)

    resultDict = {}
    for i in records:
        resultDict[i[0].id] = {}
        resultDict[i[0].id]["status"] = i[0].BeachStatus
        resultDict[i[0].id]["date"] = i[1].pdfdate.isoformat()
        resultDict[i[0].id]["s3PDFURL"] = create_presigned_url(i[1].pdfName)
        # print(i[0].FecColi)

    session.close()
    return resultDict
Esempio n. 27
0
def updateExistingActivity(update):
    """
    Updates existing activity in database, currently only handles activity title updates.

    @param update: Stravalib update instance
    @return: Nothing.
    """
    # Get object ID
    objectID = update.object_id
    # Get new activity title, if applicable
    newTitle = update.updates['title']
    session = Session()
    # use SQL alchemy to update existing feature title
    session.query(strava_activities).filter(strava_activities.actID == objectID). \
        update({strava_activities.name: newTitle})
    session.commit()
    session.close()
Esempio n. 28
0
def AOIIntersection(geomdat):
    """
    Issues a SQLAlchemy/GeoAlchemy intersection query against the AOI PostGIS table and returns a string of results.

    The AOI table contains hand digitized locations of interest.

    Currently only single POI results are returned, however this will change as POIs are added.

    Parameters
    ----------
    geomdat : String
        WKT representation of the incoming GPS point coordinates.

    Returns
    -------
    result : String
        AOI results as a string with individual results comma seperated or None in case
            of empty result.
        A comma seperated string is returned for easier processing and insertion into
        dictionary created by the handleTrackerQueries function.

    """
    session = Session()
    # SQLAlchemy and GeoAlchemy SQL query
    query = session.query(AOI).filter(AOI.geom.ST_Intersects(geomdat))
    # Get the size of the result, used for building out the string result.
    query_count = 0
    for i in query:
        query_count += 1

    if query_count > 0:
        result = ""
        count = 0
        # Iterate over SQL Alchemy result object, if greater than 1 result build out with comma seperation.
        for POI in query:
            if count > 0:
                # result object columns can be individually called with their column names, only want location info.
                result += "," + POI.location
                count += 1
            else:
                result += POI.location
                count += 1
    else:
        session.close()
        return None
    session.close()
    return result
Esempio n. 29
0
def getPathPointRecords():
    """
    Gets the most recently recorded GPS point, used to check for movement and to generate a GPS track. This entire function may not be needed
    and can likely be combined with "getrecords".

    Returns a dictionary with a single top level key and a nested dictionary of record details, kept logic for multiple top level keys in
    case I need to build this function out.

    Parameters
    ----------
    Returns
    -------
    res_dict : Dictionary
       Results of gps point query with details as keys.

    """

    session = Session()
    # records = session.query(gpsdatmodel.id, gpsdatmodel.lat, gpsdatmodel.lon, gpsdatmodel.geom, gpsdatmodel.timeutc,
    #                         gpsdatmodel.date). \
    #     filter(gpsdatmodel.getLocalTime() >= dateTime). \
    #     order_by(gpsdatmodel.timeutc.desc()).limit(1).all()
    records = session.query(gpsPointModel.id, gpsPointModel.lat, gpsPointModel.lon, gpsPointModel.geom,
                            gpsPointModel.timeutc, gpsPointModel.date). \
        order_by(gpsPointModel.timeutc.desc()).limit(1).all()
    res_dict = {}
    row_count = 0
    for row in records:
        row_count += 1
    if row_count > 0:
        for row in records:
            res_dict[row.id] = {
                "id": row.id,
                "lat": row.lat,
                "lon": row.lon,
                "utc": row.timeutc,
                "date": row.date,
                "geom": row.geom
            }
        session.close()
        return res_dict
    else:
        session.close()
        return None
Esempio n. 30
0
def countyIntersection(geomdat):
    """
    Issues a SQLAlchemy/GeoAlchemy intersection query against the county PostGIS table and returns a string of results.

    Parameters
    ----------
    geomdat : String
        WKT representation of the incoming GPS point coordinates.

    Returns
    -------
    TYPE
        String of intersecting city or None if empty result.
        Should only ever be a single result but logic is included in case of multiple
        records returned.

    """

    session = Session()
    query = session.query(CACounty).filter(
        CACounty.geom.ST_Intersects(geomdat))

    query_count = 0
    for i in query:
        query_count += 1
        # Logic to create a comma separted string of all results in case multiple counties
    # are returned, this should not happen under normal circumstances
    if query_count > 0:
        result = ""
        count = 0
        for county in query:
            if count > 0:
                result += "," + county.name
                count += 1
            else:
                result += county.name
                count += 1
    else:
        session.close()
        return "Out of State!"
    session.close()
    return result