Postgresql + Python = Power!

O tema aborda a intereção entre Python e PostgreSQL.
É uma introdução de Python para profissionais PostgreSQL, além de diversos exemplos de códigos Python interagindo com o banco e também a linguagem procedural PL/Python.
Há demonstrações de vários pontos em comum e equivalências entre ambos.
Apresentação (palestra) feita no PGBR 2015 (19/11), em Porto Alegre, na UniRitter.

Transcript

2. 2/55 Sobre Python • Linguagem de programação de altíssimo nível; • Interpretada e compilada (bytecodes: .pyc ou .pyo); • Totalmente orientada a objetos; • Suporta herança múltipla; • Tipagem dinâmica e forte; • Objetiva; • Fácil de aprender; • Muito Produtiva (faça muito escrevendo pouco);
3. 3/55 Sobre Python • Comunidade forte e solícita; • O nome da linguagem foi inspirado no grupo humorístico britânico Monty Python's Flying Circus; • Criada pelo holandês Guido van Rossum em 1991 no Instituto de Pesquisa Nacional para Matemática e Ciência da Computação de Amsterdam.
4. 4/55 Ambientes Python • Web: Django, Web2Py, Flask, Plone, Zope, ...; • GUI: PyGTK, PyQT...; • Embarcados: MicroPython, Raspberry Pi, Arduino...; • Scripts; • Etc...
5. 5/55 Python é Multiplataforma • Linux; • BSDs; • *nix; • MacOS; • Windows
6. 6/55 Filosofia: The Zen of Python :) > import this The Zen of Python, by Tim Peters Beautiful is better than ugly. Explicit is better than implicit. Simple is better than complex. Complex is better than complicated. Flat is better than nested. Sparse is better than dense. Readability counts. Special cases aren't special enough to break the rules. Although practicality beats purity. Errors should never pass silently. Unless explicitly silenced. In the face of ambiguity, refuse the temptation to guess. There should be one-- and preferably only one --obvious way to do it. Although that way may not be obvious at first unless you're Dutch. Now is better than never. Although never is often better than *right* now. If the implementation is hard to explain, it's a bad idea. If the implementation is easy to explain, it may be a good idea. Namespaces are one honking great idea -- let's do more of those!
7. 7/55 Quem Utiliza Python?
8. 8/55 Python: Strings, Comentários e Doc Strings • Uma string pode ser delimitada por apóstrofos ('string') ou aspas ("string"); • '''string''' ou """string""": Um tipo de comentário que gera documentação ou também pode ser utilizado como string ou comentário de múltiplas linhas; • # : O caractere sustenido é utilizado para fazer comentários de uma única linha.
9. 9/55 Python: Strings, Comentários e Doc Strings > frase = '''Uma frase qualquer que usa mais de uma linha''' > print(frase) Uma frase qualquer que usa mais de uma linha > # Uma simples soma > print(2 + 5) 7
10. 10/55 Python: Orientação a Objeto > class Pessoa(object): ''' Classe mãe Pessoa que é base para gerar outros tipos de pessoa por herança. ''' # Atributos nome = '' # string idade = 0 # int telefones = [] # list # Métodos def meu_nome(self): print(self.nome) def minha_idade(self): print(self.idade) def meus_telefones(self): for i in self.telefones: print(i) Diferente de outras linguagens, blocos não são delimitados por chaves, mas sim por endentação, cuja recomendação segundo a PEP8* [1] é de 4 (quatro) espaços ← [1] https://www.python.org/dev/peps/pep-0008/ * Python Enhancement Proposal, a PEP 8 é um Guia de Estilo de Codificação Python para fins de padronização e boas práticas.
11. 11/55 Python: Orientação a Objeto > # Criação de classe filha > class Funcionario(Pessoa): ''' Classe para funcionários herdada da classe Pessoa ''' matricula = '' > # Instanciando a classe Funcionario > f1 = Funcionario() > # Dando valores a atributos > f1.nome = 'Chiquinho da Silva' > f1.matricula = '2014xyz113' > f1.telefones = ['11-91111-1111', '11-5111-1111', '11-3111-1111']
12. 12/55 Python: Orientação a Objeto > # Executando um método > f1.meu_nome() Chiquinho da Silva > # Exibindo em tela o valor de um atributo > print(f1.matricula) 2014xyz113 > # Executando o método para exibir telefones > f1.meus_telefones() 11-91111-1111 11-5111-1111 11-3111-1111
13. 13/55 Python: Orientação a Objeto > # A função help exibe uma ajuda sobre um objeto > help(f1) Help on Funcionario in module __main__ object: class Funcionario(Pessoa) | Classe para funcionários herdada | da classe Pessoa | | Method resolution order: | Funcionario | Pessoa | __builtin__.object | | Data and other attributes defined here: | | matricula = '' | | ---------------------------------------------------------------------- Continua →
14. 14/55 Python: Orientação a Objeto | Methods inherited from Pessoa: | | meu_nome(self) | | meus_telefones(self) | | minha_idade(self) | | ---------------------------------------------------------------------- | Data descriptors inherited from Pessoa: | | __dict__ | dictionary for instance variables (if defined) | | __weakref__ | list of weak references to the object (if defined) | | ---------------------------------------------------------------------- Continua →
15. 15/55 Python: Orientação a Objeto | Data and other attributes inherited from Pessoa: | | idade = 0 | | nome = '' | | telefones = []
16. 16/55 Shell interativo • python (vem por padrão na maioria das distros Linux); • ipython; • DreamPie; • BPython; • web [1]. [1] https://www.python.org/shell/
17. 17/55 Python + PostgreSQL = Produtividade e Poder!
18. 18/55 Equivalências... PostgreSQL Python Licença PostgreSQL (derivada da BSD) PSF License (derivada da BSD) Site global www.postgresql.org www.python.org Site nacional www.postgresql.org.br www.python.org.br Lista de discussão (Br) https://listas.postgresql.org.br/ http://www.python.org.br/wiki/ListasDeDiscussao Evento Internacional PgCon https://www.pgcon.org/ PyCon http://www.pycon.org/ Evento Nacional PgBr http://pgbr.postgresql.org.br Python Brasil ou PyBr http://www.pythonbrasil.org.br/ Evento Regional PgDay GruPy (UF) / Python Day PyPgDay (Python + PostgreSQL) Último realizado: (https://us.pycon.org/2013/events/pgday/)
19. 19/55 Tipos de Dados (PostgreSQL → Python) PostgreSQL Python integer, smallint int bigint long real, double float numeric Decimal boolean bool text, varchar, char str array list tipos personalizados dict null None
20. 20/55 Laboratório de Teste Criação de uma base de exemplo: > CREATE DATABASE db_pypg; Conectar à nova base: > \c db_pypg Criação de tabela de teste: > CREATE TABLE tb_foo( id serial PRIMARY KEY, campo text); Popular a tabela: > INSERT INTO tb_foo (campo) VALUES ('foo'), ('bar'), ('baz'), ('spam'), ('eggs');
21. 21/55 Driver PostgreSQL para Python: psycopg2 Site oficial: http://initd.org/psycopg/ ___ conecta_pg.py _________________________________________________________ #_*_ encoding: utf8 _*_ # importação do módulo import psycopg2 # Definição da string de conexão conn_string = """ host='localhost' dbname='db_pypg' user='postgres' password='secret' port='5432' """ ________________________________________________________________________ Continua →
22. 22/55 Driver PostgreSQL para Python: psycopg2 ________________________________________________________________________ # ========= Tratamento de Erros =================================== try: # Faz uma conexão, se não puder ser feita haverá uma exceção conn = psycopg2.connect(conn_string) # conn.cursor retornará um objeto do tipo cursor # que é utilizado para fazer consultas cursor = conn.cursor() print('Conectado!\n') except psycopg2.Error as e: print('\nFalha de conexão!\n%s' % (e)) #================================================================== ________________________________________________________________________ Continua →
23. 23/55 Driver PostgreSQL para Python: psycopg2 ________________________________________________________________________ # Variável de consulta sql = "SELECT id, campo FROM tb_foo WHERE campo != 'foo';" # Execução de uma consulta cursor.execute(sql) # Tuplas buscadas dentro de uma lista records = cursor.fetchall() # Loop para buscar os valores na lista records for _id, _campo in records: print('%s -> %s' % (_id, _campo)) # Fechando a conexão conn.close() #================================================================= ________________________________________________________________________
24. 24/55 Driver PostgreSQL para Python: psycopg2 No shell do sistema operacional executa o arquivo: $ python conecta_pg.py 2 -> bar 3 -> baz 4 -> spam 5 -> eggs
25. 25/55 SQLAlchemy SQLAlchemy é um dos mais conhecidos ORMs (Object Relational Mapper - Mapeador Objeto-Relacional) para Python. Site oficial do projeto: http://www.sqlalchemy.org ___ sqlalchemy_pg.py _________________________________________________________ #_*_ encoding: utf-8 _*_ from sqlalchemy import * # URL => driver://username:password@host:port/database engine = create_engine('postgresql://postgres@localhost:5432/db_pypg') # Torna acessíveis os metadados metadata = MetaData(engine) ________________________________________________________________________ Continua →
26. 26/55 SQLAlchemy ____________________________________________________________________________ # Exibe na tela o que é feito no banco metadata.bind.echo = True # Definição de estrutura de tabela tabela_carro = Table('tb_carro', metadata, Column('id', Integer, primary_key = True), Column('nome', Text), Column('ano', SmallInteger)) # Criação da tabela tabela_carro.create() # Criação de um objeto para inserir dados ins = tabela_carro.insert() ________________________________________________________________________ Continua →
27. 27/55 SQLAlchemy ____________________________________________________________________________ # Executa o INSERT conforme o dicionário passado como parâmetro ins.execute( {'nome': 'Fiat 147', 'ano': 1980}, {'nome': 'VW Kombi', 'ano': 1977}, {'nome': 'Ford Corcel', 'ano': 1985}) # Criação de um objeto para consulta sel = tabela_carro.select() # Consulta feita e resultado armazenado res = sel.execute() ________________________________________________________________________ Continua →
28. 28/55 SQLAlchemy ____________________________________________________________________________ # Loop para buscar os resultados for i in res.fetchall(): print('Carro: %s - Ano: %s' % (i['nome'], i['ano'])) # Fecha a conexão com o banco engine.dispose() ________________________________________________________________________ No shell do sistema operacional executar: $ python sqlalchemy_pg.py Carro: Fiat 147 - Ano: 1980 Carro: VW Kombi - Ano: 1977 Carro: Ford Corcel - Ano: 1985
29. 29/55 PL/Python • Pode utilizar todas as bibliotecas Python; • Faz tudo o que Python pode fazer; • Python é bem mais amigável do que PL/pgSQL; • É uma linguagem procedural que possibilita escrever funções em Python no PostgreSQL. Site oficial: http://www.postgresql.org/docs/current/static/plpython.html
30. 30/55 PL/Python: PL/PythonU O “U” da Questão... “U” de “untrusted” (não confiável), ou seja, uma linguagem não confiável… 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. No entanto, somente superusers podem criar funções em linguagens como plpythonu.
31. 31/55 PL/Python: Versões de Python Suporta ambas as variantes de versões de Python (Python 2 e Python 3); Python 2 → PLPYTHON2U; Python 3 → PLPYTHON3U; Pouca diferença entre ambas as versões.
32. 32/55 PL/Python: Preparação • A instalação de PL/Python pode ser feita via pacote, de acordo com sua distro ou sistema operacional ou via código-fonte; • Pode-se optar por instalar entre as versões 2 e 3 de Python ou mesmo ambas; • Evite instalar versões diferentes de PL/Python em uma base, pois não poderão ser usadas na mesma sessão. No shell acessar a base via psql: $ psql db_pypg
33. 33/55 PL/Python: Preparação Verificando se há linguagens procedurais Python disponíveis: > SELECT tmplname AS nome, tmpllibrary AS biblioteca FROM pg_pltemplate WHERE tmplname ~ 'python'; nome | biblioteca ------------+------------------- plpythonu | $libdir/plpython2 plpython2u | $libdir/plpython2 plpython3u | $libdir/plpython3 Foram constatadas 3 (três) ocorrências, sendo que plpythonu e plpython2u são para Python 2 e plpython3u para Python 3.
34. 34/55 PL/Python: Preparação Quais são as linguagens procedurais instaladas na base atual: > SELECT lanname FROM pg_language; lanname ---------- internal c sql Plpgsql Para os testes utilizaremos a versão 2 de Python: > CREATE LANGUAGE plpython2u;
35. 35/55 PL/Python: Preparação Simples teste: > DO LANGUAGE plpython2u $$ import sys plpy.info('\n\nVersão -> %s\n\n' % sys.version) $$; INFO: Versão -> 2.7.9 (default, Mar 1 2015, 13:01:26) [GCC 4.9.2]
36. 36/55 PL/Python vs PL/pgSQL: Blocos Anônimos PL/pgSQL: > DO LANGUAGE plpgsql $marcador$ DECLARE var1 INT2 := 21; var2 INT2 := 3; BEGIN RAISE NOTICE 'O número é %', (var1 / var2); END; $marcador$;
37. 37/55 PL/Python vs PL/pgSQL: Blocos Anônimos PL/Python: > DO LANGUAGE plpython2u $marcador$ var1 = 21 var2 = 3 plpy.notice('O número é %i' % (var1 / var2)) $marcador$;
38. 38/55 PL/Python vs PL/pgSQL: Tratamento de Exceção PL/pgSQL: > DO $marcador$ DECLARE var1 INT2 := 21; var2 INT2 := 0; BEGIN RAISE NOTICE 'O número é %', (var1/var2); EXCEPTION WHEN division_by_zero THEN RAISE EXCEPTION 'Não dividirás por zero!!!'; END; $marcador$ LANGUAGE plpgsql;
39. 39/55 PL/Python vs PL/pgSQL: Tratamento de Exceção PL/Python: > DO $marcador$ var1 = 21 var2 = 0 try: plpy.notice('O número é %i' % (var1/var2)) except ZeroDivisionError: plpy.info('Não dividirás por zero!!!') $marcador$ LANGUAGE plpython2u;
40. 40/55 PL/Python vs PL/pgSQL: Criação de Função PL/pgSQL: > CREATE OR REPLACE FUNCTION fc_hello_world() RETURNS TEXT AS $longliverockinroll$ BEGIN RETURN 'Hello, World!'; END; $longliverockinroll$ LANGUAGE plpgsql;
41. 41/55 PL/Python vs PL/pgSQL: Criação de Função PL/Python: > CREATE OR REPLACE FUNCTION fc_py_hello_world() RETURNS TEXT AS $longliverockinroll$ return 'Hello, World!' $longliverockinroll$ LANGUAGE plpython2u;
42. 42/55 PL/Python vs PL/pgSQL: Função com Parâmetro PL/pgSQL: > CREATE OR REPLACE FUNCTION fc_teste(preco NUMERIC, qtd SMALLINT) RETURNS NUMERIC(7, 2) AS $heavymetalneverdie$ DECLARE total NUMERIC (7, 2) := (preco * qtd); BEGIN IF total <= 4999.99 THEN RAISE NOTICE 'Sem taxação'; ELSIF total <= 9999.99 THEN RAISE NOTICE 'Taxação em 30%%'; total := total * 1.3; ELSE RAISE NOTICE 'Taxação em 80%%'; total := total * 1.8; END IF; RETURN round(total, 2); END; $heavymetalneverdie$ LANGUAGE plpgsql;
43. 43/55 PL/Python vs PL/pgSQL: Função com Parâmetro PL/Python: > CREATE OR REPLACE FUNCTION fc_py_teste(preco NUMERIC, qtd SMALLINT) RETURNS NUMERIC(7, 2) AS $heavymetalneverdie$ import decimal total = decimal.Decimal(preco * qtd) if total <= 4999.99: plpy.notice('Sem taxação') elif total <= 9999.99: plpy.notice('Taxação em 30%') total *= decimal.Decimal(1.3) else: plpy.notice('Taxação de 80%') total *= decimal.Decimal(1.8) return round(total, 2) $heavymetalneverdie$ LANGUAGE plpython2u;
44. 44/55 PL/Python: Triggers Para os testes a seguir consideremos a tabela de produtos (tb_produto) e para os testes temos 2 (duas) regras de negócio: 1) Não podem ser aceitos valores negativos para o campo de preço (preco); 2) Para produtos não ativos (campo “ativo”) não serão aceitas modificações a não ser que também seja mudado seu valor para ativo (true) novamente. O item 1 poderia ser feito facilmente por uma constraint CHECK, mas ambos serão via trigger.
45. 45/55 PL/Python: Triggers Criação de tabela de teste: > CREATE TABLE tb_produto( id serial PRIMARY KEY, nome varchar(50), descricao TEXT, ativo boolean DEFAULT true, preco numeric(7, 2) ); Comentário de coluna (apenas para fins informativos): > COMMENT ON COLUMN tb_produto.ativo IS 'Produto ativo ou não';
46. 46/55 PL/Python: Triggers Criação da função para o gatilho: > CREATE OR REPLACE FUNCTION fc_tg_py_ckvalues() RETURNS TRIGGER AS $sabbracadabra$ if TD['event'] == 'INSERT': from decimal import Decimal if Decimal(TD['new']['preco']) < 0: plpy.error('Não é permitido preço com valor negativo!') if TD['event'] == 'UPDATE': if not TD['old']['ativo'] and not TD['new']['ativo']: plpy.error('Produto não ativo!') $sabbracadabra$ LANGUAGE plpython2u;
47. 47/55 PL/Python: Triggers Criação do trigger (gatilho): > CREATE TRIGGER tg_ckvalues BEFORE INSERT OR UPDATE ON tb_produto FOR EACH ROW EXECUTE PROCEDURE fc_tg_py_ckvalues(); Populando a tabela e utilizando RETURNING: > INSERT INTO tb_produto (nome, ativo, preco) VALUES ('foo', 't', 10.00), ('bar', 't', 5.40), ('baz', 'f', 3.00) RETURNING id, nome; id | nome ----+------ 1 | foo 2 | bar 3 | baz
48. 48/55 PL/Python: Triggers Provocando um erro com valor inválido (preço negativo): > INSERT INTO tb_produto (nome, ativo, preco) VALUES ('spam', 't', -30); ERROR: plpy.Error: Não é permitido preço com valor negativo! . . . Provocando erro com valor inválido (produto não ativo): > UPDATE tb_produto SET preco = preco * 1.2 WHERE nome = 'baz'; ERROR: plpy.Error: Produto não ativo! . . .
49. 49/55 PL/Python: Triggers Teste para um novo INSERT, mas com valor positivo para preco: > INSERT INTO tb_produto (nome, ativo, preco) VALUES ('spam', TRUE, 30); Teste para um novo UPDATE, mas reativando o produto que estava desativado: > UPDATE tb_produto SET (preco, ativo) = (preco * 1.2, TRUE) WHERE nome = 'baz';
50. 50/55 Variáveis de Triggers: PL/pgSQL → PL/Python 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. Continua →
51. 51/55 Variáveis de Triggers: PL/pgSQL → PL/Python 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_schem a'] 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. Continua →
52. 52/55 Variáveis de Triggers: PL/pgSQL → PL/Python PL/pgSQL PL/Python Tipo Descrição TG_NARGS len(TD['args']) inteiro A quantidade de argumentos passados à função do gatilho. Continua → 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.
53. 53/55 O Elefante precisa de você! Contribua! :) http://www.postgresql.org/about/donate/ Doe!
54. 54/55 Save our planet! Save our planet!
55. 55/55 Até a próxima!!! :) Juliano Atanazio juliano777@gmail.com http://www.slideshare.net/spjuliano https://speakerdeck.com/julianometalsp https://juliano777.wordpress.com