Article Outline
Python mysql example 'mysql2graffle'
Functions in program:
def createOGLineFromReference( graffle, sql_reference, og_tables ) :def getOGGraphicsFromReference( sql_reference, og_tables ) :def createOGTableFromSQLTable( graffle, name, sql_table, og_tables ):def getSchemaInfo( options, sql_tables, sql_references ):def parseArguments():
Modules used in program:
import stringimport _mysqlimport reimport sys
python mysql2graffle
Python mysql example: mysql2graffle
#! /usr/bin/pythonw
#
# Modified by Michael Bianco <[email protected]> on June 26, 2009
# Written by Thomas Pan at January 21, 2007
# mysql2graffle for Omnigraffle 5 is based on
# MyDBGraffle for OmniGraffle 4 which is based on Paul Davis' work at http://www.visualdistortion.org
#
# Requires:
#
# OmniGraffle 5
# http://www.omnigroup.com/applications/omnigraffle/
#
# Python
# http://www.python.org
#
# AppScript
# http://freespace.virgin.net/hamish.sanderson/appscript.html
# sudo easy_install appscript
# sudo easy_install MySQL-python
#
# MySQL 5
# http://www.mysql.com
#
# Warranty and liability:
# THERE IS NO WARRANTY FOR THE PROGRAM, TO THE EXTENT PERMITTED
# BY APPLICABLE LAW. EXCEPT WHEN OTHERWISE STATED IN WRITING THE
# COPYRIGHT HOLDERS AND/OR OTHER PARTIES PROVIDE THE PROGRAM "AS
# IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED,
# INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
# MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE
# RISK AS TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU.
# SHOULD THE PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL
# NECESSARY SERVICING, REPAIR OR CORRECTION. YOU ARE LIABLE FOR
# DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL
# DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE PROGRAM
# (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED
# INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A
# FAILURE OF THE PROGRAM TO OPERATE WITH ANY OTHER PROGRAMS)
#
# TODO use ScriptingBridge instead of appscript
# TODO deal with constraint overlapping
# TODO deal with multiple column foreign key
# TODO deal with unique indexes
# TODO order columns based on names?
import sys
import re
from appscript import *
# from pg import DB
import _mysql
import string
####################################################
# Some default settings for OmniGraffle Graphics #
####################################################
# Common to title and all types of columns.
common_props = {}
common_props[ k.shadow_vector ] = [ 7.0, 7.0 ]
common_props[ k.shadow_fuzziness ] = 17.45
common_props[ k.autosizing ] = k.full
common_props[ k.text_placement ] = k.top
common_props[ k.draws_stroke ] = False
common_props[ k.fill ] = k.linear_fill
common_props[ k.fill_color ] = [ 1, 1, 1 ]
common_props[ k.gradient_center ] = [ 0.5, 0 ]
common_props[ k.magnets ] = [ [ 1, 0 ], [ -1, 0 ] ]
#common_props[ k.size ] = [ 90, 14 ]
#Table Name
table_name = common_props.copy()
table_name[ k.gradient_color ] = [ 0, 0, 1 ]
#Primary Keys
column_pkey = common_props.copy()
column_pkey[ k.gradient_color ] = [ 1, 0, 0 ]
#Foreign Keys
column_fkey = common_props.copy()
column_fkey[ k.gradient_color ] = [ 0, 1, 0 ]
#No Key
column_norm = common_props.copy()
column_norm[ k.gradient_color ] = [ 1, 1, 1 ]
#Line Properties
line_props = {}
line_props[ k.line_type ] = k.orthogonal
line_props[ k.head_type ] = "FilledArrow"
line_props[ k.jump ] = True
###########################################
# The query used to gather schema data. #
###########################################
query = """
select c.table_name,
c.column_name,
c.data_type,
c.is_nullable,
tc.constraint_type,
kcu.referenced_table_name,
kcu.referenced_column_name,
tc.constraint_name
from information_schema.columns as c
left join
information_schema.key_column_usage as kcu
on (
c.column_name = kcu.column_name
and c.table_schema = kcu.table_schema
and c.table_name = kcu.table_name
)
left join
information_schema.table_constraints as tc
on (
tc.constraint_name = kcu.constraint_name
and tc.table_schema = kcu.table_schema
and tc.table_name = kcu.table_name
)
where c.table_schema = 'SCHEMA_NAME'
group by
c.table_name,
c.column_name
order by
c.table_name,
c.ordinal_position
"""
#########################
# Method definitions. #
#########################
def parseArguments():
"""
I haven't taken the time to learn getopt, so I use regular expressions.
"""
options[ 'graffle' ] = 'OmniGraffle 5'
options[ 'dbhost' ] = 'localhost'
options[ 'dbport' ] = 5432
options[ 'dbuser' ] = ''
options[ 'dbpass' ] = ''
options[ 'dbname' ] = ''
options[ 'schema' ] = 'public'
for key in options:
value = options[key]
print("Enter %s (%s): " % (key, value))
data = raw_input()
if data: options[key] = data
options[ 'query' ] = re.compile( 'SCHEMA_NAME' ).sub( options[ 'dbname' ].lower(), query )
#Get the information we need to draw from the database
def getSchemaInfo( options, sql_tables, sql_references ):
"""
Connect to the database and retrieve our schema information.
"""
conn = _mysql.connect(host=options[ 'dbhost' ], user=options[ 'dbuser' ], db=options[ 'dbname' ], passwd=options['dbpass'])
conn.query( options[ 'query' ] )
rows = conn.store_result()
res = rows.fetch_row(0, 1)
for i in range( len( res ) ):
ftbl = res[i][ 'table_name' ]
fcol = res[i][ 'column_name' ]
type = res[i][ 'data_type' ]
nullable = res[i][ 'is_nullable' ]
keytype = res[i][ 'constraint_type' ]
ttbl = res[i][ 'referenced_table_name' ]
tcol = res[i][ 'referenced_column_name' ]
if not sql_tables.has_key( ftbl ):
sql_tables[ ftbl ] = []
sql_tables[ ftbl ] += [ [ fcol, type, nullable, keytype ] ]
if keytype == 'FOREIGN KEY' :
sql_references += [ [ ftbl, fcol, ttbl, tcol ] ]
#Create a table in OmniGraffle from database info
def createOGTableFromSQLTable( graffle, name, sql_table, og_tables ):
"""
Create a table in OmniGraffle using data from the database
"""
shapes = []
graphics = graffle.windows[1].document.canvases[1].graphics
graphics.end.make( new=k.shape, with_properties=table_name )
shape = graphics.last.get()
shape.text.set( name )
shapes += [ shape ]
use_props = None
for i in range( len( sql_table ) ):
if sql_table[i][3] == 'PRIMARY KEY' :
use_props = column_pkey
elif sql_table[i][3] == 'FOREIGN KEY' :
use_props = column_fkey
else :
use_props = column_norm
graphics.end.make( new=k.shape, with_properties=use_props )
shape = graphics.last.get()
shape.text.set( sql_table[i][0] )
shapes += [ shape ]
og_tables[ name.upper() ] = graffle.assemble( shapes, table_shape=[len( sql_table)+1,1] )
og_tables[ name.upper() ].slide( by={ k.x:25,k.y:25} )
#Get the source and destination graphics for a line to be drawn
def getOGGraphicsFromReference( sql_reference, og_tables ) :
ftbl = og_tables[ sql_reference[0].upper() ]
fg = None
for col in ftbl.columns[1].graphics.get() :
if( col.text.get() == sql_reference[1] ) :
fg = col.get() ;
break ;
else:
raise RuntimeError, "Failed to find graphic for " + sql_reference[0] + "( " + sql_reference[1] + " )"
ttbl = og_tables[ sql_reference[2].upper() ]
tg = None
for col in ttbl.columns[1].graphics.get() :
if( col.text.get() == sql_reference[3] ) :
tg = col.get() ;
break ;
else:
raise RuntimeError, "Failed to find graphic for " + sql_reference[2] + "( " + sql_reference[3] + " )"
return [ fg, tg ]
#Draw a line representing a reference in the database.
def createOGLineFromReference( graffle, sql_reference, og_tables ) :
tgs = getOGGraphicsFromReference( sql_reference, og_tables )
tgs[0].connect( to=tgs[1], with_properties=line_props )
#####################
# Run the script. #
#####################
options = {}
sql_tables = {}
sql_references = []
og_tables = {}
parseArguments()
graffle = app( options[ 'graffle' ] )
getSchemaInfo( options, sql_tables, sql_references )
for key in sql_tables.keys() :
createOGTableFromSQLTable( graffle, key, sql_tables[ key ], og_tables )
graffle.windows[1].document.canvases[1].layout_info.properties.set( { k.type:k.force_directed} )
graffle.windows[1].document.canvases[1].layout()
for i in range( len( sql_references ) ) :
createOGLineFromReference( graffle, sql_references[ i ], og_tables )
Python links
- Learn Python: https://pythonbasics.org/
- Python Tutorial: https://pythonprogramminglanguage.com