The mapping for SQLAlchemy ORM takes a lot of initial work – often too much work for just a few queries against an existing database. However, there is an extension that offers a quicker way to work with a rudimentary object model and the nice parts of ORM.
My first steps with SQLAlchemy and SQL Server ended in a lot of problems, mainly around the driver and the correct form of the connection string. Let us look what you need to successfully connect to SQL Server.
The right index can boost the performance of your SQL query enormously while constrains help you to enforce the integrity of your data in the database. Let us look how SQLAlchemy can help us with both. I made my examples for SQLAlchemy ORM, but they work in Core as well.
SQLAlchemy and Alembic work on top of an abstraction layer and not directly on your database. This may lead to a situation in which a common feature in SQL is not implemented by your specific database system. Let us look what we must do before we can work with constraints in SQLite.
SQLAlchemy and Alembic are a great combination to keep your database in sync with your application as we saw in the last post. But what do you do when you already created some tables with SQLAlchemy that now contain data we cannot lose? Let us look how we can fix this without much extra work.
SQLAlchemy does not modify existing tables. What can we do to keep our database in sync with our application?
SQLAlchemy ORM does a lot of magic in the background when we ask it to fetch data from our database. It is time to take a closer look how SQLAlchemy loads our data and what can go wrong if we are not careful.
The higher abstraction of SQLAlchemy ORM allows us to work with our objects as we usually do in Python. We do not need to think much about tables and relationships – SQLAlchemy ORM does this work for us.
SQLAlchemy ORM offers us the same basic relationship patterns as the Core part. However, with a little bit more code we can create navigation properties that makes working with our objects much simpler.
When we access our database, we most often only want to work with a few specific rows and not the whole table. Let us look what syntax SQLAlchemy ORM uses to filter our rows.