- Django-QuerySet-Cheatsheet by @chrisdl and @briandant is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License. The contributors are as gods among ants and shall forever be remembered as such. The Django web framework referenced in the Django-QuerySet-Cheatsheet is © 2005-2018 Django Software Foundation.
- Quickly find your Bootstrap classes on this interactive Bootstrap cheat sheet. It includes code samples and live preview of elements.
- Django ORM Optimization Tips. Caveats:. Only use optimizations that obfuscate the code if you need to. Not all of these tips are hard and fast rules. Use your judgement to determine what improvements are appropriate for your code.
- . Only use optimizations that obfuscate the code if you need to. Not all of these tips are hard and fast rules. Use your judgement to determine what improvements are appropriate for your code. Be aware of QuerySet's lazy evaluation.
Django management commands are scripts that can be executed on your Django project to do something with the project database, media files, or code. Django itself comes with a bunch of commands like: migrate, runserver, collectstatic, makemessages, and clearsessions. Management commands can be executed like this.
An object-relational mapper (ORM) is a code library that automates thetransfer of data stored in relational database tables into objects thatare more commonly used in application code.
Why are ORMs useful?
ORMs provide a high-level abstraction upon arelational database that allows a developer to writePython code instead of SQL to create, read, update and delete data andschemas in their database. Developers can use the programming language theyare comfortable with to work with a database instead of writing SQLstatements or stored procedures.
For example, without an ORM a developer would write the following SQLstatement to retrieve every row in the USERS table where the
zip_code
column is 94107:The equivalent Django ORM query would instead look likethe following Python code:
The ability to write Python code instead of SQL can speed up web applicationdevelopment, especially at the beginning of a project. Typescript cheat sheet react. The potentialdevelopment speed boost comes from not having to switch from Python codeinto writing declarative paradigm SQL statements. While some softwaredevelopers may not mind switching back and forth between languages, it'stypically easier to knock out a prototype or start a web application usinga single programming language.
ORMs also make it theoretically possible to switch an application betweenvarious relational databases. For example, a developer could useSQLite forlocal development and MySQL in production. A productionapplication could be switched from MySQL to PostgreSQL withminimal code modifications.
In practice however, it's best to use the same database for local developmentas is used in production. Otherwise unexpected errors could hit in productionthat were not seen in a local development environment. Also, it's rare thata project would switch from one database in production to another one unlessthere was a pressing reason.
While you're learning about ORMs you should also read up on deployment and check out the application dependencies page.
Do I have to use an ORM for my web application?
Python ORM libraries are not required for accessing relationaldatabases. In fact, the low-level access is typically provided by anotherlibrary called a database connector, such aspsycopg (for PostgreSQL)or MySQL-python (forMySQL). Take a look at the table below which shows how ORMs can work withdifferent web frameworks and connectors and relational databases.
The above table shows for example that SQLAlchemy can work with varyingweb frameworks and database connectors. Developers can also use ORMs withouta web framework, such as when creating a data analysis tool or a batchscript without a user interface.
What are the downsides of using an ORM?
There are numerous downsides of ORMs, including
- Impedance mismatch
- Potential for reduced performance
- Shifting complexity from the database into the application code
Impedance mismatch
The phrase 'impedance mismatch' is commonly used in conjunction with ORMs.Impedance mismatch is a catch-all term for the difficulties that occur whenmoving data between relational tables and application objects. The gistis that the way a developer uses objects is different from how data isstored and joined in relational tables.
This article on ORM impedance mismatchdoes a solid job of explaing what the concept is at a high level andprovides diagrams to visualize why the problem occurs.
Potential for reduced performance
One of the concerns that's associated with any higher-level abstraction orframework is potential for reduced performance. With ORMs, the performancehit comes from the translation of application code into a corresponding SQLstatement which may not be tuned properly.
ORMs are also often easy to try but difficult to master. For example, abeginner using Django might not know about the
select_related()
functionand how it can improve some queries' foreign key relationship performance.There are dozens of performance tips and tricks for every ORM. It's possiblethat investing time in learning those quirks may be better spent justlearning SQL and how to write stored procedures.There's a lot of hand-waving 'may or may not' and 'potential for' in thissection. In large projects ORMs are good enough for roughly 80-90% of usecases but in 10-20% of a project's database interactions there can bemajor performance improvements by having a knowledgeable databaseadministrator write tuned SQL statements to replace the ORM's generatedSQL code.
Shifting complexity from the database into the app code
The code for working with an application's data has to live somewhere. BeforeORMs were common, database stored procedures were used to encapsulate thedatabase logic. With an ORM, the data manipulation code instead lives withinthe application's Python codebase. The addition of data handling logic in thecodebase generally isn't an issue with a sound application design, but it doesincrease the total amount of Python code instead of splitting code betweenthe application and the database stored procedures.
Python ORM Implementations
There are numerous ORM implementations written in Python, including
- Tortoise ORM(source code)
![Django Orm Cheat Sheet Django Orm Cheat Sheet](/uploads/1/3/7/3/137376670/584308837.jpg)
There are other ORMs, such as Canonical'sStorm, but most of them do not appear tocurrently be under active development. Learn more about the major activeORMs below.
Django's ORM
The Django web framework comes withits own built-in object-relational mapping module,generally referred to as 'the Django ORM' or 'Django's ORM'.
Django's ORM works wellfor simple and medium-complexity database operations. However, there are oftencomplaints that the ORM makes complex queries much more complicated thanwriting straight SQL or using SQLAlchemy.
It is technically possible to drop down to SQL but it ties the queries to aspecific database implementation. The ORM is coupled closely with Django soreplacing the default ORM with SQLAlchemy is currently a hack workaround.Note though it is possible that swappable ORM backends will be possiblein the future as it is now possible to change thetemplate engine for rendering output in Django.
Since the majority of Django projects are tied to the default ORM, it isbest to read up on advanced use cases and tools for doing your best workwithin the existing framework.
SQLAlchemy ORM
SQLAlchemy is a well-regardedPython ORM because it gets the abstraction level 'just right' andseems to make complex database queries easier to write than the DjangoORM in most cases. There is an entire page on SQLAlchemythat you should read if you want to learn more about using the library.
Peewee ORM
Peewee is a Python ORMimplementation that is written to be'simpler, smaller and more hackable'than SQLAlchemy. Read the full Peewee page for moreinformation on the Python ORM implementation.
Pony
Pony ORM is another Python ORM available asopen source, under the Apache 2.0 license.
SQLObject ORM
SQLObject is an ORM that has been under activeopen source development for over 14 years, sincebefore 2003.
Schema migrations
Schema migrations, for example when you need to add a new column to anexisting table in your database, are not technically part of ORMs. However,since ORMs typically lead to a hands-off approach to the database (at thedevelopers peril in many cases), libraries to perform schema migrationsoften go hand-in-hand with Python ORM usage on web application projects.
Django Orm Get
Database schema migrations are a complex topic and deserve their own page.For now, we'll lump schema migration resources under ORM links below.
General ORM resources
- This detailed overview of ORMsis a generic description of how ORMs work and how to use them.
- This example GitHub projectimplements the same Flask application with several different ORMs:SQLAlchemy, Peewee, MongoEngine, stdnet and PonyORM.
- Martin Fowler addresses theORM hatein an essay about how ORMs are often misused but that they do providebenefits to developers.
- The Rise and Fall of Object Relational Mappingis a talk on the history of ORMs that doesn't shy away from somecontroversy. Overall I found the critique of conceptual ideas worththe time it took to read the presentation slides and companion text.
- If you're confused about the difference between a connector, such asMySQL-python and an ORM like SQLAlchemy, read thisStackOverflow answeron the topic.
- What ORMs have taught me: just learn SQLis another angle in the ORM versus embedded SQL / stored procedures debate.The author's conclusion is that while working with ORMs such as SQLAlchemyand Hibernate (a Java-based ORM) can save time up front there are issuesas a project evolves such as partial objects and schema redundancies.I think the author makes some valid points that some ORMs can be a shakyfoundation for extremely complicated database-backed applications. However,I disagree with the overriding conclusion to eschew ORMs in favor ofstored procedures. Stored procedures have their own issues and there areno perfect solutions, but I personally prefer using an ORM at the startof almost every project even if it later needs to be replaced withdirect SQL queries.
- The Vietnam of Computer Scienceprovides the perspective from Ted Neward, the originator of the phrase'Object/relational mapping is the Vietnam of Computer Science' that hefirst spoke about in 2004. The gist of the argument against ORMs iscaptured in Ted's quote that an ORM 'represents a quagmire which startswell, gets more complicated as time passes, and before long entraps itsusers in a commitment that has no clear demarcation point, no clearwin conditions, and no clear exit strategy.' There are follow up posts onCoding Horrorand another one from Ted entitledthoughts on Vietnam commentary.
- Turning the Tables: How to Get Along with your Object-Relational Mappercoins the funny but insightful phrase 'database denial' to describe howsome ORMs provide a usage model that can cause more issues than theysolve over straight SQL queries. The post then goes into much more detailabout the problems that can arise and how to mitigate or avoid them.
SQLAlchemy and Peewee resources
A comprehensive list of SQLAlchemy andPeewee ORM resources can be found on their respectivepages.
Django ORM links
A curated list of resources can be found on the dedicatedDjango ORM resources page.
Pony ORM resources
All Pony ORM resources are listed on the dedicatedPony ORM page.
SQLObject resources
SQLObject has been around for a long time as an open source project butunfortunately there are not that many tutorials for it. The followingtalks and posts will get you started. If you take an interest in the projectand write additional resources, file anissue ticketso we can get them added to this list.
- This post onObject-Relational Mapping with SQLObjectexplains the concept behind ORMs and shows the Python code for how theycan be used.
- Ian Bicking presented on SQLObject back in 2004 with a talk onSQLObject and Database Programming in Python.
- Connecting databases to Python with SQLObjectis an older post but still relevant with getting started basics.
What would you like to learn about building Python web apps?
What're these NoSQL data stores hipster developers keep talking about?
When it comes to optimizing an ORM, your greatest weapon is your understanding of how your ORM works under the hood (at least at a high level). This makes understanding all of the rules and guidelines for creating a speedy application much easier. Therefore, I highly recommend reading through the Django docs on the subject at least once. My goal for this post is to condense these tips and tricks into an easily-referenceable compilation, as well as adding some of my own. Also check out my Django ORM Optimization cheat sheet for an even more condensed version.
Caveats
Before blindly following any of the below guidance, keep the following in mind.
- Only use optimizations that obfuscate or complicate the code if it is absolutely necessary. Prioritize readability and maintainability where possible.
- Not all of these tips are hard-and-fast rules. Use your judgement to determine what improvements make sense for your code.
Django ORM Optimization Tips
1. Profile
- Use django-debug-toolbar and QuerySet.explain() to help determine the bottlenecks in your code.
![Django Orm Cheat Sheet Django Orm Cheat Sheet](/uploads/1/3/7/3/137376670/297787399.png)
2. Be aware of QuerySet’s lazy evaluation.
Django Orm Cheat Sheet
Perhaps the most important aspect of the Django ORM to understand is how QuerySets work. Since QuerySets are lazily-evaluated, you can chain
filter()
and exclude()
all day without actually hitting the database. Look out for this in order to evaluate QuerySets only when you actually need to.When QuerySets are evaluated:
When QuerySets are not cached:
When QuerySets are cached:
3. Be aware of which model attributes are not cached.
When Django evaluates a QuerySet, foreign-key relationships and reverse relationships are not included in the query, and thus not included in the cache, unless specified otherwise.
4. Use select_related()
and prefetch_related()
when you will need foreign-key/reverse related objects.
These tools tell Django that you actually will need these objects, so that it will go ahead and query and cache them for you. The common pitfall here is to not use these when they are needed. This results in a lot of unnecessary database queries.
5. Try to avoid database queries in a loop.
This is something you will most likely run into, as trying to write clean code can often result in this pitfall. Using
get()
or evaluating a QuerySet in a loop can be very bad for performance. Instead, do what you can to do the database work before entering the loop.Here is a contrived example:
6. Use iterator()
to iterate through a very large QuerySet only once.
If you know your QuerySet could be very large, and you only need to iterate over it once, it makes sense to eliminate usage of the cache in order to preserve memory and other overhead.
iterator()
provides just this ability.7. Do work in the database rather than in Python.
Your database can do almost anything data-related much faster than Python can. If at all possible, do your work in the database. Django provides many tools to make this possible.
Use
filter()
and exclude()
for filtering:Use F expressions:
Do aggregation in the database:
8. Use values()
and values_list()
to get only the things you need.
values()
and values_list()
provide lists, dictionaries, or tuples evaluating only the fields you specify.Use
values()
:Django Orm Tutorial
Use
values_list()
:9. Use defer()
and only()
when you only need certain fields.
Caveats:
- Use these in favor of
values()
when you need a QuerySet instead of a list of dicts. - May only make a difference if the fields you are excluding require a lot of processing to be converted to a Python object.
Use
defer()
:Django Orm Count
Use
only()
:10. Use count()
and exists()
when you don’t need the contents of the QuerySet.
Caveats:
- Only use these when you don’t need to evaluate the QuerySet for other reasons.
Use
count()
:Use
exists()
:11. Use delete()
and update()
when possible.
Instead of updating model instances one at a time,
delete()
and update()
allow you to do this in bulk.Use
delete()
:Use
update()
:12. Use bulk_create()
when possible.
Caveats:
- This works a bit differently than calling
create()
. - Read more about it in the Django docs.
Similarly, bulk-add to many-to-many fields:
13. Use foreign key values directly.
The Django ORM automatically retrieves and caches foreign keys, so use them instead of causing a needless database query.
Closing Remarks
Using just these 13 tips, you can resolve most bottlenecks with the Django ORM. For a more condensed version, see my Django ORM Optimization cheat sheet.