Ejecutando consultas SQL crudas

Django te da dos formas de ejecutar consultas SQL crudas: puedes usar Manager.raw() para ejecutar consultas crudas y devolver instancias del modelo, o puedes evitar la capa del modelo en su totalidad y ejecutar SQL personalizado directamente.

Explora el ORM antes de usar SQL crudo

El ORM de Django proporciona muchas herramientas para expresar consultas sin escribir SQL crudo. Por ejemplo:

Antes de usar SQL crudo, explora el ORM. Pregúntale a uno de los canales de soporte si el ORM admite tu caso de uso.

Advertencia

Debes ser muy cuidadoso cada vez que escribes SQL crudo. Cada vez que lo uses, debes escapar adecuadamente cualquier parámetro controlado por el usuario utilizando params para protegerte contra ataques de inyección SQL. Por favor, lee más sobre protección contra la inyección SQL.

Ejecutando consultas crudas

El método del administrador raw() se puede usar para ejecutar consultas SQL crudas que devuelven instancias del modelo:

Manager.raw(raw_query, params=(), translations=None)

Este método toma una consulta SQL bruta, la ejecuta y devuelve un objeto de tipo django.db.models.query.RawQuerySet. Este objeto RawQuerySet se puede iterar como un conjunto normal QuerySet para proporcionar instancias de objetos.

Este ejemplo es mejor ilustrado con un ejemplo. Supongamos que tienes el siguiente modelo:

class Person(models.Model):
    first_name = models.CharField(...)
    last_name = models.CharField(...)
    birth_date = models.DateField(...)

Podrías ejecutar SQL personalizado de la siguiente manera:

>>> for p in Person.objects.raw("SELECT * FROM myapp_person"):
...     print(p)
...
John Smith
Jane Jones

Este ejemplo no es muy emocionante – es exactamente lo mismo que ejecutar Person.objects.all(). Sin embargo, raw() tiene una serie de otras opciones que lo hacen muy poderoso.

Nombres de tablas del modelo

¿De dónde vino el nombre de la tabla Person en ese ejemplo?

Por defecto, Django determina el nombre de la tabla de la base de datos uniendo el «etiqueta de aplicación» del modelo – el nombre que usaste en manage.py startapp – con el nombre de la clase del modelo, con un guión bajo entre ellos. En el ejemplo asumimos que el modelo Person vive en una aplicación llamada myapp, por lo que su tabla sería myapp_person.

Para obtener más detalles, consulta la documentación para la opción db_table, que también te permite establecer manualmente el nombre de la tabla de la base de datos.

Advertencia

No se realiza ninguna comprobación en la sentencia SQL que se pasa a .raw(). Django espera que la sentencia devuelva un conjunto de filas de la base de datos, pero no hace nada para asegurarlo. Si la consulta no devuelve filas, se producirá un error (posiblemente confuso).

Advertencia

Si estás realizando consultas en MySQL, ten en cuenta que el tipo silencioso de coerción de MySQL puede causar resultados inesperados al mezclar tipos. Si consultas sobre una columna de tipo cadena pero con un valor entero, MySQL convertirá los tipos de todos los valores en la tabla a enteros antes de realizar la comparación. Por ejemplo, si tu tabla contiene los valores 'abc' y 'def' y consultas por WHERE mycolumn=0, ambas filas coincidirán. Para evitar esto, realiza el casting correcto antes de usar el valor en una consulta.

Mapping de campos de consulta a campos del modelo

raw() mapea automáticamente los campos en la consulta a los campos en el modelo.

El orden de los campos en tu consulta no importa. En otras palabras, ambas consultas funcionan de manera idéntica:

>>> Person.objects.raw("SELECT id, first_name, last_name, birth_date FROM myapp_person")
>>> Person.objects.raw("SELECT last_name, birth_date, first_name, id FROM myapp_person")

La coincidencia se hace por nombre. Esto significa que puedes utilizar las cláusulas AS del SQL para mapear los campos en la consulta a los campos del modelo. Así que si tenías alguna otra tabla con datos de Persona, podrías mapearlo fácilmente a instancias de Persona:

>>> Person.objects.raw("""
...     SELECT first AS first_name,
...            last AS last_name,
...            bd AS birth_date,
...            pk AS id,
...     FROM some_other_table
...     """)
...

A medida que los nombres coincidan, se crearán las instancias del modelo correctamente.

Alternativamente, puedes mapear campos en la consulta a campos del modelo utilizando el argumento translations de raw(). Esto es un diccionario que mapea nombres de campos en la consulta a nombres de campos en el modelo. Por ejemplo, la consulta anterior también podría escribirse:

