Django le ofrece dos formas de realizar consultas SQL sin procesar: puede usar Manager.raw() para realizar consultas sin procesar y devolver instancias de modelo, o puede evitar la capa de modelo por completo y ejecutar SQL personalizado directamente.

¡Explore el ORM antes de usar SQL sin formato!

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

  • los API QuerySet es extenso.
  • Usted puede annotate y agregar usando muchos incorporados funciones de base de datos. Más allá de eso, puede crear expresiones de consulta personalizadas.

Antes de usar SQL sin formato, explore el ORM. Pregunte en usuarios de django o la #django canal de IRC para ver si el ORM es compatible con su caso de uso.

Advertencia

Debe tener mucho cuidado siempre que escriba SQL sin formato. Cada vez que lo use, debe escapar correctamente de cualquier parámetro que el usuario pueda controlar usando params para protegerse contra ataques de inyección SQL. Por favor lea más sobre Protección de inyección SQL.

Realización de consultas sin procesar

los raw() El método de administrador se puede utilizar para realizar consultas SQL sin procesar que devuelven instancias de modelo:

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

Este método toma una consulta SQL sin procesar, la ejecuta y devuelve un django.db.models.query.RawQuerySet ejemplo. Esta RawQuerySet La instancia se puede iterar como una QuerySet para proporcionar instancias de objetos.

Esto se ilustra mejor con un ejemplo. Suponga que tiene el siguiente modelo:

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

A continuación, puede ejecutar SQL personalizado así:

>>>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 un montón de otras opciones que lo hacen muy poderoso.

Nombres de tablas de modelos

¿De dónde salió el nombre del Person tabla de donde viene en ese ejemplo?

De forma predeterminada, Django descubre el nombre de una tabla de base de datos uniendo la “etiqueta de la aplicación” del modelo, el nombre que usó en manage.py startapp – al nombre de la clase del modelo, con un guión bajo entre ellos. En el ejemplo asumimos que el Person modelo vive en una aplicación llamada myapp, por lo que su mesa sería myapp_person.

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

Advertencia

No se realiza ninguna comprobación en la instrucción SQL que se pasa a .raw(). Django espera que la declaración devuelva un conjunto de filas de la base de datos, pero no hace nada para hacer cumplir eso. Si la consulta no devuelve filas, se producirá un error (posiblemente críptico).

Advertencia

Si está realizando consultas en MySQL, tenga en cuenta que la coerción de tipo silenciosa de MySQL puede causar resultados inesperados al mezclar tipos. Si consulta sobre un string type column, pero con un valor entero, MySQL convertirá los tipos de todos los valores en la tabla en un número entero antes de realizar la comparación. Por ejemplo, si su tabla contiene los valores 'abc', 'def' y tu consultas por WHERE mycolumn=0, ambas filas coincidirán. Para evitar esto, realice el encasillado correcto antes de usar el valor en una consulta.

Cambiado en Django 3.2:

El valor predeterminado de la params el argumento fue cambiado de None a una tupla vacía.

Asignación de campos de consulta a campos de modelo

raw() asigna automáticamente los campos de la consulta a los campos del modelo.

El orden de los campos en su consulta no importa. En otras palabras, las dos consultas siguientes funcionan de forma 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')...

El emparejamiento se realiza por nombre. Esto significa que puede utilizar SQL AS cláusulas para asignar campos en la consulta a campos de modelo. Entonces, si tuvieras otra mesa que tuviera Person datos en él, puede mapearlo fácilmente en Person instancias:

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

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

Alternativamente, puede mapear campos en la consulta para modelar campos usando el translations argumento para raw(). Se trata de un diccionario que asigna los nombres de los campos de la consulta a los nombres de los campos del 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)

Búsquedas de índice

raw() admite la indexación, por lo que si solo necesita el primer resultado, puede escribir:

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

Sin embargo, la indexación y el corte no se realizan a nivel de la base de datos. Si tiene una gran cantidad de Person objetos en su base de datos, es más eficiente limitar la consulta en el nivel SQL:

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

Aplazamiento de campos del modelo

Los campos también pueden quedar fuera:

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

los Person Los objetos devueltos por esta consulta serán instancias de modelo diferidas (consulte defer()). Esto significa que los campos que se omiten de la consulta se cargarán a pedido. 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

A juzgar por las apariencias, parece que la consulta ha recuperado tanto el nombre como el apellido. Sin embargo, este ejemplo en realidad generó 3 consultas. La consulta raw () solo recuperó los nombres; los apellidos se recuperaron a pedido cuando se imprimieron.

