existing database?

[AlJohri]

hi, is there any way to use this to generate a diagram for an existing database? perhaps using sqlalchemy metadata reflection?

thanks!

[laowantong]

You mean, a relational diagram, the one with arrows?

[AlJohri]

@laowantong, yeah the one with the arrows.

I was trying to create a nice looking ER diagram within a jupyter notebook for an existing mysql database. Is that something this tool can help accomplish? I know there are definitely other tools out there but mocodo's diagrams looked pretty nice so I figured I'd ask.

Also, I couldn't entirely understand what the MCD input format was reading the docs - is this a standard format or something custom to mocodo?

[laowantong]

OK, with sqalchemy this should not be too difficult, I'll have a look.

The .mcd format is specific to Mocodo. As for the MCD diagrams, they appear to be a French speciality, like the baguette. Most French teachers use them to teach relational databases. This is why the documentation of Mocodo currently only exists in French. You may experiment with the syntax on http://www.mocodo.net.

[laowantong]

I have just written this function. It takes the same parameters as sqlalchemy.create_engine() and returns a string that can be pasted on http://www.mocodo.net. Use the shuffle-like button to rearrange the tables.

[AlJohri]

Thanks a bunch! I'll give it a try.

[wochner]

This seems promising, but got an error:

  File "<ipython-input-23-0c6686fdc017>", line 19, in relational_diagram
    prefix = ("_" if i and column["primary_key"] else "")

KeyError: 'primary_key'

On closer inspection, the inspect method does not return a primary_key column:

insp.get_columns('mn_entity')
Out[19]: 
[{'name': 'ID_BB_GLOBAL_COMPANY',
  'type': CHAR(length=12),
  'default': None,
  'comment': None,
  'nullable': False},
 {'name': 'ID_BB_GLOBAL_COMPANY_NAME',
  'type': VARCHAR(length=255),
  'default': None,
  'comment': None,
  'nullable': True}]

Maybe sqlalchemy changed in the meantime? Need to use the following method maybe? method sqlalchemy.engine.reflection.Inspector.get_pk_constraint(table_name, schema=None, **kw)