>>> name_map = {"first": "first_name", "last": "last_name", "bd": "birth_date", "pk": "id"}
>>> Person.objects.raw("SELECT * FROM some_other_table", translations=name_map)

Consultas de índice

raw() admite consultas de índice, por lo que si solo necesitas el primer resultado puedes escribir:

>>> first_person = Person.objects.raw("SELECT * FROM myapp_person")[0]

Sin embargo, la indexación y la slicing no se realizan a nivel de base de datos. Si tienes un gran número de objetos Persona en tu base de datos, es más eficiente limitar la consulta a nivel SQL:

>>> first_person = Person.objects.raw("SELECT * FROM myapp_person LIMIT 1")[0]

Diferir campos del modelo

Campos también pueden ser omitidos:

>>> people = Person.objects.raw("SELECT id, first_name FROM myapp_person")

Los objetos Person devueltos por esta consulta serán instancias de modelo diferidas (consulte defer()). Esto significa que los campos omitidos en la consulta se cargarán según sea necesario. Por ejemplo:

>>> for p in Person.objects.raw("SELECT id, first_name FROM myapp_person"):
...     print(
...         p.first_name,  # This will be retrieved by the original query
...         p.last_name,  # This will be retrieved on demand
...     )
...
John Smith
Jane Jones

Desde la apariencia exterior, esto parece que la consulta ha recuperado tanto el nombre de pila como el apellido. Sin embargo, este ejemplo realmente emitió 3 consultas. Solo se recuperaron los nombres de pila mediante la consulta raw() – los apellidos fueron ambos recuperados a medida que se imprimían.

Hay solo un campo que no puedes dejar de lado - el campo primario. Django utiliza el campo primario para identificar instancias del modelo, por lo que debe incluirse siempre en una consulta bruta. Se levantará una excepción FieldDoesNotExist si olvidas incluir el campo primario.

Agregando anotaciones

Puedes ejecutar también consultas que contengan campos no definidos en el modelo. Por ejemplo, podríamos utilizar la función age() de PostgreSQL para obtener una lista de personas con sus edades calculadas por la base de datos:

>>> people = Person.objects.raw("SELECT *, age(birth_date) AS age FROM myapp_person")
>>> for p in people:
...     print("%s is %s." % (p.first_name, p.age))
...
John is 37.
Jane is 42.
...

Puedes evitar con frecuencia el uso de SQL bruto para calcular anotaciones utilizando en su lugar una expresión Func().

Pasando parámetros a raw()

Si necesitas realizar consultas parametrizadas, puedes utilizar el argumento params de raw():

>>> lname = "Doe"
>>> Person.objects.raw("SELECT * FROM myapp_person WHERE last_name = %s", [lname])

params es una lista o diccionario de parámetros. Utilizarás lugares de reemplazo %s en la cadena de consulta para una lista, o lugares de reemplazo %(clave)s para un diccionario (donde clave se reemplaza por una clave del diccionario), sin importar el motor de base de datos. Dichos lugares de reemplazo se reemplazarán con parámetros del argumento params.

Nota

Los textos traducidos son:

Advertencia

No utilices la formación de cadenas en consultas SQL o coloque comillas alrededor de los reemplazos de marcadores!

Es tentador escribir la consulta anterior de esta manera:

>>> query = "SELECT * FROM myapp_person WHERE last_name = %s" % lname
>>> Person.objects.raw(query)

También podrías pensar que debes escribir tu consulta como esto (con comillas alrededor de %s):

>>> query = "SELECT * FROM myapp_person WHERE last_name = '%s'"

No cometan ninguno de estos errores.

Como se discute en protección-contra-inyecciones-sql, utilizar el argumento params y dejar los marcadores sin comillas te protege contra ataques de inyección SQL, un exploit común donde los atacantes inyectan SQL arbitrario en tu base de datos. Si utilizas interpolación de cadenas o citas el marcador, estás en riesgo de inyección SQL.

Ejecución de consultas SQL personalizadas

A veces incluso Manager.raw() no es suficiente: podrías necesitar realizar consultas que no se mapean limpiamente a modelos, o ejecutar directamente consultas UPDATE, INSERT o DELETE.

En estos casos, siempre puedes acceder a la base de datos directamente, evitando el capa de modelo por completo.

El objeto django.db.connection representa la conexión de base de datos predeterminada. Para utilizar la conexión de base de datos, llama a connection.cursor() para obtener un objeto cursor. Luego, llama a cursor.execute(sql, [params]) para ejecutar la consulta SQL y cursor.fetchone() o cursor.fetchall() para devolver las filas resultantes.

