Raw SQL queries in Django views

How would I perform the following using raw SQL in views.py?

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)
    )

What would this results function look like?

>>> 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

use WHERE clause to filter vote for yes:

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

The Django Documentation is really really good. You have basically two options to execute raw SQL. You can use Manager.raw() to perform raw queries which return model instances, or you can avoid the model layer and execute custom SQL directly.

Using the raw() manager:

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

If you want to bypass the model layer directly you can use django.db.connection which represents the default database 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

It can be done within one query if you are using PostgreSQL. If not, you can change the query accordingly and get the results.

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

Raw sql with the specific database name:

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

You Can try this

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 ")

raw() method can be used to perform raw sql queries that return model instances ..see docs

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

if you might perform queries that don't map cleanly to models .. django.db.connection represents default database connection so call connection.cursor() to use database connection. see docs

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

For example, you have Person model as shown below:

# "store/models.py"

from django.db import models

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

Then, you can run the raw SQL query with cursor.execute() and get the result with cursor.fetchall() and if there is no more result, cursor.fetchall() returns [] as shown below. *The documentation explains more about it:

# "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")

Output on console:

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

And, you can also use cursor.fetchone() to get the result and if there is no more result, cursor.fetchone() returns None as shown below:

# "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")

Output on console:

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

And, you can also use transaction as shown below:

# "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")

Or:

# "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")

Or:

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

Then, transaction is run according to these PostgreSQL logs below. *I used PostgreSQL and my answer explains how to log PostgreSQL queries:

[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