Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Usando recursos avançados da ORM do Django para...

Usando recursos avançados da ORM do Django para consultas mais eficientes

Usando Django ORM para fazer consultas complexas em um banco de dados relacionais.

Labcodes Software Studio

October 20, 2018
Tweet

More Decks by Labcodes Software Studio

Other Decks in Programming

Transcript

  1. Olar :) Mariana Bedran Lesche Desenvolvedora na Labcodes Software Studio

    @maribedran twitter.com gmail.com github.com https://github.com/labcodes/ dados_brasil_io
  2. Motivação Explorar as vantagens e desvantagens dos recursos avançados da

    ORM do Django comparados com abordagens mais simples em termos de performance e legibilidade. Trabalhando um pouco com SQL já conseguimos escrever consultas difíceis de implementar usando Django. Como juntar esses dois conhecimentos?
  3. Quando eu devo usar isso? Já experimentou todas as estratégias

    de otimização do banco? ✔ Indexar tabelas ✔ Paginar requisições ✔ Usar select_related e prefetch_related nos querysets ✔ Usar values, values_list, only e defer nos querysets ✔ Configurar cache do banco ✔ Usar assertNumQueries nos tests para prevenir excesso de consultas
  4. Metodologia - Escolher um conjunto de dados grande - Elaborar

    perguntas complexas - Tentar respondê-las com os recursos que só conhecia da documentação e nunca tinha usado
  5. - Procrastinação - Validar os dados durante a importação -

    Minha máquina não tinha memória para fazer a importação - Carregar os dados levou mais tempo que o desenvolvimento - Juntar o conhecimento técnico com as ideias sobre os dado é mais difícil do que parece O que falhou
  6. Dados abertos do Brasil A Lei nº 12.527/2011 (Lei de

    Acesso à Informação) regulamenta o direito constitucional de acesso às informações públicas. Essa norma entrou em vigor em 16 de maio de 2012 e criou mecanismos que possibilitam, a qualquer pessoa, física ou jurídica, sem necessidade de apresentar motivo, o recebimento de informações públicas dos órgãos e entidades.
  7. Apesar dos dados estarem lá, o acesso não é simples.

    - Nem todas as agências governamentais cumprem a lei ou levam muito tempo para disponibilizar os dados - Os dados são dispersos - Muitas vezes vêm em formatos não fechados - A maioria das pessoas que têm interesse nos dados não têm o conhecimento técnico para processá-los Na prática a teoria é outra
  8. Projeto criado pelo Álvaro Justen (@turicas) para coletar, limpar e

    disponibilizar dados públicos. - Site: https://brasil.io/home - Datasets: https://brasil.io/datasets - GitHub: https://github.com/turicas/brasil.io - Colabore com o projeto: https://apoia.se/brasilio O projeto Brasil IO
  9. Sócios de empresas no Brasil - CNPJ - Chave primária

    - Nome do sócio - Categoria do sócio - Pessoa Jurídica - Pessoa Física - Estrangeiro - Categoria da sociedade - CNPJ do sócio - se for PJ
  10. Deputados no Congresso Nacional recebem uma cota para adquirir produtos

    e serviços no exercício da atividade parlamentar. Gastos de Cota Parlametar dos Deputados - ID do Deputado - Partido - Nome - Data do gasto - Mês de referência - Ano de referência - Quantia gasta - Descrição - CNPJ (se houver uma empresa) - Outros campos ...
  11. class DeputadoGastosListView(generics.ListAPIView): serializer_class = DeputadoGastosSerializer def get_queryset(self): queryset = Deputado.objects.all().select_related(

    'partido', 'uf' ).prefetch_related( 'gastos' ) # Retorna todos os gastos relacionados ao deputado GastoCotaParlamentar Deputado
  12. class DeputadoListView(generics.ListAPIView): serializer_class = DeputadoSerializer def get_queryset(self): queryset = Deputado.objects.all().select_related(

    'partido', 'uf' ) today = date.today() filtros = self.request.query_params.dict() filtros.setdefault('gastos_mes', today.month) filtros.setdefault('gastos_ano', today.year) return queryset.prefetch_gastos(**{ campo.replace('gastos_', ''): valor for campo, valor in filtros.items() if campo.startswith('gastos_') })
  13. from django.db.models import Prefetch class DeputadoQuerySet(models.QuerySet): def prefetch_gastos(self, **kwargs): gastos_qs

    = GastoCotaParlamentar.objects.select_related( 'empresa' ).filter( **kwargs # {‘ano’: 2018, ‘mes’: 1} ) # Nunca faça isso na vida real prefetch = Prefetch('gastos', queryset=gastos_qs) return self.prefetch_related(prefetch) GastoCotaParlamentar Deputado
  14. Filters • mês 3 gastos_mes=3 • ano 2018 gastos_ano=2018 •

    valor líquido maior que 100 gastos_valor_liquido__gt=100
  15. from django.db.models import Q, Sum class DeputadoQuerySet(models.QuerySet): def annotate_gasto_no_mes_por_deputado(self, mes,

    ano): annotation = { f'gastos_{ano}_{mes:02}': Sum( # ‘gastos_2018_01’ 'gastos__valor_liquido', filter=Q( gastos__mes=mes, gastos__ano=ano ) ) } return self.annotate(**annotation)
  16. SELECT "politicos_deputado"."id", "politicos_deputado"."nome", "politicos_deputado"."partido_id", "politicos_deputado"."uf_id", "politicos_deputado"."id_legislatura", "politicos_deputado"."carteira_parlamentar", SUM("politicos_gastocotaparlamentar"."valor_liquido") FILTER (

    WHERE ( "politicos_gastocotaparlamentar"."ano" = 2018 AND "politicos_gastocotaparlamentar"."mes" = 1 ) ) AS "gastos_2018_01" FROM "politicos_deputado" LEFT OUTER JOIN "politicos_gastocotaparlamentar" ON ( "politicos_deputado"."id" = "politicos_gastocotaparlamentar"."deputado_id" ) GROUP BY "politicos_deputado"."id" ORDER BY "politicos_deputado"."id" ASC LIMIT 1;
  17. from django.db.models import FilteredRelation, Q, Sum class DeputadoQuerySet(models.QuerySet): def annotate_gasto_no_mes_por_deputado2(self

    , mes, ano): return self.annotate( gastos_filtrados=FilteredRelation( 'gastos', condition=Q( gastos__mes=mes, gastos__ano=ano, ) ) ).annotate(**{ f'gastos_{ano}_{mes:02}': Sum( 'gastos_filtrados__valor_liquido' ) })
  18. SELECT "politicos_deputado"."id", "politicos_deputado"."nome", "politicos_deputado"."partido_id", "politicos_deputado"."uf_id", "politicos_deputado"."id_legislatura", "politicos_deputado"."carteira_parlamentar", SUM(gastos_filtrados."valor_liquido") AS "gastos_2018_01"

    FROM "politicos_deputado" LEFT OUTER JOIN "politicos_gastocotaparlamentar" gastos_filtrados ON ( "politicos_deputado"."id" = gastos_filtrados."deputado_id" AND ( ( gastos_filtrados."ano" = 2018 AND gastos_filtrados."mes" = 1 ) ) ) GROUP BY "politicos_deputado"."id" ORDER BY "politicos_deputado"."id" ASC LIMIT 1;
  19. from django.db.models import Avg, Sum, Q class DeputadoQuerySet(models.QuerySet): def annotate_gasto_mensal_por_deputado(self):

    meses = range(1, 13) anos = range(2009, 2019) annotations = { f'gastos_{ano}_{mes:02}': Sum( # ‘gastos_2018_01’ 'gastos__valor_liquido', filter=Q(gastos__mes=mes, gastos__ano=ano) ) for ano in anos for mes in meses } return self.annotate(**annotations)
  20. from django.db.models import Avg class DeputadoQuerySet(models.QuerySet): def get_media_mensal(self): meses =

    range(1, 13) anos = range(2009, 2019) aggregations = { f'media_{ano}_{mes:02}': Avg( # ‘media_2018_01’ f'gastos_{ano}_{mes:02}' # gastos_2018_01 ) for ano in anos for mes in meses } return self.annotate_gasto_mensal_por_deputado() \ .aggregate(**aggregations)
  21. from django.db.models import Exists, OuterRef class DeputadoQuerySet(models.QuerySet): def annotate_empresas(self): empresas_qs

    = Empresa.objects.filter( sociedades__socio_pessoa_fisica__nome=OuterRef( 'nome'), uf=OuterRef('uf') ) return self.annotate( empresas=Exists(empresas_qs) )
  22. SELECT "politicos_deputado"."id", "politicos_deputado"."nome", "politicos_deputado"."partido_id", "politicos_deputado"."uf_id", "politicos_deputado"."id_legislatura", "politicos_deputado"."carteira_parlamentar", EXISTS( SELECT U0."cnpj",

    U0."nome", U0."uf_id" FROM "empresas_empresa" U0 INNER JOIN "empresas_sociedade" U1 ON (U0."cnpj" = U1."empresa_id") INNER JOIN "empresas_pessoafisica" U2 ON (U1."socio_pessoa_fisica_id" = U2."id") WHERE ( U2."nome" = ("politicos_deputado"."nome") AND U0."uf_id" = ("politicos_deputado"."uf_id") ) ) AS "empresas" FROM "politicos_deputado" ;