Raw SQL queries in Django views

Solution 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

use WHERE clause to filter vote for yes:

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

Solution 2:

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

Solution 3:

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
from app.models import Picture

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

Solution 4:

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

Solution 5:

Raw sql with the specific database name:

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

database_name = Any database created by us

table_name = Any table name created by us