将复杂的 SQL 查询转换为 SQLAlchemy

2021-12-30 00:00:00 python sqlalchemy geospatial mysql

我的想法用完了.我现在用谷歌搜索了超过一天，但我仍然找不到任何有用的答案来解决我的问题.

I run out of ideas. I googled now more then one day and I still can't find any useful answer to my question.

到目前为止我所做的，我尝试使用原始 SQL 但没有成功.

What I did until now, I tried to use raw SQL but without luck.

locations = db.session.query(Location, select([text('( 6371 * acos( cos( radians("53.6209798282177") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians("13.96948162900808") ) + sin( radians("53.6209798282177") ) * sin( radians( lat ) ) ) )')]).label('distance')).having('distance' < 25).all()

当使用这个原始 SQL 查询时，我返回零结果，但是在 mysql 中运行相同的查询时，它返回正确的结果.

When using this raw SQL query I get zero results returned, but when running the same query in mysql it returns correct results.

我进一步发现，当将查询打印到终端时，它没有正确处理 HAVING() 子句.

Further more I figured out, that when printing the query to terminal, it does not handle the HAVING() clause correctly.

我的查询在打印时如下所示:

My query looks like this when printed:

SELECT location.id AS location_id, location.created_date AS location_created_date, location.zip AS location_zip, location.user_id AS location_user_id, location.lat AS location_lat, location.lng AS location_lng, location.city AS location_city 
FROM location 
HAVING false = 1

如何将此 SQL 查询转换为 SQLAlchemy

SELECT *, ( 6371 * acos( cos( radians(53.6209798282177) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(11.96948162900808) ) + sin( radians(53.6209798282177) ) * sin( radians( lat ) ) ) ) AS distance FROM location HAVING distance < 25 ORDER BY distance;

我的桌子是这样的:

+--------------+----------------+------+-----+---------+-------+
| Field        | Type           | Null | Key | Default | Extra |
+--------------+----------------+------+-----+---------+-------+
| id           | varchar(50)    | NO   | PRI | NULL    |       |
| created_date | datetime       | YES  |     | NULL    |       |
| zip          | varchar(5)     | NO   | UNI | NULL    |       |
| user_id      | varchar(50)    | NO   |     | NULL    |       |
| lat          | decimal(15,13) | NO   |     | NULL    |       |
| lng          | decimal(15,13) | NO   |     | NULL    |       |
| city         | text           | NO   |     | NULL    |       |
+--------------+----------------+------+-----+---------+-------+

感谢任何帮助.

推荐答案

您的 HAVING 处理正确，但您传递的表达式错误.看来您正在使用 Python 2，因为字符串和整数之间的关系比较

Your HAVING is handled correctly, but you're passing it the wrong expression. It seems that you're using Python 2, since the relational comparison between a string and an integer

'distance' < 25

不会引发异常，而是评估为 False.换句话说，您的查询等于

does not raise an exception, but evaluates to False instead. In other words your query is equal to

locations = db.session.query(...).having(False).all()

这解释了为什么你得到零结果:所有行都被 HAVING 子句显式过滤掉，如打印版本所示:

which explains why you get zero results: all rows are explicitly filtered out by the HAVING clause, as seen in the printed version:

...
HAVING false = 1  -- remove all rows

解决方案是使用合适的结构，例如 column()，生成表达式:

A solution is to use a suitable construct, such as column(), to produce the expression:

locations = db.session.query(...).having(column('distance') < 25).all()

您不应将复杂的选择列表项表达式包装在 select()，代表一条SELECT语句.按原样标记 text():

You shouldn't wrap the complex select list item expression in a select(), which represents a SELECT statement. Either label the text() as is:

text('( 6371 * acos( cos( radians("53.6209798282177") ) * '
     'cos( radians( lat ) ) * cos( radians( lng ) - radians("13.96948162900808") ) + '
     'sin( radians("53.6209798282177") ) * sin( radians( lat ) ) ) ) '
     'AS distance')

或使用模型构建表达式:

or build the expression using the model:

(6371 *
 func.acos(func.cos(func.radians(53.6209798282177)) *
           func.cos(func.radians(Location.lat)) *
           func.cos(func.radians(Location.lng) - func.radians(13.96948162900808)) +
           func.sin(func.radians(53.6209798282177)) *
           func.sin(func.radians(Location.lat)))).label('distance')

您可以通过为 great-circle distance<创建函数来提高查询构造的可读性/a>，通过一些工作，你可以实现一个 Location 上的 rel="noreferrer">混合方法:

You could improve the readability of your query construction by making a function for the great-circle distance, and with a little bit of work you could implement a hybrid method on Location:

import math

def gc_distance(lat1, lng1, lat2, lng2, math=math):
    ang = math.acos(math.cos(math.radians(lat1)) *
                    math.cos(math.radians(lat2)) *
                    math.cos(math.radians(lng2) -
                             math.radians(lng1)) +
                    math.sin(math.radians(lat1)) *
                    math.sin(math.radians(lat2)))

    return 6371 * ang

class Location(db.Model):
    ...
    @hybrid_method
    def distance(self, lat, lng):
        return gc_distance(lat, lng, self.lat, self.lng)

    @distance.expression
    def distance(cls, lat, lng):
        return gc_distance(lat, lng, cls.lat, cls.lng, math=func)

locations = db.session.query(
        Location,
        Location.distance(53.6209798282177,
                          13.96948162900808).label('distance')).
    having(column('distance') < 25).
    order_by('distance').
    all()

<小时>

请注意，您使用 HAVING 消除非组行的方式不可移植.例如在 Postgresql 中存在 HAVING 子句将查询转换为分组查询，即使没有 GROUP BY 子句.您可以改用子查询:

Note that the way you use HAVING to eliminate non-group rows is not portable. For example in Postgresql the presence of HAVING clause turns a query in to a grouped query, even without a GROUP BY clause. You could use a subquery instead:

stmt = db.session.query(
        Location,
        Location.distance(53.6209798282177,
                          13.96948162900808).label('distance')).
    subquery()

location_alias = db.aliased(Location, stmt)

locations = db.session.query(location_alias).
    filter(stmt.c.distance < 25).
    order_by(stmt.c.distance).
    all()

相关文章