Django视图中的原始SQL查询

Question

我该如何在 views.py 中使用原始SQL执行以下操作？

from app.models import Picture

def results(request):
    all = Picture.objects.all()
    yes = Picture.objects.filter(vote='yes').count()
    return render_to_response(
        'results.html', 
        {'picture':picture, 'all':all, 'yes': yes}, 
        context_instance=RequestContext(request)
    )

这个 results 函数会是什么样子？

- David542

---

为什么你会想要在查询中使用原生 SQL 而不是过滤和计数？ - dting

没有实际的理由需要处理这个案例，只是想看看/知道如何处理它。 - David542

Answer 1

>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute('''SELECT count(*) FROM people_person''')
1L
>>> row = cursor.fetchone()
>>> print row
(12L,)
>>> Person.objects.all().count()
12

使用WHERE子句来过滤投票为“是”的内容：

>>> cursor.execute('''SELECT count(*) FROM people_person WHERE vote = "yes"''')
1L

- dting

---

非常有用！谢谢！我使用了“rows = cursor.fetchall()”。 - mescarra

Answer 2

Django文档非常好。你基本上有两个选项来执行原生SQL。你可以使用Manager.raw()来执行返回模型实例的原始查询，或者你可以避免使用模型层并直接执行自定义SQL。

使用raw()管理器：

>>> for p in Person.objects.raw('SELECT * FROM myapp_person'):
...     print p
John Smith
Jane Jones

如果您想直接绕过模型层，可以使用 django.db.connection，它表示默认的数据库连接：

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

- Zach Kelling

---

+1 很好的解释，您能否使用原始数据返回计数？ - dting

不，它返回一个RawQuerySet，仅在您想要返回模型实例时才有用。如果要执行count(*)或任何其他不想返回模型实例的操作，最好只使用django.db.connection。 - Zach Kelling

不同意关于Django文档的观点。 - java-addict301

Answer 3

如果你使用的是PostgreSQL，那么可以通过一次查询完成。否则，你需要相应地更改查询以获得结果。

from django.db import connection

def results(request):
    with connection.cursor() as cursor:
        query = """
        SELECT count(*) as all_count, 
        count(*) FILTER(WHERE vote = 'yes') as yes_count
        FROM people_person;
        """
        cursor.execute(query)
        row = cursor.fetchone()
        all_count, yes_count = row

- Aditya

Answer 4

使用特定数据库名称的原始SQL语句：

from django.db import connections
cursor = connections['default'].cursor()
cursor.execute("select * from table_name")
print(cursor.fetchall())

# manually close the cursor if you are done!
cursor.close()

database_name = Any database created by us
table_name = Any table name created by us

- santhosh_dj

Answer 5

你可以尝试这个。

Picture.objects.raw("SELECT 1 as id ,"\
 "(SELECT  count(*) as yes FROM people_person WHERE vote='yes') as yes ,"\
 "(SELECT  count(*) FROM people_person WHERE vote='no') as no ,"\
 "(SELECT  count(*) FROM people_person WHERE vote='all') as all ")

- Tinashe Robert

---

这是一个脆弱的技巧，它只能使用整数主键（例如不支持uuid）工作。 - minusf

当我使用这种运行原始SQL的方式时，我得到了NameError: name 'MyModel' is not defined。你如何在Django ORM的上下文中运行它？ - snso

Answer 6

raw()方法可以用于执行返回模型实例的原始 SQL 查询.. 查看文档

books = Book.objects.raw('SELECT id,name,pages FROM app_books WHERE pages>100')

如果您可能执行的查询与模型不匹配，可以使用connection.cursor()调用django.db.connection表示默认数据库连接。 请参阅文档

from django.db import connection
def my_custom_sql(self):
    with connection.cursor() as cursor:
        cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
        cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
        row = cursor.fetchone()

    return row

- omar ahmed

Answer 7

例如，您有如下所示的“人员（Person）”模型：Person model。

# "store/models.py"

from django.db import models

class Person(models.Model):
    name = models.CharField(max_length=30)

然后，您可以使用cursor.execute()运行原始SQL查询，并使用cursor.fetchall()获取结果，如果没有更多结果，则cursor.fetchall()将返回[]，如下所示。 *文档中对此进行了更详细的说明：

# "store/views.py"

from django.db import connection
from django.http import HttpResponse

def test(request):
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM store_person;") # Here

        for row in cursor.fetchall(): # Here
            print(row)

        print(cursor.fetchall()) # []

    return HttpResponse("Test")

控制台输出：

(1, 'Tom')
(2, 'David')
(3, 'Lisa')
[]

此外，您还可以使用cursor.fetchone()来获取结果。如果没有更多的结果，cursor.fetchone()将返回None，如下所示：

# "store/views.py"

from django.db import connection
from django.http import HttpResponse

def test(request):
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM store_person;") # Here

        print(cursor.fetchone()) # (1, 'Tom')
        print(cursor.fetchone()) # (2, 'David')
        print(cursor.fetchone()) # (3, 'Lisa')
        print(cursor.fetchone()) # None

    return HttpResponse("Test")

在控制台上输出：

(1, 'Tom')
(2, 'David')
(3, 'Lisa')
None

而且，您还可以像下面这样使用事务(transaction)：

# "store/views.py"

from django.db import transaction
from django.db import connection
from django.http import HttpResponse

@transaction.atomic # Here
def test(request):
    with connection.cursor() as cursor:
        cursor.execute('''SELECT * FROM store_person;''')

        for row in cursor.fetchall():
            print(row)

    return HttpResponse("Test")

或者：

# "store/views.py"

from django.db import transaction
from django.db import connection
from django.http import HttpResponse

def test(request):
    with transaction.atomic(): # Here
        with connection.cursor() as cursor:
            cursor.execute('''SELECT * FROM store_person;''')

            for row in cursor.fetchall():
                print(row)

    return HttpResponse("Test")

或者：

DATABASES = {
    'default':{
        'ENGINE':'django.db.backends.postgresql',
        'NAME':'postgres',
        'USER':'postgres',
        'PASSWORD':'admin',
        'HOST':'localhost',
        'PORT':'5432',
        'ATOMIC_REQUESTS': True, # Here
    },
}

然后，根据以下PostgreSQL日志运行事务。*我使用PostgreSQL，并且我的回答解释了如何记录PostgreSQL查询：

[21200] LOG:  duration: 0.008 ms  statement: BEGIN
[21200] LOG:  duration: 1.232 ms  statement: SELECT * FROM store_person;
[21200] LOG:  duration: 0.024 ms  statement: COMMIT

- Kai - Kazuya Ito