SQL Relationships With SQLAlchemy

How manually configurations make querying easier

A couple of weeks ago, I wrote an article about leveraging the power of Entity Framework to minimize having to do complicated Linq queries. Essentially, the solution was to create configurations for each table in the database and then define the relationship between the tables within the configuration. It wasn’t exactly an easy task. In fact, it was a little confusing. But it got me thinking if something similar could be accomplished using SQLAlchemy for Python.

The Situation

In our scenario, we have a database containing three tables, Application, ApplicationRunner, and ApplicationLog. The Application table contains a foreign key called ApplicationRunnerId. This key creates a one-to-one relationship between the Application and ApplicationRunner tables. As for the ApplicationLog table, it holds a foreign key to the Application table specifying a one-to-many relationship.

Taking a look at our current code, all of the heavy lifting of determining table attributes and relationships is being automatically handled by SQLAlchemy.

As for getting the data, we have to either manually query each table, or use a join method. For the sake of simplicity, we are manually querying each table.

What Can Be Done Differently

Instead of being lazy and letting SQLAlchemy do all the hard work, we can take more of a code-first approach and create manual configurations for our tables.

Before we can start modifying the table classes, there are a few extra things we need to import. The first will allow us to define different types for the table attributes, while the second gives us the functionality to create relationships between tables.

from sqlalchemy import Column, ForeignKey, Integer, String, Numeric, DateTime, ForeignKey, CHAR, Table
from sqlalchemy.orm import relationship

Once imported, we can start working on the Application table by defining all the attributes and setting a primary key. The attribute types will match what the field type is in the database (INT will be Integer, VARCHAR will be String, etc.).

class Application(Base):
     __tablename__ = "Application"
     ApplicationId = Column(Integer, primary_key=True)
     ApplicationName = Column(String(100), nullable=False)

Next, the ApplicationRunner table.

class ApplicationRunner(Base):
     __tablename__ = "ApplicationRunner"
     ApplicationRunnerId = Column(Integer, primary_key=True)
     ApplicationRunnerName = Column(String(50), nullable=False)

Before going any further, we need to create a one-to-one relationship between these two tables. To start off, we will need to add a foreign key to the Application table.

ApplicationRunnerId = Column(Integer, ForeignKey("ApplicationRunner.ApplicationRunnerId"))

Then, we will add a new class attribute to create a relationship with the ApplicationRunner table. This relationship is special because we will utilize lazy joined loading. Essentially, lazy loading is the objects that are returned from a query without their related objects loaded in. Once the related objects are referenced, another SELECT statement will be run to load in the requested object. As for the “joined”, this adds a JOIN to the initial SELECT to keep everything in the same result set.

Runner = relationship("ApplicationRunner", lazy="joined")

The next thing we need to do is add a relationship to the ApplicationRunner table. While doing so, we need to make sure this relationship will back reference the ApplicationRunner table and that it does not need a list of objects since it's a one-to-one relationship.

ApplicationRelationship = relationship("Application", backref="ApplicationRunner", uselist=False)

Now that we have these tables defined and configured, we can move on to the ApplicationLog table. Just like we did with the previous two tables, the attributes and primary key need to be defined first.

class ApplicationLog(Base):
     ApplicationLogId - Column(Integer, primary_key=True)
     ApplicationLogMessage - Column(String(250), nullable=False)

Again, in order to create the relationship, a foreign key is needed in this new table.

ApplicationId = Column(Integer, ForeignKey("Application.ApplicationId"))

Lastly, the one-to-many relationship will look like this for the ApplicationLog table.

ApplicationRelationship = relationship("Application", backref="ApplicationLog", uselist=True)

But for the application table, it will look like this.

Log = relationship(“ApplicationLog”, lazy=”joined”)

With everything finally set up, we can run a query to get our data.

application = session.query(Application).filter(Application.ApplicationId == command.ApplicationId).one()

Getting data from the Application table can be easily done.

print(application.the_attribute)

But, if you’re looking to access the data from the ApplicationRunner table:

print(application.ApplicationRunner.the_attribute)

Finally, to view the records from the ApplicationLog table, a FOR loop will be needed.

for item in application.ApplicationLog:
     print(item.the_attribute)

Pros vs. Cons

Right off the bat, you’ll notice that setting up the configurations takes way longer than having SQLAlchemy automatically handle it for you. Just like in Entity Framework, it can also be a little confusing/complicated to get things up and running. However, this process wasn’t all bad. By configuring the tables through a code-first view, it removes some of the black box that SQLAlchemy can be and give more power back to the developer. With that being said though, it does create more mystery around the querying side since you can’t see the extra joins or selects.

Closing Thoughts

All in all, I thought this was a very enlightening and fun experience. It definitely answered my questions regarding SQLAlchemy’s abilities. However, at the same time, it also brought up some new questions about performance. As someone who writes a lot of APIs where fast requests are a necessity, would this kind of query and configuring be advisable. Let me know what your thoughts are about SQLAlchemy and configuring table relationships within it. In a future post, I plan on exploring these performance questions. Until the next time, happy coding and cheers!

Read all my articles for free with my weekly newsletter, thanks!

Want to read all articles on Medium? Become a Medium member today!

Check out some of my recent articles:

References: