If you are Python developer and you work with SQL databases, then SQLAlchemy is most likely a library you are familiar with. It’s a powerful, yet flexible toolkit for working with SQL in Python with lots of features. Some of these features like ORM and basic queries are common knowledge, but there are quite a few features you might not know about and should definitely be taking advantage of. So, let’s se how to leverage things like hybrid properties, nested queries, table metadata, dialects and more!
Let’s start simple. I think it’s pretty common that you might want to create mapped attribute based on other columns — essentially creating computed column. The simplest example would be string concatenation:
This is nice, but it’s much more useful when we use SQL expressions to create such attribute:
For the example above we added a little bit more code. We created
CreditCard class which has many-to-one relationship with
User. This user - on top of the columns and attributes from first example - has also column property named
has_credit_card, which is computed by checking whether credit card with users ID exists.
One thing you should be mindful of though when using this feature is that column properties won’t be populated before you commit the session, which might be unexpected when working with freshly created record:
To follow up on the previous tip, let me also show you hybrid properties. They are similar to column properties in the sense that they produce computed attributes. Hybrid properties however, produce value from Python expression on instance level and SQL expression on class level. Little confusing? Alright, let’s see an example:
To show off the capabilities of
hybrid_property, we implement simple relationship between
Order, where each user has list of orders which have
.state - in this case either Pending or Complete. Now, if we want to find out whether user has any Pending orders, we need to think of 2 cases - If we are working with rows that were already loaded into Python objects, then we can just use Python expression and produce Python value (
has_pending_orders(self)). If we are on the other hand querying this information directly from database, we can't use Python expression as database engine won't understand it. Therefore, for this case (
has_pending_orders(cls)) we write SQL expression, that can be ran against the database.
As a side note — if your expression is same for both Python and SQL evaluation, then you can omit the second function decorated with
.expression and SQLAlchemy will use the first one for both cases.
One of my favourite features are Mixin classes. Mixins aren’t something specific only to SQLAlchemy, but they are especially useful in conjunction with ORM models. Quite often you might run into situation, where you have multiple classes (models) that require same attribute or same
classmethod. One such example is
User model below:
In this example, we have 2 Mixin classes from which the
User model inherits. First of them -
MixinAsDict provides method
as_dict(self), that can be used to get
dict representation of the model. The other one
MixinGetByUsername provides both
username column as well as static method for querying users by their username.
Defining these functions as Mixins allows us to make them reusable and add them to other models without copy-pasting same code everywhere.
If you don’t want to write all the Mixins yourself, then you can take a look at https://github.com/absent1706/sqlalchemy-mixins which is a collection of common SQLAlchemy Mixins.
Working with Metadata
Sometimes you might need to access table column names, check constraints on the table or maybe check if columns is nullable. All of this can be done with
The important part here are the
Some of your database tables might require a bit more extensive initial setup. For example — you might want to include a few check constraints, indexes or specify different schema:
All of these things can be configured using
__table_args__ class attribute. Here, we setup 2 check constraints, 1 index for ID column and foreign key constraint. We also turn on automatic table extensions, which means that if we add columns to this table after it was created, then it will be automatically added. Lastly, we also specify to which schema this table belongs to.
Using Custom Dialects
Every database engine has some custom features, which you might want to make use of. For me — as a PostgreSQL user — I would like to use some of the custom column types that PostgreSQL has. So how would one use those with SQLAlchemy?
The code above shows one
Example table that has PostgreSQL
ARRAY columns. All of these and more can be imported from
Creating rows that include values of these types is pretty self-explanatory. When it comes to querying them though, you will need to use the dialect and type specific comparators as shown above with PostgreSQL
ARRAY type and
For other types like
JSON you might be able to get away with just comparing them as text (using
To make your life easier when creating these queries, I recommend setting
echo=True when creating engine, which will make SQLAchemy print all SQL queries into console, so that you can check whether your code actually produces correct queries.
All of the dialects, their types and comparators are documented at https://docs.sqlalchemy.org/en/13/dialects/.
Full-text Search with PostgreSQL
While on the topic of PostgreSQL features. What about the full-text search with
tsvector? We can do that with SQLAchemy too:
Once again we create Mixin class for full-text search, as this is something that a lot of models can use. This Mixin has single static method, which takes search string and column to search in (
field). To do the actual search we use
func.to_tsvector to which we pass language and reference to tables column. On that, we chain call to
.match function which really is a call to
to_tsquery in PostgreSQL and we give it search string and search configuration as arguments.
From the generated SQL we can see that the Python code really produces correct SQL queries.
Tracking Last Update on Rows
updated_at column is pretty common practice. This can be done very simply with SQLAlchemy:
updated_at you just need to set
func.now() which will make it so that every time the row is updated, this column will be set to current timestamp. As for the
created_at column, you can omit the
onupdate argument and instead use
server_default which sets the function that is called when row is created.
It’s not uncommon to have recursive/self-referential relations in database — whether it’s manager -> employee relationship, tree structures or some materialized path. This tip shows how you can setup this kind of relationship using SQLAlchemy:
For this example we use tree structure created using
Node records. Each node has some
data, reference to its parent and list of its children. As a convenience method we also include
__repr__ to help us visualize the tree little better.
If you are fine with normal one-to-many relationship, then you can do it the same way as for any non-self-referential relationship. To make it work for bi-directional relationships however, you need to also include the
remote_side=[id] as shown above.
Binding Multiple Databases with Flask
The last one is for all the Flask users. If you ever need to connect to multiple databases — for example, because of multiple geographies or multiple data sources — then you can use
SQLALCHEMY_BINDS to specify extra database binds:
In the code snippet above we configure default database by setting
SQLALCHEMY_DATABASE_URI and alternative binds in
SQLALCHEMY_BINDS. With this configuration, all the above databases will be available to us. Next, we set
__bind_key__ of a table to refer to one of the binds, so that whenever we interact with this particular table, SQLAlchemy will know which database to connect to.
If you, however, need to connect to multiple DBs with same tables/schema, you can use multiple engines and sessions — one for each database and switch between them as you wish, like so:
Hopefully, at least a few of these tips and tricks shown here will be useful to you and will make your life just a little bit easier next time you need work with SQLAlchemy. This article is definitely not an exhaustive list of all the cool things you can do with SQLAlchemy and you can find a bunch of useful things just by scrolling through SQLAlchemy API reference.