Solo hay un campo que no puede omitir: el campo de clave principal. Django usa la clave principal para identificar las instancias del modelo, por lo que siempre debe incluirse en una consulta sin formato. A FieldDoesNotExist Se generará una excepción si olvida incluir la clave principal.

Agregar anotaciones

También puede ejecutar consultas que contengan campos que no están definidos en el modelo. Por ejemplo, podríamos usar 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 is37.
Jane is42....

A menudo, puede evitar el uso de SQL sin procesar para calcular anotaciones utilizando en su lugar un Expresión func ().

Pasando parámetros a raw()

Si necesita realizar consultas parametrizadas, puede utilizar el params argumento para raw():

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

params es una lista o diccionario de parámetros. Usarás %s marcadores de posición en la consulta string para una lista, o %(key)s marcadores de posición para un diccionario (donde key se reemplaza por una clave de diccionario), independientemente de su motor de base de datos. Dichos marcadores de posición se reemplazarán con parámetros del params argumento.

Nota

Los parámetros de diccionario no son compatibles con el backend de SQLite; con este backend, debe pasar los parámetros como una lista.

Advertencia

No utilice string formatear en consultas sin formato o comillas marcadores de posición en sus cadenas SQL!

Es tentador escribir la consulta anterior como:

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

También podría pensar que debería escribir su consulta de esta manera (con comillas alrededor %s):

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

No cometa ninguno de estos errores.

Como se discutió en Protección de inyección SQL, utilizando el params argumento y dejar los marcadores de posición sin comillas lo protege de Ataques de inyección SQL, un exploit común en el que los atacantes inyectan SQL arbitrario en su base de datos. Si utiliza string interpolación o citar el marcador de posición, corre el riesgo de una inyección SQL.

Ejecutar SQL personalizado directamente

A veces incluso Manager.raw() no es suficiente: es posible que deba realizar consultas que no se asignan limpiamente a los modelos, o que se ejecutan directamente UPDATE, INSERT, o DELETE consultas.

En estos casos, siempre puede acceder a la base de datos directamente, recorriendo la capa del modelo por completo.

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

Por ejemplo:

from django.db import connection

defmy_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

Para protegerse contra la inyección de SQL, no debe incluir comillas alrededor del %s marcadores de posición en el SQL string.

Tenga en cuenta que si desea incluir signos de porcentaje literal en la consulta, debe duplicarlos en el caso de que esté 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 esta usando más de una base de datos, puedes usar django.db.connections para obtener la conexión (y el cursor) para una base de datos específica. django.db.connections es un objeto similar a un diccionario que le permite recuperar una conexión específica usando su alias:

from django.db import connections
with connections['my_db_alias'].cursor()as cursor:# Your code here...

De forma predeterminada, la API de Python DB devolverá resultados sin sus nombres de campo, lo que significa que terminará con un list de valores, en lugar de un dict. Con un bajo costo de memoria y rendimiento, puede obtener resultados como dict usando algo como esto:

defdictfetchall(cursor):"Return all rows from a cursor as a dict"
    columns =[col[0]for col in cursor.description]return[dict(zip(columns, row))for row in cursor.fetchall()]

Otra opción es usar collections.namedtuple() de la biblioteca estándar de Python. A namedtuple es un objeto similar a una tupla que tiene campos accesibles mediante búsqueda de atributos; también es indexable e iterable. Los resultados son inmutables y accesibles mediante nombres de campo o índices, lo que puede resultar útil:

from collections import namedtuple

defnamedtuplefetchall(cursor):"Return all rows from a cursor as a namedtuple"
    desc = cursor.description
    nt_result = namedtuple('Result',[col[0]for col in desc])return[nt_result(*row)for row in cursor.fetchall()]

A continuación, se muestra 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].id54360982>>> results[0][0]54360982

Conexiones y cursores

connection y cursor implementan principalmente el estándar Python DB-API descrito en PEP 249 – excepto cuando se trata de manejo de transacciones.

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

También tenga en cuenta que Django espera "%s" marcador de posición no los "?" marcador de posición, que es utilizado por los enlaces SQLite Python. Esto es por el bien de la coherencia y la 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()

Llamar a procedimientos almacenados

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

Llama a un procedimiento almacenado de base de datos con el nombre dado. Una secuencia (params) o diccionario (kparams) de los parámetros de entrada. La mayoría de las bases de datos no admiten kparams. De los backends integrados de Django, solo Oracle lo admite.

Por ejemplo, dado este procedimiento almacenado en una base de datos de Oracle:

CREATEPROCEDURE"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;

Esto lo llamará:

with connection.cursor()as cursor:
    cursor.callproc('test_procedure',[1,'test'])