Re: Problem with pl/python procedure connecting to the internet

On 08/22/2015 10:16 AM, Igor Sosa Mayor wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
>
>
>> Is the function you show here:
>>
>> http://pastie.org/10366558
>>
>> the actual function you are using in the trigger?
>>
>> If so, there is no mechanism for getting the location coordinates into
>> the table, if that is what you want.
>>
>
> this is the function and there is a trigger which gets the coordinates
> into the table. It works perfectly when postgres is able to make a
> connection and get the data. But this is exactly what works only
> rarely... And I dont know the reason...

What is your trigger definition? Please post here not on Pastie.

So if you just run the Postgres function on its own, not via the
trigger, does it always return a value?

Are you taking lack of coordinates in the table as evidence of no
connection or are you determining that some other way?

Does anything show up in the Postgres logs when the trigger runs?

Have you tried using plpy.notice in plpythonu and RAISE NOTICE in
plpgsql to track what is happening?

Also from here:

http://geopy.readthedocs.org/en/latest/#module-geopy.geocoders

geocode()

exactly_one (bool) – Return one result or a list of results, if available.

Not sure what is going to happen to your code if it gets a list instead
of a single value.

>
> PS: this is the trigger, but as I said, it works if there is a
> connection...
> http://pastie.org/10368578

FYI, just include code snippets in the post. This keeps the code in the
thread and makes the thread not dependent on an external source. To that
end:

CREATE TYPE coordenadas AS (lat numeric, lon numeric);

CREATE OR REPLACE FUNCTION geocodificar(direccion text)
returns coordenadas
AS $$
from geopy.geocoders import Nominatim
geoloc = Nominatim()
location = geoloc.geocode(direccion)
return(location.latitude, location.longitude)
$$
LANGUAGE 'plpython2u';

CREATE OR REPLACE FUNCTION anadirgeocoord() RETURNS TRIGGER

AS $$
DECLARE
coord coordenadas;
BEGIN
SELECT * INTO coord from geocodificar(NEW.ciudad || ' ' ||
NEW.pais) ;
NEW.latitute := (coord).lat;
NEW.longitude := (coord).lon;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;

>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com