The “Dumb” Way to Do Database Queries in Views

Just as in earlier chapter, detailed a “dumb” way to produce output within a view (by hard-coding the text directly within the view), there’s a “dumb” way to retrieve data from a database in a view. It’s simple: just use any existing Python library to execute an SQL query and do something with the results. In this example view, we use the MySQLdb library to connect to a MySQL database, retrieve some records, and feed them to a template for display as a web page:

from django.shortcuts import render
import MySQLdb

def book_list(request):
db = MySQLdb.connect(user='me', db='mydb', passwd='secret', host='localhost')
cursor = db.cursor()
cursor.execute('SELECT name FROM books ORDER BY name')
names = [row[0] for row in cursor.fetchall()]
db.close()
return render(request, 'book_list.html', {'names': names})

This approach works, but some problems should jump out at you immediately:

• We’re hard-coding the database connection parameters. Ideally, these parameters would be stored in the Django configuration.
• We’re having to write a fair bit of boilerplate code: creating a connection, creating a cursor, executing a statement, and closing the connection. Ideally, all we’d have to do is specify which results we wanted.
• It ties us to MySQL. If, down the road, we switch from MySQL to PostgreSQL, we’ll most likely have to rewrite a large amount of our code. Ideally, the database server we’re using would be abstracted, so that a database server change could be made in a single place. (This feature is particularly relevant if you’re building an open-source Django application that you want to be used by as many people as possible.)

As you might expect, Django’s database layer solves these problems.

Back to Tutorial