def create_schema_image(metadata):
graph = create_schema_graph(metadata=metadata,
show_datatypes=True, show_indexes=False, rankdir='LR')
import tempfile, Image
with tempfile.NamedTemporaryFile(suffix=".png") as fout:
graph.write(fout.name, format="png")
Image.open(fout.name).show()
def plain_result(**kw):
if 'metadata' in kw:
kw['metadata'].create_all()
elif 'tables' in kw:
if len(kw['tables']):
kw['tables'][0].metadata.create_all()
return parse_graph(sasd.create_schema_graph(**kw))
def generate_schema_graph(uri, output):
graph = create_schema_graph(metadata=MetaData(uri),
show_datatypes=False,
show_indexes=False,
rankdir='LR', # or TP
concentrate=False) # no joins of tables together
graph.write_png(output)
def write_graph():
# create the pydot graph object by autoloading all tables via a bound metadata object
graph = create_schema_graph(metadata=Base.metadata, #metadata=MetaData('postgres://*****:*****@host/database'),
show_datatypes=False, # The image would get nasty big if we'd show the datatypes
show_indexes=False, # ditto for indexes
rankdir='TB',# was LR # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
graph.write_png('/home/hdeeken/dbschema.png') # write out the file
def create_schema(dbpath):
from sqlalchemy_schemadisplay import create_schema_graph
graph = create_schema_graph(metadata=MetaData(dbpath),
show_datatypes=False, # The image would get nasty big if we'd show the datatypes
show_indexes=False, # ditto for indexes
rankdir='LR', # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
graph.write_png('dbschema.png') # write out the file
def erdump(**dbinf):
graph = create_schema_graph(
metadata=MetaData('mysql://%(user)s:%(passwd)s@%(host)s/%(db)s' % dbinf),
show_datatypes=False,
show_indexes=False,
# rankdir='TB',
rankdir='LR',
concentrate=False
)
graph.write_png('erd.png')
def plot_schema(conn_str):
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph
# create the pydot graph object by autoloading all tables via a bound metadata object
graph = create_schema_graph(metadata=MetaData(conn_str),
show_datatypes=False, # The image would get nasty big if we'd show the datatypes
show_indexes=False, # ditto for indexes
rankdir='LR', # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
graph.write_png('dbschema.png') # write out the file
def schema_diagram():
"""
Draw an Entity Relationship Diagram
"""
from sqlalchemy_schemadisplay import create_schema_graph
graph = create_schema_graph(
metadata=db.MetaData(app.config['SQLALCHEMY_DATABASE_URI']),
show_datatypes=True,
show_indexes=True
)
graph.write_png('schema.png')
def _draw_entity_diagrama(self):
# create the pydot graph object by autoloading all tables via a bound metadata object
graph = create_schema_graph(
metadata=MetaData(server.config.database.connection_string.strip("'")),
show_datatypes=False, # The image would get nasty big if we'd show the datatypes
show_indexes=False, # ditto for indexes
rankdir='LR', # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
try:
graph.write_png('entity_dbschema.png') # write out the file
except OSError as ex:
if 'dot' in ex.strerror:
print('Rendering entity scheam requires dot. Please install it with: sudo apt install xdot')
sys.exit(1)
raise
def draw_schema(self,filename="schema.png"):
if createGraph:
# create the pydot graph object by autoloading all tables via a bound metadata object
graph = create_schema_graph(metadata=self.meta,
show_datatypes=False, # can get large with datatypes
show_indexes=False, # ditto for indexes
rankdir='LRA', # From left to right (LR), top to bottom (TB)
concentrate=False # Don't try to join the relation lines together
)
if re.search("\.png",filename):
graph.write_png(filename)
elif re.search("\.svg",filename):
graph.write_svg(filename)
else:
raise Exception("invalid filename specified [*.png or *.svg")
print("...%s created"%filename)
else:
print "Not creating schema figure because 'sqlalchemy_schemadisplay' is not installed"
def diagram():
from sqlalchemy_schemadisplay import create_schema_graph
from plyus import db
# Database
host = "localhost"
engine = "postgresql"
database = "database"
username = "******"
password = "******"
# General
data_types = False
indexes = False
# Generation
dsn = engine + "://" + username + ":" + password + "@" + host + "/" + database
# graph = create_uml_graph(db.Model)
graph = create_schema_graph(metadata=db.Model.metadata)
graph.write_png("schema.png")
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph
import config
# create the pydot graph object by autoloading all tables via a bound metadata object
graph = create_schema_graph(
metadata=MetaData(config.SQLALCHEMY_DATABASE_URI),
show_datatypes=
False, # The image would get nasty big if we'd show the datatypes
show_indexes=False, # ditto for indexes
rankdir='LR', # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
graph.write_png('dbschema.png') # write out the file
def test_empty_db(metadata):
graph = sasd.create_schema_graph(metadata=metadata)
assert isinstance(graph, pydot.Graph)
assert graph.create_plain() == 'graph 1 0 0\nstop\n'
# lets find all the mappers in our model
mappers = []
for attr in dir(model):
if attr[0] == '_': continue
try:
cls = getattr(model, attr)
mappers.append(class_mapper(cls))
except:
pass
# pass them to the function and set some formatting options
graph = create_uml_graph(
mappers,
show_operations=False, # not necessary in this case
show_multiplicity_one=False # some people like to see the ones, some don't
)
graph.write_pdf('uml.pdf') # write out the file
graph = create_schema_graph(
metadata=MetaData(
'mysql://*****:*****@localhost/ctf2'
),
show_datatypes=
False, # The image would get nasty big if we'd show the datatypes
show_indexes=False, # ditto for indexes
#rankdir='LR', # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
graph.write_pdf('schema.pdf') # write out the file
def create_db(inp_args):
from idm.role_model import Permission, personal_permission_user_assign, UserAssignment, User, Base, Organisation, OrganisationType, Unit
engine = create_engine('sqlite:///idm.db')
session = sessionmaker(bind=engine)
Base.metadata.create_all(engine)
s = session()
permissions = [
('god_mode', 'Full access and anarchy'),
('create_permission', 'User can create permission'),
('create_role', 'User can create role'),
('set_role', 'User can assign role for other user'),
('set_permission', 'User can assign permission for other user'),
]
for i, t in enumerate(permissions):
db_obj = Permission(id=str(uuid4()), name=t[0], description=t[1])
s.add(db_obj)
s.commit()
org_id_ = str(uuid4())
ot = OrganisationType(id=org_id_, org_type='owner')
o = Organisation(id=str(uuid4()),
name='BuzzWords',
organisation_type_id=id_)
s.add(ot)
s.add(o)
# id_ = str(uuid4())
# ot = OrganisationType(id=id_, org_type='client')
# o = Organisation(id=str(uuid4()), name='Test Organisation', organisation_type_id=id_)
# s.add(ot)
# s.add(o)
unit_id = str(uuid4())
unit = Unit(id=unit_id, organisation_id=org_id_, name='dev')
id_ = str(uuid4())
user_asg = UserAssignment(id=id_)
s.add(user_asg)
user = User(
id=id_,
login='******',
password=None,
first_name='System',
last_name='',
user_assignment_id=id_,
is_activated=True,
)
s.add(user)
id_ = str(uuid4())
user_asg = UserAssignment(id=id_)
s.add(user_asg)
user = User(
id=id_,
login=inp_args.admin_login,
password=make_sha256(inp_args.admin_pwd),
first_name='admin',
last_name='admin',
user_assignment_id=id_,
is_activated=True,
organisation_id=org_id_,
unit_id=unit_id,
)
s.add(user)
s.commit()
s.execute(personal_permission_user_assign.insert().values({
'user_assignment_id':
0,
'permission_id':
0
}))
s.execute(personal_permission_user_assign.insert().values({
'user_assignment_id':
1,
'permission_id':
0
}))
s.commit()
s.close()
if inp_args.save_schema is not None:
# create the pydot graph object by autoloading all tables via a bound metadata object
graph = create_schema_graph(
metadata=MetaData('sqlite:///idm.db'),
show_datatypes=
False, # The image would get nasty big if we'd show the datatypes
show_indexes=False, # ditto for indexes
rankdir='LR', # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
graph.write_png(inp_args.save_schema + '.png')
def test_no_args():
with pytest.raises(ValueError) as e:
sasd.create_schema_graph()
assert e.value.args[0] == 'You need to specify at least tables or metadata'
query = session.query(Gene).filter_by(ncbi_id='3771877').first()
if query.symbol != 'Adh':
print("ERROR: bad gene symbol found")
print(query.symbol)
sys.exit()
## test the 'Uniprot' table
if session.query(Uniprot).count() < 100:
print("ERROR: not enough uniprot found")
sys.exit()
if createGraph == True:
# create the pydot graph object by autoloading all tables via a bound metadata object
graph = create_schema_graph(
metadata=Base.metadata,
show_datatypes=False, # can get large with datatypes
show_indexes=False, # ditto for indexes
rankdir='LR', # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
graph.write_svg('dbschema.svg') # write out the file
#graph.write_png('dbschema.png') # write out the file
else:
print(
"Not creating schema figure because 'sqlalchemy_schemadisplay' is not installed"
)
print('all tests pass.')
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph
# Database
host = 'localhost'
engine = 'sqlite'
database = ''
username = ''
password = ''
# General
data_types = False
indexes = False
# Generation
dsn = engine + ':///forjar.db';
graph = create_schema_graph(
metadata = MetaData(dsn),
show_datatypes = data_types,
show_indexes = indexes
)
print 'Writing schema...'
graph.write_png('schema.png')
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from sqlalchemy_schemadisplay import create_uml_graph, create_schema_graph
from sqlalchemy.orm import class_mapper
from sqlalchemy.orm.exc import UnmappedClassError
from inyoka.core.api import db, ctx, IResource
from inyoka.utils import flatten_iterator
models = list(flatten_iterator(x.models for x in ctx.get_implementations(IResource, instances=True)))
# lets find all the mappers in our model
mappers = []
tables = []
for model in models:
try:
mappers.append(class_mapper(model))
tables.extend(mappers[-1].tables)
except UnmappedClassError:
continue
# pass them to the function and set some formatting options
uml = create_uml_graph(mappers,
show_operations=False, # not necessary in this case
show_multiplicity_one=False # some people like to see the ones, some don't
)
uml.write_png('uml.png') # write out the file
schema = create_schema_graph(list(set(tables)))
schema.write_png('schema.png')
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph
# create the pydot graph object by autoloading all tables via a bound metadata object
graph = create_schema_graph(
metadata=MetaData("postgresql://localhost/rootio"),
show_datatypes=
False, # The image would get nasty big if we'd show the datatypes
show_indexes=False, # ditto for indexes
# rankdir='LR', # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
graph.write_png('dbschema.png') # write out the file
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph
# create the pydot graph object by autoloading all tables via a bound metadata object
graph = create_schema_graph(metadata=MetaData('postgresql+psycopg2://taste:tastedb@localhost/test'),
show_datatypes=False, # The image would get nasty big if we'd show the datatypes
show_indexes=False, # ditto for indexes
rankdir='LR', # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
graph.write_png('/home/assert/Desktop/C_DRIVE/work/dbschema.png') # write out the file
'''
Created on Jun 6, 2013
@author: hill
'''
import ConfigParser
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph
OS_PROJECTS= {"nova":"/etc/nova/nova.conf",
"keystone":"/etc/keystone/keystone.conf",
"glance":"/etc/glance/glance-registry.conf",
"cinder":"/etc/cinder/cinder.conf"
}
config = ConfigParser.RawConfigParser()
config.read('/etc/nova/nova.conf')
connection = config.get("DEFAULT", "sql_connection")
graph = create_schema_graph(metadata=MetaData(connection),
show_datatypes=False,
show_indexes=False,
rankdir='LR',
concentrate=False)
graph.write_png('nova.png')
if session.query(Gene).count() < 4:
print("ERROR: not enough genes found")
sys.exit()
query = session.query(Gene).filter_by(ncbi_id='3771877').first()
if query.symbol != 'Adh':
print("ERROR: bad gene symbol found")
print(query.symbol)
sys.exit()
## test the 'Uniprot' table
if session.query(Uniprot).count() < 100:
print("ERROR: not enough uniprot found")
sys.exit()
if createGraph == True:
# create the pydot graph object by autoloading all tables via a bound metadata object
graph = create_schema_graph(metadata=Base.metadata,
show_datatypes=False, # can get large with datatypes
show_indexes=False, # ditto for indexes
rankdir='LR', # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
graph.write_svg('dbschema.svg') # write out the file
#graph.write_png('dbschema.png') # write out the file
else:
print("Not creating schema figure because 'sqlalchemy_schemadisplay' is not installed")
print('all tests pass.')
from sqlalchemy.orm import class_mapper
from sqlalchemy_schemadisplay import create_schema_graph, create_uml_graph
from adhocracy import model
# create a diagram of all tables
graph = create_schema_graph(
metadata=model.meta.data,
show_datatypes=True,
show_indexes=False,
rankdir='LR',
concentrate=False # Don't try to join the relation lines together
)
graph.write_png('adhocracy-tables.png')
# create an uml diagramm of all mapped classes
mappers = []
for attr in dir(model):
if attr[0] == '_':
continue
try:
cls = getattr(model, attr)
mappers.append(class_mapper(cls))
except:
pass
graph = create_uml_graph(mappers,
show_operations=False,
from sqlalchemy_schemadisplay import create_schema_graph
import sys
sys.path.append('.')
sys.path.insert(0, '../ajna_docs/commons')
sys.path.insert(0, '../virasana')
from bhadrasana.models.ovr import metadata
filename = 'mymodel.png'
create_schema_graph(metadata).write(filename)
sacFlies = Column(Float)
gamesPlayed = Column(Float)
numberOfPitches = Column(Float)
completeGames = Column(Float)
stolenBases = Column(Float)
homeRuns = Column(Float)
outs = Column(Float)
hitByPitch = Column(Float)
catchersInterference = Column(Float)
strikes = Column(Float)
saves = Column(Float)
inheritedRunners = Column(Float)
rbi = Column(Float)
doubles = Column(Float)
pitchesThrown = Column(Float)
battersFaced = Column(Float)
sacBunts = Column(Float)
gamesStarted = Column(Float)
flyOuts = Column(Float)
blownSaves = Column(Float)
if __name__ != '__main__':
db.Base.metadata.create_all()
# Create visualization
import sqlalchemy_schemadisplay
from sqlalchemy import MetaData
schema_viz = sqlalchemy_schemadisplay.create_schema_graph(
metadata=MetaData(db.db_engine))
schema_viz.write_png('images/dbschema.png')
#!/usr/bin/env python
import ConfigParser
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph
#veritabina baglanma
connection = "mysql://*****:*****@127.0.0.1/mlistdata?charset=utf8"
#Baglanilan veritabanindan graph uretme
graph = create_schema_graph(metadata=Metadata(connection), show_datatypes=False, show_indexes=False, rankdir='LR', concentrate=False)
#Png resim uretme
graph.write_png('dbschema.png')
# coding= utf-8
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph
# create the pydot graph object by autoloading all tables via a bound metadata object
graph = create_schema_graph(
metadata=MetaData("sqlite:///db.sqlite3"),
show_datatypes=
True, # The image would get nasty big if we'd show the datatypes
show_indexes=True, # ditto for indexes
rankdir='LR', # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
graph.write_png('dbschema.png') # write out the file
# coding= utf-8
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph
# create the pydot graph object by autoloading all tables via a bound metadata object
graph = create_schema_graph(metadata=MetaData("sqlite:///db.sqlite3"),
show_datatypes=True, # The image would get nasty big if we'd show the datatypes
show_indexes=True, # ditto for indexes
rankdir='LR', # From left to right (instead of top to bottom)
concentrate=False # Don't try to join the relation lines together
)
graph.write_png('dbschema.png') # write out the file
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph
connection = "postgres://*****:*****@localhost/sparkifydb"
graph = create_schema_graph(metadata=MetaData(connection),
show_datatypes=True, # show datatypes
show_indexes=True, # show index (in ourcase unique)
rankdir='LR', # left to right alignment
concentrate=False)
graph.write_png('database_schema_diagram.png')
print('Problems accessing database: %s' % e)
sys.exit(1)
# remove unwanted tables
if args.exclude is not None:
if args.exclude is list:
for t in args.exclude:
m.remove(m.tables[t])
else:
m.remove(m.tables[args.exclude])
# generate the schema graph
graph = create_schema_graph(
tables=[m.tables[x] for x in list(m.tables.keys())],
show_datatypes=False,
show_indexes=False,
rankdir='TB',
concentrate=True,
)
# Write out graph to the corresponding file
if args.file is not None:
# get file extension
filename, fileext = os.path.splitext(args.file)
fn = args.file
print(fn)
if fileext == '.pdf':
graph.write_pdf(fn)
elif fileext == '.png':
graph.write_png(fn)
elif fileext == '.svg':
try:
from sqlalchemy_schemadisplay import create_schema_graph, create_uml_graph
except ImportError:
print(
"ERD diagrams require 'pip install sqlalchemy_schema_display' to run")
sys.exit(1)
from application.config import SQLALCHEMY_DATABASE_URI
from application.app import db
output_dir = os.path.join(os.path.dirname(__file__), 'erd_diagrams')
if not os.path.exists(output_dir):
os.mkdir(output_dir)
graph = create_schema_graph(metadata=MetaData(SQLALCHEMY_DATABASE_URI),
show_datatypes=True,
show_indexes=True,
rankdir='LR',
concentrate=False)
graph.write_png(os.path.join(output_dir, 'erd_diagram_eaterator.png'))
models = [
m for m in
[model for model in db.Model.__subclasses__()[0].__subclasses__()]
]
mappers = []
for model in models:
try:
cls = model
mappers.append(class_mapper(cls))
except:
raise
gxy_root = os.path.abspath(os.path.join(os.path.dirname(__file__), os.pardir))
sys.path.insert(1, os.path.abspath(os.path.join(gxy_root, 'lib')))
from galaxy import model
if __name__ == "__main__":
gxy_root = os.path.abspath(
os.path.join(os.path.dirname(__file__), os.pardir))
sqlitedb = os.path.join(gxy_root, 'database/universe.sqlite')
# Try to build a representation of what's in the sqlite database
if os.path.exists(sqlitedb):
graph = create_schema_graph(metadata=MetaData('sqlite:///' + sqlitedb),
show_datatypes=False,
show_indexes=False,
rankdir='LR',
concentrate=False)
print(f"Writing galaxy_universe.png, built from {sqlitedb}")
graph.write_png('galaxy_universe.png')
else:
print(f"No sqlitedb available at {sqlitedb}, skipping rendering")
# Build UML graph from loaded mapper
mappers = []
for attr in dir(model):
if attr[0] == '_':
continue
try:
cls = getattr(model, attr)
mappers.append(class_mapper(cls))
f.CalendarQuarter ,
f.Year ,
f.Month ,
f.Day ,
f.Day_of_Week ;""",
con=db_connection)
#DfDim = pd.read_sql('SELECT * FROM DimTbl_may5', con=db_connection)
# # BUILDING Entity relationship diagram using python
# In[ ]:
from sqlalchemy_schemadisplay import create_schema_graph
from sqlalchemy import MetaData
graph = create_schema_graph(
metadata=MetaData('mysql+pymysql://root:test@123@localhost/testdb'))
graph.write_png('my_erd.png')
# In[ ]:
import matplotlib.pyplot as plt
import matplotlib.image as mpimg
image = mpimg.imread("my_erd.png")
plt.imshow(image)
plt.show()
# # Finally Once data is retrieved, Close the DB connections
# In[ ]:
db_connection.close()
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph
# Database
host = 'localhost:3306'
engine = 'mysql'
database = 'doroto'
username = '******'
password = '******'
# General
data_types = True
indexes = False
# Generation
dsn = engine + '://' + username + ':' + password + '@' + host + '/' + database
graph = create_schema_graph(metadata=MetaData(dsn),
show_datatypes=data_types,
show_indexes=indexes)
graph.write_png('schema.png')
from sqlalchemy.orm import class_mapper
from sqlalchemy_schemadisplay import create_schema_graph, create_uml_graph
from adhocracy import model
# create a diagram of all tables
graph = create_schema_graph(
metadata=model.meta.data,
show_datatypes=True,
show_indexes=False,
rankdir='LR',
concentrate=False # Don't try to join the relation lines together
)
graph.write_gif('adhocracy-tables.gif')
# create an uml diagramm of all mapped classes
mappers = []
for attr in dir(model):
if attr[0] == '_':
continue
try:
cls = getattr(model, attr)
mappers.append(class_mapper(cls))
except:
pass
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph
if __name__ == "__main__":
graph = create_schema_graph(metadata=MetaData("sqlite:///data/database/data_2018-05-10.db"),
show_datatypes=False,
show_indexes=False,
rankdir="LR",
concentrate=False
)
graph.write_png("database_schema.png")
