alexis-benoist/eralchemy

Entity Relation Diagrams generation tool

Entity relation diagrams generator

ERAlchemy generates Entity Relation (ER) diagram (like the one below) from databases or from SQLAlchemy models.

Example

Example for NewsMeme

Quick Start

Install on a mac

The simplest way to install eralchemy on OSX is by using Homebrew

$ brew install eralchemy

Install

To install ERAlchemy, just do:

$ pip install eralchemy

ERAlchemy requires GraphViz to generate the graphs and Python. Both are available for Windows, Mac and Linux.

Usage from Command Line

From a database

$ eralchemy -i sqlite:///relative/path/to/db.db -o erd_from_sqlite.pdf

The database is specified as a SQLAlchemy database url.

From a markdown file.

$ curl 'https://raw.githubusercontent.com/Alexis-benoist/eralchemy/master/example/newsmeme.er' > markdown_file.er
$ eralchemy -i 'markdown_file.er' -o erd_from_markdown_file.pdf

From a Postgresql DB to a markdown file excluding tables named temp and audit

$ eralchemy -i 'postgresql+psycopg2://username:password@hostname:5432/databasename' -o filtered.er --exclude-tables temp audit

From a Postgresql DB to a markdown file excluding columns named created_at and updated_at from all tables

$ eralchemy -i 'postgresql+psycopg2://username:password@hostname:5432/databasename' -o filtered.er --exclude-columns created_at updated_at

From a Postgresql DB to a markdown file for the schema schema

$ eralchemy -i 'postgresql+psycopg2://username:password@hostname:5432/databasename' -s schema

Usage from Python

from eralchemy import render_er
## Draw from SQLAlchemy base
render_er(Base, 'erd_from_sqlalchemy.png')

## Draw from database
render_er("sqlite:///relative/path/to/db.db", 'erd_from_sqlite.png')

Architecture

Thanks to it's modular architecture, it can be connected to other ORMs/ODMs/OGMs/O*Ms.

Contribute

Every feedback is welcome on the GitHub issues.

To run the tests, use : $ py.test. Some tests require a local postgres database with a schema named test in a database named test all owned by a user named postgres with a password of postgres.

All tested PR are welcome.

Notes

ERAlchemy was inspired by erd, though it is able to render the ER diagram directly from the database and not just only from the ER markup language.

Released under an Apache License 2.0

Creator: Alexis Benoist Alexis_Benoist

eralchemy's Issues

Make it possible to print the schema with landscape orientation

It would be nice to add a feature for changing the orientation of the print layout.

Depending on the schema in fact sometimes a landscape orientation could look nicer and user would be able to do a test of the print and choose the best option for their schema,

fix versionining, please release 1.5+

Cheers! Today I created release monitoring entry [1] and I noticed that the latest released version of eralchemy is v1.4.0, but according to github page - the latest seems to be 1.2.10.
To not confuse anyone, it would be nice to not continue with 1.2 serie, but bump the version right to 1.5.0+

[1] https://release-monitoring.org/project/21897/

Upgrading SQLAlchemy can break ERAlchemy

As of this date, the latest release of SQLAlchemy is 1.4.37. This release of SQLAlchemy causes the render_er function to throw an exception. I'm guessing a number of releases earlier than this one will probably have the same effect. The latest release of SQLAlchemy that successfully creates the ER diagram that I have used is 1.3.24.

My recommendation: pip install SQLAlchemy==1.3.24 Either this or this library has to be updated which I may have to do anyway for the sake of our customized version of JupyterLab. Don't know yet if downgrading SQLAlchemy will cause a problem for another Python library, but I am sure my users will let me know soon enough.

Restrict to tables

Hi,

I'm trying to generate graph for several tables out of large db model.
Is there anyway to specify table/model names to eralchemy renderer?
I'm asking basically if I can restricts graph without using exclude, when I want to graph just several tables outside large db?

Thanks

Fresh Homebrew can't find `psycopg2`

Environment

macOS 10.14.6

$ brew doctor
Your system is ready to brew.
$ brew --version
Homebrew 2.1.11

Steps to reproduce

1. Install eralchemy via Homebrew
2. Run eralchemy w/ postgresql+pscyopg2 url
3. Enjoy cool new ERD pdf

Expected Result

Cool new ERD pdf

Actual Result

Errors relating to psycopg2:

LAPT-redactedusername-MAC:source:$ brew install eralchemy
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 1 tap (homebrew/cask).
No changes to formulae.

==> Downloading https://homebrew.bintray.com/bottles/eralchemy-1.2.10_2.mojave.bottle.tar.gz
Already downloaded: /Users/redactedusername/Library/Caches/Homebrew/downloads/138ced36c6b040547d8324b7a1755d9c8cc373e282ea7ac53d45165f151b1e73--eralchemy-1.2.10_2.mojave.bottle.tar.gz
==> Pouring eralchemy-1.2.10_2.mojave.bottle.tar.gz
🍺  /usr/local/Cellar/eralchemy/1.2.10_2: 784 files, 10.5MB
LAPT-redactedusername-MAC:source:$ eralchemy -i '[connection string]' -o erd.pdf
Traceback (most recent call last):
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/main.py", line 231, in render_er
    tables, relationships = all_to_intermediary(input, schema=schema)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/main.py", line 147, in all_to_intermediary
    return database_to_intermediary(filename_or_input, schema=schema)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/sqla.py", line 78, in database_to_intermediary
    engine = create_engine(database_uri)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/sqlalchemy/engine/__init__.py", line 424, in create_engine
    return strategy.create(*args, **kwargs)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 81, in create
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 584, in dbapi
    import psycopg2
