如何查看 Django 正在运行的原始 SQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1074212/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I see the raw SQL queries Django is running?
提问by spence91
Is there a way to show the SQL that Django is running while performing a query?
有没有办法在执行查询时显示 Django 正在运行的 SQL？
回答by geowa4
See the docs FAQ: "How can I see the raw SQL queries Django is running?"
请参阅文档常见问题解答：“如何查看 Django 正在运行的原始 SQL 查询？”
django.db.connection.queriescontains a list of the SQL queries:
django.db.connection.queries包含 SQL 查询列表：
from django.db import connection
print(connection.queries)
Querysets also have a queryattributecontaining the query to be executed:
查询集还有一个包含要执行的查询的query属性：
print(MyModel.objects.filter(name="my name").query)
Note that the output of the query is not valid SQL, because:
请注意，查询的输出不是有效的 SQL，因为：
"Django never actually interpolates the parameters: it sends the query and the parameters separately to the database adapter, which performs the appropriate operations."
“Django 从不实际插入参数：它将查询和参数分别发送到执行适当操作的数据库适配器。”
From Django bug report #17741.
来自 Django 错误报告#17741。
Because of that, you should not send query output directly to a database.
因此，您不应将查询输出直接发送到数据库。
回答by Patrick Z
Django-extensionshave a command shell_pluswith a parameter print-sql
Django 扩展有一个带参数的命令shell_plusprint-sql
./manage.py shell_plus --print-sql
In django-shell all executed queries will be printed
在 django-shell 中，所有执行的查询都会被打印出来
Ex.:
前任。：
User.objects.get(pk=1)
SELECT "auth_user"."id",
"auth_user"."password",
"auth_user"."last_login",
"auth_user"."is_superuser",
"auth_user"."username",
"auth_user"."first_name",
"auth_user"."last_name",
"auth_user"."email",
"auth_user"."is_staff",
"auth_user"."is_active",
"auth_user"."date_joined"
FROM "auth_user"
WHERE "auth_user"."id" = 1
Execution time: 0.002466s [Database: default]
<User: username>
回答by Glader
Take a look at debug_toolbar, it's very useful for debugging.
看看debug_toolbar，它对调试非常有用。
Documentation and source is available at http://django-debug-toolbar.readthedocs.io/.
文档和源代码可在http://django-debug-toolbar.readthedocs.io/ 获得。
回答by Bryce
No other answer covers this method, so:
没有其他答案涵盖此方法，因此：
I find by far the most useful, simple, and reliable method is to ask your database. For example on Linux for Postgres you might do:
我发现迄今为止最有用、最简单、最可靠的方法是询问您的数据库。例如，在 Linux for Postgres 上，您可以执行以下操作：
sudo su postgres
tail -f /var/log/postgresql/postgresql-8.4-main.log
Each database will have slightly different procedure. In the database logs you'll see not only the raw SQL, but any connection setup or transaction overhead django is placing on the system.
每个数据库的程序略有不同。在数据库日志中，您不仅会看到原始 SQL，还会看到 django 放置在系统上的任何连接设置或事务开销。
回答by jgabrielsk8
q = Query.objects.values('val1','val2','val_etc')
print q.query
回答by googletorp
Though you can do it with with the code supplied, I find that using the debug toolbar app is a great tool to show queries. You can download it from github here.
虽然您可以使用提供的代码来完成，但我发现使用调试工具栏应用程序是一个很好的显示查询的工具。你可以从这里的github 下载它。
This gives you the option to show all the queries ran on a given page along with the time to query took. It also sums up the number of queries on a page along with total time for a quick review. This is a great tool, when you want to look at what the Django ORM does behind the scenes. It also have a lot of other nice features, that you can use if you like.
这使您可以选择显示在给定页面上运行的所有查询以及查询所用的时间。它还总结了页面上的查询数量以及快速查看的总时间。这是一个很棒的工具，当您想查看 Django ORM 在幕后所做的事情时。它还具有许多其他不错的功能，您可以根据需要使用它们。
回答by Overclocked
Another option, see logging options in settings.py described by this post
另一种选择，请参阅本文描述的 settings.py 中的日志记录选项
http://dabapps.com/blog/logging-sql-queries-django-13/
http://dabapps.com/blog/logging-sql-queries-django-13/
debug_toolbar slows down each page load on your dev server, logging does not so it's faster. Outputs can be dumped to console or file, so the UI is not as nice. But for views with lots of SQLs, it can take a long time to debug and optimize the SQLs through debug_toolbar since each page load is so slow.
debug_toolbar 会减慢开发服务器上的每个页面加载速度，日志记录不会因此速度更快。输出可以转储到控制台或文件，因此 UI 不是很好。但是对于包含大量 SQL 的视图，通过 debug_toolbar 调试和优化 SQL 可能需要很长时间，因为每个页面加载都很慢。
回答by Mike Howsden
If you make sure your settings.py file has:
如果你确定你的 settings.py 文件有：
django.core.context_processors.debuglisted inCONTEXT_PROCESSORSDEBUG=True- your
IPin theINTERNAL_IPStuple
django.core.context_processors.debug列于CONTEXT_PROCESSORSDEBUG=True- 你
IP在INTERNAL_IPS元组中
Then you should have access to the sql_queriesvariable. I append a footer to each page that looks like this:
那么您应该可以访问该sql_queries变量。我在每个页面上附加一个页脚，如下所示：
{%if sql_queries %}
<div class="footNav">
<h2>Queries</h2>
<p>
{{ sql_queries|length }} Quer{{ sql_queries|pluralize:"y,ies" }}, {{sql_time_sum}} Time
{% ifnotequal sql_queries|length 0 %}
(<span style="cursor: pointer;" onclick="var s=document.getElementById('debugQueryTable').style;s.disp\
lay=s.display=='none'?'':'none';this.innerHTML=this.innerHTML=='Show'?'Hide':'Show';">Show</span>)
{% endifnotequal %}
</p>
<table id="debugQueryTable" style="display: none;">
<col width="1"></col>
<col></col>
<col width="1"></col>
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">SQL</th>
<th scope="col">Time</th>
</tr>
</thead>
<tbody>
{% for query in sql_queries %}
<tr class="{% cycle odd,even %}">
<td>{{ forloop.counter }}</td>
<td>{{ query.sql|escape }}</td>
<td>{{ query.time }}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
{% endif %}
I got the variable sql_time_sumby adding the line
我sql_time_sum通过添加行获得了变量
context_extras['sql_time_sum'] = sum([float(q['time']) for q in connection.queries])
to the debug function in django_src/django/core/context_processors.py.
到 django_src/django/core/context_processors.py 中的调试功能。
回答by rabbit.aaron
I developed an extension for this purpose, so you can easily put a decorator on your view function and see how many queries are executed.
我为此开发了一个扩展，因此您可以轻松地在视图函数上放置一个装饰器，并查看执行了多少查询。
To install:
安装：
$ pip install django-print-sql
To use as context manager:
用作上下文管理器：
from django_print_sql import print_sql
# set `count_only` to `True` will print the number of executed SQL statements only
with print_sql(count_only=False):
# write the code you want to analyze in here,
# e.g. some complex foreign key lookup,
# or analyzing a DRF serializer's performance
for user in User.objects.all()[:10]:
user.groups.first()
To use as decorator:
用作装饰器：
from django_print_sql import print_sql_decorator
@print_sql_decorator(count_only=False) # this works on class-based views as well
def get(request):
# your view code here
回答by chander
I believe this ought to work if you are using PostgreSQL:
如果您使用 PostgreSQL，我相信这应该可行：
from django.db import connections
from app_name import models
from django.utils import timezone
# Generate a queryset, use your favorite filter, QS objects, and whatnot.
qs=models.ThisDataModel.objects.filter(user='bob',date__lte=timezone.now())
# Get a cursor tied to the default database
cursor=connections['default'].cursor()
# Get the query SQL and parameters to be passed into psycopg2, then pass
# those into mogrify to get the query that would have been sent to the backend
# and print it out. Note F-strings require python 3.6 or later.
print(f'{cursor.mogrify(*qs.query.sql_with_params())}')
