# Documentation Django 3.1.9.dev

# Lancement de requêtes SQL brutes

Django propose deux manières d’exécuter des requêtes SQL brutes : vous pouvez utiliser `Manager.raw()` pour exécuter des requêtes brutes et renvoyer des instances de modèles ou vous pouvez outrepasser complètement la couche des modèles et exécuter directement du code SQL personnalisé.

Explorez les possibilités de l’ORM avant d’avoir recours à du code SQL brut !

L’ORM de Django fournit de nombreux outils pour formuler des requêtes sans recourir à du code SQL brut. Par exemple :

- L’API QuerySet est bien documentée.
- Il est possible d”`annoter` et d”agréger en utilisant de nombreuses fonctions de base de données intégrées. Au-delà de ça, il est possible de créer des expressions de requête personnalisées.

Avant de recourir à du code SQL brut, explorez l’ORM. Posez vos questions sur les canaux IRC django-users ou #django pour savoir si l’ORM peut satisfaire votre cas de figure.

Avertissement

Vous devez être très prudent lors de l’écriture d’instructions SQL brutes. Lors de chaque utilisation, vous devez échapper correctement tout paramètre pouvant être contrôlé par les utilisateurs en employant `params` afin de vous protéger contre les attaques par injection SQL. Lisez attentivement les paragraphes sur la protection contre les injections SQL.

## Lancement de requêtes brutes

La méthode de gestionnaire `raw()` peut être utilisée pour exécuter des requêtes SQL brutes qui renvoient des instances de modèles :

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

Cette méthode accepte une requête SQL brute, l’exécute et renvoie une instance `django.db.models.query.RawQuerySet`. Il est possible alors d’effectuer une boucle sur cette instance `RawQuerySet` comme pour un objet `QuerySet` normal afin d’accéder aux instances d’objets.

Un exemple vaut mieux que mille mots. Supposons que vous ayez créé le modèle suivant :

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

Vous pouvez alors exécuter du code SQL personnalisé comme ceci :

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

Cet exemple n’est pas des plus passionnants, car il correspond exactement à l’expression `Person.objects.all()`. Toutefois, `raw()` comporte quelques autres options qui en font un outil très puissant.

Noms de table des modèles

D’où vient le nom de la table `` Person`` dans cet exemple?

Par défaut, Django compose un nom de table de base de données en combinant l”« étiquette d’application » du modèle (le nom utilisé dans `manage.py startapp`) avec le nom de classe du modèle, séparés par un soulignement. Dans l’exemple, nous sommes partis du principe que le modèle `Person` se trouvait dans une application nommée `myapp`, et donc que le nom de la table était `myapp_person`.

Pour plus de détails, consultez la documentation de l’option `db_table` qui vous permet également de définir manuellement le nom de table de la base de données.

Avertissement

Aucune vérification n’est effectuée pour les instructions SQL transmises à `.raw()`. Django s’attend à ce que la requête renvoie un ensemble de lignes de la base de données, mais ne fait rien pour le vérifier a priori. Si la requête ne retourne pas de ligne, une erreur (potentiellement cryptique) sera générée.

Avertissement

Si vous effectuez des requêtes vers MySQL, notez que le forçage de type silencieux de MySQL peut produire des résultats inattendus lors du mélange de types. Si une requête porte sur une colonne de type chaîne mais contient une valeur nombre entier, MySQL transforme le type de toutes les valeurs de la table en nombre entier avant d’effectuer la comparaison. Par exemple, si la table contient les valeurs `'abc'`, `'def'` et que la requête contient `WHERE macolonne=0`, les deux lignes seront sélectionnées. Pour empêcher cela, effectuez les transformations de type avant d’utiliser une valeur dans une requête.

### Correspondance entre champs de requête et champs de modèle

`raw()` fait automatiquement correspondre les champs de la requête avec les champs du modèle.

L’ordre des champs dans la requête n’est pas important. En d’autres termes, les deux requêtes suivantes donneront le même résultat :

    >>> 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 correspondance se fait sur le nom. Cela signifie que vous pouvez utiliser les clauses SQL `AS` pour faire correspondre les champs de la requête aux champs du modèle. Ainsi, si vous disposez d’une autre table contenant les données de `Person`, vous pouvez facilement faire correspondre ces données avec des instances de `Person`:

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

Tant que les noms correspondent, les instances de modèle seront créées correctement.

Il est aussi possible de faire correspondre les champs de requête aux champs de modèle en utilisant le paramètre `translations` de `raw()`. Il s’agit d’un dictionnaire faisant correspondre les noms des champs de la requête aux noms des champs du modèle. Par exemple, la requête ci-dessus aurait aussi pu être écrite de cette manière :

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

### Filtrage par index

`raw()` autorise l’utilisation d’index ; dans le cas où vous souhaitez obtenir uniquement le premier résultat, vous pouvez écrire :

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

Cependant, l’indexation et la segmentation ne sont pas effectuées au niveau de la base de données. Si la base de données contient une grande quantité d’objets `Person`, il est plus efficace de limiter la requête au niveau SQL :

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

### Report des champs de modèle

Il est aussi possible d’ignorer certains champs :

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

Les objets `Person` renvoyés par cette requête constitueront des instances de modèle différées (voir `defer()`). Cela signifie que les champs omis dans la requête seront chargés à la demande. Par exemple :

    >>> 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

En apparence, il semble que la requête ait récupéré à la fois le prénom et le nom. Cependant, cet exemple effectue en réalité 3 requêtes. Seuls les prénoms (first_name) ont été obtenus par la requête `raw()`, les noms (last_name) ont été obtenus chacun à la demande au moment où ils ont été affichés.

