CODEWITHSUNDEEP
pythonpostgresqlflasksqlalchemy
RichS
RichS

Reputation: 117

Is it possible to have a function call in raw SQL query in SQLAlchemy (Flask)

I have a function distance(lat1, lon1, lat2, lon2) that can output the distance between two sets of latitude and longitude. Is it possible to have a function call in raw sql query like this:

current_city = {"lat": 34, "lon": 118}
query = text("""
             SELECT * , distance(current_city["lat"], current_city["lon"], lat, lon) as 
             curr_distance from cities 
             WHERE curr_distance <= 100
             """) 
### The last two arguments, lat and lon, are columns in "cities" table ###
city_within_100_miles = db.session.execute(query)

Any help would be greatly appreciated. Thank you all so much.

Upvotes: 0

Views: 508

Answers (1)

Jos&#233; Valent&#237;n
Jos&#233; Valent&#237;n

Reputation: 11

# what you have to do first is extract the data from your database

current_city = {"lat": 34, "lon": 118}

query = text("""
             SELECT  (lat, lon) as 
             curr_distance from cities 
             WHERE curr_distance <= 100
             """) 
resultset = conn.execute(query)
results_as_dict = resultset.mappings().all()
print(results_as_dict)
  """
    [{'lat': '35', 'lon': '22'}, 
     {'lat': '44', 'lon': '33'}]
    """
for data in results_as_dict:
    getDistance(data.lat, data.lon, current_city["lat"],current_city["lon"] )

 
def getDistance(lat1, lon1, lat2, lon2):
    """Logic your code here"""
    
    return (distance_final)

Upvotes: 1

Related Questions