Raw SQL queries in Django views
DjangoDjango ModelsDjango ViewsDjango QuerysetDjango Problem Overview
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?
Django Solutions
Solution 1 - Django
>>> 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 - Django
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 - Django
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 - Django
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 - Django
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
Solution 6 - Django
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