On Oct 15, 2011, at 7:52 AM, Krishnakant Mane wrote:
>
> On 12/09/11 03:09, Michael Bayer wrote:
>> On Sep 11, 2011, at 3:43 PM, Krishnakant Mane wrote:
>>
>>> On 12/09/11 00:56, Michael Bayer wrote:
>>>> You use the "func" construct to invoke a function. This can be passed to
>>>> an execute() method directly where it should embed itself into a SELECT:
>>>>
>>>> from sqlalchemy import func
>>>>
>>>> result = engine.execute(func.name_of_my_pg_function(1, 2, 3))
>
>
>
> Can you please give a complete example.
one concrete example, coming up. this function is a widely used "distance
calculation" function that can be found all over google. the last line is the
execute.
from sqlalchemy import func, create_engine
engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
engine.execute(
"""
CREATE OR REPLACE FUNCTION calc_distance(lat_1 float, lon_1 float,
lat_2 float, lon_2 float) RETURNS float AS $$
BEGIN
RETURN 3963.0 * acos(sin(lat_1 / 57.2958) * sin(lat_2 / 57.2958) +
cos(lat_1 / 57.2958) * cos(lat_2 / 57.2958) * cos((lon_2 / 57.2958) - (lon_1 /
57.2958)));
END;
$$ LANGUAGE plpgsql;
""")
print "Sample distance:", engine.execute(func.calc_distance(32.9697, -96.80322,
29.46786, -98.53506)).scalar()
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
from sqlalchemy import func, create_engine
engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
engine.execute(
"""
CREATE OR REPLACE FUNCTION calc_distance(lat_1 float, lon_1 float, lat_2 float, lon_2 float) RETURNS float AS $$
BEGIN
RETURN 3963.0 * acos(sin(lat_1 / 57.2958) * sin(lat_2 / 57.2958) + cos(lat_1 / 57.2958) * cos(lat_2 / 57.2958) * cos((lon_2 / 57.2958) - (lon_1 / 57.2958)));
END;
$$ LANGUAGE plpgsql;
""")
print "Sample distance:", engine.execute(func.calc_distance(32.9697, -96.80322, 29.46786, -98.53506)).scalar()
