How to fetch a custom SELECT query in Flask?

Question

I'd like to fetch this custom query on 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

I tried the following piece of code code but didn't work and I'm getting 'long' object has no attribute '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)

Any help would be greatfully appreciated.

EDITED>

Here is connect_db function:

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()

Answer 1

With flask-peewee if you want to run a SQL query you can do:

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

If MerchantDetails is a model, you could try:

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

To get:

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

Answer 2

My guess would be that connect_db() actually returns a cursor and not a db connection. As such, the return value from db.execute would in fact be a long (the number of rows returned from the query). What you should try as a first step would be to change to the following:

curr = connect_db()
curr.execute("QUERY")
entries = [... for row in curr.fetchall()]

It is also very possible (I did not look at source) that the connect_db is returning a new object which wraps the functionality of the connection object and cursor object such that you can interact with it as either. I have done this in the past myself. The only oddity is handling of the close method, since both cursor and connection define close. In my case, close closed both.

EDIT based on your comment
Again, without reading the source code for connect_db() I can only really guess. But you may want to try:

conn = connect_db()
curr = conn.cursor()

And proceed as before. If that still gives you an error, take a look at its properties and methods by adding a print dir(conn) after the call toconnect_db(). My guess now is that it will show acursor` function in that list.