将复杂的SQL查询转换为SQLAlchemy [英] Convert a complex SQL query to SQLAlchemy
问题描述
我没办法了.现在，我有一天在Google上进行了谷歌搜索，但仍然找不到对我的问题有用的答案.
到目前为止，我一直尝试使用原始SQL，但是没有运气.
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中运行相同的查询时，它返回正确的结果.
我进一步发现，将查询打印到终端时，它不能正确处理HAVING()子句.
我的查询在打印时如下所示:
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，因为字符串和整数之间的关系比较
'distance' < 25
不会引发异常，而是求值为False.换句话说，您的查询等于
locations = db.session.query(...).having(False).all()
这说明了为什么会得到零结果的原因:所有行均由HAVING子句明确过滤掉，如印刷版本所示:
...
HAVING false = 1 -- remove all rows
一种解决方案是使用合适的构造，例如 select() ，代表SELECT语句.要么按原样标记text():
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')
或使用模型构建表达式:
(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')
通过为大圆距离，只需做一些工作，您就可以实现混合方法在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子句，也可以将查询转换为分组查询.您可以改用子查询:
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()
I run out of ideas. I googled now more then one day and I still can't find any useful answer to my question.
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()
When using this raw SQL query I get zero results returned, but when running the same query in mysql it returns correct results.
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
How do I convert this SQL query to 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;
My table looks like this:
+--------------+----------------+------+-----+---------+-------+
| 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 | |
+--------------+----------------+------+-----+---------+-------+
Any help is appreciated.
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
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()
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
A solution is to use a suitable construct, such as column(), to produce the expression:
locations = db.session.query(...).having(column('distance') < 25).all()
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')
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()
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()
这篇关于将复杂的SQL查询转换为SQLAlchemy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
