Juliano Atanazio PL/Python: PL/Python: Programando em Pyhon no PostgreSQL Programando em Pyhon no PostgreSQL 

2/52 About me Juliano Atanazio ● Graduated in Computer Science for Business Management (Informática para Gestão de Negócios), FATEC Zona Sul, São Paulo – SP; ● PostgreSQL DBA; ● Linux admin; ● Instructor (PostgreSQL); ● LPIC-1, LPIC-2 Certified; ● Linux user since 2000; ● Free Software enthusiast; ● Favorite technologies: PostgreSQL, Linux, Python, Shell Script, FreeBSD, etc...; ● Headbanger :) \m/ 

3/52 Sobre esta Apresentação Esta apresentação é uma versão alternativa, em português de "On snakes and elephants Using Python inside PostgreSQL" de Jan Urbański https://wulczer.org/pywaw-summit.pdf 

4/52 Sobre Python ● Linguagem de altíssimo nível; ● Totalmente orientada a objetos; ● Fácil de aprender e de dar manutenção; ● Rica em bibliotecas; ● Propósitos gerais; ● Tipagem forte e dinânica; ● Comunidade forte e solícita; ● Objetiva e muito produtiva: faça mais escrevendo menos. import this www.python.org 

5/52 Sobre PostgreSQL É um sistema gerenciador de banco de dados objeto-relacional originalmente desenvolvido na Universidade da Califórnia no Departamento de Ciências da Computação em Berkeley. Hoje é desenvolvido e mantido pelo PGDG (PostgreSQL Global Development Group – Grupo de Desenvolvimento Global do PostgreSQL). ● Open source; ● Rico em recursos avançados; ● Muito amigável para programadores; ● Extensível www.postgresql.org 

6/52 Stored Procedures no PostgreSQL Em outros SGBDs há diferenças entre stored procedures e functions, no PostgreSQL é tudo function; Implementa operações que não seriam fáceis ou sequer seriam possíveis com SQL; Uma função é chamada dentro de SQL; Pode encapsular lógica de negócio; 

7/52 Stored Procedures no PostgreSQL PL/pgSQL é a linguagem padrão para se escrever functions no Postgres; PL/Python, PL/Perl e PL/Tcl são nativamente suportadas, mas é possível utilizar outras linguagens procedurais tais como PL/Java, PL/PHP, PL/v8 (JavaScript), PL/R e etc; Suporta sobrecarga de função. 

8/52 Funções: Sintaxe e Exemplos Criação da função fc_foo sem parâmetros: > CREATE OR REPLACE FUNCTION fc_foo() RETURNS VARCHAR AS $$ BEGIN RETURN 'Hello, World!'; END; $$ LANGUAGE PLPGSQL; 

9/52 Funções: Sintaxe e Exemplos Criação da função fc_foo com parâmetros: > CREATE OR REPLACE FUNCTION fc_foo(num1 INT, num2 INT) RETURNS INT AS $$ BEGIN RETURN (num1 + num2) * 2; END; $$ LANGUAGE PLPGSQL; 

10/52 Funções: Sintaxe e Exemplos Execução da função fc_foo sem parâmetros: > SELECT fc_foo(); fc_foo --------------- Hello, World! 

11/52 Funções: Sintaxe e Exemplos Execução da função fc_foo com parâmetros: > SELECT fc_foo(2, 5); fc_foo -------- 14 

12/52 Blocos Anônimos Sintaxe: DO $$ . . . codigo . . . $$ LANGUAGE linguagem; 

13/52 Blocos Anônimos > SET client_min_messages = 'notice'; > DO $$ DECLARE n_con INT; BEGIN SELECT count(client_addr) INTO n_con FROM pg_stat_activity; RAISE NOTICE 'Conexões não-locais: %', n_con; END; $$ LANGUAGE PLPGSQL; NOTICE: Conexões não-locais: 10 

14/52 PL/Python PL/Python é a implementação de Python como linguagem procedural no Postgres; Permite utilizar todas as bibliotecas de Python instaladas no sistema operacional; Ao rodar PL/Python pela primeira vez após o servidor PostgreSQL inicializar, um interpretador Python é inicializado dentro do processo backend; https://www.postgresql.org/docs/current/static/plpython.html 

