是否有一种方法可以在执行查询时显示Django正在运行的SQL ?
当前回答
为CREATE / UPDATE / DELETE /命令生成SQL,这在Django中是即时的
from django.db.models import sql
def generate_update_sql(queryset, update_kwargs):
"""Converts queryset with update_kwargs
like : queryset.update(**update_kwargs) to UPDATE SQL"""
query = queryset.query.clone(sql.UpdateQuery)
query.add_update_values(update_kwargs)
compiler = query.get_compiler(queryset.db)
sql, params = compiler.as_sql()
return sql % params
from django.db.models import sql
def generate_delete_sql(queryset):
"""Converts select queryset to DELETE SQL """
query = queryset.query.chain(sql.DeleteQuery)
compiler = query.get_compiler(queryset.db)
sql, params = compiler.as_sql()
return sql % params
from django.db.models import sql
def generate_create_sql(model, model_data):
"""Converts queryset with create_kwargs
like if was: queryset.create(**create_kwargs) to SQL CREATE"""
not_saved_instance = model(**model_data)
not_saved_instance._for_write = True
query = sql.InsertQuery(model)
fields = [f for f in model._meta.local_concrete_fields if not isinstance(f, AutoField)]
query.insert_values(fields, [not_saved_instance], raw=False)
compiler = query.get_compiler(model.objects.db)
sql, params = compiler.as_sql()[0]
return sql % params
测试和使用
def test_generate_update_sql_with_F(self):
qs = Event.objects.all()
update_kwargs = dict(description=F('slug'))
result = generate_update_sql(qs, update_kwargs)
sql = "UPDATE `api_event` SET `description` = `api_event`.`slug`"
self.assertEqual(sql, result)
def test_generate_create_sql(self):
result = generate_create_sql(Event, dict(slug='a', app='b', model='c', action='e'))
sql = "INSERT INTO `api_event` (`slug`, `app`, `model`, `action`, `action_type`, `description`) VALUES (a, b, c, e, , )"
self.assertEqual(sql, result)
其他回答
如果你确保你的settings.py文件有:
django.core.context_processors.debug中列出的 DEBUG = True INTERNAL_IPS元组中的IP
然后您应该可以访问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 %}
通过添加一行,我得到了变量sql_time_sum
context_extras['sql_time_sum'] = sum([float(q['time']) for q in connection.queries])
到django_src/django/core/context_processors.py中的debug函数。
使用django.db.connection.queries查看查询
from django.db import connection
print(connection.queries)
访问QuerySet对象上的原始SQL查询
qs = MyModel.objects.all()
print(qs.query)
如果您需要为一些自定义SQL重用查询,还有另一种非常有用的方法。我曾在一个分析应用程序中使用过这种方法,它远远超出了Django的ORM所能轻松完成的范围,所以我将ORM生成的SQL作为子查询包含进来。
from django.db import connection
from myapp.models import SomeModel
queryset = SomeModel.objects.filter(foo='bar')
sql_query, params = queryset.query.as_sql(None, connection)
这将为您提供带有占位符的SQL,以及可以使用的带有查询参数的元组。你可以直接把它传递给DB:
with connection.connection.cursor(cursor_factory=DictCursor) as cursor:
cursor.execute(sql_query, params)
data = cursor.fetchall()
你可以使用连接。在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
然后,把reset_queries()放在Person.objects.select_for_update()之后,如果你想只得到UPDATE和DELETE查询,而没有INSERT和SELECT FOR UPDATE查询,如下所示:
# "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
我相信这应该工作,如果你使用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())}')
推荐文章
- 数据类vs类型。NamedTuple主要用例
- 如何从macOS完全卸载蟒蛇
- 是否有可能键入提示一个lambda函数?
- 'dict'对象没有has_key属性
- 使用Pandas groupby连接来自几行的字符串
- Pandas:给定列的数据帧行之和
- 如何避免在为Python项目构建Docker映像时重新安装包?
- 如何激活蟒蛇环境
- 省略[…]意思是在一个列表里?
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 将表从一个数据库复制到另一个数据库的最简单方法?
- 为什么我得到“'str'对象没有属性'读取'”当尝试使用' json。载入字符串?
- 不区分大小写的列表排序,没有降低结果?
- 排序后的语法(key=lambda:…)