Por ejemplo:

from django.db import connection


def my_custom_sql(self):
    with connection.cursor() as cursor:
        cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
        cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
        row = cursor.fetchone()

    return row

To protect against SQL injection, you must not include quotes around the %s placeholders in the SQL string.

Ten en cuenta que si deseas incluir signos de porcentaje literales en la consulta, debes duplicarlos en el caso en que estés pasando parámetros:

cursor.execute("SELECT foo FROM bar WHERE baz = '30%'")
cursor.execute("SELECT foo FROM bar WHERE baz = '30%%' AND id = %s", [self.id])

Si estás utilizando más de una base de datos, puedes utilizar django.db.connections para obtener la conexión (y cursor) para una base de datos específica. django.db.connections es un objeto similar a un diccionario que te permite recuperar una conexión específica utilizando su alias:

from django.db import connections

with connections["my_db_alias"].cursor() as cursor:
    # Your code here
    ...

Por defecto, la API del DB de Python devolverá resultados sin sus nombres de campo, lo que significa que terminarás con una list de valores en lugar de un dict. A un pequeño costo de rendimiento y memoria, puedes devolver resultados como un dict utilizando algo así:

def dictfetchall(cursor):
    """
    Return all rows from a cursor as a dict.
    Assume the column names are unique.
    """
    columns = [col[0] for col in cursor.description]
    return [dict(zip(columns, row)) for row in cursor.fetchall()]

Otra opción es utilizar collections.namedtuple() desde la biblioteca estándar de Python. Un namedtuple es un objeto similar a una tupla que tiene campos accesibles mediante búsqueda por atributo; también es indexable e iterable. Los resultados son inmutables y accesibles tanto por nombres de campo como por índices, lo que podría ser útil:

from collections import namedtuple


def namedtuplefetchall(cursor):
    """
    Return all rows from a cursor as a namedtuple.
    Assume the column names are unique.
    """
    desc = cursor.description
    nt_result = namedtuple("Result", [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

El ejemplo de dictfetchall() y namedtuplefetchall() asume nombres de columna únicos, ya que un cursor no puede distinguir columnas de diferentes tablas.

Aquí está un ejemplo de la diferencia entre los tres:

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
>>> cursor.fetchall()
((54360982, None), (54360880, None))

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
>>> dictfetchall(cursor)
[{'parent_id': None, 'id': 54360982}, {'parent_id': None, 'id': 54360880}]

>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2")
>>> results = namedtuplefetchall(cursor)
>>> results
[Result(id=54360982, parent_id=None), Result(id=54360880, parent_id=None)]
>>> results[0].id
54360982
>>> results[0][0]
54360982

Conexiones y consultores

connection y cursor implementan principalmente la API del DB de Python descrita en PEP 249 — excepto cuando se trata del manejo de transacciones: manejo de transacciones.

Si no estás familiarizado con la API del DB de Python, ten en cuenta que la sentencia SQL en cursor.execute() utiliza marcadores de posición, "%s", en lugar de agregar parámetros directamente dentro de la SQL. Si utilizas esta técnica, la biblioteca de base de datos subyacente escapará automáticamente tus parámetros según sea necesario.

También ten en cuenta que Django espera el reemplazador "%s" , no el reemplazador "? " , que se utiliza por las vinculaciones de Python para SQLite. Esto se hace por razones de coherencia y cordura.

Usando un cursor como administrador de contexto:

with connection.cursor() as c:
    c.execute(...)

es equivalente a:

c = connection.cursor()
try:
    c.execute(...)
finally:
    c.close()

Llamando procedimientos almacenados

CursorWrapper.callproc(procname, params=None, kparams=None)

Llama una procedura almacenada de base de datos con el nombre dado. Puede proporcionarse una secuencia (params) o un diccionario (kparams) de parámetros de entrada. La mayoría de las bases de datos no admiten kparams. Solo Oracle, entre los backends integrados de Django, lo admite.

Ejemplo de procedimiento almacenado en una base de datos Oracle:

CREATE PROCEDURE "TEST_PROCEDURE"(v_i INTEGER, v_text NVARCHAR2(10)) AS
    p_i INTEGER;
    p_text NVARCHAR2(10);
BEGIN
    p_i := v_i;
    p_text := v_text;
    ...
END;

Este llamará a él:

with connection.cursor() as cursor:
    cursor.callproc("test_procedure", [1, "test"])
« previous | up | next »