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é.
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 :
This method takes a raw SQL query, executes it, and returns a django.db.models.query.RawQuerySet instance. This RawQuerySet instance can be iterated over just like a normal QuerySet to provide object instances.
This is best illustrated with an example. Suppose you have the following model:
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.
Model table names
D’où peut bien provenir 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
If you are performing queries on MySQL, note that MySQL’s silent type coercion may cause unexpected results when mixing types. If you query on a string type column, but with an integer value, MySQL will coerce the types of all values in the table to an integer before performing the comparison. For example, if your table contains the values 'abc', 'def' and you query for WHERE mycolumn=0, both rows will match. To prevent this, perform the correct typecasting before using the value in a query.
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)
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]
However, the indexing and slicing are not performed at the database level. If you have a large number of Person objects in your database, it is more efficient to limit the query at the SQL level:
>>> first_person = Person.objects.raw('SELECT * from myapp_person LIMIT 1')[0]
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.
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.
...
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 de paramètres. Dans la chaîne de requête, il faut alors inclure des substituants %s (quel que soit le moteur de base de données) ; ils seront remplacés par les paramètres de la liste params.
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 la liste params comme paramètre, 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 la liste params, vous serez protégé.
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 et django.db.transaction représente la transaction à 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. Après avoir effectué une opération qui modifie des données, vous devriez ensuite appeler transaction.commit_unless_managed() pour vous assurer que les modifications ont bien été commitées dans la base de données. Si votre requête se limite à sélectionner des données, le commit n’est pas obligatoire. Par exemple :
def my_custom_sql():
from django.db import connection, transaction
cursor = connection.cursor()
# Data modifying operation - commit required
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
transaction.commit_unless_managed()
# Data retrieval operation - no commit required
cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
row = cursor.fetchone()
return row
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...
transaction.commit_unless_managed(using='my_db_alias')
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, vous pouvez obtenir les résultats sous forme de dictionnaire en écrivant quelque chose comme :
def dictfetchall(cursor):
"Returns all rows from a cursor as a dict"
desc = cursor.description
return [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
Voici un exemple de la différence entre les deux :
>>> cursor.execute("SELECT id, parent_id from test LIMIT 2");
>>> cursor.fetchall()
((54360982L, None), (54360880L, None))
>>> cursor.execute("SELECT id, parent_id from test LIMIT 2");
>>> dictfetchall(cursor)
[{'parent_id': None, 'id': 54360982L}, {'parent_id': None, 'id': 54360880L}]
Lorsque vous exécutez du SQL brut, Django marque automatiquement la transaction actuelle comme « dirty » (sale). C’est alors à vous de vous assurer que la transaction contenant ces appels est fermée correctement. Voir les notes sur les prérequis de la gestion des transactions par Django pour plus de détails.
connection et cursor implémentent en grande partie l’API DB standard de Python décrite dans la PEP 249 (sauf en ce qui concerne la gestion des transactions). Si vous n’êtes pas familier de cette API DB de Python, 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 de placer entre guillemets et 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).
Jan 13, 2016