Python Friday #80: Creating the Foundation for SQLAlchemy ORM

The Object Relational Mapper (ORM) part of SQLAlchemy offers us a way to persist data in a database without the need to write our own SQL statements. Let us look how that works and what rules we need to follow.

This post is part of my journey to learn Python. You can find the other parts of this series here. You find the code for this post in my PythonFriday repository on GitHub

 

Big thanks to TalkPython

What I know about SQLAlchemy ORM I got from Michael Kennedy’s course Building Data-Driven Web Apps with Flask and SQLAlchemy at TalkPython.fm. It is a great course to learn Flask & SQLAlchemy and I can highly recommend it.

In my posts on SQLAlchemy ORM I use a few code examples from the course Git repository that helped me a lot in my Python projects.

 

A bit of structure for our code

We need a place where we can put all the files we need for the ORM (like the connection setup, the mapping classes and so on). To do this, we create a data folder and put all the scripts there:

 

Create a base class

The base class wires everything up and helps the ORM to map our classes to the tables in our database. SQLAlchemy has a declarative way that lets us create our base class with this code:

A base class belongs to one database. If our application talks to multiple databases, we need multiple base classes.

 

A model for each table

For all our classes that we want to persist in the database, we need to create a model. This model has all the fields we need and the metadata for SQLAlchemy in the same place. As with our first steps in SQLAlchemy Core, we start with the Employee table of the Northwind database:

Our Employee class inherits from our base class ModelBase and maps to the table Employee. The table mapping goes through the __tablename__ field what gives us great flexibility with naming our class. Nevertheless, try to name your models the same way you name your tables – or at least similar. Otherwise you need to open your model to figure out to what table it maps.

The mapping for the columns is similar to the mapping we did with in SQLAlchemy Core. For a more consistent experience to the documentation in the web I prefixed all SQLAlchemy objects with sa.

The database probably has its own naming convention, which is different from Python’s. If this is the case, we can use the first argument in sa.Column() to tell SQLAlchemy which column it should map our property to. That lets us use first_name in Python and keep the database column at FirstName.

The __repr__(self) method is not strictly necessary but it will help us a lot when we print our objects to the console.

 

One file to catch all models

SQLAlchemy ORM can only work with models it knows about. Do yourself a favour and create a __all_models.py file that contains imports for all your models. This allows you to import this one file whenever the ORM part needs to know your models.

 

Session factory

The ORM part of SQLAlchemy works with a session. That session does a lot more than the connection and we need a single place to put all the logic that goes into creating a session factory that we then can use in our application:

The global_init() method gets the SQLite database filename as a parameter. If we already created a factory instance, it takes it and does not spend time to create another one. Otherwise it makes sure that we have a valid name for our database, creates an engine and maps that engine to the sessionmaker factory.

The last two lines load all our models and create the database tables when they do not yet exist. It uses a different syntax to SQLAlchemy Core, where we created our bookstore database from metadata.

 

Our application

To simulate an application, we can create a Python script next to the data folder (NOT inside). In our script we need a bit of glue code to initialise our session factory:

We need to call this method somewhere at the beginning of our application before we try to work with the database:

With the setup in place, we can now create an employee and set its values. To persist our employee, we get a session from our factory, add the employee and call commit() on the session:

When we run our script, it will create a session factory, create the database with the Employee table and inserts a row. All that without any SQL statements written by us.

 

Next

The ORM part needs a lot of preparation, but we can take all this code and put it into a real application without any change. Next week we can use our configuration and explore CRUD operations in SQLAlchemy ORM.