SQL to SQLALCHEMY

Question

Base on this post, I have this SQL query:

SELECT 
    id, (6371.009 * acos(cos(radians(37)) * cos(radians(lat)) * 
cos(radians(lng) - radians(-122)) + sin(radians(37)) * 
sin(radians(lat)))) AS distance 
FROM 
    company 
HAVING
    distance < 25 
ORDER BY 
    distance 
LIMIT 
    0 , 20;

and I want to convert this query to SQLALCHEMY. How I can convert SQL queries that are included of functions (e.g. acos, cos, sin, radians, ...) to SQLALCHEMY queries?

I have tried this:

company_nearby = s.query(Company).filter(
    (6371.009 * acos(
        cos(radians(37)) * cos(radians(Company.latitude)) *
        cos(radians(Company.longtitude) - radians(-122)) +
        sin(radians(37)) * sin(radians(Company.latitude))
    )) < 25
).limit(20).all()

But I have this error:

TypeError: a float is required

Probably because Company.latitude returns None, but company table has 16000 record with latitude and longitude.

This is Company table:

class Company(db.Model):
    __tablename__ = 'company'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255))
    latitude = db.Column(db.Float)
    longtitude = db.Column(db.Float)

Any help will be much appreciated.

[`func`](http://docs.sqlalchemy.org/en/rel_0_9/core/sqlelement.html#sqlalchemy.sql.expression.func) construct is what you're looking for. — Audrius Kažukauskas, Nov 17 '14 at 11:32

score 0 · Answer 1 · answered Nov 18 '14 at 10:16

0

To use functions within your database you can use the func method: http://docs.sqlalchemy.org/en/latest/core/functions.html#sqlalchemy.sql.functions.func

Something like this for example:

print select([func.cos(func.radians(table.c.lng))

answered Nov 18 '14 at 10:16

Wolph

78,177
11
137
148

score -1 · Accepted Answer · answered Nov 18 '14 at 16:00

Using raw query execution is one of the methods to run SQL queries in SQLALCHEMY. for this example this is how I proceed:

from math import sqrt, radians, cos, acos, sin, pow

...

raw_con = engine.raw_connection()
raw_con.create_function("cos", 1, cos)
raw_con.create_function("acos", 1, acos)
raw_con.create_function("sin", 1, sin)
raw_con.create_function("radians", 1, radians)
raw_con.create_function("sqrt", 1, sqrt)
raw_con.create_function("pow", 1, pow)

results = []

try:
    cursor = raw_con.cursor()

    cursor.execute('SELECT id, '
                   '( 6371.009 * acos( cos( radians(37) ) * '
                   'cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + '
                   'sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance '
                   'FROM company '
                   'WHERE  distance < 25 '
                   'ORDER BY distance '
                   'LIMIT 0 , 20 ;')

    results = cursor.fetchall()
    cursor.close()
finally:
    raw_con.close()

and:

print results

gives:

[(68, 4958.2844864897925), (50, 4958.399769765616), (17, 4958.417639125849), (13, 4958.701413904964), (16, 4958.720521740769), (46, 4958.758729888283), (54, 4958.769050291066), (47, 4958.789468071686), (57, 4958.833125409799), (14, 4958.843715312685), (45, 4958.853
383812306), (60, 4958.870656980226), (35, 4958.901153544977), (65, 4958.901643917998), (37, 4958.941355727554), (33, 4958.974325724986), (41, 4958.985977481861), (1, 4959.002398001085), (38, 4959.093650178002), (8, 4959.142373411243)]

SQL to SQLALCHEMY

2 Answers2

Linked