Un seul champ ne peut pas être omis, c’est le champ clé primaire. Django utilise la clé primaire pour identifier les instances de modèle, elle doit donc être obligatoirement incluse dans la requête brute. Une exception `FieldDoesNotExist` est générée si vous oubliez d’inclure la clé primaire.

### Ajout d’annotations

Vous pouvez aussi exécuter des requêtes contenant des champs qui ne sont pas définis dans le modèle. Par exemple, il serait possible d’utiliser la fonction age() de PostgreSQL pour obtenir une liste de personnes avec leur âge calculé par la base de données :

    >>> 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.
    ...

Il est souvent possible d’éviter de devoir utiliser du code SQL brut pour calculer des annotations en faisant plutôt appel à une expression Func().

### Transmission de paramètres dans `raw()`

S’il est nécessaire d’effectuer des requêtes paramétrées, il est possible d’utiliser le paramètre `params` de `raw()`:

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

`params` est une liste ou un dictionnaire de paramètres. Dans la chaîne de requête, il faut alors inclure des substituants `%s` pour une liste ou des substituants``%(clé)s`` pour un dictionnaire (où `clé` est remplacé par une clé de dictionnaire), quel que soit le moteur de base de données. Ces substituants seront remplacés par le contenu du paramètre `params`.

Note

Les paramètres de type dictionnaire ne sont pas pris en charge par le moteur SQLite ; avec ce moteur, vous devez transmettre les paramètres sous forme de liste.

Avertissement

N’utilisez pas de formatage de chaîne dans les requêtes brutes, ni de substituants entre guillemets dans vos chaînes SQL !

Il est tentant d’écrire la requête ci-dessus comme ceci :

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

Vous pourriez aussi imaginer devoir écrire votre requête comme ceci (avec des guillemets autour de `%s`)

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

Ne faites aucune de ces erreurs.

Comme discuté dans Protection contre l’injection SQL, l’utilisation du paramètre `params` et le fait de laisser les substituants sans guillemets vous protègent contre les attaques d’injection SQL, une faille courante où un attaquant injecte du code SQL arbitraire dans votre base de données. Si vous utilisez l’interpolation de chaîne ou que vous placez les substituants entre guillemets, vous êtes exposé aux injections SQL.

## Exécution directe de code SQL

Dans certains cas, même `Manager.raw()` ne suffit pas : il se peut que des requêtes doivent être effectuées sans correspondre proprement à des modèles ou que vous vouliez exécuter directement des requêtes `UPDATE`, `INSERT` ou `DELETE`.

Dans ces situations, vous pouvez toujours accéder directement à la base de données, outrepassant complètement la couche des modèles.

L’objet `django.db.connection` représente la connexion à la base de données par défaut. Pour utiliser la connexion à la base de données, appelez `connection.cursor()` pour obtenir un objet curseur. Puis, appelez `cursor.execute(sql, [params])` pour exécuter le code SQL et `cursor.fetchone()` ou `cursor.fetchall()` pour obtenir les lignes de résultat.

Par exemple :

    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

Pour se protéger des injections SQL, vous devez vous abstenir de placer des guillemets autour des substituants `%s` dans la chaîne SQL.

Notez que si vous voulez inclure des signes « pour cent » littéraux dans la requête, vous devez les doubler dans le cas où vous transmettez des paramètres :

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

Si vous utilisez plus d’une base de données, vous pouvez utiliser `django.db.connections` pour obtenir la connexion (et le curseur) pour une base de données spécifique. `django.db.connections` est un objet de type dictionnaire permettant de récupérer une connexion spécifique en employant son alias :

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

Par défaut, l’API de base de données de Python renvoie les résultats sans les noms de champs, ce qui signifie que vous vous retrouvez avec une liste de valeurs plutôt qu’un dictionnaire. Pour un faible coût en performances et en mémoire, vous pouvez obtenir les résultats sous forme de dictionnaire en écrivant quelque chose comme :

    def dictfetchall(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()
        ]

Une autre option est d’utiliser une structure `collections.namedtuple()` de la bibliothèque Python standard. Un `namedtuple` est un objet de type tuple dont les champs sont accessibles sous forme d’attribut ; l’accès par indice est aussi possible et l’objet est itérable. Les résultats sont immuables et accessibles par nom de champ ou par indice, ce qui pourrait être pratique :

    from collections import namedtuple

    def namedtuplefetchall(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()]

Voici un exemple de la différence entre les trois :

    >>> 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

### Connexions et curseurs

`connection` et `cursor` implémentent essentiellement l’API de base de données standard de Python décrite dans la PEP 249, à l’exception de ce qui concerne la gestion des transactions.

Si cette API DB de Python ne vous est pas familière, notez que l’instruction SQL dans `cursor.execute()` utilise des substituants, `"%s"`, plutôt que d’ajouter directement les paramètres dans la chaîne SQL. Si vous utilisez cette technique, la bibliothèque sous-jacente de base de données s’occupe automatiquement d’échapper vos paramètres au besoin.

Notez également que Django compte sur des substituants `"%s"`, pas de substituants `"?"` qui sont utilisés par la bibliothèque SQLite de Python, pour des raisons de cohérence et de bon sens.

L’utilisation d’un curseur en tant que gestionnaire de contexte :

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

est équivalent à :

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

#### Appeler des procédures stockées

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

  Appelle une procédure stockée de base de données ayant le nom indiqué. Une liste (`params`) ou un dictionnaire (`kparams`) de paramètres d’entrée peuvent être fournis. La plupart des bases de données n’acceptent pas `kparams`. Parmi celles qui sont prises en charge nativement par Django, seule Oracle accepte `kparams`.

  Par exemple, avec cette procédure stockée dans une base de données 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;

  Ceci l’appellera

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