Visualizando query SQL a partir do ORM Django
Falei sobre esse assunto na palestra que fiz na TDC 2017 (The Developer’s Conference) e vou mostrar como visualizar query SQL do ORM do Django.
Um dos motivos para falar sobre esse assunto foi a dificuldade que tive para entender como funcionava a ORM do Django. Em uma das minhas primeiras experiências com Django precisei gerar um relatório e como já tinha trabalhado com SQL resolvi fazer com SQL mesmo e posteriormente e passaria a query pra ORM pra entender. Uma maneira simples para o meu entendimento foi associar o que eu sabia de SQL para entender o ORM.
ORM o que?
ORM significa Object Relational Mapper e é uma biblioteca que automatiza a transferência de dados do banco de dados relacional entre objetos do model. O legal de se usar é que pode acelerar a velocidade do desenvolvimento permitindo ler, escrever, atualizar e deletar dados do banco, além de ser compatível com diferentes banco de dados (MySQL, SQLite, PostgreSQL…) e ser nativa no Django.
Model vs Tabela
Criei um exemplo (e você pode conferir nesse repositório aqui e fazer na sua máquina para entender também) para mostrar como fica no model e na tabela do banco de dados (estou usando mysql).
Models:
Enquanto no banco de dados temos:
Como Funciona?
Tenho uma determinada ORM, vou utilizar essa abaixo como exemplo:
Produto.objects.all()Vamos entender o fluxo para obtermos o script SQL:
Model
É a representação dos dados, contém os campos e os métodos. No nosso exemplo, o nosso model é Produto.
Manager
O manager está acoplado ao Model, para acessar qualquer objeto salvo no banco é preciso acessar o Manager, isto é o objects presente em todo Model.
QuerySet
É o conjunto de ações quer serão realizadas no banco (select, insert, update, delete). Representado pelo all() no nosso exemplo.
Query
Cria uma estrutura de dados complexa com todos os elementos presentes em uma consulta. É gerado uma representação SQL de um QuerySet, ou seja, é através desse método que conseguimos ver as conversões do ORM para SQL (MySQL, PostgreSQL, SQLite).
SQLCompiler
Recebe o SQL da Query e executa de acordo com as regras específicas do backend escolhido.
Métodos
Vou abordar alguns métodos da ORM, você pode visualizar todos os métodos aqui.
.all()
O método .all() retornará todos os produtos.
In [1]:from core.produtos.models import ProdutoIn [2]:Produto.objects.all()Out [2]:<QuerySet [<Produto: Produto 1 da Categoria 1>, <Produto: Produto 2 da Categoria 1>, <Produto: Produto 3 da Categoria 1>, <Produto: Produto 4 da Categoria 1>, <Produto: Produto 5 da Categoria 1>, <Produto: Produto 1 da Categoria 2>, <Produto: Produto 2 da Categoria 2>, <Produto: Produto 3 da Categoria 2>, <Produto: Produto 4 da Categoria 2>, <Produto: Produto 5 da Categoria 2>, <Produto: Produto 1 da Categoria 3>, <Produto: Produto 2 da Categoria 3>, <Produto: Produto 3 da Categoria 3>, <Produto: Produto 4 da Categoria 3>, <Produto: Produto 5 da Categoria 3>, <Produto: Produto 1 da Categoria 4>, <Produto: Produto 2 da Categoria 4>, <Produto: Produto 3 da Categoria 4>, <Produto: Produto 4 da Categoria 4>, <Produto: Produto 5 da Categoria 4>, ‘…(remaining elements truncated)…’]>
e para visualizarmos o script SQL:
In [3]: orm = Produto.objects.all()In [4]: print(orm.query)
SELECT `produtos_produto`.`id`, `produtos_produto`.`nome`, `produtos_produto`.`valor`, `produtos_produto`.`categoria_id` FROM `produtos_produto`
.filter()
O método .filter() retornará todos os produtos em uma determinada condição.
In [1]: from core.produtos.models import ProdutoIn [2]: Produto.objects.filter(id=1)
Out[2]: <QuerySet [<Produto: Produto 1 da Categoria 1>]>
e para visualizarmos o script SQL:
In [3]: orm = Produto.objects.filter(id=1)In [4]: print(orm.query)
SELECT `produtos_produto`.`id`, `produtos_produto`.`nome`, `produtos_produto`.`valor`, `produtos_produto`.`categoria_id` FROM `produtos_produto` WHERE `produtos_produto`.`id` = 1
.filter(campo__lookup)
Podemos passar lookups quando utilizamos o .filter(), você pode conferir todos os lookups aqui:
In [1]: from core.produtos.models import ProdutoIn [2]: Produto.objects.filter(id__in=[1,3,5])
Out[2]: <QuerySet [<Produto: Produto 1 da Categoria 1>, <Produto: Produto 3 da Categoria 1>, <Produto: Produto 5 da Categoria 1>]>
e para visualizarmos o script:
In [3]: orm = Produto.objects.filter(id__in=[1, 3 ,5])In [4]: print(orm.query)
SELECT `produtos_produto`.`id`, `produtos_produto`.`nome`, `produtos_produto`.`valor`, `produtos_produto`.`categoria_id` FROM `produtos_produto` WHERE `produtos_produto`.`id` IN (1, 3, 5)
.filter(fk__campo)
Conseguimos acessar o campo de uma chave estrangeira utilizando ORM:
In [1]: from core.produtos.models import ProdutoIn [2]: Produto.objects.filter(categoria__nome=’categoria_1')
Out[2]: <QuerySet [<Produto: Produto 1 da Categoria 1>, <Produto: Produto 2 da Categoria 1>, <Produto: Produto 3 da Categoria 1>, <Produto: Produto 4 da Categoria 1>, <Produto: Produto 5 da Categoria 1>]>
visualizando em SQL:
In [3]: orm = Produto.objects.filter(categoria__nome=’categoria_1')In [4]: print(orm.query)
SELECT `produtos_produto`.`id`, `produtos_produto`.`nome`, `produtos_produto`.`valor`, `produtos_produto`.`categoria_id` FROM `produtos_produto` INNER JOIN `categorias_categoria` ON (`produtos_produto`.`categoria_id` = `categorias_categoria`.`id`) WHERE `categorias_categoria`.`nome` = categoria_1
o interessante é que não precisamos nos preocupar com os joins do SQL quando utilizamos ORM!
.get()
O método .get() é semelhante ao .filter(), vamos entender as diferenças posteriormente.
In [1]: from core.produtos.models import ProdutoIn [2]: Produto.objects.get(id=1)
Out[2]: <Produto: Produto 1 da Categoria 1>
visualizando em SQL:
In [3]: orm = Produto.objects.get(id=1)In [4]: print(orm.query)
— — — — — —— — — — — — — — — — — — — — — — — — — — — — — — — —
AttributeError Traceback (most recent call last)
<ipython-input-4–9d5bf753c2b6> in <module>()
— → 1 print(script.query)AttributeError: ‘Produto’ object has no attribute ‘query’
EITA! Caímos em um erro. Isso ocorreu porque diferente do .filter(), o .get() não retorna um queryset e sim o objeto do model.
In [5]: script = Produto.objects.filter(id=1)In [6]: type(script)
Out[6]: django.db.models.query.QuerySetIn [7]: script = Produto.objects.get(id=1)In [8]: type(script)
Out[8]: core.produtos.models.Produto
Mas ainda sim conseguimos visualizar o SQL dessa ORM:
In [5]: from django.db import connectionIn [6]: connection.queries
Out[6]: [{'sql': 'SELECT `produto_produto`.`id`, `produto_produto`.`nome`, `produto_produto`.`valor`, `produto_produto`.`categoria_id` FROM `produto_produto` WHERE `produto_produto`.`id` = 1', 'time': '0.000'}]
.exclude()
Esse método trará os objetos que não correspondem aos parâmetros fornecidos:
In [1]: from core.produtos.models import ProdutoIn [2]: Produto.objects.exclude(id__gte=4)
Out[2]: <QuerySet [<Produto: Produto 1 da Categoria 1>, <Produto: Produto 2 da Categoria 1>, <Produto: Produto 3 da Categoria 1>]>
visualizando em SQL:
In [3]: orm = Produto.objects.exclude(id__gte=4)In [4]: print(orm.query)
SELECT `produtos_produto`.`id`, `produtos_produto`.`nome`, `produtos_produto`.`valor`, `produtos_produto`.`categoria_id` FROM `produtos_produto` WHERE NOT (`produtos_produto`.`id` >= 4)
.values()
Com o .values() podemos passar os campos que sejam retornados no queryset, além disso a queryset retornará dicionários.
In [1]: from core.produtos.models import ProdutoIn [2]: Produto.objects.values(‘nome’, ‘valor’)
Out[2]: <QuerySet [{‘valor’: Decimal(‘2.00’), ‘nome’: ‘Produto 1 da Categoria 1’}, {‘valor’: Decimal(‘5.00’), ‘nome’: ‘Produto 2 da Categoria 1’}, {‘valor’: Decimal(‘20.00’), ‘nome’: ‘Produto 3 da Categoria 1’}, {‘valor’: Decimal(‘15.00’), ‘nome’: ‘Produto 4 da Categoria 1’}, {‘valor’: Decimal(‘10.00’), ‘nome’: ‘Produto 5 da Categoria 1’}, {‘valor’: Decimal(‘5.00’), ‘nome’: ‘Produto 1 da Categoria 2’}, {‘valor’: Decimal(‘7.00’), ‘nome’: ‘Produto 2 da Categoria 2’}, {‘valor’: Decimal(‘20.00’), ‘nome’: ‘Produto 3 da Categoria 2’}, ‘…(remaining elements truncated)…’]>
visualizando em SQL:
In [3]: orm = Produto.objects.values(‘nome’, ‘valor’)In [4]: print(orm.query)
SELECT `produtos_produto`.`nome`, `produtos_produto`.`valor` FROM `produtos_produto`
.values_list()
Similar ao .values(), a diferença é que a queryset retorna tupla.
In [1]: from core.produtos.models import ProdutoIn [2]: Produto.objects.values_list(‘nome’)
Out[2]: <QuerySet [(‘Produto 1 da Categoria 1’,), (‘Produto 2 da Categoria 1’,), (‘Produto 3 da Categoria 1’,), (‘Produto 4 da Categoria 1’,), (‘Produto 5 da Categoria 1’,), (‘Produto 1 da Categoria 2’,), (‘Produto 2 da Categoria 2’,), (‘Produto 3 da Categoria 2’,), (‘Produto 4 da Categoria 2’,),’…(remaining elements truncated)…’]>
Visualizando em SQL:
In [3]: orm = Produto.objects.values_list(‘nome’)In [4]: print(orm.query)
SELECT `produtos_produto`.`nome` FROM `produtos_produto`
.values_list(field, flat=True)
Quando utilizamos o flat=True como paramêtro do .values_list() o resultado são retornado em valores únicos ao em vez de tuplas.
In [1]: from core.produtos.models import ProdutoIn [2]: Produto.objects.values_list(‘nome’, flat=True)
Out[2]:<QuerySet [‘Produto 1 da Categoria 1’, ‘Produto 2 da Categoria 1’, ‘Produto 3 da Categoria 1’, ‘Produto 4 da Categoria 1’, ‘Produto 5 da Categoria 1’, ‘Produto 1 da Categoria 2’, ‘Produto 2 da Categoria 2’, ‘Produto 3 da Categoria 2’, ‘Produto 4 da Categoria 2’, ‘Produto 5 da Categoria 2’, ‘Produto 1 da Categoria 3’, ‘Produto 2 da Categoria 3’, ‘…(remaining elements truncated)…’]>
Visualizando em SQL:
In [3]: orm = Produto.objects.values_list(‘nome’, flat=True)In [4]: print(orm.query)
SELECT `produtos_produto`.`nome` FROM `produtos_produto`
.annotate()
Retorna um queryset agrupado levando em consideração cada objeto.
In [1]: from core.produtos.models import ProdutoIn [2]: from django.db.models import SumIn [3]: Produto.objects.values(‘categoria’).annotate(Sum(‘valor’))
Out[3]: <QuerySet [{‘valor__sum’: Decimal(‘52.00’), ‘categoria’: 1}, {‘valor__sum’: Decimal(‘66.00’), ‘categoria’: 2}, {‘valor__sum’: Decimal(‘147.60’), ‘categoria’: 3}, {‘valor__sum’: Decimal(‘209.30’), ‘categoria’: 4}, {‘valor__sum’: Decimal(‘87.20’), ‘categoria’: 5}]>
No exemplo acima foi agrupado a soma de todas as categorias dos produtos.
Visualizando em SQL:
In [4]: orm = Produto.objects.values(‘categoria’).annotate(Sum(‘valor’))In [5]: print(orm.query)
SELECT `produtos_produto`.`categoria_id`, SUM(`produtos_produto`.`valor`) AS `valor__sum` FROM `produtos_produto` GROUP BY `produtos_produto`.`categoria_id` ORDER BY NULL
.aggregate()
Semelhante ao .annotate() com a diferença de retornar um dicionário com valores agregados calculado sobre todo o queryset
In [1]: from core.produtos.models import ProdutoIn [2]: from django.db.models import Avg, Max, MinIn [3]: Produto.objects.aggregate(
...: valor_maximo=Max('valor'),
...: valor_minimo=Min('valor'),
...: valor_medio=Avg('valor')
...: )
Out[3]:
{'valor_maximo': Decimal('100.00'),
'valor_medio': 22.484,
'valor_minimo': Decimal('2.00')}
Visualizando SQL:
In [4]: orm = Produto.objects.aggregate(
…: valor_maximo=Max(‘valor’),
…: valor_minimo=Min(‘valor’),
…: valor_medio=Avg(‘valor’)
…: )In [5]: print(orm.query)
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -
AttributeError Traceback (most recent call last)
<ipython-input-5–02ba74239328> in <module>()
— → 1 print(orm.query)AttributeError: ‘dict’ object has no attribute ‘query’
Caímos no erro novamente, o .aggregate() não retona um queryset.
In [4]: orm = Produto.objects.values(‘categoria’).annotate(Sum(‘valor’))In [5]: type(orm)
Out[5]: django.db.models.query.QuerySetIn [6]: orm = Produto.objects.aggregate(
…: valor_maximo=Max(‘valor’),
…: valor_minimo=Min(‘valor’),
…: valor_medio=Avg(‘valor’)
…: )In [7]: type(orm)
Out[7]: dict
Assim como .get() também conseguimos visualizar a query SQL:
In [4]: from django.db import connectionIn [5]: connection.queries
Out[5]:
[{‘sql’: ‘SELECT MAX(`produtos_produto`.`valor`) AS `valor_maximo`, AVG(`produtos_produto`.`valor`) AS `valor_medio`, MIN(`produtos_produto`.`valor`) AS `valor_minimo` FROM `produtos_produto`’,
‘time’: ‘0.000’}]
QuerySet são Lazy
Todos os métodos que retornam um QuerySet são considerados lazy.
Se eu tiver essas consultas abaixo, quantas consultas foram realizadas no banco de dados?
In [1]: from core.produtos.models import ProdutoIn [2]: produtos = Produto.objects.all()In [3]: categoria_2 = produtos.filter(categoria=2)In [4]: lista_produto = produtos.values_list(‘nome’, flat=True)In [5]: print(lista_produto)
APENAS UMA!
In [1]: from core.produtos.models import ProdutoIn [2]: from django.db import connectionIn [3]: produtos = Produto.objects.all()In [4]: categoria_2 = produtos.filter(categoria=2)In [5]: lista_produto = produtos.values_list(‘nome’, flat=True)In [6]: print(len(connection.queries))
0In [7]: print(lista_produto)
<QuerySet [‘Produto 1 da Categoria 1’, ‘Produto 2 da Categoria 1’, ‘Produto 3 da Categoria 1’, ‘Produto 4 da Categoria 1’, ‘Produto 5 da Categoria 1’, ‘Produto 1 da Categoria 2’, ‘Produto 2 da Categoria 2’, ‘Produto 3 da Categoria 2’, ‘Produto 4 da Categoria 2’, ‘Produto 5 da Categoria 2’, ‘Produto 1 da Categoria 3’, ‘Produto 2 da Categoria 3’, ‘Produto 3 da Categoria 3’, ‘Produto 4 da Categoria 3’, ‘Produto 5 da Categoria 3’, ‘Produto 1 da Categoria 4’, ‘Produto 2 da Categoria 4’, ‘Produto 3 da Categoria 4’, ‘Produto 4 da Categoria 4’, ‘Produto 5 da Categoria 4’, ‘…(remaining elements truncated)…’]>In [8]: print(len(connection.queries))
1
Isso significa que as consulta são realizadas no banco de dados quando pedimos. Mas quando pedimos?
Podemos pedir para serem executadas nas seguintes formas:
- Quando solicitamos somente um resultado:
>> Produto.objects.all()[0]- Quando fazemos um slicinf passando um ‘step’:
>> Produtos.objects.all()[::2]- Quando iteramos:
>> categoria for categoria in Categoria.objects.all()- Quando chamamos o método
len():
>> len(Produto.objects.all())- Quando chamamos um
list():
>> list(Produto.objects.all())- Quando chamamos um
bool():
>> bool(Produto.objects.all())- Quando chamamos o
repr():
>> repr(Produto.objects.all())DICAS
reset_queries
Se precisar limpar a lista de query do connection, utilize:
from django.db import reset_queries>> reset_queries()
Django logging
Caso queira que a query sql apareça toda vez que seja executada uma consulta orm, adicione ao arquivo settings.py
Espero que tenha ajudado a entender um pouco sobre ORM Django :)