ModuleNotFoundError: No module named 'psycopg2'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/bin/eralchemy", line 11, in <module>
    load_entry_point('ERAlchemy==1.2.10', 'console_scripts', 'eralchemy')()
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/main.py", line 38, in cli
    schema=args.s
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/main.py", line 238, in render_er
    module_name = e.message.split()[-1]
AttributeError: 'ModuleNotFoundError' object has no attribute 'message'
LAPT-redactedusername-MAC:source:$ pip install psycopg2-binary
Collecting psycopg2-binary
  Using cached https://files.pythonhosted.org/packages/ee/ed/2772267467ba5c21a73d37149da0b49a4343c6646d501dbb1450b492d40a/psycopg2_binary-2.8.3-cp37-cp37m-macosx_10_6_intel.macosx_10_9_intel.macosx_10_9_x86_64.macosx_10_10_intel.macosx_10_10_x86_64.whl
Installing collected packages: psycopg2-binary
Successfully installed psycopg2-binary-2.8.3
LAPT-redactedusername-MAC:source:$ eralchemy -i '[connection string]' -o erd.pdf
Traceback (most recent call last):
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/main.py", line 231, in render_er
    tables, relationships = all_to_intermediary(input, schema=schema)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/main.py", line 147, in all_to_intermediary
    return database_to_intermediary(filename_or_input, schema=schema)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/sqla.py", line 78, in database_to_intermediary
    engine = create_engine(database_uri)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/sqlalchemy/engine/__init__.py", line 424, in create_engine
    return strategy.create(*args, **kwargs)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 81, in create
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 584, in dbapi
    import psycopg2
ModuleNotFoundError: No module named 'psycopg2'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/bin/eralchemy", line 11, in <module>
    load_entry_point('ERAlchemy==1.2.10', 'console_scripts', 'eralchemy')()
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/main.py", line 38, in cli
    schema=args.s
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/main.py", line 238, in render_er
    module_name = e.message.split()[-1]
AttributeError: 'ModuleNotFoundError' object has no attribute 'message'
LAPT-redactedusername-MAC:source:$ pip install psycopg2
Collecting psycopg2
  Downloading https://files.pythonhosted.org/packages/5c/1c/6997288da181277a0c29bc39a5f9143ff20b8c99f2a7d059cfb55163e165/psycopg2-2.8.3.tar.gz (377kB)
     |████████████████████████████████| 378kB 6.2MB/s 
Building wheels for collected packages: psycopg2
  Building wheel for psycopg2 (setup.py) ... done
  Stored in directory: /Users/redactedusername/Library/Caches/pip/wheels/48/06/67/475967017d99b988421b87bf7ee5fad0dad789dc349561786b
Successfully built psycopg2
Installing collected packages: psycopg2
Successfully installed psycopg2-2.8.3
LAPT-redactedusername-MAC:source:$ eralchemy -i '[connection string]' -o erd.pdf
Traceback (most recent call last):
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/main.py", line 231, in render_er
    tables, relationships = all_to_intermediary(input, schema=schema)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/main.py", line 147, in all_to_intermediary
    return database_to_intermediary(filename_or_input, schema=schema)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/sqla.py", line 78, in database_to_intermediary
    engine = create_engine(database_uri)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/sqlalchemy/engine/__init__.py", line 424, in create_engine
    return strategy.create(*args, **kwargs)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 81, in create
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 584, in dbapi
    import psycopg2
ModuleNotFoundError: No module named 'psycopg2'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/bin/eralchemy", line 11, in <module>
    load_entry_point('ERAlchemy==1.2.10', 'console_scripts', 'eralchemy')()
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/main.py", line 38, in cli
    schema=args.s
  File "/usr/local/Cellar/eralchemy/1.2.10_2/libexec/lib/python3.7/site-packages/eralchemy/main.py", line 238, in render_er
    module_name = e.message.split()[-1]
