python - 如何在Flask中获取自定义SELECT查询？

我想在Flask-Peewee

SELECT A.* , haversine('34.0160',' -118.4925', A.lat, A.long, 'MILES') AS dist FROM merchant_details A HAVING haversine('34.0160', '-118.4925', A.lat, A.long, 'MILES') <6000

我尝试了下面这段代码，但没有成功，我得到的“long”对象没有“fetchall”属性：

@app.route('/api/results/')
def results():
    db = connect_db()
    cur = db.execute("SELECT A.* , haversine('34.0160',' -118.4925', A.lat, A.long, 'MILES') AS dist FROM merchant_details A HAVING haversine('34.0160', '-118.4925', A.lat, A.long, 'MILES') <6000 LIMIT 1")
    entries = [dict(id=row[0], merchant_id=row[1], merchant_name=row[2], first_name=row[3]) for row in cur.fetchall()]
    return repr(entries)

任何帮助都将不胜感激。
编辑>
以下是连接数据库功能：

from torndb import Connection

LOCALHOST = "localhost"
DBNAME = "XXXX"
DBUSER = "XXXX"
DBPASSWORD = "XXXX"


    #connect with DB
    def connect_db():
        db = Connection(LOCALHOST,DBNAME, user=DBUSER, password=DBPASSWORD)
        return  db

    #close the connection from DB
    def close_db(db):
        db.close()

最佳答案

如果要运行SQL查询，可以使用flask peewee：

db = Database(app)
db.database.execute_sql("some query", [param1, param2])

如果MerchantDetails是模型，您可以尝试：

dist = fn.haversine(
    34.0160,
    -118.4925,
    MerchantDetails.lat,
    MerchantDetails.long,
    'MILES')
MerchantDetails.select(MerchantDetails, dist.alias('dist')).having(dist < 6000)

得到：

SELECT A.* , haversine('34.0160',' -118.4925', A.lat, A.long, 'MILES') AS dist
FROM merchant_details A
HAVING haversine('34.0160', '-118.4925', A.lat, A.long, 'MILES') <6000

关于python - 如何在Flask中获取自定义SELECT查询？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20683978/