返回顶部

收藏

用Python开发MySQL Workbench的插件:自动创建外键

更多

安装方法: 1.下载 relationship_create_grt.py 2. 从菜单项 Scripting -> Install Plugin/Module… 中安装. 3. 重启 Workbench 4. Plugins->Catalog 中就会出现一个新的菜单项 Create Relationships

# import the wb module
from wb import *
# import the grt module
import grt
# import the mforms module for GUI stuff
import mforms

# define this Python module as a GRT module
ModuleInfo = DefineModule(name= "AutoRelationshipUtils", author= "Oracle Corp.", version="1.0")

def get_fk_candidate_list(schema, fk_name_format, match_types):
    candidate_list = []
    possible_fks = {}
    # create the list of possible foreign keys out of the list of tables
    for table in schema.tables:
        if table.primaryKey and len(table.primaryKey.columns) == 1: # composite FKs not supported
            format_args = {'table':table.name, 'pk':table.primaryKey.columns[0].name}
            fkname = fk_name_format % format_args
            possible_fks[fkname] = table

    # go through all tables in schema again, this time to find columns that seem to be a fk
    for table in schema.tables:
        for column in table.columns:
            if possible_fks.has_key(column.name):
                ref_table = possible_fks[column.name]
                ref_column = ref_table.primaryKey.columns[0].referencedColumn
                if ref_column == column:
                    continue
                if match_types and ref_column.formattedType != column.formattedType:
                    continue

                candidate_list.append((table, column, ref_table, ref_column))
    return candidate_list

class RelationshipCreator(mforms.Form):
  def __init__(self, catalog):
    mforms.Form.__init__(self, None, mforms.FormNone)

    self.catalog = catalog

    self.set_title("Create Relationships for Tables")

    box = mforms.newBox(False)
    self.set_content(box)
    box.set_padding(12)
    box.set_spacing(12)

    label = mforms.newLabel(
"""This will automatically create foreign keys for tables that match
a certain column naming pattern, allowing you to visualize relationships 
between MyISAM tables.

To use, fill the Column Pattern field with the naming convention used for
columns that are meant to be used as foreign keys. The %(table)s and %(pk)s
variable names will be substituted with the referenced table values.""")
    box.add(label, False, True)

    hbox = mforms.newBox(True)
    hbox.set_spacing(12)
    box.add(hbox, False, True)

    label = mforms.newLabel("Column Pattern:")
    hbox.add(label, False, True)
    self.pattern = mforms.newTextEntry()
    hbox.add(self.pattern, True, True)
    self.matchType = mforms.newCheckBox()
    self.matchType.set_text("Match column types")
    hbox.add(self.matchType, False, True)
    self.matchType.set_active(True)
    search = mforms.newButton()
    search.set_text("Preview Matches")
    search.add_clicked_callback(self.findMatches)
    hbox.add(search, False, True)

    self.pattern.set_value("%(table)s_id")

    self.candidateTree = mforms.newTreeView(mforms.TreeShowHeader)
    self.candidateTree.add_column(mforms.StringColumnType, "From Table", 100, False)
    self.candidateTree.add_column(mforms.StringColumnType, "Column", 100, False)
    self.candidateTree.add_column(mforms.StringColumnType, "Type", 100, False)
    self.candidateTree.add_column(mforms.StringColumnType, "To Table", 100, False)
    self.candidateTree.add_column(mforms.StringColumnType, "Column", 100, False)
    self.candidateTree.add_column(mforms.StringColumnType, "Type", 100, False)
    self.candidateTree.end_columns()
    box.add(self.candidateTree, True, True)

    hbox = mforms.newBox(True)
    hbox.set_spacing(12)
    self.matchCount = mforms.newLabel("")
    hbox.add(self.matchCount, False, True)
    self.cancelButton = mforms.newButton()
    self.cancelButton.set_text("Cancel")
    hbox.add_end(self.cancelButton, False, True)
    self.okButton = mforms.newButton()
    self.okButton.set_text("Create FKs")
    hbox.add_end(self.okButton, False, True)
    self.okButton.add_clicked_callback(self.createFKs)
    box.add(hbox, False, True)

    self.set_size(700, 600)

  def findMatches(self):
    candidates = []
    for schema in self.catalog.schemata:
      candidates += get_fk_candidate_list(schema, self.pattern.get_string_value(), self.matchType.get_active())
    self.candidateTree.clear_rows()
    for table, column, ref_table, ref_column in candidates:
      row = self.candidateTree.add_row()
      self.candidateTree.set_string(row, 0, table.name)
      self.candidateTree.set_string(row, 1, column.name)
      self.candidateTree.set_string(row, 2, column.formattedType)
      self.candidateTree.set_string(row, 3, ref_table.name)
      self.candidateTree.set_string(row, 4, ref_column.name)
      self.candidateTree.set_string(row, 5, ref_column.formattedType)
    self.matchCount.set_text("%i matches found" % len(candidates))

  def createFKs(self):
    candidates = []
    for schema in self.catalog.schemata:
      candidates += get_fk_candidate_list(schema, self.pattern.get_string_value())

    for table, column, ref_table, ref_column in candidates:
      fk = table.createForeignKey(ref_column.name+"_fk")
      fk.referencedTable = ref_table
      fk.columns.append(column)
      fk.referencedColumns.append(ref_column)

  def run(self):
    self.run_modal(self.okButton, self.cancelButton)

@ModuleInfo.plugin("wb.catalog.util.autoCreateRelationships", caption= "Create Relationships from Columns", input= [wbinputs.currentCatalog()], pluginMenu= "Catalog", type="standalone")
@ModuleInfo.export(grt.INT, grt.classes.db_Catalog)
def autoCreateRelationships(catalog):
  form = RelationshipCreator(catalog)
  form.run()
  return 0

#该片段来自于http://outofmemory.cn

标签:python,数据库

收藏

0人收藏

支持

0

反对

0

相关聚客文章
  1. zhiwei 发表 2013-12-21 05:24:36 在virtualenv环境下如何安装psycopg2
  2. 半天河 发表 2014-08-22 01:04:44 SQLAlchemy的SQL引擎学习笔记
  3. 叶金荣 发表 2017-03-02 05:15:01 干货分享《如何系统的学好Python》
  4. 龍昌 发表 2014-04-02 12:23:09 mongoengine教程(1)——概述
  5. tony 发表 2017-03-02 02:23:41 使用python-ctp接收行情并保存到HBase数据库
  6. 龍昌 发表 2014-04-03 12:46:12 mongoengine教程(2)——文档模式
  7. kaka_ace 发表 2015-06-20 10:49:02 SQLAlchemy Model 实例数据转 Dict 数据结构
  8. 龍昌 发表 2014-04-05 06:01:03 mongoengine教程(3)——数据查询
  9. 博主 发表 2011-03-29 21:37:00 python访问纯真IP数据库
  10. zhu329599788@126 发表 2016-02-25 15:54:07 Python数据库的连接
  11. 龍昌 发表 2014-04-05 12:45:50 mongoengine教程(4)——文件存储
  12. 龍昌 发表 2014-04-06 11:24:39 mongoengine教程(5)——信号

发表评论