Create a simple table using SQLAlchemy and enable autogeneration by Alembic

The purpose of this article is to share some basics of SQLAlchemy and Alembic via a small project. The project is developed on Mac and using Python version 3.8. As you can see, I use the terminal in the project. I prefer working using a python virtual environment for different projects.

This is the initial structure of the project:

Project initial structure

In data/models.py , I defined a simple class calledUser. SQLAlchemy will map it to the project database:

This is the __init__.py:

The Session and the engine objects are typically at the module level. In __init__.py line 12, Base.metadata.create_all(engine) will create user table stored in the metadata when the data package is imported. It will not attempt to recreate tables that already exist in the target database.

My db.py looks like this:

In db.py line 23, the items in the dictionary data are unpacked using ** and passed to User() as keyword arguments. In line 42, I’m using setattr() to assign the value to the attribute of the User. You may question why I didn’t put session.commit() in the functions. The reason is that I prefer doing it in the upper function, which calls the CrudTable class.

I use pytest for the test. In the folder of tests , my package_test.py looks like this:

In line 38, by using "with Session.begin() as s:" , all the transactions will be committed as well as the Session will be closed at the end of the block. You can read the official document here.

My test data is located in test_data.json :

Located at the root of this project, conftest.py is an empty file used to help pytest find the data module. You can find a detailed explanation here.

Now, in my terminal, I run the test by:

(venv) $ cd tests && pytest

Notice that, the app.db is created after the test.

Now, I want to enable the autogeneration of the Table using Alembic. Before doing so, I comment theBase.metadata.create_all(engine) in the __init__.py , as this line of code will generate the table automatically, which will lead to some violation with the Alembic autogeneration feature.

Then I start using the alembic by:

(venv) $ pip install alembic
(venv) $ alembic init alembic

After that, a folder named alembic and a file named alembic.ini are created:

Notice that the versions folder is empty right now. Then I modify the sqlalchemy.url located in the alembic.ini to the URL of the app.db. After that, by following the official documentation, I make two changes in the alembic/env.py :

from data.models import Base
target_metadata = Base.metadata

Now, it is time to autogenerate the table in the database by:

(venv) $ alembic revision --autogenerate -m "Added user table"

You will see the message in the terminal like this:

We can then view our file ec7d02f185ab_added_user_table.py in the versions folder and see that a rudimentary migration is already present:

Right now, the table is not created in the database. To do so, we need to use the upgradeupgrade command:

(venv) $ alembic upgrade head

After that, you will see the message like this:

The table is added successfully into the database. If you want to remove the table from the database, you can just run:

(venv) $ alembic downgrade -1

You can find the code of this project over here. I hope this project is helpful.

Reference:

SQLAlchemy 1.4 Documentation