如何在SQLAlchemy中将数学方程式用作过滤器 [英] How to Use Mathematic Equations as Filters in SQLAlchemy

问题描述

我正在使用SQLAlchemy ORM在应用程序中构造MySQL查询，并且完全能够向查询中添加基本过滤器，如下所示:

I'm using the SQLAlchemy ORM to construct the MySQL queries in my application, and am perfectly able to add basic filters to the query, like so:

query = meta.Session.query(User).filter(User.user_id==1)

这给了我基本上等同于此的东西:

Which gives me something basically equivalent to this:

SELECT * FROM users WHERE user_id = 1

我的问题是如何将一些基本的MySQL数学函数集成到查询中.因此，举例来说，我想让用户接近一定的经度和纬度.因此，我需要生成此SQL($ mylatitude和$ mylongitude是我要比较的静态纬度和经度):

My question is how I would integrate some basic MySQL math functions into my query. So, say for instance I wanted to get users near a certain latitude and longitude. So I need to generate this SQL ($mylatitude and $mylongitude are the static latitude and longitude I'm comparing against):

SELECT * FROM users 
WHERE SQRT(POW(69.1 * (latitude - $mylatitude),2) + POW(53.0 * (longitude - $mylongitude),2)) < 5

是否可以使用SQLAlchemy ORM将这些功能合并到查询中?

Is there a way I can incorporate these functions into a query using the SQLAlchemy ORM?

推荐答案

我将使用查询生成器界面和func SQL函数构造函数将计算抽象为一个函数.这样，您就可以自由地将其与别名或联接一起使用.

I'd use the query builder interface and the func SQL function constructor to abstract the calculation as a function. This way you can use it freely with aliases or joins.

User.coords = classmethod(lambda s: (s.latitude, s.longitude))

def calc_distance(latlong1, latlong2):
    return func.sqrt(func.pow(69.1 * (latlong1[0] - latlong2[0]),2)
                   + func.pow(53.0 * (latlong1[1] - latlong2[1]),2))

meta.Session.query(User).filter(calc_distance(User.coords(), (my_lat, my_long)) < 5)

这篇关于如何在SQLAlchemy中将数学方程式用作过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！