Stored Procedures in Django- ChrisUmbel.com

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.

Database

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

Model

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.

Execution

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'])

Conclusion

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.

Created on 2009-08-09 18:08:37 UTC

Digg it

Delicous

Facebook

2 Comments -

Comment Feed - Permalink

Do you have an example of where you can't avoid stored procs? I can't think of any except when your using a shared-db legacy application, which is not the case when your using Django.

by Paco on 2009-08-10 14:08:11 UTC

Darn close.  In my case I'm conditionally performing some write operations via other procedures (essentially logging and transaction tracking) in a third-party second database 

/* if some condition is met */
call OtherDatabase.LogSearch(...);

and conditionally union-ing my query results with a table in that second database;  

/* if some condition is met */
select title, url from searcher_document 
union
select title, url from OtherDatabase.documents;

It's a definite edge-case.

by chrisumbel on 2009-08-10 16:08:19 UTC