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

What is `connect_db`? I don't see that function in neither the Peewee nor the Flask-Peewee API, so this must be a function you defined. We don't know what `db` is so we can't definitively tell you why you are getting that error. — Mark Hildreth, Dec 19 '13 at 19:44
@MarkHildreth Sorry I just edited the question and connect_db() is my 2nd connection to connect to mysql, but I don't think you need more information to answer my question, Do you know any other way to run custom query in flask-peewee like above query? — Alireza Seifi, Dec 19 '13 at 21:27

score 2 · Accepted Answer · answered Dec 20 '13 at 00:03

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

sberry · Answer 2 · 2013-12-19T22:44:25.773

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.

Thanks for your note, I'm getting "AttributeError: 'Connection' object has no attribute 'fetchall', is it related to the same issue? or the query runs correctly and this is another issue? — Alireza Seifi, Dec 19 '13 at 21:50

How to fetch a custom SELECT query in Flask?

2 Answers2