Readers of this blog and Coder Radio listeners will now that I have fallen for the snake and by that I of course mean Python! Typing that out cracks my little Ruby heart but for reasons that I’ve explained at length on the show The Mad Botter has moved to Python as its primary programming language over the last year or so; that of course comes with a healthy does of C + + and a not-so-healthy dose of Javascript. SQLAlchemy is the standard ORM toolkit for Python programs and it can be a little intimidating, especially for folks who haven’t done much database development. It also, like just about every ORM, is laden with pitfalls for developers who haven’t deal with even medium-scale database-powered applications. Once you get some of the basics down, these three tips should help you avoid those pitfalls.
But first, we need to get the code down. I am not going to produce every single line of glue / config code you need to get this working but this is based on a real FastAPI Python application and generally valid Python. First we configure a database; your particular configuration will vary.
# app.db.database from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine from sqlalchemy.orm import declarative_base, sessionmaker Base = declarative_base() # Do your specific DB setup here
Once that’s all setup we can create our model class! Being a dude of a certain age, I am a big fan of Nintendo and GameFreak’s Pokémon as readers of this site probably know. Part of that is pure love of the old games but a lot of it is that the original Pokémon games are actually pretty perfect examples of database-powered applications; in fact, if you really think about it, they are basically a database with some gameplay wrapped around it. So here we go! Let’s write code ‘em all!
from sqlalchemy.orm import deferred
from sqlalchemy.sql.sqltypes import LargeBinary
from sqlalchemy.util.langhelpers import hybridproperty
from app.db.database import Base
from sqlalchemy import Column, Integer, String
class Pokemon(Base):
__tablename__ = 'pokemon'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
attack_points = Column(Integer, nullable=False, default=10)
hit_points = Column(Integer, nullable=False, default=100)
current_hit_points = Column(Integer, nullable=False, default=100)
image = deferred(Column(LargeBinary))
@hybridproperty
def fainted(self):
if self.hit_points - self.current_hit_points < 1:
return True
else:
return False
Here we have a reasonable if not extremely naive implementation of a database-backed Pokémon class! It makes me want to turn my hat backward and send a yellow electric mouse into battle!
So let’s get to it! Hybrid Properties I choose you!
Hybrid Properties: OK, so I am cheating a little here! I wrote a post about this some months ago, but Hybrid Properties are just so useful I had to include them here. Basically, they allow you to use the result of an expression as though it were a set property on an instance of a model object. If you call them via an espression on a class you will get a SQL query, but that’s a little more advanced for the purposes of this post. Looking at our example, we can write: pikachu.fainted and the fainted method will be called on the model assuming that pikachu is an instance of the Pokemon class. At that point the calculation will be done on the fly and return the correct result. It’s important to note that we are calling this as a property; it’s .fainted not .fainted(). Hybrid Properties is one the most powerful SQLAlchemy features that is simple to use but is often overlooked by new Python developers. You may find that this straightforward feature can simplify your model logic and possibly eliminate unnecessary database queries; I’ve seen some scary stuff….
Deferred Columns: Since the days of the original iOS SDK, list views with images have been the bane of my existence for many reasons, some of which particular to the first iOS SDK and hardware, but the primary problem is that images were and are large; in fact, with our high-resolution smart-phone cameras they are getting larger year over year, making our punishment all the more severe.
Clearly, we need to keep Tom Hardy at bay. The way we do that is not loading that image data until we actually need to use it. By using deferred on the image column it simply won’t load that data until we do something like: pikachu.image, thus keeping the talented Mr Hardy at bay for at least a little longer. A word of caution, however, it’s a common early-learner mistake to over-defer columns. The cruel irony is that over-using this may actually make your application slower. A good rule of thumb is to only defer loading on datatypes that tend to be or become large like images or other media.
Load Only: Where deferred excludes loading a column, load_only selectively includes columns. For instance, if we wanted to just get a list of names out f all the Pokémon in the database we could simply do: Pokemon.query.options(load_only(Pokemon.name)).all(). Again, it’s a simple tactic, but it can save you a ton of unneeded database query and therefore help to avoid data-access bottlenecks in your applications.
There are plenty of other ways to optimize your SQLAlchemy code in your Python applications and I plan to share a few more of them in the coming months. Speaking of Python apps, checkout my recent data comprehension and migration tool Alice! Also, find me on Twitter and let me know what you think of this post, thanks!
