Tutorial, Python, Code Generation, Flask, Database

Generating Flask-SQLAlchemy Models with flask-sqlacodegen

I'm adding this post more for posterity and my own forgetfulness, because it's one of those things I don't use often enough to remember how to do it each time, but it is super helpful when I do need it.

Kamil Sindi has created a fork of the sqlacodegen package originally created by Alex Gronholm. This package allows you to generate models for use in your SQLAlchemy applications.

Sindi's fork, flask-sqlacodegen, adds support for Flask-SQLAlchemy applications, which is exactly what the controller of my home automation assistant, T.I.T.U.S., is built upon.

This package is super simple to install with pip by running a standard install command:

pip install flask-sqlacodegen

Of course, Sindi also provided instructions in his GitHub repo's README on how to install without pip, if you'd prefer to go that route:

git clone https://github.com/ksindi/flask-sqlacodegen.git 
cd flask-sqlacodegen/ 
python setup.py install

Once installed, you just call the flask-sqlacodegen app directly via command-line and give it whatever arguments you need. For me, I always specify the --flask flag to use Flask-SQLAlchemy columns, as well as the --outfile flag to tell the code generator where to dump the models (usually a file called 'models.py').

Other than those two flags, all I do is give it a connection string to the database and let it go to work:

flask-sqlacodegen --flask --outfile models.py mysql+pymysql://user-name:password@host:port/db-schema

And just F Your I, this command will generate models for ALL of the tables in your database, which is probably what you want when you run this for the first time.

However, you probably don't want this to happen on subsequent runs, so another flag you can pass the app is the --tables flag followed by a comma-separated list of the tables for which you want models generated.

(NOTE: This flag is super strict, so make sure your table names are wrapped in quotes and there are no spaces before/after the commas):

flask-sqlacodegen --flask --outfile models.py --tables "Schedule,ScheduleType,Task,TaskParameter,Workflow,WorkflowSchedule,WorkflowTask" mysql+pymysql://user-name:password@host:port/db-schema

Another forewarning I want to throw your way is that sometimes the column names get a little jacked up, especially when they end with 's'. I'm not sure if there's some logic behind the scenes that is treating these as plural names and is trying to handle them differently, but I just wanted to give you a heads up that you might have to tweak some things a bit after the models are generated. They tend to be minor tweaks, though, and the time savings you get from running this generator far outweigh the hiccups.

One final note, and this is really just a reminder for myself, is that if you install the flask-sqlacodegen package in a virtual environment using something like virtualenv, you will need to activate it before trying to run the generator. Otherwise, your command prompt will have no idea what command you are trying to run without specifying the full path. :-)

Enjoy!

Author image

About Tony Thorsen

Father of two, husband of one, Maker of many things. Tinkerer, dreamer, pixel nudger.