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
– Mark Hildreth, Commented Dec 19, 2013 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
– Alireza Seifi, Commented Dec 19, 2013 at 21:27

coleifer · Accepted Answer · 2013-12-20 00:03:19Z

2

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

answered Dec 20, 2013 at 0:03

coleifer

26.4k7 gold badges67 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sberry · Accepted Answer · 2013-12-19 22:44:25Z

1

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.

edited Dec 19, 2013 at 22:44

answered Dec 19, 2013 at 14:31

sberry

133k20 gold badges145 silver badges171 bronze badges

1 Comment

Alireza Seifi Over a year ago

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?

Collectives™ on Stack Overflow

How to fetch a custom SELECT query in Flask?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related