15/52 PL/Python Para evitar uma demora inicial pode-se carregar a biblioteca de sistema plpython.so em alguma configuração “preload”; Tipos de dados PostgreSQL são convertidos para os de Python e vice-versa; Os argumentos de uma função têm escopo global; O módulo plpy fornece acesso ao banco e funções; O “path” dos módulos depende da variável de ambiente PYTHONPATH do usuário que inicia o serviço do PostgreSQL. 

16/52 PL/Python: Tipos de Dados PostgreSQL Python bigint, integer, smallint int Real, double float numeric Decimal boolean bool text, varchar, char str array list Tipos personalizados dict null None 

17/52 PL/Python: O “U” da Questão PL/Python é uma linguagem procedural considerada “untrusted” (não confiável): o que implica na denominação PL/PYTHONU; O que significa que não é oferecida qualquer forma de restrição para usuários fazer; Permite executar ações fora do banco de dados; Somente superusers podem criar funções em linguagens como PL/PYTHONU. “Com grandes poderes vêm grandes responsabilidades”. Tio Ben (Stan Lee) 

18/52 PL/Python vs PL/pgSQL Qual é a melhor linguagem procedural para PostgreSQL? PL/Python ou PL/pgSQL? Depende… De forma geral podemos dizer que para operações puras na base de dados é melhor utilizar PL/pgSQL. E PL/Python para operações que vão além do banco, dando um maior poder de fogo para o PostgreSQL. Cada uma tem seu valor e sua aplicabilidade dentro de um contexto :) 

19/52 PL/Python vs PL/pgSQL PL/pgSQL PL/Python Tipo Descrição TG_OP TD['event'] string Qual tipo de evento disparou o trigger. Possíveis valores: INSERT, UPDATE, DELETE, ou TRUNCATE. TG_WHEN TD['when'] Quando o gatilho (trigger) foi disparado. Possíveis valores: BEFORE, AFTER, ou INSTEAD OF. TG_LEVEL TD['level'] Qual o nível, por linha ou por comando. Possíveis valores: ROW ou STATEMENT. NEW TD['new'] record Variável que contém a nova linha para INSERT ou UPDATE em nível de linha (ROW level). É nula para DELETE ou nível STATEMENT. OLD TD['old'] Para nível em linha (ROW level), contém a linha antiga para UPDATE ou DELETE. É nula para o vível STATEMENT ou para INSERT. TG_NAME TD['name'] name Nome do gatilho. Variáveis Especiais para Triggers 

20/52 PL/Python vs PL/pgSQL Variáveis Especiais para Triggers PL/pgSQL PL/Python Tipo Descrição TG_TABLE_NAME / TG_RELNAME (depreciado) TD['table_name'] name Nome da tabela que disparou o gatilho. TG_TABLE_SCHEMA TD['table_schema'] Nome do esquema (schema) da tabela que disparou o gatilho. TG_RELID TD['relid'] oid OID da tabela que disparou o gatilho. TG_ARGV[] TD['args'] array De string Se o comando CREATE TRIGGER inclui argumentos para a função eles estão disponíveis de TD['args'][0] a TD['args'][n - 1]. Índices de array inválidos (menor do que zero ou maior do que TG_NARGS) resultam em um valor nulo. 

21/52 PL/Python vs PL/pgSQL Variáveis Especiais para Triggers PL/pgSQL PL/Python Tipo Descrição TG_NARGS len(TD['args']) inteiro A quantidade de argumentos passados à função do gatilho. Se TD['when'] for BEFORE ou INSTEAD OF e TD['level'] for ROW, você pode retornar None ou “OK” da função Python para indicar que a linha não foi modificada, “SKIP” para abortar o evento de se TD['event'] for INSERT ou UPDATE você pode retornar “MODIFY” para indicar que você modificou nova linha. Caso contrário o valor de retorno será ignorado. 

22/52 PL/Python: Instalação Nosso Laboratório SO: CentOS Linux 7.5 Python: 3.6 PostgreSQL: 10 Diretório para bibliotecas Python : /var/lib/pgsql/python PYTHONPATH: ${PYTHONPATH}:/var/lib/pgsql/python Obs.: Sinta-se à vontade para usar qualquer versão diferente. Informações meramente ilustrativas que é conforme o laboratório de testes aqui. 

