safe parameter bindings in sqlalchemy filter

Question

I need to pass a partial raw sql query into sqlalchemy filter, like

s.query(account).filter("coordinate <@> point(%s,%s) < %s"%(lat,long,distance))

Yes, I'm trying to use earthdistance function in postgresql.

Of course, I could use PostGis and GeoAlchemy2, but I want to know the general solution to this kind of problems.

I know sqlalchemy can safely pass raw sql query .

result = db.engine.execute("select * coordinate <@> point(:lat,:long) < :distance",**params)

Is there any similar function that can be used to bind parameter of partial(?) sql query? I guess someone who implements custom sql function like func.ll_to_earth have used the function.

Answer 1

There is .params() on query. Try this:

query = s.query(account).filter(
    "coordinate <@> point(:lat, :long_) < :dist").params(
        lat=lat, long_=long_, dist=distance)

And there is the documentation on it.

Note: I renamed your long param, because there is alread a __builtin__ named long (long int) in python, it's good practice to not overwrite already used words for obvious reasons.