Infer relationships in a mysql database and generate an ERD based on those relationships. Foreign keys must be named as tablename_id for this to work correctly. Actual real life foreign keys are ignored. Diagrams are generated in plantuml format http://plantuml.com/

generate_erd.py

from mysql.connector import connection
 
db_uname = CHANGEME
db_pword = CHANGEME
db_host = CHANGEME

schema_name = 'SCHEMA_NAME'
table_names = ['TABLE1', 'TABLE2', 'ETC...']


conn = connection.MySQLConnection(user=db_uname, password=db_pword, host=db_host)
cursor = conn.cursor()
 
 
query = """
SELECT TABLE_NAME t_name, COLUMN_NAME col_name, COLUMN_KEY
  FROM information_schema.columns
  WHERE (table_schema=%%s)
  and TABLE_NAME in (%s)
  order by t_name, ORDINAL_POSITION;
"""
query = query % ', '.join(['%s'] * len(table_names))
 
cursor.execute(query, ([schema_name] + table_names))
 
tables = {}
 
for tname, cname, ktype in cursor:
    if not tname in tables:
        tables[tname] = {'pkeys': [], 'cols': []}
    if ktype == 'PRI':
        tables[tname]['pkeys'].append(cname)
    else:
        tables[tname]['cols'].append(cname)
conn.close()
 
print """
@startuml
hide circle
hide empty members
 
"""
for tname, data in tables.items():
    o = []
    o.append('entity %s {' % tname )
    for k in data['pkeys']:
        o.append('\t* %s' % k)
    o.append('\t--')
    for c in data['cols']:
        o.append('\t%s' % c)
    o.append('}')
    print('\n'.join(o))
 
for tname, data in tables.items():
    for c in data['cols']:
        if c.endswith('_id'):
            if c.startswith(tname):
                joiner = "*-"
            else:
                joiner = "*--"
            print('%s %s %s' % (tname, joiner, c[:-3]))
             
print("@enduml")