23/52 PL/Python: Instalação Antes da instalação do PostgreSQL indique para a variável de ambiente o binário referente à versão de Python utilizada: $ export PYTHON=`which python3.6` É preciso ter também os pacotes com os headers em C de Python (-dev, -devel). No processo de configure utilize a opção --with-python: $ ./configure --with-python 

24/52 PL/Python: Instalação Crie o diretório Python: $ mkdir /var/lib/pgsql/python No arquivo de perfil de shell do usuário (.bashrc, .bashrc_profile, .profile) coloque a seguinte linha: export PYTHONPATH="${PYTHONPATH}:/var/lib/pgsql/python" 

25/52 PL/Python: Instalação Para testar podemos iniciar o PostgreSQL passando o valor de PYTHONPATH: $ PYTHONPATH="${PYTHONPATH}:/var/lib/pgsql/python" \ pg_ctl start Podemos incluir a variável PYTHONPATH no Unit File SystemD do serviço do PostgreSQL: # systemctl edit --full postgresql.service 

26/52 PL/Python: Instalação Na sessão Unit, diretiva Environment do Unit File: [Unit] . . . Environment=PYTHONPATH=/var/lib/pgsql/python Reinicialize o serviço do PostgreSQL: # systemctl restart postgresql.service 

27/52 PL/Python: Exemplos Criação da base de dados de exemplo: > CREATE DATABASE db_plpython; Conectar à base: > \c db_plpython Habilitar PL/Python 3 na base de dados atual: > CREATE EXTENSION plpython3u; 

28/52 PL/Python: Exemplos Uma simples função anônima: > DO $$ from sys import path plpy.notice(path) $$ LANGUAGE PLPYTHON3U; NOTICE: ['/var/lib/pgsql/python', '/usr/lib64/python36.zip', '/usr/lib64/python3.6', '/usr/lib64/python3.6/lib-dynload', '/usr/ lib64/python3.6/site-packages', '/usr/lib/python3.6/site- packages'] 

29/52 PL/Python: Exemplos Função em PL/Python 3 sem argumentos: > CREATE OR REPLACE FUNCTION fc_py() RETURNS VARCHAR AS $$ return 'Hello, World!!!' $$ LANGUAGE plpython3u; 

30/52 PL/Python: Exemplos Sobrecarga de função: > CREATE OR REPLACE FUNCTION fc_py(num1 INT, num2 INT) RETURNS INT AS $$ return num1 + num2 $$ LANGUAGE plpython3u; 

31/52 PL/Python: Exemplos Testando a primeira função criada: > SELECT fc_py(); fc_py ----------------- Hello, World!!! Testando a segunda função criada: > SELECT fc_py(2, 5); fc_py ------- 7 

32/52 PL/Python: Boas Práticas ● Mantenha seu código Python em um módulo; ● Faça todas suas funções SQL com apenas duas linhas; ● Teste o código Python fazendo mock [1]. [1] https://docs.python.org/3/library/unittest.mock.html 

33/52 PL/Python: Boas Práticas Criação do módulo teste dentro do diretório que está em PYTHONPATH: $ cat << EOF > /var/lib/pgsql/python/teste.py def py_version(): import sys return sys.version.split()[0] EOF Conexão ao banco via psql: $ psql db_plpython 

34/52 PL/Python: Boas Práticas Criação da função seguindo as boas práticas: > CREATE OR REPLACE FUNCTION fc_py_version() RETURNS VARCHAR AS $$ from teste import py_version return py_version() $$ LANGUAGE plpython3u; 

35/52 PL/Python: Boas Práticas Teste da função: > SELECT 'Minha versão de Python é: '|| fc_py_version() AS "Versão de Python"; Versão de Python --------------------------------- Minha versão de Python é: 3.6.3s 

36/52 PL/Python: Use Case Serão demonstrados 2 (dois) casos de uso • Converter XML para JSON, na base de dados de um campo do tipo xml para jsonb; • Em um sistema de cadastro de apostilas PDF extrair o sumário (TOC – Table Of Contents). 

37/52 PL/Python: Use Case Instalar os módulos que serão utilizados: # python3 -m pip install xmltodict pdfminer.six Editar o arquivo que será o módulo com funções personalizadas: $ vim /var/lib/pgsql/python/MyFunctions.py 

38/52 PL/Python: Use Case /var/lib/pgsql/python/MyFunctions.py import json import pprint import xmltodict from pdfminer.pdfdocument import PDFDocument from pdfminer.pdfparser import PDFParser Continua... 

