Lancement de requêtes SQL brutes

Lorsque l’API d’interrogation des modèles atteint ses limites, il est possible de recourir à l’écriture de code SQL brut. 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é.

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

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

Avertissement

Alors qu’il est possible d’itérer sur une instance RawQuerySet comme pour un objet QuerySet normal, RawQuerySet n’implémente pas toutes les méthodes qu’il est possible d’utiliser avec un QuerySet. Par exemple, __bool__() et __len__() ne sont pas définis pour RawQuerySet, ce qui fait que toutes les instances RawQuerySet sont considérées comme True. La raison pour laquelle ces méthodes ne sont pas implémentées par RawQuerySet est que leur implémentation sans cache interne serait ruineuse en terme de performances et que l’ajout de cache briserait la rétrocompatibilité.

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

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, bien sûr), 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 !

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)

Ne le faites pas.

En utilisant le paramètre params, vous êtes complètement protégé 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, vous serez tôt ou tard victime d’injection SQL. Tant que vous n’oubliez pas de toujours utiliser le paramètre params, vous serez protégé.

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):
    cursor = connection.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

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
cursor = connections['my_db_alias'].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.

Changed in Django 1.7.

PEP 249 ne précise pas si un curseur doit pouvoir être utilisé comme gestionnaire de contexte. Avant Python 2.7, un curseur était utilisable comme gestionnaire de contexte en raison d’un comportement inattendu lors des recherches de méthodes magiques (ticket Python #9220). Django 1.7 ajoute explicitement la prise en charge des curseurs comme gestionnaires de contexte.

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