Django Query Expressions
Hola.
Esta es la primera entrada de una serie de post en donde hablaremos sobre las query expression en Django, o mejor dicho, cómo hacer querysets mas avanzadas o complejas que se asemejan en su mayoría a SQL.
En Django puedes realizar consultas a la base de datos de tres diversas maneras: mediante el ORM, usando una RawQuerySet, o finalmente mediante la DB-API descrita en el PEP 249.
Teniendo en cuenta el siguiente modelo en Django:
class Book(models.Model):
title = models.CharField(...)
price = models.FloatField(...)
publish_date = models.DateField(...)
Podemos obtener toda la lista de libros mediante tres maneras:
ORM
>>> q = Book.objects.all()
>>> for book in q:
... print(book.title)
Two Scoops of Django
Django for Professionals
High Performance Django
RawQuerySet
>>> for book in Book.objects.raw('SELECT * FROM myapp_book'):
... print(book)
Two Scoops of Django
Django for Professionals
High Performance Django
DB-API
>>> from django.db import connection
>>> with connection.cursor() as cursor:
... cursor.execute(‘SELECT id, title FROM myapp_book’)
... row = cursor.fetchall()
...
>>> print(row)
((1, ‘Two Scoops of Django’), (2, ‘Django for Professionals’), (3, ‘High Performance Django’))
Expresiones
Django tiene varias expresiones que se asemejan o cumplen la misma función que ciertos tokens en SQL, las expresiones mas comunes son:
- F(): Representa un campo en especifico del modelo.
- Func(): Un envoltorio para llamar funciones específicas de tu motor de base de datos.
- Concat(): A como su nombre lo dice, concatenar dos o mas campos.
- Lower() y Upper(): Para transformar los valores de un campo en minúsculas o mayúsculas respectivamente.
- Sum() y Count(): Dos de las operaciones aritméticas mas usadas, para sumar y contar correspondientemente.
- Value(): La manera mas práctica de exponer un valor no relacionado a un dato en la base de datos.
- Subquery(): Una manera de encapsular otras operaciones en la base de datos, fuera de la consulta principal.
- OuterRef(): Para referenciar el campo a usar en el filtro de una Subquery.
- Case() y When(): A como su nombre lo indica, son expresiones condicionales o la manera “pythonica” de utilizar
if,else,elifdentro del ORM de Django.
Aggregate vs Annotate
aggregate: Genera valores de resultados (resumen) en todo un QuerySet. Podemos decir que opera sobre el conjunto de filas para obtener un solo valor de este mismo conjunto.
Por ejemplo, calcular el precio promedio de los libros:
>>> Book.objects.aggregate(average_price=Avg('price'))
{'average_price': 34.35}
annotate: Genera un resumen independiente para cada objeto en un QuerySet. Podemos decir que itera cada objeto dentro de una operación QuerySet y aplica dicha operación.
Por ejemplo, contar el número de autores que cada libro tiene:
>>> q = Book.objects.annotate(num_authors=Count('authors'))
>>> q[0].num_authors
2
>>> q[1].num_authors
1
Dando una respuesta mas práctica, aggregate ejecuta una operación para toda una QuerySet, mientras que annotate lo hace para cada item dentro del mismo QuerySet.
Ejemplo
Ahora bien, considerando la siguiente consulta SQL:
SELECT
DISTINCT
p.bpid AS post_id,
CASE
WHEN pr.review1_flag IS NULL AND pr.review2_flag IS NULL
THEN 'Sin Revisar'
ELSE 'Sin Revisar'
END AS upload_status,
CASE
WHEN (
pr.post_rejected = True
AND pr.post_rejected_at_stage = 1
) THEN 'Rechazado'
WHEN pr.review1_flag = True THEN 'Revisado'
WHEN pr.review1_flag IS NULL THEN 'Sin Revisar'
ELSE ''
END AS review1_flag,
CASE
WHEN (
pr.post_rejected = True
AND pr.post_rejected_at_stage = 2
) THEN 'Rechazado'
WHEN (
pr.review1_flag = True AND pr.review2_flag = True
) THEN 'Aprobado'
WHEN (
pr.review1_flag = True AND pr.review2_flag IS NULL
) THEN 'Sin Revisar'
ELSE ''
END AS review2_flag,
CASE
WHEN pr.post_pushed_date IS NOT NULL THEN (
to_char(pr.post_pushed_date, 'MM/DD/YYYY HH:MI AM')
)
ELSE ''
END AS pushed_date,
CASE
WHEN pr.in_review_userid IS NOT NULL THEN (
SELECT first_name || ' ' || last_name
FROM auth_user WHERE username = pr.in_review_userid
)
ELSE ''
END AS in_review_fullname
FROM post AS n
LEFT JOIN post_review AS pr ON n.postid = pr.postid
En esta tenemos una union con una tabla, una subquery y varios case que generan otras columnas, esta consulta se puede escribir de igual manera utilizando el ORM de Django y las funciones descritas anteriormente:
usernames_query = get_user_model().objects.filter(
username=OuterRef('in_review_userid')
).annotate(
full_name=Concat(
F('first_name'),
Value(' '),
F('last_name'),
output_field=CharField(),
)
)
posts = (
models.Post.objects.all().annotate(
post_id=F('bpid'),
upload_status=Case(
When(
postreview__review1_flag__isnull=True,
postreview__review2_flag__isnull=True,
then=Value('Sin Revisar')
),
default=Value('Sin Revisar'),
output_field=CharField(),
),
review1_flag=Case(
When(
postreview__post_rejected=True,
postreview__post_rejected_at_stage=1,
then=Value('Rechazado')
),
When(
postreview__review1_flag=True,
then=Value('Revisado')
),
When(
postreview__review1_flag=False,
then=Value('Sin Revisar'),
),
default=Value(''),
output_field=CharField(),
),
review2_flag=Case(
When(
postreview__post_rejected=True,
postreview__post_rejected_at_stage=2,
then=Value('Rechazado'),
),
When(
postreview__review2_flag=True,
then=Value('Aprobado')
),
When(
postreview__review2_flag__isnull=True,
then=Value('Sin Revisar'),
),
default=Value(''),
output_field=CharField(),
),
pushed_date=ToChar(
F('postreview__post_pushed_date'),
'MM/DD/YYYY HH:MI AM',
),
in_review_fullname=Case(
When(
postreview__in_review_userid__isnull=False,
then=Subquery(
usernames_query.values('full_name')[:1]),
),
),
default=Value(''),
output_field=CharField(),
)
).prefetch_related(
'postreview',
).order_by(
'post_id',
).distinct(
'post_id',
).values(
'post_id',
'review1_flag',
'review2_flag',
'pushed_date',
'in_review_fullname',
)
)
De la consulta anterior se puede resaltar la creación de una Subquery para obtener el nombre completo del usuario que esta revisando el post, en los Case() se ha agregado como parámetro output_field=CharField() esto debido a que Django necesita saber que tipo de dato va a retornar al momento de ejecutar la operación, de igual forma se utilizó el prefetch_related para precargar la cache de la BD con los registros de revisiones del post y asi evitar generar una consulta N+1, y por ultimo el uso de values para convertir la consulta a un diccionario y limitar los campos retornados por la instrucción SELECT, haciendo de esta forma la consulta mas ligera en el uso de memoria.
A su vez, si se hace un print() mediante print(posts.query) se puede saber la forma en la que Django esta traduciendo o generando la consulta mediante el ORM hacia algo entendible por el gestor de base de datos:
SELECT DISTINCT
"post"."bpid" AS "post_id",
CASE
WHEN
(
"post_review"."review1_flag" IS NULL
AND "post_review"."review2_flag" IS NULL
)
THEN
'Sin Revisar'
ELSE
'Sin Revisar'
END
AS "upload_status",
CASE
WHEN
(
"post_review"."post_rejected" = true
AND "post_review"."post_rejected_at_stage" = 1
)
THEN
'Rechazado'
WHEN
"post_review"."review1_flag" = true
THEN
'Revisado'
WHEN
"post_review"."review1_flag" = false
THEN
'Sin Revisar'
ELSE
''
END
AS "review1_flag",
CASE
WHEN
(
"post_review"."post_rejected" = true
AND "post_review"."post_rejected_at_stage" = 2
)
THEN
'Rechazado'
WHEN
"post_review"."review2_flag" = true
THEN
'Aprobado'
WHEN
"post_review"."review2_flag" IS NULL
THEN
'Sin Revisar'
ELSE
''
END
AS "review2_flag",
TO_CHAR(
"post_review"."post_pushed_date",
"MM/DD/YYYY HH:MI AM"
) AS "approval_date",
CASE
WHEN
"post_review"."in_review_userid" IS NOT NULL
THEN (
SELECT
CONCAT(
U0."first_name",
CONCAT(' ', U0."last_name")
) AS "full_name"
FROM
"auth_user" U0
WHERE
U0."username" =
CASE
WHEN
"post_review"."in_review_userid" IS NULL
THEN
''
ELSE
"post_review"."in_review_userid"
END
LIMIT 1
)
ELSE
''
END
AS "in_review_fullname"
FROM "post"
LEFT OUTER JOIN "post_review" ON (
"post"."bpid" = "post_review"."bpid"
)
ORDER BY "post"."bpid" ASC;
Se se considera la diferencia no es mucha entre hacerla de manera manual y utilizar el ORM, aunque mas allá del “performance” lo que se gana en usar el ORM es la facilidad de los desarrolladores de entender la consulta (en algunos casos) y la capacidad de iterar sobre esta sin introducir errores de seguridad.
Y de esa manera se ha transformado una consulta SQL a Django, hay que recordar que estas acciones no siempre serán los mas idóneos o se convertirán en una mejoría a largo plazo, es por ello que te dejo algunas recomendaciones a continuación.
Recomendaciones
Cabe aclarar que no siempre una consulta generada por el ORM de Django pueda ser mas rápida que una consulta usando SQL plano, por tal razón cuando tengas que sobre reescribir una consulta de SQL a ORM se debe tener en cuenta los siguientes consejos para validar que los cambios valgan la pena:
- Lee la documentación de Django primero.
- Recuerda siempre “El Zen de Python” (Explicit is better than implicit. Readability counts. PEP 20)
- Los cálculos a nivel de datos es mejor hacerlo en la base de datos en lugar de hacerlos en Python.
- Se puede usar Django-Debug-Toolbar para monitorear consultas.
- No esta de más usar el método
querypara indagar sobre el SQL generado por Django.
>>> q = Book.objects.all()
>>> print(q.query)
SELECT
“myapp_book”.”id”,
“myapp_book”.”title”
…
FROM “myapp_book”;
explainpara revisar el plan de ejecución de la consulta, y de esta manera indagar sobre qué índices o uniones se están utilizando.
>>> q = Book.objects.filter(title='Two Scoops of Django')
>>> print(q.explain(verbose=True, analyze=True))
Seq Scan on public.blog (cost=0.00..35.50 rows=10 width=12) (actual time=0.004..0.004 rows=10 loops=1)
Output: id, title, price, publish_date
Filter: (book.title = 'Two Scoops of Django'::bpchar)
Planning time: 0.064 ms
Execution time: 0.058 ms
- En algunos casos usar consultas fuera del ORM puede resultar en posibles agujeros de seguridad ante ataques de
SQL Injection. - Cuando se usa una
RawQuerySetDjango no verifica que la consulta SQL sea correcta, lo que podria generar errores. - En una
RawQuerySetel indizado y limite no se hacen a nivel de base de datos. - Es muy común que al usar el ORM tu consulta pueda resultar en un error del tipo N+1 .
- Utiliza
prefetch_relatedyselect_relatedsegún sea necesario. - Y finalmente DON’T TAKE ANYTHING FOR GRANTED.
Espero que este post sea de utilidad, mas adelante explicaré como construir nuestras propias Query Expressions y de ser posibles algunas recomendaciones para tener consultas SQL sanas.
Agradecer a @luismejiadev por motivarme a escribir este post y a @nicolefrale por las debidas revisiones y sugerencias.
Hasta la próxima.