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 manage.py inspectdb and manage.py 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 models.py 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 code.google.com. Here’s the bash trifecta that’ll install it an model your database:

hg clone https://bitbucket.org/x746e/sqlautocode
pip install sqlautocode
sqlautocode postgres://username:password@host.domain.com/dewey -o models.py

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 https://pypi.python.org/packages/source/p/pyparsing/pyparsing-1.5.7.tar.gz#md5=9be0fcdcc595199c646ab317c1d9a709 pydot

To diagram a PostrgreSQL database:

from sqlalchemy_schemadisplay import create_schema_graph
connection = "postgres://username:password@host.server.url.com/database_name"
graph = create_schema_graph(metadata=MetaData(connection), show_datatypes=False, show_indexes=False, rankdir='LR', concentrate=False)
graph.write_png('images/database_schema_diagram.png')

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.

Glossary

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