Python - Postgres función para envío de mails

CREATE OR REPLACE FUNCTION py_sendmail()
  RETURNS trigger AS
$BODY$
    import smtplib
    from email.MIMEMultipart import MIMEMultipart
    from email.mime.text import MIMEText
    from email.MIMEImage import MIMEImage

    # parte 1ra.
    sql = 'SELECT  campo1, campo2  FROM table'
    sql += 'WHERE  id = '
    sql +=  str(TD['new']['id'])

    mensaje = plpy.execute(sql)
    for msg in evento:
        txt_mensaje += 'texto del mail']+'\n'
        txt_mensaje += 'Dato recuperado de la tabla: ' + str(msg['campo1']) +'\n'

    # parte 2da.
    usuarios = plpy.execute('select nombre, email from users ')
    for usuario in usuarios:
          msg = MIMEText(txt_mensaje)
          msg['Subject'] = 'Prueba'
          msg['From'] = 'Juancito Pinto <juancitopinto@gmail.com>'
          msg['To'] = str(usuario['email'])
        smtpserver = smtplib.SMTP('smtp.gmail.com',587)
        smtpserver.ehlo()
        smtpserver.starttls()
        smtpserver.ehlo()
        smtpserver.login('juancitopinto@gmail.com','pwd_de_juancitopinto')
         smtpserver.sendmail('juancitopinto@gmail.com',str(usuario['email']),msg.as_string())
        smtpserver.close()
   
$BODY$
  LANGUAGE plpython2u VOLATILE

Descripción:

La función descrita esta previsto ser ejecutada por un trigger (cada vez que se adicione un registro por ejemplo).

En la primera parte realiza una consulta a una "tabla" para luego enviar el restultado por mail (Ejm. campo1).

En la segunda parte realiza la consulta a la tabla "users" obteniendo las direcciones de correo para luego enviar el mail correspondiente a todos los usarios existentes en users. Utiliza el servidor SMTP de gmail.

Algo para considerar es que normalmente se recuperan datos de diferentes tipos: enteros, date, time,... etc. de la base de datos, los cuales debén ser previamente convertidos a String para componer una cadena en el mensaje a envíar; una opción si la base de datos es Postgres realizar la conversión con funciones de Postgres Ejm:

SELECT to_char(hora_type, \'HH24:MI:SS\') as hora_string from tabla    

mas referencias: http://www.postgresql.org/docs/9.3/static/functions-formatting.html

Postgres - Triggers invocando a función Python - Ejemplo

Dadas las siguientes tablas:

create table t3 (id int, name varchar);
create table t4 (id int, name varchar);

Con la función en python:

create or replace function py_addpar()
returns trigger as $$
     if int(TD['new']['id'])%2 == 0:
        sql = plpy.prepare(""" INSERT INTO t4 (id, name) values ($1, $2); """, ("INTEGER", "TEXT",))
        ss = plpy.execute(sql, (TD['new']['id'], TD['new']['name'],))
$$ language plpython2u;


y el trigger que lo invoca:

create trigger tr_add
    after insert
    on t3
    for each row
    execute procedure py_addpar();

Verificando:

insert into t3 (id, name) values (1, 'uno');
insert into t3 (id, name) values (2, 'dos');

select * from t4 ;

visualizará:

        id |  name
     ------+-----------
        2  |  'dos'

Es decir el trigger ejecuta la función py_addpar que adiciona en la tabla t4  los valores de la tabla t3  si es que el campo id es par.

Consideraciones:

El lenguaje PL/Python importa automáticamente el módulo plpy. que proporciona dos funciones (execute y prepare). Llamando con plpy.execute con un string de consulta (query) y el argumento (limit) opcional retorna en un objeto resultado. La segunda función "prepare" prepara la ejecución, es llamado con un string de consulta y una lista de tipos de parámetro.

A diferencia de las funciones invocadas en plpgsql, los valores enviados como parámetros  se encuentran en el diccionario [TD] (http://www.postgresql.org/docs/9.1/static/plpython-trigger.html)

Los valores de los parámetros deben ser previamente convertidos (cast) al tipo de dato que corresponde, Ejm:  int(TD['new']['id'])

Ahora con PL/PGSql:

El siguiente código realiza lo mismo con la única diferencia que utiliza el lenguaje PL/PGSql:

Función:

create or replace function  py_addpar()
returns trigger as $$
BEGIN
if NEW.id % 2 = 0 then
    insert into t4 (id, name) values (NEW.id, NEW.name);
end if;
return NULL;
END;
$$ language plpgsql;

Postgres - Creando extensión python

Para usar PL/Python, primero se requier instalar Python en el servidor. Para Linux/Unix, los binarios de Python usualmente están disponibles.

Luego, se debe instalar la extensión PostgreSQL Python:

CREATE EXTENSION plpython2u;       --para python 2.7 por ejemplo
ó
CREATE EXTENSION plpython3u;

 Listando los lenguajes instalados (debería visualizar: plpgsql y plpython2u

 \dL

Linux - Búsqueda de texto en carpetas

Realiza la búsqueda en todos los archivos, visualizando aquellos donde se encuentra "txt_searched".

$ find . -exec grep -l "txt_searched" {} \;

Postgres - update desde otra tabla (three tables)

UPDATE tabla1
SET  campo1 = t2.campo2
FROM  tabla2 t2
WHERE tabla1.campo3 = t2.campo3

three tables:

UPDATE aa  
SET id_unico = ii.id_unico, dist = mm.dist
FROM ii, mm 
WHERE aa.idloc = mm.idloc
          AND ii.id_unico = mm.id_unico

Postgres - Adicionando registros en tabla con campo "id" autoincremental

INSERT tabla (id, nombre, direccion) VALUES (default, 'Juan Loza Castillo', '6 de Agosto #2649')

Para el caso de existir una tabla a la cual se desea adicionar el campo id autoincremental (serial)

ALTER TABLE tabla ADD COLUMN  id SERIAL;

Si se desea adicionar a partir del  valor 101 asignado al campo id:

CREATE SEQUENCE seq_test
  INCREMENT 1
  START  100

CREATE TABLE test (
  id integer DEFAULT NEXTVAL('seq_test') NOT NULL,
  nombre varchar(20)
)


Modificando un campo ya existen (id) a tipo autoincremental:

ALTER TABLE tabla ALTER COLUMN  id SET DEFAULT NEXTVAL('seq_test')