有没有办法在执行查询时显示Django正在运行的SQL语句?
connection.queries
来获取 Django 中运行的原始 SQL 查询,如下所示:# "store/views.py"
from django.db import transaction
from .models import Person
from django.db import connection
from django.http import HttpResponse
@transaction.atomic
def test(request):
Person.objects.create(name="John") # INSERT
qs = Person.objects.select_for_update().get(name="John") # SELECT FOR UPDATE
qs.name = "Tom"
qs.save() # UPDATE
qs.delete() # DELETE
for query in connection.queries: # Here
print(query)
return HttpResponse("Test")
然后,原始查询将如下所示打印在控制台上:
{'sql': 'INSERT INTO "store_person" ("name") VALUES (\'John\') RETURNING "store_person"."id"', 'time': '0.000'}
{'sql': 'SELECT "store_person"."id", "store_person"."name" FROM "store_person" WHERE "store_person"."name" = \'John\' LIMIT 21 FOR UPDATE', 'time': '0.000'}
{'sql': 'UPDATE "store_person" SET "name" = \'Tom\' WHERE "store_person"."id" = 179', 'time': '0.000'}
{'sql': 'DELETE FROM "store_person" WHERE "store_person"."id" IN (179)', 'time': '0.000'}
[24/Dec/2022 06:29:32] "GET /store/test/ HTTP/1.1" 200 9
如果您只想获取UPDATE
和DELETE
查询而不包含INSERT
和SELECT FOR UPDATE
查询,那么请在Person.objects.select_for_update()
之后添加reset_queries()
如下所示:
# "store/views.py"
from django.db import transaction
from .models import Person
from django.db import reset_queries
from django.db import connection
from django.http import HttpResponse
@transaction.atomic
def test(request):
Person.objects.create(name="John") # INSERT
qs = Person.objects.select_for_update().get(name="John") # SELECT FOR UPDATE
reset_queries() # Here
qs.name = "Tom"
qs.save() # UPDATE
qs.delete() # DELETE
for query in connection.queries: # Here
print(query)
return HttpResponse("Test")
然后,只有UPDATE
和DELETE
查询被打印出来,没有INSERT
和SELECT FOR UPDATE
查询,如下所示:
{'sql': 'UPDATE "store_person" SET "name" = \'Tom\' WHERE "store_person"."id" = 190', 'time': '0.000'}
{'sql': 'DELETE FROM "store_person" WHERE "store_person"."id" IN (190)', 'time': '0.000'}
[24/Dec/2022 07:00:01] "GET /store/test/ HTTP/1.1" 200 9
settings.py
文件:# settings.py
LOGGING = {
'version': 1,
'formatters': {
'verbose': {
'format': '{levelname} {asctime} {module} {message}',
'style': '{',
},
},
'handlers': {
'console': {
'level': 'DEBUG',
'class': 'logging.StreamHandler',
'formatter': 'verbose',
},
},
'loggers': {
'django.db.backends': {
'handlers': ['console'],
'level': 'DEBUG',
'propagate': True,
},
},
}
# settings.py
DEBUG = True
对于那些只想从查询本身获取结果的人来说,有一种最简单的方法:
假设我们有一个名为Musico
的模型:
class Musico(models.Model):
INSTRUMENTOS = [
('violao', 'Violão'),
('piano', 'Piano'),
('cavaquinho', 'Cavaquinho'),
]
usuario = models.OneToOneField(User, on_delete=models.DO_NOTHING, null=True)
primeiro_nome = models.CharField(max_length=120)
sobrenome = models.CharField(max_length=120, null=True, blank=True)
tipo_instrumento = models.CharField(choices=INSTRUMENTOS, max_length=200)
idade = models.IntegerField(null=True, blank=True)
def __str__(self):
return f"Musico: {self.primeiro_nome}"
检查原始的SQL查询将会像这样:
>>> str(Musico.objects.all().query)
'SELECT "model_lesson_app_musico"."id", "model_lesson_app_musico"."usuario_id", "model_lesson_app_musico"."primeiro_nome", "model_lesson_app_musico"."sobrenome", "model_lesson_app_musico"."tipo_instrumento", "model_lesson_app_musico"."idade" FROM "model_lesson_app_musico"'
已经有几个很好的答案了。
再来一个方法。
在测试中,可以这样做:
with self.assertNumQueries(3):
response = self.client.post(reverse('payments:pay_list'))
# or whatever
对于Django 2.2:
由于大多数答案在使用./manage.py shell
时并没有帮助我太多,最终我找到了答案。希望这能帮助到某些人。
要查看所有查询:
from django.db import connection
connection.queries
查看单个查询的查询:
q=Query.objects.all()
q.query.__str__()
q.query
只是为我显示对象。
使用 __str__()
(字符串表示)显示完整查询。
使用django.db.connection.queries查看查询
from django.db import connection
print(connection.queries)
在 QuerySet 对象上访问原始 SQL 查询
qs = MyModel.objects.all()
print(qs.query)