Model and Diagram Any Database Using SQLAlchemy

I needed to model and diagram (ERD) a client’s database schema in order to understand their machine learning task. They don’t use Django, so I can’t just inspectdb and graph_models. But fortunately, sqlalchemy makes both of these tasks easy.

It will work on any database that sqlalchemy knows how to deal with (many more supported databases than Django’s ORM). Look, Ma, no Django!

Autocoding Your SQLAlchemy Models (inspectdb)

The sqlautocode package creates files (an sqlalchemy schema) for any database. Unfortunately there’s an incompatibility with the latest sqlalchemy, but [a patch] and fork fixed that up. So you’ll want to download this fork rather than installing from pypi or the official mercurial repo on Here’s the bash trifecta that’ll install it an model your database:

hg clone
pip install sqlautocode
sqlautocode postgres:// -o

Diagramming Your Database (graph_models)

Unless sqlalchemy has fixed things, you’ll need a special pyparsing version for pydot to play nice with sqlalchemy:

pip uninstall pyparsing
pip install -Iv pydot

To diagram a PostrgreSQL database:

from sqlalchemy_schemadisplay import create_schema_graph
connection = "postgres://"
graph = create_schema_graph(metadata=MetaData(connection), show_datatypes=False, show_indexes=False, rankdir='LR', concentrate=False)

I’ll post an example once I have need to model an open-data database. They are really quite well-done. The layout optimizer leaves just enough white space and the edges/relationships are untangled into a presentable/readable form.


ERD: Entity-Relationship Diagram – a block diagram of your DB tables and their connections (relationships)

Written on April 27, 2015