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.