Django 原始 SQL 查询 - 循环结果，它为每次迭代执行一个查询

发布于 2024-11-08 12:35:05 字数 679 浏览 0 评论 0原文

在注意到 Django 的一些内置查询效率低下后，我开始编写一些原始 SQL 查询。我正在尝试循环遍历 QuerySet 结果并将它们分组（我知道 regroup 模板标记，这对我不起作用 - 我需要能够独立访问不同的组）。这是我的代码：

m = Media.objects.raw('SELECT * FROM table') # query simplified for sake of example

media_items = {'aim-icons' : [], 'banners' : [], 'hi-res-photos' : [], 'photos' : [], 'print-ads' : [], 'videos' : [], 'wallpapers' : [] }

for item in m:
    media_items[item.type_slug].append(item)

这给了我想要的东西（例如，我可以像 media_items['wallpapers'] 一样访问的列表），但它为每次迭代运行数据库查询以获取 type_slug< /代码> 字段。我尝试在循环之前添加 m = list(m) ，但没有效果。

有人可以帮我吗？这看起来应该很简单。

谢谢，马特

原文

Been writing some raw SQL queries after noticing how inefficient some of Django's built-in queries were. I'm trying to loop through the QuerySet result and group them into categories (I'm aware of the regroup template tag, this doesn't work for me - I need to be able to access the separate groups independently). Here's my code:

m = Media.objects.raw('SELECT * FROM table') # query simplified for sake of example

media_items = {'aim-icons' : [], 'banners' : [], 'hi-res-photos' : [], 'photos' : [], 'print-ads' : [], 'videos' : [], 'wallpapers' : [] }

for item in m:
    media_items[item.type_slug].append(item)

This gives me what I want (eg a list that I can access like media_items['wallpapers']) but it runs a database query for every iteration to fetch the type_slug field. I tried adding m = list(m) before the loop, no effect.

Can anyone help me out here? This seems like it should be simple.

Thanks,
Matt

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

感性不性感 2024-11-15 12:35:05

编辑：

问题在这里分解为 Django 的 raw() 方法如何工作。它返回模型实例（其中具有您正在访问的属性，导致额外的查询）。

这里合适的工具是 connection.cursor()、cursor.execute() 和 cursor.fetchall()。这是文档中的示例：

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

http:// /docs.djangoproject.com/en/dev/topics/db/sql/#executing-custom-sql-directly

Edit:

The issue breaks down here to how Django's raw() method works. It returns model instances (which had properties you were accessing, resulting in the extra query).

The proper tools here are connection.cursor(), cursor.execute() and cursor.fetchall(). Here's the example from the docs:

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

http://docs.djangoproject.com/en/dev/topics/db/sql/#executing-custom-sql-directly

回复收藏 0 原文

~没有更多了~