39/52 PL/Python: Use Case /var/lib/pgsql/python/MyFunctions.py def conv_xml_2_json(doc): 'Converte uma string XML para JSON' doc = xmltodict.parse(doc) return json.dumps(doc, ensure_ascii=False) Continua... 

40/52 PL/Python: Use Case /var/lib/pgsql/python/MyFunctions.py def pdf_extract_toc(f): 'Extrai o sumário de um PDF' f = open(f, 'rb') parser = PDFParser(f) document = PDFDocument(parser) outlines = document.get_outlines() lista_tmp = [] for (level, title, dest, a, se) in outlines: lista_tmp.append(title) return '\n'.join(lista_tmp) 

41/52 PL/Python: Use Case Converter XML para JSON No banco criar a função para converter de XML para JSON: > CREATE OR REPLACE FUNCTION fc_py_xml2jsonb(doc TEXT) RETURNS jsonb AS $$ from MyFunctions import conv_xml_2_json return conv_xml_2_json(doc) $$ LANGUAGE PLPYTHON3U; 

42/52 PL/Python: Use Case Criação da tabela: > CREATE TABLE tb_xml( id serial PRIMARY KEY, campo_xml xml); 

43/52 PL/Python: Use Case Inserir o registro: > INSERT INTO tb_xml (campo_xml) VALUES ( ' Pão simples Farinha Fermento Água Sal Misture todos os ingredientes, e dissolva bem. Cubra com um pano e deixe por uma hora em um local morno. Misture novamente, coloque numa bandeja e asse num forno. '); 

44/52 PL/Python: Use Case Converter para JSON: > SELECT jsonb_pretty(fc_py_xml2jsonb(campo_xml::text)) FROM tb_xml; jsonb_pretty ------------------------------------------------------------------------------ { + "receita": { + "@nome": "pão", + "titulo": "Pão simples", + "instrucoes": { + "passo": [ + "Misture todos os ingredientes, e dissolva bem.", + "Cubra com um pano e deixe por uma hora em um local morno.",+ "Misture novamente, coloque numa bandeja e asse num forno." + ] + }, + . . . 

45/52 PL/Python: Use Case Extrair o Sumário de um PDF Criação da função: > CREATE OR REPLACE FUNCTION fc_py_pdf_extract_toc (arquivo TEXT) RETURNS TEXT AS $$ from MyFunctions import pdf_extract_toc return pdf_extract_toc(arquivo) $$ LANGUAGE PLPYTHON3U; 

46/52 PL/Python: Use Case Criação da tabela: > CREATE TABLE tb_apostila( id serial PRIMARY KEY, titulo VARCHAR(200), autor VARCHAR(70), toc text); 

47/52 PL/Python: Use Case Inserir um registro: > INSERT INTO tb_apostila (titulo, autor, toc) VALUES ( 'PostgreSQL - SQL Básico', 'Juliano Atanazio', fc_py_pdf_extract_toc('/tmp/postgresql_sql_basico.pdf')); 

48/52 PL/Python: Use Case Verificar o que foi inserido: > SELECT id, titulo, autor, toc FROM tb_apostila; id | titulo | autor | toc ----+-------------------------+------------------+------------------------------------------------------------- 1 | PostgreSQL - SQL Básico | Juliano Atanazio | 1 SQL + | | | 1.1 O que é SQL + | | | 1.2 Subdivisões SQL + | | | 1.2.1 DDL + | | | 1.2.2 DML + | | | 1.2.3 DCL + | | | 1.3 Identificadores + | | | 1.4 Operadores + | | | 1.5 Comentários SQL + | | | 2 Tipos de Dados + | | | 2.1 Sobre Tipos de Dados + . . . 

49/52 Conclusão Utilize PL/Python para coisas que PL/pgSQL não atende. PL/Python por ser a implementação de Python como linguagem procedural no PostgreSQL permite que se use todo seu poder das “baterias inclusas” além de pacotes de bibliotecas extras instaladas via pip, por exemplo. 

50/52 Donate! The elephant needs you! Contribute! :) http://www.postgresql.org/about/donate/ 

51/52 Save our planet! 

52/52 See you soon!!! Juliano Atanazio [email protected] http://slideshare.net/spjuliano https://speakerdeck.com/julianometalsp https://juliano777.wordpress.com :)