Web development platforms like Django go a long way to removing the need for writing hand-crafted SQL with all of their ORM goodness. Sometimes, for some reason or another, it's handy to execute hand-crafted SQL code or even a stored procedure. Naturally that should be a last resort and I don't intend on starting a discussion about when it is and when is not appropriate, but for those situations where it is this article applies.
To illustrate how to do this I'll start out with the following MySQL table (the remainder of the post assumes it's filled with sample data). It'll sit underneath the the Document class in an application named "searcher". I'm sort of working backwards here as I've actually generated this table from a model I've included later in the article.
CREATE TABLE searcher_document ( id integer AUTO_INCREMENT NOT NULL PRIMARY KEY, url varchar(900) NOT NULL, content longtext NOT NULL, title longtext NOT NULL );
Then, just to give us something fun to do, I'll slap a fulltext index on the content and title columns:
CREATE FULLTEXT INDEX FIX ON searcher_document(content, title);
Now I need a stored procedure to call. Here's a simple procedure that performs a fulltext query on the table I just created. In reality I'd probably only employ a procedure for a somewhat more complex task but it'll work for demonstrative purposes.
CREATE PROCEDURE searcher_document_search( search_string varchar(255) ) BEGIN SELECT id, url, content, title FROM searcher_document WHERE MATCH(content, title) AGAINST (search_string); END
With the database work out of the way I'm ready to define my model:
from django.db import models from django.db import connection # Create your models here. class Document(models.Model): # fields url = models.CharField(max_length=900) content = models.TextField() title = models.TextField() # static method to perform a fulltext search @staticmethod def search(search_string): # create a cursor cur = connection.cursor() # execute the stored procedure passing in # search_string as a parameter cur.callproc('searcher_document_search', [search_string,]) # grab the results results = cur.fetchall() cur.close() # wrap the results up into Document domain objects return [Document(*row) for row in results]
And that's about it, the comments tell the tale. I created a cursor and employed its callproc method to execute my procedure. Then I wrapped the results up into a list of Document objects.
With the business end of this out of the way I can now perform searches in a view as follows:
results = Document.search(request.GET['search_string'])
Like I said before, when developing web applications in ORM based frameworks such as Django there's almost always a way to avoid hand-crafting SQL or using stored procedures. In those situations where it can't be avoided or is inadvisable it's nice to know that you still have the option.
Sun Aug 09 2009 18:08:37 GMT+0000 (UTC)