Python Scripting in Workbench

Updated: 2010-8-11 updated sample code to reflect changes in plugin registration API

Python support has been added to the latest version of MySQL Workbench.

In addition to Lua, you can now write scripts and modules or interact with the GRT shell using the Python language. The integration allows you to use GRT objects and modules mostly in the same way you would do with normal Python objects. The built-in grt module contains everything related to the GRT that’s exposed to Python, including:

  • custom types for GRT lists, dicts and objects
  • wrappers for GRT classes, that can be used and instantiated as a normal Python class;
  • wrappers for registered GRT modules, that can be used like normal modules;
  • a reference to the root node of the GRT globals tree;

You can inspect these objects with the standard dir() command and in some cases with help(), to access the built-in documentation (although still incomplete).

Below is a sample shell session demonstrating the Python support:

>>> import grt

>>> dir(grt)
[‘DICT’, ‘DOUBLE’, ‘Dict’, ‘Function’, ‘INT’, ‘LIST’, ‘List’, ‘Method’, ‘Module’, ‘OBJECT’, ‘Object’, ‘STRING’, ‘__GRT__’, ‘__doc__’, ‘__name__’, ‘classes’, ‘get’, ‘modules’, ‘root’, ‘send_error’, ‘send_info’, ‘send_output’, ‘send_warning’, ‘softspace’, ‘write’]

>>> grt.classes
<module ‘grt.classes’ (built-in)>

>>> dir(grt.classes)

