PL/Python: Programando em Python no PostgreSQL

PL/Python é a implementação de Python como linguagem procedural no PostgreSQL.

Permite utilizar todas as bibliotecas de Python instaladas no sistema operacional.

PL/Python proporciona uma gama de possibilidades de recursos maior ao banco de dados graças às bibliotecas Python que permite fazer operações que transcendem os limites da base de dados.

Pode-se por exemplo com uma função PL/Python fazer raspagem de dados e jogá-los diretamente no banco (e pelo banco).

1. Juliano Atanazio PL/Python: PL/Python: Programando em Pyhon no PostgreSQL Programando em Pyhon no PostgreSQL
2. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 12/52 Blocos Anônimos Sintaxe: DO $$ . . . codigo . . . $$ LANGUAGE linguagem;
13. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 42/52 PL/Python: Use Case Criação da tabela: > CREATE TABLE tb_xml( id serial PRIMARY KEY, campo_xml xml);
43. 43/52 PL/Python: Use Case Inserir o registro: > INSERT INTO tb_xml (campo_xml) VALUES ( '<receita nome="pão" tempo_de_preparo="5 minutos" tempo_de_cozimento="1 hora"> <titulo>Pão simples</titulo> <ingredientes> <ingrediente quantidade="3" unidade="xícaras">Farinha</ingrediente> <ingrediente quantidade="7" unidade="gramas">Fermento</ingrediente> <ingrediente quantidade="1.5" unidade="xícaras" estado="morna">Água</ingrediente> <ingrediente quantidade="1" unidade="colheres de chá">Sal</ingrediente> </ingredientes> <instrucoes> <passo>Misture todos os ingredientes, e dissolva bem.</passo> <passo>Cubra com um pano e deixe por uma hora em um local morno.</passo> <passo>Misture novamente, coloque numa bandeja e asse num forno.</passo> </instrucoes> </receita>');
44. 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. 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. 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. 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. 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. 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. 50/52 Donate! The elephant needs you! Contribute! :) http://www.postgresql.org/about/donate/
51. 51/52 Save our planet!
52. 52/52 See you soon!!! Juliano Atanazio juliano777@gmail.com http://slideshare.net/spjuliano https://speakerdeck.com/julianometalsp https://juliano777.wordpress.com :)