python - Accessing table variables in Flask-Sqlalchemy query during search

Question

I am writing a RESTful api using Flask and Flask-SQLalchemy, as well as Flask-Login. I have a model Users that's initialized like:

class Users(UserMixin, db.Model):
    __tablename__ = "users"
    # STATIC Standard required info
    id = db.Column("id", db.Integer, primary_key = True)
    public_id = db.Column("public_id", db.String(50), unique = True)
    email = db.Column("email", db.String(50), unique = True)
    username = db.Column("username", db.String(20), unique = True)
    password = db.Column("password", db.String(20))

    # DYNAMIC Standard required info
    lat = db.Column("lat", db.Float)
    lon = db.Column("lon", db.Float)

There are other elements in the table but the lat and lon are most relevant. I have a method distanceMath() that takes in the currently logged in user's latitude and longitude, as well as a latitude and longitude from the table to calculate distance between them. But I can't figure out how to access the table values during the following query that calls distanceMath without receiving a syntax error:

lat1 = current_user.lat
lon1 = current_user.lon
users = Users.query.filter(distanceMath(lat1, Users.lat, lon1, Users.lon)==1)
    output = []
    for user in users:
        data = {}
        data["username"] = user.username
        output.append(data)
    return str(output)

Just in case, here's the distanceMath method:

# Haversine Distance
def distanceMath(lat1, lat2, lon1, lon2):
    distance = math.acos(math.sin(math.radians(lat1))*math.sin(math.radians(lat2))) + math.cos(math.radians(lat1))*math.cos(math.radians(lat2))*math.cos(math.radians(lon1)-math.radians(lon2))
    return distance

An example of the error is:

TypeError: must be real number, not InstrumentedAttribute

Which, in my understanding, is essentially saying that User.lat and User.lon don't refer to a float or even a number, and instead an attribute to a table. My question is how to actually use what lat and lon are equal to (In the database they are equal to 37.7 and -122.4 respectively).

Answer 1

Your intuition is correct and the error is the result of passing SQLAlchemy's instrumented attributes to Python's math library functions, which have no clue what to do with such.

In order to query based on distance you can either fetch all the data from the database and filter in Python (usually not desired), or send the operation to the DBMS in the query for evaluation. The trick is then to form a suitable SQL expression. Instead of calling Python's math functions you need to create SQL function expressions using func:

# Bind math as a keyword argument and provide Python's math module as the default
def distanceMath(lat1, lat2, lon1, lon2, math=math):
    distance = math.acos(math.sin(math.radians(lat1))*math.sin(math.radians(lat2))) + math.cos(math.radians(lat1))*math.cos(math.radians(lat2))*math.cos(math.radians(lon1)-math.radians(lon2))
    return distance

and in the view:

users = Users.query.filter(distanceMath(lat1, Users.lat, lon1, Users.lon, math=db.func) == 1)

You could also wrap the distance calculations in a hybrid attribute that would handle either producing an SQL expression or performing the calculation on instances in Python.

But there is a problem: SQLite does not have the required math functions by default. It does allow creating custom functions easily, though. Such operations should be handled once when a connection is created by the pool for the first time:

import math
from sqlalchemy import event

# Before anything else DB related is performed:
event.listens_for(db.get_engine(), 'connect')
def create_math_functions_on_connect(dbapi_connection, connection_record):
    dbapi_connection.create_function('sin', 1, math.sin)
    dbapi_connection.create_function('cos', 1, math.cos)
    dbapi_connection.create_function('acos', 1, math.acos)
    dbapi_connection.create_function('radians', 1, math.radians)