[‘GrtLogEntry’, ‘GrtLogObject’, ‘GrtMessage’, ‘GrtNamedObject’, ‘GrtObject’, ‘GrtStoredNote’, ‘GrtVersion’, ‘__doc__’, ‘__name__’, ‘app_Application’, ‘app_CommandItem’, ‘app_CustomDataField’, ‘app_Document’, ‘app_DocumentInfo’, ‘app_DocumentPlugin’, ‘app_Info’, ….

>>> for schema in grt.root.wb.doc.physicalModels[0].catalog.schemata:
… print schema.name

mydb
sakila

>>> table=grt.root.wb.doc.physicalModels[0].catalog.schemata[1].tables[0]

>>> dir(table)
[‘__callmethod__’, ‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__getattribute__’, ‘__grtclassname__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__str__’, ‘__weakref__’, ‘addColumn’, ‘addIndex’, ‘addPrimaryKeyColumn’, ‘avgRowLength’, ‘checksum’, ‘columns’, ‘comment’, ‘commentedOut’, ‘connection’, ‘connectionString’, ‘createDate’, ‘createForeignKey’, ‘customData’, ‘defaultCharacterSetName’, ‘defaultCollationName’, ‘delayKeyWrite’, ‘foreignKeys’, ‘indices’, ‘inserts’, ‘isDependantTable’, ‘isForeignKeyColumn’, ‘isPrimaryKeyColumn’, ‘isStub’, ‘isSystem’, ‘isTemporary’, ‘lastChangeDate’, ‘maxRows’, ‘mergeInsert’, ‘mergeUnion’, ‘minRows’, ‘modelOnly’, ‘name’, ‘nextAutoInc’, ‘oldName’, ‘owner’, ‘packKeys’, ‘partitionCount’, ‘partitionDefinitions’, ‘partitionExpression’, ‘partitionType’, ‘password’, ‘primaryKey’, ‘raidChunkSize’, ‘raidChunks’, ‘raidType’, ‘removeColumn’, ‘removeForeignKey’, ‘removeIndex’, ‘removePrimaryKeyColumn’, ‘rowFormat’, ‘subpartitionCount’, ‘subpartitionExpression’, ‘subpartitionType’, ‘tableDataDir’, ‘tableEngine’, ‘tableIndexDir’, ‘temp_sql’, ‘temporaryScope’, ‘triggers’]

>>> table.name
‘actor’

>>> table.name= table.name.upper()

>>> table.name
‘ACTOR’

>>> table.columns[1].formattedType(1)
‘VARCHAR(45)’

>>> isinstance(table, grt.classes.db_mysql_Table)
True

>>> dir(grt.modules)
[‘DbMySQL’, ‘DbUtils’, ‘MySQLDbDiffReportingModule’, ‘MySQLDbModule’, ‘MySQLEditorsModule’, ‘MySQLSchemaReportingModule’, ‘MysqlSqlFacade’, ‘PluginManager’, ‘PropelExport’, ‘PyWbUtils’, ‘WbEditorsModule’, ‘WbModel’, ‘WbModuleValidation’, ‘WbModuleValidationMySQL’, ‘WbMysqlImport’, ‘Workbench’, ‘__doc__’, ‘__name__’]

>>> from grt.modules import Workbench
>>> Workbench.copyToClipboard(“HELLO WORLD”)
1L

Modules and Plugins

Writing Workbench modules and plugins is very simple task in Python. For modules (collections of routines available to other scripts and modules, from any language); all you need to do is to import the wb module and call DefineModule() with a few arguments. Defining a function you wrote to be exported can be done using the @export decorator from the object returned by DefineModule().

To make your module also export plugins (a function that is exposed to the user in menus or others ways), the @plugin can be used in addition to @export.

Here’s a sample Python module, exporting a function which also works as a Plugin, callable from Plugins -> Catalog -> . It will simply dump a list of all columns from all tables in all schemata in your model to the Output pane.

# import the wb module, must be imported this way for the automatic module setup to work

from wb import *

# import the grt module

import grt

# define this Python module as a GRT module

ModuleInfo=DefineModule(name= “PyWbUtils”, author= “Sun Microsystems Inc.”, version=“1.0”)

# this is just a function used by the plugin, it’s not exported

def printTableLine(fields, filler= ” “):

print “|”,

for text, size in fields:

print text.ljust(size, filler), “|”,

print

# @wbexport makes this function be exported by the module and also describes the return and

# argument types of the function

# @wbplugin defines the name of the plugin to “wb.catalog.util.dumpColumns”, sets the caption to be

# shown in places like the menu, where to take input arguments from and also that it should be included

# in the Catalog submenu in Plugins.

@ModuleInfo.plugin(“wb.catalog.util.dumpColumns”, caption= “Dump All Table Columns”, input= [wbinputs.currentCatalog()], pluginMenu= “Catalog”)

@ModuleInfo.export(grt.INT, grt.classes.db_Catalog)

def printAllColumns(catalog):

lines= []

schemalen= 0

tablelen= 0

columnlen= 0

typelen= 0

for schema in catalog.schemata:

schemalen= max(schemalen, len(schema.name))

for table in schema.tables:

tablelen= max(tablelen, len(table.name))

for column in table.columns:

columnlen= max(columnlen, len(column.name))

typelen= max(typelen, len(column.formattedType(1)))

lines.append((schema.name, table.name, column.name, column.formattedType(1)))

printTableLine([(“-“, schemalen),
(“-“, tablelen),
(“-“, columnlen),
(“-“, typelen)], “-“)

printTableLine([(“Schema”, schemalen),
(“Table”, tablelen),
(“Column”, columnlen),
(“Type”, typelen)])

printTableLine([(“-“, schemalen),
(“-“, tablelen),
(“-“, columnlen),
(“-“, typelen)], “-“)

for s,t,c,dt in lines:

printTableLine([(s, schemalen), (t, tablelen), (c, columnlen), (dt, typelen)])

printTableLine([(“-“, schemalen),
(“-“, tablelen),
(“-“, columnlen),
(“-“, typelen)], “-“)

span style=”color: #cdbe70″>print len(lines), “columns printed”

return 0

Truncated output for the sakila model:

	| ------ | ------- | ----------- | ------------- |
	| Schema | Table   | Column      | Type          |
	| ------ | ------- | ----------- | ------------- |
	| sakila | actor   | actor_id    | SMALLINT      |
	| sakila | actor   | first_name  | VARCHAR(45)   |
	| sakila | actor   | last_name   | VARCHAR(45)   |
	| sakila | actor   | last_update | TIMESTAMP     |
	| sakila | address | address_id  | SMALLINT      |
	| sakila | address | address     | VARCHAR(50)   |
	| sakila | address | address2    | VARCHAR(50)   |
	| sakila | address | district    | VARCHAR(20)   |
	| sakila | address | city_id     | SMALLINT      |
	| sakila | address | postal_code | VARCHAR(10)   |
	....
	| ------ | ------- | ----------- | ------------- |
	89 columns printed

With the large amount of libraries available for Python, the possibilities you have for writing Workbench plugins and modules are now much bigger.

Also, to facilitate executing your own scripts, a new command to run script files has been added to the Tools menu.

Author: Mike Lischke

Team Lead MySQL Workbench

7 thoughts on “Python Scripting in Workbench”

  1. plese consider PHP as a scripting language too, php is a serious tool and significant part of the comunity using mysql knows PHP, probably better then lua and even python. thanks

  2. Before adding PHP, we will work on getting the language support pluggable so that anyone can add support to (almost) any language they want without bloating the default distribution with lots of other
    unused language runtimes. That should allow making Python support optional and adding PHP, JavaScript, ruby, perl or whatever is ones cup of tea 🙂

    But that’s sort of a lower priority feature, considering all the many other things we have to work on.

  3. That’s great news. I’ve allready seen that Python is a very well designed language. I am a php developer but I am happy to learn Python.

Leave a Reply