AttributeError: 'ModuleNotFoundError' object has no attribute 'message'```

reading relationships from Postgresql database

Hello,

I wanted to create .er file based on schema in Postgresql database.
It works well for tables, but ignores relationships.

Example script

psql t1 -c "CREATE SCHEMA t1;
            CREATE TABLE  t1.parent(id INTEGER PRIMARY KEY);
            CREATE TABLE  t1.child (id INTEGER PRIMARY KEY,
                                   parent_id INTEGER REFERENCES t1.parent(id) NOT NULL)"
eralchemy -i 'postgresql+psycopg2:///t1' -s t1 -o t1.er
cat t1.er

yields output

[t1.parent]
    *id {label:"INTEGER"}
[t1.child]
    *id {label:"INTEGER"}
    parent_id {label:"INTEGER"}

ignoring FOREIGN KEY constraint. Is it intended?

I'm using PostgreSQL 9.6.10 on Ubuntu 18.04 and ERAlchemy version 1.2.10.

Spelling error

GRAPH_BEGINING

Anyone know Python and OSX can help here?

pip install psycopg2
Requirement already satisfied (use --upgrade to upgrade): psycopg2 in /usr/local/lib/python2.7/site-packages

eralchemy -i 'postgresql+psycopg2://localhost:5432/assembler_development' -s public -o foobaz.er

Please install psycopg2 using "pip install psycopg2".

So what is the magic Python people? How does this ancient incantation language work?

Proposition: allow multiple output formats

Hello,

For now, we can specify one -o filename.format parameter.
It would be useful to be able to passe multiple -o parameters, or a filename base and a list of extensions/formats, on a single run.
I find myself running multiple commands to generate SVG, PNG and PDF formats for each .er file.

Have a good day !

Jupyter notebook / IPython Magic for eralchemy

I made a start on some IPython magic for rendering eralchemy generated images in a Jupyter notebook: ipython_magic_eralchemy

It really needs factoring into eralchemy and perhaps ipython-sql somehow so that it could make use of a persistent SQL connection. (ipython-sql magic uses import sql.connection from that package to persist connections in a notebook so you only have to declare/open them once.)

Access to push to new branches / create PRs

Hi there. I think this tool looks really useful and I'd like to contribute so that it supports dbml. However, right now when I try push to the repo I'm getting access denied:

$ git push origin fix-tests
ERROR: Permission to Alexis-benoist/eralchemy.git denied to louis-vines.
fatal: Could not read from remote repository.

Please make sure you have the correct access rights
and the repository exists.

Can you give me access to contribute to the project?

cli "ER" output can't be converted to PDF because missing schema "indication".

Great tool!
I have an issue when running this tool in redshift:
pip freeze:

$pip freeze
appnope==0.1.0
backports-abc==0.4
backports.shutil-get-terminal-size==1.0.0
certifi==2016.2.28
configparser==3.5.0
decorator==3.4.0
entrypoints==0.1
ERAlchemy==1.1.0
functools32==3.2.3.post2
ipykernel==4.2.1
ipython==5.3.0
ipython-genutils==0.1.0
ipywidgets==4.1.1
Jinja2==2.9.5
jsonschema==2.5.1
jupyter==1.0.0
jupyter-client==4.2.2
jupyter-console==4.1.1
jupyter-core==4.1.0
MarkupSafe==0.23
mistune==0.7.3
nbconvert==4.2.0
nbformat==4.0.1
notebook==4.2.0
path.py==8.2.1
pathlib2==2.1.0
pexpect==3.3
pickleshare==0.5
prompt-toolkit==1.0.13
psycopg2==2.7.1
ptyprocess==0.5
Pygments==2.2.0
pygraphviz==1.3.1
pyzmq==15.2.0
qtconsole==4.1.1
simplegeneric==0.8.1
singledispatch==3.4.0.3
six==1.10.0
SQLAlchemy==1.1.10
sqlalchemy-redshift==0.6.0
terminado==0.5
tornado==4.4.2
traitlets==4.2.2
wcwidth==0.1.7

when i run
eralchemy -i 'redshift+psycopg2://user:pass@<host>:5439/db?sslmode=prefer' -o paths_goals.er -s retail -x tables i can't convert to pdf b/c there is a missing schema alias in the output.

Output in er is missing table alias:

$eralchemy -i paths_goals.er -o erd_paths_goals.pdf
ERAlchemy couldn't complete the generation due the 13 following errorsError on line 95: paths *--? retail.path_enrollments

Cannot add a relation with column "paths" which is undefined
HINT: Try to declare the tables before the relationships.

Error on line 96: paths *--? retail.path_instructor_registrations

in paths_goals.er a snippet:

[retail.foo]
    path_id {label:"CHAR(36)"}
    sequence_id {label:"CHAR(36)"}
    ordinal {label:"INTEGER"}
    lo {label:"CHAR(36)"}
bam *--? retail.bar
splok *--? retail.biz
bang *--? retail.baff
wiz *--? retail.bang
bang *--? retail.boom
pop *--? retail.pow

Note that tables are in the spec like [retail.foo] but in the "relationship section" the schema 'retail' is missing.

Missing tests

Python 3.x compatibility issue (print function)

I found some Python 3.x compatibility issue.

My environment is following

$ python --version
Python 3.6.3

-s option does not work at Python 3.x environment.

$ eralchemy -i sqlite:///example.db -s example.er 
Traceback (most recent call last):
  File "/Users/amedama/.virtualenvs/py36/bin/eralchemy", line 11, in <module>
    sys.exit(cli())
  File "/Users/amedama/.virtualenvs/py36/lib/python3.6/site-packages/eralchemy/main.py", line 29, in cli
    check_args(args)
  File "/Users/amedama/.virtualenvs/py36/lib/python3.6/site-packages/eralchemy/helpers.py", line 22, in check_args
    fail('Cannot draw ER diagram with no output file.')
  File "/Users/amedama/.virtualenvs/py36/lib/python3.6/site-packages/eralchemy/helpers.py", line 6, in fail
    print >> sys.stderr, 'Error:', message % args
TypeError: unsupported operand type(s) for >>: 'builtin_function_or_method' and '_io.TextIOWrapper'. Did you mean "print(<message>, file=<output_stream>)"?

Since print became a function at Python 3.x.

Request: Add eralchemy to nixpkgs

I'm sadly not doing any work with Python, but it would be very cool if anybody is able to add eralchemy to Nixpkgs.

Different things (just as question):
Is it possible to change the font size and lowercase the types in the output image?
Is it possible to color the table name background in any way?

relation cardinality labels drawn in reverse position

Example model:

from sqlalchemy import Column, Integer, String, ForeignKey                                                                                                                                                   
from sqlalchemy.ext.declarative import declarative_base                                                                                                                                                      
from sqlalchemy.orm import relationship                                                                                                                                                                      
                                                                                                                                                                                                             
from eralchemy import render_er                                                                                                                                                                              
                                                                                                                                                                                                             
Base = declarative_base()                                                                                                                                                                                    
                                                                                                                                                                                                             
class Person(Base):                                                                                                                                                                                          
    __tablename__ = 'person'                                                                                                                                                                                 
    name = Column(String(255), primary_key=True)                                                                                                                                                             
    height = Column(Integer)                                                                                                                                                                                 
    weight = Column(Integer)                                                                                                                                                                                 
    birth_location_id = Column(ForeignKey('location.id'))                                                                                                                                                    
    location = relationship('Location')                                                                                                                                                                      
                                                                                                                                                                                                             
class Location(Base):                                                                                                                                                                                        
    __tablename__ = 'location'                                                                                                                                                                               
    id = Column(Integer, primary_key=True)                                                                                                                                                                   
    city = Column(String(255))                                                                                                                                                                               
    state = Column(String(255))                                                                                                                                                                              
    country = Column(String(255))

The way I read the source code:

• Person has at most one birth location
• Multiple persons may have the same birth location

Here's the respective diagram generated by eralchemy

The way I read the diagram:

• 0 to N Locations relate to 0 or 1 Persons

It seems to me that relation representation in the diagram conveys the exact opposite meaning than it should.

Please also note that eralchemy's cardinality label style most closely resembles the UML specification, but label positioning doesn't.
This adds another layer of confusion when interpreting eralchemy's diagrams.

Solution proposal:
Swap cardinality labels' positions or at least add an option (to CLI and render_er) so that the user can chose for themself.

Running eralchemy gives error

Running eralchemy gives following error on Win 10

Please install application. using "pip install application.".

I have already installed these ->

1. Install python
2. Visual C++ for python -> https://download.microsoft.com/download/7/9/6/796EF2E4-801B-4FC4-AB28-B59FBF6D907B/VCForPython27.msi
3. graphviz -> http://www.graphviz.org/Download_windows.php
4. pygraphviz -> pip install --global-option=build_ext --global-option="-IC:\Program Files (x86)\Graphviz2.38\include" --global-option="-LC:\Program Files (x86)\Graphviz2.38\lib\release\lib" pygraphviz
5. eralchemy -> pip install eralchemy
6. Install instant client for oracle -> http://www.oracle.com/technetwork/topics/winx64soft-089540.html
7. pip install cx_Oracle-5.3+oci12c-cp27-cp27m-win32.whl after downloading from http://www.lfd.uci.edu/~gohlke/pythonlibs/#cx_oracle

How arrows that are linking between the entities will exactly point to right comlumns

How to handle override naming schemes?

I'm getting the following exception when I run the example postgresql string on my database--

sqlalchemy.exc.ArgumentError: WARNING: when configuring property 'mttakertradetype' on Mapper|RequestNew|RequestNew, column 'mttakertradetype' conflicts with property '<RelationshipProperty at 0x39472b8; mttakertradetype>'. To resolve this, map the column to the class under a different name in the 'properties' dictionary.  Or, to remove all awareness of the
column entirely (including its availability as a foreign key), use the 'include_properties' or 'exclude_properties' mapper arguments to control specifically which table columns get mapped.
As defined here http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#overriding-naming-schemes

My string - eralchemy -i 'postgresql+psycopg2://postgres:just4fun!@@10.10.100.85:5432/bridge_latest' -o filtered.er -x temp audit

This error comes from sqlalchemy, referenced in this ticket which suggests using an overriding naming scheme strategy. Does eralchemy provide any way to do this?

CI fails with PostgreSQL 10

I looked into the error with my PR (#34) failure.
https://travis-ci.org/Alexis-benoist/eralchemy/jobs/322844083

It seems psycopg2 2.6.1 does not support to PostgreSQL 10.
IMHO, Therefore we should upgrade the version to use in testing.

support for mysql and mssql?

Hi, just wondering if this package supports mysql? If so, what's an example command for mysql?

Missing relationships when using `Base`

Hi,

I'm noticing an issue where when I provide a Base the rendered PNG does not map the ForeignKey relationships.

As an example, I have a Facebook table declared with a campaign_id column like so:

...
    campaign_id = sa.Column(
        sa.BigInteger(),
        sa.ForeignKey(
            "facebook.campaigns.id",
            name="campaign_hourly_stats_campaign_id_fkey",
        ),
    )
...

However the rendered ERD doesn't recognize this relationship:

Any idea on why this might be?

in newer version of sqlalchemy table.c._data is gone

see

should replace table.c._data with table.columns

Support mermaid markdown

Now that Github supports rendering mermaid markdown for diagrams (https://github.blog/2022-02-14-include-diagrams-markdown-files-mermaid/), it would be nice for ERAlchemy to have the option of emitting mermaid-flavored markdown.

Failure when using on PG with citext extension enabled

I try to make a DB diagram of a postgresql database using eralchemy version 1.2.7 and I get the following error:

/home/cmignanti/.envs/labjack/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2650: SAWarning: Did not recognize type 'citext' of column 'name'
  (attype, name))
/home/cmignanti/.envs/labjack/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2650: SAWarning: Did not recognize type 'citext' of column 'email'
  (attype, name))
/home/cmignanti/.envs/labjack/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2650: SAWarning: Did not recognize type 'citext' of column 'first_name'
  (attype, name))
/home/cmignanti/.envs/labjack/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2650: SAWarning: Did not recognize type 'citext' of column 'last_name'
  (attype, name))
/home/cmignanti/.envs/labjack/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2650: SAWarning: Did not recognize type 'citext' of column 'account_number'
  (attype, name))
Traceback (most recent call last):
  File "/home/cmignanti/.envs/labjack/bin/eralchemy", line 11, in <module>
    sys.exit(cli())
  File "/home/cmignanti/.envs/labjack/local/lib/python2.7/site-packages/eralchemy/main.py", line 38, in cli
    schema=args.s
  File "/home/cmignanti/.envs/labjack/local/lib/python2.7/site-packages/eralchemy/main.py", line 231, in render_er
    tables, relationships = all_to_intermediary(input, schema=schema)
  File "/home/cmignanti/.envs/labjack/local/lib/python2.7/site-packages/eralchemy/main.py", line 152, in all_to_intermediary
    raise ValueError(msg)
ValueError: Cannot process filename_or_input str

I believe this is related to the usage of citext extension in on db table. You can find more info about citext here [1] but I believe that for this project those can be consider text fields.

1. https://www.postgresql.org/docs/current/static/citext.html

CI fails with Python 3.3, 3.5, 3.6

My PR (#36) fixed an issue with PostgreSQL 10.
But the test still fails partly yet.
https://travis-ci.org/Alexis-benoist/eralchemy/builds/322863346?utm_source=github_status&utm_medium=notification

It seems travis environment have no Python 3.3, 3.5 and 3.6 processors.
Maybe, we should add 'python' section to travis.yml.
https://docs.travis-ci.com/user/languages/python/

[eralchemy2] Fork of ERAlchemy for further development

Unfortunatley, this repo became stale for a quite a while.

As it is not compatible with current SQLAlchemy versions I created a fork of it.
I wrote Alexis by mail and asked if he could integrate the needed change, but did not get an answer for a few months.

My fork can be found here:
https://github.com/maurerle/eralchemy2/

Or installed from here:
https://pypi.org/project/eralchemy2/

Or by writing
pip install eralchemy2

You can support further development there.
If Alexis comes back to this repo I would be happily handing this back over to the original eralchemy which was very good supported for years.
Thank you @Alexis-benoist - I hope this is also okay for you.

For all users trying to install ERAlchemy on Windows 10

After much effort I gave up and installed:

Bash on Ubuntu on Windows

1. Open PowerShell as Administrator and run:
   PS:> Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux
2. Restart your computer when prompted.
3. Install Ubuntu:

More info @ https://docs.microsoft.com/en-us/windows/wsl/install-win10.

Next open up your new bash terminal (Start > bash).
Then run your basic update all command.

1. $sudo apt-get update https://askubuntu.com/a/196777/793432.
2. $sudo apt-get install python-2.7 python-pip
3. $sudo pip install graphviz
4. $sudo pip install eralchemy
5. $sudo pip install SQLAlchemy

You will also need to install the python driver for your database.
eg: sudo pip install mysql-python for MySQL driver. See http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#dialect-mysql
And you will need to install MySQL (if you are using that).
eg: sudo apt-get install mysql-server
See https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/

And you will probably need to set up the MySQL database just like you would for Windows.

If you are missing any modules I recommend googling for them like so:

apt-get install some-missing-module ubuntu

Compatability: Windows Subsystem for Linux is only available on x64 editions of Windows 10 and can be activated on Windows 10 version 1607 and later.

Not compatible with Flask-SQLAlchemy==2.3.2

Hi,

In Flask-SQLAlchemy==2.1:

In [1]: db.Model.__class__.__name__
Out[1]: '_BoundDeclarativeMeta'

In Flask-SQLAlchemy==2.3.2:

In [7]: db.Model.__class__.__name__
Out[7]: 'DefaultMeta'

Which is not one of the expected values in:
eralchemy/main.py

switch_input_class_to_method = {
    'MetaData': metadata_to_intermediary,
    'DeclarativeMeta': declarative_to_intermediary,
    # For compatibility with Flask-SQLAlchemy
    '_BoundDeclarativeMeta': declarative_to_intermediary
}

And the 'DefaultMeta' is not iterable and is not a string so it crashes.

Installation fails if SQLAlchemy is not installed

Error output is below. I believe this issue is caused because setup.py imports __version__ from the eralchemy.__init__.py and that file imports a bunch of other stuff in order to support the cli method. I think it could be fixed by moving the cli method to the main.py file and making the appropriate adjustment to the entrypoint value in setup.py.

Collecting ERAlchemy==1.0.5 (from -r test_requirements.txt (line 3))
  Using cached ERAlchemy-1.0.5.tar.gz
    Complete output from command python setup.py egg_info:
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/tmp/pip-build-g6whlgt5/ERAlchemy/setup.py", line 2, in <module>
        from eralchemy import __version__
      File "/tmp/pip-build-g6whlgt5/ERAlchemy/eralchemy/__init__.py", line 2, in <module>
        from eralchemy.main import render_er
      File "/tmp/pip-build-g6whlgt5/ERAlchemy/eralchemy/main.py", line 3, in <module>
        from eralchemy.sqla import metadata_to_intermediary, declarative_to_intermediary, database_to_intermediary
      File "/tmp/pip-build-g6whlgt5/ERAlchemy/eralchemy/sqla.py", line 8, in <module>
        from sqlalchemy.exc import CompileError
    ImportError: No module named 'sqlalchemy'

KeyError: '_data'

With

• Python 3.9.2
• sqlalchemy 1.4.0

I get this error:

Traceback (most recent call last):
  File "/home/julian/src/bruce-leads/.venv/lib/python3.9/site-packages/sqlalchemy/sql/base.py", line 1104, in __getattr__
    return self._index[key]
KeyError: '_data'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/julian/src/bruce-leads/.venv/bin/eralchemy", line 8, in <module>
    sys.exit(cli())
  File "/home/julian/src/bruce-leads/.venv/lib/python3.9/site-packages/eralchemy/main.py", line 31, in cli
    render_er(
  File "/home/julian/src/bruce-leads/.venv/lib/python3.9/site-packages/eralchemy/main.py", line 231, in render_er
    tables, relationships = all_to_intermediary(input, schema=schema)
  File "/home/julian/src/bruce-leads/.venv/lib/python3.9/site-packages/eralchemy/main.py", line 147, in all_to_intermediary
    return database_to_intermediary(filename_or_input, schema=schema)
  File "/home/julian/src/bruce-leads/.venv/lib/python3.9/site-packages/eralchemy/sqla.py", line 82, in database_to_intermediary
    return declarative_to_intermediary(Base)
  File "/home/julian/src/bruce-leads/.venv/lib/python3.9/site-packages/eralchemy/sqla.py", line 61, in declarative_to_intermediary
    return metadata_to_intermediary(base.metadata)
  File "/home/julian/src/bruce-leads/.venv/lib/python3.9/site-packages/eralchemy/sqla.py", line 54, in metadata_to_intermediary
    tables = [table_to_intermediary(table) for table in metadata.tables.values()]
  File "/home/julian/src/bruce-leads/.venv/lib/python3.9/site-packages/eralchemy/sqla.py", line 54, in <listcomp>
    tables = [table_to_intermediary(table) for table in metadata.tables.values()]
  File "/home/julian/src/bruce-leads/.venv/lib/python3.9/site-packages/eralchemy/sqla.py", line 49, in table_to_intermediary
    return Table(name=table.fullname, columns=[column_to_intermediary(col) for col in table.c._data.values()])
  File "/home/julian/src/bruce-leads/.venv/lib/python3.9/site-packages/sqlalchemy/sql/base.py", line 1106, in __getattr__
    util.raise_(AttributeError(key), replace_context=err)
  File "/home/julian/src/bruce-leads/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 180, in raise_
    raise exception
AttributeError: _data

I have investigated to fix it for sqlachemy but don't know about backwards compat.

eralchemy/eralchemy/sqla.py

Line 50 in d6fcdc6

columns=[column_to_intermediary(col) for col in table.c._data.values()]

needs to be replaced with:

columns=[column_to_intermediary(col) for col in table.c._colset]

I can create a tested PR if you are willing to merge it.

Python 3 compatibility

It would be great if it also works on Python 3. FYI pygraphviz 1.3rc2 currently supports Python 3.

broken renderings for larger databases.

I have tried out this software on a pair of large open datasets, the eICU Collaborative Research Database, and the MIMIC-III.

Though access to the data itself must be requested along with completion of a brief online certification, the descriptions of the tables and the columns they comprise are openly available, so the attached images do not reveal any sensitive information.

The structure/relationships of their tables and columns are described on their respective websites, http://eicu-crd.mit.edu/ and https://mimic.physionet.org/.

We host these datasets as postgresql databases, and are now attempting to use eralchemy to generate ER diagrams for them.
We use the command line route,
eralchemy -i <dburl> -s <schema> -o <output.png>

Below I have attached eralchemy renderings of both databases.
Though the rendering is technically correct, in terms of displaying table columns and relationships of foreign keys, the image is unusable.

Problems:

• Image is very tall, and very thin.
• Edges from tables with foreign keys do not go to the table they refer to, but to a text label of the table name located far away from the actual table image. A good example of this is in the EICU image is the patient table with a foreign key to the hospital table having an edge that just goes to the label hospital slightly to the left, and then several other tables that refer to the patient table having their edges go toward a patient label right next to the representation of the hospital table. Also can be seen in MIMIC , how the edges for foreign keys all direct to disembodied table names rather than to the tables themselves.
• Labels often overlap, as can be seen how the 0..N labels overlap eachother in the EICU render, obscuring the patient tablename.

A thought I had is that this could possibly stem from schema handling,
as all the tables in both datasets belong to a schema. The rendered tables have the full schemaname.tablename format, however the bodiless labels that the edges point to only have the tablename. Maybe it is pointing to a label, and not the table as it cant recognize that schemaname.tablename refers to the same thing as tablename. I am still reading through the source code, so I am not too familiar with the mechanics quite yet.

Though the data itself is not available without being granted access, code for creating the databases themselves, ergo that which is necessary for eralchemy to generate an ER diagram is available on the datasets' respective repositories at

• https://github.com/MIT-LCP/eicu-code
• https://github.com/MIT-LCP/mimic-code

which should come in handy for reproducing the issue.

I am also available to cooperate and collaborate to resolve this issue.

EICU

MIMIC

`from eralchemy import render_er` is broken from #16

This is my own darn fault. I'm about to submit a PR that fixes it while retaining the advantages gained in #16. Basically, it makes more sense to leave render_er in __init__.py and move version to version.py. The only thing that has to change is the version string import in setup.py. This also seems much less likely to break downstream packages.

pygraphiz appears to be deprecated?

pygraphviz looks to be no longer maintained. Would it be worth migrating eralchemy to use graphviz?

#Rendering from dot file using graphviz
from graphviz import Source
Source.from_file(dotfilename)

The format parameter to Source defines the output format and the engine parameter the layout engine (dot, neato etc).

The only place pygraphviz appears to be used is in eralchemy/eralchemy/main.py in the function:

def intermediary_to_schema(tables, relationships, output):
    """ Transforms and save the intermediary representation to the file chosen. """
    dot_file = _intermediary_to_dot(tables, relationships)
    graph = AGraph()
    graph = graph.from_string(dot_file)
    extension = output.split('.')[-1]
    graph.draw(path=output, prog='dot', format=extension)

Unable to import psycopg2 error in brew install.

I installed eralchemy using brew, but when I attempt to run it, I'm getting the following error:

$ eralchemy -i 'postgresql+psycopg2://dakota@localhost:5432/logbook' -o logbook.pdf
Please install psycopg2 using "pip install psycopg2".

So for the sake of not reporting an obvious issue; I went ahead and ran the command as requested:

$ pip install psycopg2
Requirement already satisfied (use --upgrade to upgrade): psycopg2 in /usr/local/lib/python2.7/site-packages

And of course, opening a Python terminal and importing psycopg2 works. I even tried creating a virtual environment, installing pscyopg2 there, and running eralchemy. No joy.

ERAlchemy is working for me now; I uninstalled the brew installation and used pip to install the latest and am now good to go. Not sure if anyone else has had this issue; and I don't really know how to resolve it.

Specify multiple schemas

Since tables can be in different schemas within the same database, it would be nice if we could show the tables across multiple schemas.

Currently this just seems to take the last specified schema, c.

eralchemy -i postgresql:///dbname -o er.pdf -s a -s b -s c

When i pass a schema to the declaritive_base it breaks the whole drawing

metadata = MetaData(schema="test_db")
Base = declarative_base(metadata=metadata)

When i add in a custom schema all the relationships break? Any ideeas?

GeoAlchemy support

I have a postgres db that use PostGIS, so i'm using GeoAlchemy with SQLAlchemy to operate that db, but when i try to use eralchemy show me this warning:
Did not recognize type 'geography' of column 'coordinates'

Entities do not align

I am using eralchemy on a MySQL database.

The relationship indicator lines in the produced graph do not align with the columns in the erd.

The tables are defined like this:
CREATE TABLE cpsc_enrollment_info (
ContractNumber varchar(256) NOT NULL,
PlanID varchar(256) NOT NULL,
SSACode int(11) NOT NULL,
FIPSCode int(11) DEFAULT NULL,
State varchar(2) DEFAULT NULL,
County varchar(256) DEFAULT NULL,
Enrollment int(11) DEFAULT NULL,
Period date NOT NULL,
PRIMARY KEY (ContractNumber,PlanID,SSACode,Period),
CONSTRAINT cpsc_enrollment_info_ibfk_1 FOREIGN KEY (ContractNumber, PlanID) REFERENCES cpsc_contract_info (ContractNumber, PlanID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE cpsc_contract_info (
ContractNumber varchar(256) NOT NULL,
PlanID varchar(256) NOT NULL,
OrganizationType varchar(256) DEFAULT NULL,
PlanType varchar(256) DEFAULT NULL,
OffersPartD tinyint(1) DEFAULT NULL,
SNPPlan tinyint(1) DEFAULT NULL,
EGHP tinyint(1) DEFAULT NULL,
OrganizationName varchar(256) DEFAULT NULL,
OrganizationMarketingName varchar(256) DEFAULT NULL,
PlanName varchar(256) DEFAULT NULL,
ParentOrganization varchar(256) DEFAULT NULL,
ContractEffectiveDate date DEFAULT NULL,
Period date DEFAULT NULL,
PRIMARY KEY (ContractNumber,PlanID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The erd produced looks like this. ContracNumber, PlanID should be connected to ContractNumber, PlanID in each table.

This indicates that there is a connection between the tables but doesn't show which columns. Is this a bug? If it is expected behavior what can I pursue to fix it?

Getting CI back on track

Hi there,

I was looking at using this tool as a handy "plugged onto SQLAlchemy" replacement to a custom script. I hit #80 and saw that #86 might fix it. Unfortunately there are unrelated CI errors which seem to have stalled development.

I'm opening this issue because I think the first step towards getting this project in shape so we can fix items such as #80 would be having CI pass on master again. Right now this project has over 100 forks which sometimes exist just so people and make the necessary fixes.

I am going to investigate and report, or open a PR if I find what needs fixing. :)

1.2.5 AttributeError: 'Namespace' object has no attribute 'x'

With 1.2.5

bash-3.2$ eralchemy -v
Traceback (most recent call last):
  File "/usr/local/bin/eralchemy", line 11, in <module>
    load_entry_point('ERAlchemy==1.2.5', 'console_scripts', 'eralchemy')()
  File "/usr/local/Cellar/eralchemy/1.2.5/libexec/lib/python2.7/site-packages/eralchemy/main.py", line 37, in cli
    check_args(args)
  File "/usr/local/Cellar/eralchemy/1.2.5/libexec/lib/python2.7/site-packages/eralchemy/helpers.py", line 17, in check_args
    args.x is not None:
AttributeError: 'Namespace' object has no attribute 'x'
bash-3.2$   

1.1.0 is fine.

Support for defining the color of tables

Hello :)
First of all, thank you for this project!! It works really well!
I want to ask if it is possible to define the colours of the tables?

Something like:
[table_name]{color: "#77fc03"}
+attribute {label: "integer"}

Thanks :)

Alternative depictions - eg crow foot

Given that there are several graphical notations for displaying ERDs, it might be useful to allow the user to select what sort of visual representation they prefer.

For example, I have a requirement to use crow foot notation. Searching around, it seems as if Graphviz supports a wide range of relevant arrow styles that could presumably be used?

http://www.graphviz.org/doc/info/arrows.html

Cryptic ArgumentError lets ERAlchemy fail to load sqlite db

Some time ago, I managed to generate a nice ER diagram from a previous version of my database. Howerver today, running eralchemy on my sqlite database

$ file lexirumah.sqlite
lexirumah.sqlite: SQLite 3.x database

now fails with a value error which does not make obvious what the problem is. I have checked with the current version of eralchemy from pip, with the most recent checkout of the master branch and with tag v1.0.15, and in all cases I get

$ eralchemy -i "sqlite://lexirumah.sqlite" -o erd_from_sqlite.svg
Traceback (most recent call last):
  File "/vol/home/kaipingga/.local/bin/eralchemy", line 11, in <module>
    load_entry_point('ERAlchemy', 'console_scripts', 'eralchemy')()
  File "/vol/home/kaipingga/devel/eralchemy/eralchemy/main.py", line 33, in cli
    render_er(args.i, args.o, exclude=args.x, schema=args.s)
  File "/vol/home/kaipingga/devel/eralchemy/eralchemy/main.py", line 179, in render_er
    tables, relationships = all_to_intermediary(input, schema=schema)
  File "/vol/home/kaipingga/devel/eralchemy/eralchemy/main.py", line 130, in all_to_intermediary
    raise ValueError(msg)
ValueError: Cannot process filename_or_input str

Running pdb on the problem shows that the problem is that database_to_intermediary finds some problem somewhere in my DB and fails with an ArgumentError, which leads the calling function to assume that it got passed something that was not a DB URI as filename_or_input (which in this case is the wrong conclusion).

(Pdb) database_to_intermediary(filename_or_input, schema=None)
*** sqlalchemy.exc.ArgumentError: WARNING: when configuring property 'value' on Mapper|value_data|value_data, column 'value' conflicts with property '<RelationshipProperty at 0x7fb3554abd48; value>'. To resolve this, map the column to the class under a different name in the 'properties' dictionary.  Or, to remove all awareness of the column entirely (including its availability as a foreign key), use the 'include_properties' or 'exclude_properties' mapper arguments to control specifically which table columns get mapped.

I have no idea what this means. I have attached the output of sqlite3 lexirumah.sqlite .dump | grep -v INSERT, in case looking at the table schema helps finding the issue.
db_schema.txt

Feature proposal: show database constraints

Hi maintainers,

It looks that currently an output of eralchemy doesn't have database constraints such as NOT NULL or UNIQUE(I tried drawing from SQLAlchemy Base). I think it's helpful if they are drawn in the output.
How do you think of?

Anyway, thank you for creating this package.

SyntaxError when querying postgresql db

I'm running from a jupyter notebook (ubuntu).
I can import eralchemy and import psycopg2.
No error there.

However, when I run
eralchemy -i 'postgresql+psycopg2://postgres:[email protected]:1198/dbname' -o er.pdf

File "", line 1
eralchemy -i 'postgresql+psycopg2://postgres:[email protected]:1198/dbname' -o er.pdf
^
SyntaxError: invalid syntax

ps. I have no problem querying db itself using panda or psycopg2 on the same notebook page.

support for sql server

Hi, do you support the creation of er diagram directly from the SQL server? I am using SQL server management studio. and manage to make the connection using pyodbc:

conn = pyodbc.connect(
"Driver={SQL Server Native Client 11.0};"
"Server=servername;"
"Database=TEST;"
"Trusted_Connection=yes;"
)

Allow the dot file's graph beginning to be customized

Hi,

It would be great if we could customize the GRAPH_BEGINNING.

I have a SQLite database that we are trying to generate a diagram automatically for our documentation. eralchemy works great, but the final layout uses some default dot settings that may not play well if different DB schemas.

I believe this could provide workarounds for #41 and #39.

Here's what I get when I run eralchemy with our current schema.

By changing the graph beginning manually (and I am now in the progress of monkey-patching eralchemy), I get a landscape output, and can also control the space between nodes/edges in the graph. The result then is:

Add version info of required compatible Graphviz on Windows

Maybe consider adding on the readme.MD info of the version of Graphviz that is compatible, especially for Windows. I used the latest version (3.0.0) and installing pygraphviz (from the requirements.txt) didn't work. The latest version that works for Windows is 2.49.0.