sqlalchemy 中的半正矢公式

发布于 2024-11-15 06:25:55 字数 864 浏览 7 评论 0原文

我的代码中有以下几行，

    query = "SELECT id, " \
        "( 3959 * acos( cos( radians(37) ) * cos( radians( %(lat)i ) ) * " \
        "cos( radians( %(lng)i ) - radians(-122) ) + sin( radians(37) ) * " \
        "sin( radians( %(lat)i ) ) ) ) AS `distance` from message where" \
        " `distance` <= %(drange)d" % {'lat': float(lat), 'lng': float(lng), 'drange': int(drange)}
    print query
    messages = db.session.query(Message).from_statement(query).all()

当我使用此错误时，出现以下错误

OperationalError: (OperationalError) (1054, "Unknown column 'distance' in 'where clause'") 'SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( 0 ) ) * cos( radians( 0 ) - radians(-122) ) + sin( radians(37) ) * sin( radians( 0 ) ) ) ) AS `distance` from message where `distance` <= 50' ()

修复此问题的正确方法是什么？

原文

I'm having the following lines in my code

    query = "SELECT id, " \
        "( 3959 * acos( cos( radians(37) ) * cos( radians( %(lat)i ) ) * " \
        "cos( radians( %(lng)i ) - radians(-122) ) + sin( radians(37) ) * " \
        "sin( radians( %(lat)i ) ) ) ) AS `distance` from message where" \
        " `distance` <= %(drange)d" % {'lat': float(lat), 'lng': float(lng), 'drange': int(drange)}
    print query
    messages = db.session.query(Message).from_statement(query).all()

I get the following error when I use this

OperationalError: (OperationalError) (1054, "Unknown column 'distance' in 'where clause'") 'SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( 0 ) ) * cos( radians( 0 ) - radians(-122) ) + sin( radians(37) ) * sin( radians( 0 ) ) ) ) AS `distance` from message where `distance` <= 50' ()

What is the correct way to fix this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

糖粟与秋泊 2024-11-22 06:25:55

您不能在 WHERE 子句中引用命名表达式（distance）（我不知道这对于所有数据库系统是否普遍如此，但至少对于MySQL）。您可以使用 HAVING 代替（请参阅选项 C）。

选项：

A. 在 where 子句中再次重复该表达式：

SELECT id, (long_formula) as distance FROM message WHERE (long_formula) <= ...

B. 使用嵌套查询：

SELECT * FROM 
(SELECT id, (long_formula) AS distance FROM message) inner_query 
WHERE distance <= ...

C. 使用 HAVING 子句（我已经使用 SQL 多年，但不知道 HAVING< /code> 直到我读到这个）：

SELECT id, (long_formula) as distance FROM message HAVING distance <= ...

You cannot reference the named expression (distance) in the WHERE clause (I don't know if this is generally true for all database systems, but it is at least so for MySQL). You can use HAVING instead (see option C).

Options:

A. Repeat the expression again in the where clause:

SELECT id, (long_formula) as distance FROM message WHERE (long_formula) <= ...

B. Use a nested query:

SELECT * FROM 
(SELECT id, (long_formula) AS distance FROM message) inner_query 
WHERE distance <= ...

C. Use the HAVING clause (I have used SQL for years but was not aware of HAVING until I read this):

SELECT id, (long_formula) as distance FROM message HAVING distance <= ...

回复收藏 0 原文

等数载，海棠开 2024-11-22 06:25:55

与您的问题无关，但如果开始使用“””，您可以提高查询的可读性
例如，您的查询可以按以下方式修改：

query = """
            SELECT id,
            ( 3959 * acos( cos( radians(37) ) * cos( radians( %(lat)i ) ) *  cos( radians( %(lng)i ) - radians(-122) ) +
              sin( radians(37) ) * sin( radians( %(lat)i ) ) ) ) AS distance
            FROM message
            WHERE distance <= %(drange)d
         """ % {'lat': float(lat), 'lng': float(lng), 'drange': int(drange)}

另外，对于我来说，使用“ AS 'distance'”而不是“ AS distance”作为别名是否正确，我的意思是是否允许使用“ ' ”？

Not related to your question but you can increase readability of your query if start using """
For example your query could be modified the following way:

query = """
            SELECT id,
            ( 3959 * acos( cos( radians(37) ) * cos( radians( %(lat)i ) ) *  cos( radians( %(lng)i ) - radians(-122) ) +
              sin( radians(37) ) * sin( radians( %(lat)i ) ) ) ) AS distance
            FROM message
            WHERE distance <= %(drange)d
         """ % {'lat': float(lat), 'lng': float(lng), 'drange': int(drange)}

Also just for me is this correctly to use " AS 'distance'" instead of " AS distance" as alias i mean is it allowed to use " ' "?

回复收藏 0 原文

~没有更多了~