A guest post by Garry Polley, a Python developer devoted to creating remarkable apps for the healthcare industry, and the creator of an online mathematics curriculum called GeckoMathematics. Education is a thriving passion for Garry and it’s what he would do if he were not a developer.
The Django Object Relational Mapper (ORM) helps bridge the gap between the database and our code. In this post I plan to give a very brief overview of what the ORM is and how to use it. The meat of this post, however, will be in how you can customize and extend the ORM to create specialized SQL. Finally, I’ll leave you with a few points on why it’s good to be careful when going outside the standard ORM. Let’s get started.
What Does the ORM get me?
First and foremost, the ORM will help encapsulate logic to your application rather than your database. Keeping application logic at the ORM layer helps ensure code is easy to explain and maintain. A central place of logic removes the guessing game of how an application works.
The ORM can also help to validate the data your DB is returning or having inserted into it. This validation step is critical for any application that cares about data integrity and database security. The built-in type checking of a DB is nice, but the Django ORM helps to apply the business rules around your data before it ever gets to the database. Furthermore, when the data is returned from the DB, the ORM helps ensure you get values that are expected.
MyModelClass.objects.all() is a very simple example of how to use the ORM. This code will return the entire contents of the database table. This is the same as running:
select * from MyModelTable. There are many other modifiers for queries/querysets that can be used, however, that is not the focus of this article. You’ll want to be very familiar with how the ORM works before you start to generate custom SQL with it.
Why would I Customize the ORM?
Well, there are a few cases I’ve seen in action. The first is when there is no obvious way to generate the query you’re thinking about. This, however, is not a good assumption to work under. If you come from the land of SQL, try to think of your data in terms of objects and object relations rather than tables. This may lead to a good solution for the query. For example a lot of SQL users may want to write this:
ON ("user_user"."id" = "address_address"."user_id")
IN (hello, world) AND "address_address"."street" = main )
If the data is modeled correctly, the corresponding Python code is simply:
User.objects.filter(name__in=['hello', 'world'], address__street='main')
For SQL-oriented users, I recommend always taking a step back. For the strong Python/Django users, I recommend remembering what exactly is getting created from a SQL standpoint. This leads to the other, more correct, reason to extend custom SQL: having a very specific logic or performance gain that the ORM will not give alone.
How do I Customize Queries?
The first option for customizing queries, that is well documented, is using the
extra method. Django gives great examples in their documentation. I’m going to cover a good use case for using
raw, which is also documented. However, the existing examples are all fairly trivial. I want to empower you to take advantage of advanced database features within the ORM.
For example, let’s say you have a heavy data application that needs to show some aggregate values, and require a large amount of custom SQL to create. Step one, you’ll create a stored procedure that encapsulates that query, and a SQL function that makes calling that query easy (you’ll also have to setup a type table, but that’s all standard SQL stuff). For performance reasons perhaps you’ve moved this logic to the DB, so your app now can only manage the business logic after the data is aggregated.
Let’s also say you have a SQL function called
aggregate_profile_metrics that’s helping your application have super fast performance. Currently with the standard ORM, you’re out of luck. A SQL function is not usable, since you can only use
tables. Enter in the power of the
raw query. So first, let’s define the model:
avg_posts_per_day = models.IntegerField()
avg_comments_per_day = models.IntegerField()
avg_respondees_per_day = models.IntegerField()
managed = False
db_table = 'fake_table_name' # This is the trickier part
Notice this model is all about data, which we could get doing a lot of aggregate calls across all of our different tables. As a database gets sufficiently large enough, however, this does not scale when you have 10s of tables and millions of users. Since these numbers will most likely be constantly updated, a SQL function will be very performant. So now comes the interesting part, you can achieve calling the SQL function fairly easily by doing:
query = "select avg_posts_per_day, avg_comments_per_day, avg_respondees_per_day
profile_metrics = ProfileMetric.objects.raw(query, )
With that query you’ve now called a SQL function and it was fairly painless. Note that the SQL function takes a parameter of
user_id to get data and that we are not putting parameter values in the query directly. If your SQL function does not take parameters, you should be using a database view. With this approach you can now use the
profile_metrics the same as you would a normal queryset, with some minor differences (Read the difference between a RawQueryset and a normal Queryset).
Beyond this point, Django also allows users to call the DB directly without interfacing to a model. I strongly urge against ever doing this. There are very few reasons to need to call the DB without a model in mind. In the cases that those exist, I urge extreme caution. As soon as non-model based raw queries occur, the power of the ORM is lost. At that point it doesn’t make much sense to be using Django.
.raw based SQL keep these points in mind:
- Make sure the query can’t be accomplished using the existing QuerySet API.
- Ensure there is a need to create custom SQL (e.g. performance gains of SQL functions, or building on a legacy application).
- Use the
.rawfrom the QuerySet to create your custom SQL call.
- To never use
curser.executefrom the connection directly.
By this point you know that Django has a fairly powerful ORM. Keep in mind that the ORM is powerful when you venture down the path of SQL statements and commands. Follow these recommendations on when to use raw SQL and you should be okay:
- Don’t use raw SQL (if you can help it).
- Use the object relationships built into the ORM.
- Try to use
rawas a last resort.
For more details about Django, see the Safari Books Online resources referenced below.
Not a subscriber? Sign up for a free trial.
Safari Books Online has the content you need
|Introduction to Django is a video where you’ll learn the fundamentals of development with Django, generate a Django data model, and put together a simple web site using the framework.|
|Pro Django, Second Edition goes above and beyond other books, leaving the basics far behind and showing how Django can do things even its core developers never expected. By drawing on the dynamic nature of Python, you can write Django applications that stretch the limits of what you thought possible!|
|Instant Django 1.5 Application Development Starter will get you learning Django by creating a web application step by step. Offering you a quick but comprehensive immersion into the world of Python development, this book begins with a practical exploration of the framework’s powerful mechanisms and tools and finishes your journey by taking you through the creation of one sample application.|
About the author
|Garry Polley is a Python developer devoted to creating remarkable apps for the healthcare industry. Before joining his current employer, Garry helped to modify and create an online mathematics curriculum called GeckoMathematics. Education is a thriving passion for Garry and it’s what he would do if he were not a developer. He can be reached at garrypolley.com.|