Raw SQL queries in Django views

Question

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?

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 

use WHERE clause to filter vote for yes:

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

Answer 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