Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
building entitty relation ship diagram from a db by using introspection
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from sqlsoup import SQLSoup as sql
from sys import argv
DIGRAPH = """digraph structs {
graph [
rankdir= "LR"
bgcolor=white
]
node [
fontsize=12
shape=record
]
%s
}
"""
db=sql(argv[1])
print "introspecting %s" % argv[1]
to_scan = db.engine.table_names()
vertices = []
nodes = dict()
interesting = set([])
fk_count = 0
field_count = 0
while to_scan:
node_str = ''
try:
table_name = to_scan.pop()
table = getattr(db,table_name)
node_str += """
%s [
label="%s""" % (table_name, table_name)
has_fk=False
for c in table._table.c:
node_str += "|<%s>%s" % (c.name, c.name)
field_count += 1
#print "adding %s" % c.name
if c.foreign_keys:
for fk in c.foreign_keys:
interesting |= { table_name, fk.column.table.name, }
fk_count+=1
# if you want to make a progressive scan around a table vicinity
#to_scan += [ fk.column.table.name ]
vertices += [ (
":".join([table_name, c.name]),
":".join([ fk.column.table.name, fk.column.name]),
fk.name or '""'),
]
nodes[table_name] = """%s"
color=%%s
bgcolor=%%s
]""" % node_str
except Exception as e:
print "problem with %r" % table_name
print repr(e)
to_print = ""
for node in nodes:
#to_print += node % (("grey","grey"), ("black", "white"))[node in interesting]
to_print += nodes[node] % (("grey","grey"), ("black", "white"))[node in interesting]
for v in vertices:
to_print+="""
%s -> %s [ label=%s ]
""" % v
to_print = DIGRAPH % to_print
print "nb col = %r" % field_count
print "nb fk = %r" % fk_count
with open("out.dot", "w") as f: f.write(to_print)
@truijllo

This comment has been minimized.

Show comment Hide comment
@truijllo

truijllo Sep 24, 2015

very nice script!
just a little patch on line 68:

 to_print += nodes[node] % (("grey","grey"), ("black", "white"))[node in interesting]

very nice script!
just a little patch on line 68:

 to_print += nodes[node] % (("grey","grey"), ("black", "white"))[node in interesting]
@jul

This comment has been minimized.

Show comment Hide comment
@jul

jul Apr 17, 2016

merci @Trujillo

Owner

jul commented Apr 17, 2016

merci @Trujillo

@amirouche

This comment has been minimized.

Show comment Hide comment
@amirouche

amirouche Jul 16, 2017

Tx a lot for this program!

amirouche commented Jul 16, 2017

Tx a lot for this program!

@amirouche

This comment has been minimized.

Show comment Hide comment
@amirouche

amirouche Jul 23, 2017

Can I use this program in my project which is AGPLv3? cf. https://github.com/amirouche/socialite

TIA!

Can I use this program in my project which is AGPLv3? cf. https://github.com/amirouche/socialite

TIA!

@jul

This comment has been minimized.

Show comment Hide comment
@jul

jul Apr 10, 2018

@amirouche sorry for the lag, the permission is hereby officially granted, eventually send me an email so you have the explicit verbatim auhtoàrization

Owner

jul commented Apr 10, 2018

@amirouche sorry for the lag, the permission is hereby officially granted, eventually send me an email so you have the explicit verbatim auhtoàrization

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment