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.

MySQL Workbench 5.1.4 Alpha Available

We’ve packaged another alpha-version of our current work-in-progress version of workbench – version 5.1.4. We’ve added more features, fixed problems and started to build packages for Fedora Core 9 along with our binaries for Ubuntu 8.04.

MySQL Workbench OSS 5.1.4 Alpha – Linux

Source Tar Ball

ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-workbench-5.1.4alpha.tar.gz
ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-workbench-5.1.4alpha.tar.gz.md5

Binaries for Fedora Core 9

ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-workbench-5.1.4-1fc9.i386.rpm
ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-workbench-5.1.4-1fc9.i386.rpm.md5

Source Packages for Fedora Core 9

ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-workbench-5.1.4-1fc9.src.rpm
ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-workbench-5.1.4-1fc9.src.rpm.md5

Binaries/Sources for Ubuntu 8.04

Add following 2 lines to your /etc/apt/sources.list:

deb ftp://ftp.mysql.com/pub/mysql/download/gui-tools/ubuntu/ binary/
deb-src ftp://ftp.mysql.com/pub/mysql/download/gui-tools/ubuntu/ source/

To download and install the binary use the command

> apt-get update

followed by

> apt-get install mysql-workbench

Build instructions for Ubuntu 8.04

http://dev.mysql.com/workbench/?page_id=152

Please grab a package (either a bin for your distro or the source pkg to build it yourself) and give it a shot. But keep in mind that this is an alpha build, so don’t use it on your production-models yet.

OpenGL and Workbench Canvas News

Recently, a bug that caused excessive memory usage and slowness (specially in large models) in Workbench has been fixed in its canvas. From the testing I’ve done, the speed for displaying diagrams seems to scale much better and dragging around tables in large models is doable in an acceptable speed. This fix is in the upcoming releases of the 5.0 and 5.1 alpha versions of Workbench.

Apart from that, we’ve been doing some work on reintroducing OpenGL support to the Workbench canvas. Unlike the initial implementation, this time OpenGL is used directly instead of going through Cairo’s experimental (and not quite reliable) support through the Glitz layer. This means that in machines with a recent graphics card, hardware acceleration will be used and working with large diagrams will be a lot smoother and faster. Of course, software rendering backends (GDI+, Quartz and Xlib) will still be supported and used in case OpenGL is not the best option.

We still use Cairo to render everything (or most things) as before, but now the pre-rendered object bitmaps are uploaded to the video card and displayed through textures. Although the “software” rendering we were using was also hardware accelerated in most cases (because GDI+ et al are implemented with acceleration by the OS), using OpenGL textures seems much faster than blitting these bitmaps to screen in the old way. Naturally, this also opens the door for some totally useless and cool eye candy ūüôā But that’s for after we get the essentials done…

There are still problems to be worked out (some of the transparencies are not working well and there are some refreshing glitches), but you can try it with the --force-opengl-render option.

MySQL Workbench 5.0.25 coming next week

As you may have noticed we’re going for a major milestone on our Workbench 5.1 roadmap. So our resources are pretty much concentrated on this√ā¬† task at the moment. But it’s just as important to keep improving our 5.0 GA version, so we will reserve the first days next week to make progress here as well. We will publish the next release of 5.0 mid-next-week. So stay tuned for a better 5.0 next week and the upcoming 5.1 alpha later this month.

Preparing For Next Release – MySQL Workbench 5.0.24 Coming Soon

Due to summer vacations – yes, even we take some time off – our release-cycle has slowed down quite a bit so it has already been more than 4 weeks since we aired an update of Workbench. Right in the middle of testing and preparing the next build, our build-server has raised the white flag. Looks like the cooling system above the unit decided, that its too hot, and put a cold shower right down on our machine. Our engineers are quickly gathering some new iron and we are meanwhile preparing the build manually this time. There are a few bugs remaining but next package will be ready soon and we put it on our download servers – so stay tuned for the next Workbench 5.0 release.

Nice Blog Postings About MySQL Workbench

Quite a few Blog postings pop up recently, let me outline two of them.

Gerry Narvaja talks about multi-page printing by using the MySQL Workbench community edition utilizing the PDF output. Find his posting here.

Weizh posted a nice step-by-step tutorial on how to use Workbench to show differences between two databases. Find it here.

Both workflows get you what you want by using the Community Edition. For those of you who want everything on a silver plate there is the Standard Edition of course, which simplifies these tasks even more.

On a side note: To get more information about the printing topic Gerry joined us on our IRC #workbench channel on irc.freenode.net. Somebody from the team usually hangs out there from 10:00 am European time till 18:00 EST. Don’t be shy and join us there if you have a WB-related question. We might also be able to answer general MySQL questions but the better channel for that is #mysql. For more information see this page.

Printing in Workbench

A common misunderstanding seems to be that the only way to increase the available “paper space” for a diagram is by increasing the size of the paper. But there is another way to do that, which is in the Model -> Diagram Size dialog, where you can set the number of pages your diagram has, vertically and horizontally (perhaps it’s not very obvious to first time users, so we’ll be working on making it more easily accessible).

model menu

diagram size

Another recurring issue is that of printing in the community version. Direct printing is currently only available in Workbench SE, but that doesn’t mean you cannot print using the OSS version. You can still export the model to a variety of formats, like PDF, PNG and SVG. And once you export as an image you can print it with any tool that can handle such files, like Acrobat Reader or something similar.

New MySQL Workbench Auto-Update Feature for SE in 5.0.22

We introduced a new library to use with our update-feature inside Workbench. With the new lib (yassl) it’s possible to use SSL encryption for the authentication/download of new SE releases. This was not possible with libcurl because we’re not allowed to use/bundle openSSL. As this update is included staring with 5.0.22 all SE users will have to download version 5.0.22 manually from the enterprise pages one more time. Please logon to https://enterprise.mysql.com/software/gui_tools.php with your mysql username and password, download the latest release and launch the installer manually. Sorry for the inconvenience.

What’s new in MySQL Workbench 6.2

The new MySQL Workbench version 6.2 has its usual share of new¬†features but also¬†brings¬†some big improvements that will be very welcome even to long time¬†users that only need the bread and butter basic functionality. Here’s the list:

Spatial Data Viewer

MySQL 5.7 will include much awaited GIS support for InnoDB tables. To make it easier to quickly visualize spatial/geometry data in geographic context, Workbench 6.2 includes a viewer for resultsets containing that type of data. The viewer will render data from each row as a separate clickable element. When clicked, you can view the rest of the data from that row in the textbox. If you have multiple queries with geometry data, you can overlay them in the same map. spatial_viewer

Geometry Data Viewer

Both the Field and Form Editors were updated to support the GEOMETRY datatype. You can view geometry data like polygons from a single row as an image or as text, in any of the common WKT, GeoJSON, GML or KML formats. form_editor

MS Access Migration

MS Access support was added to the¬†Migration Wizard in MySQL Workbench 6.2, joining¬†MS SQL Server, Sybase ASE, Sybase SQL Anywhere, PostgreSQL, SQLite and “Generic ODBC” as migration sources. You can read more about it in¬†How-To: Guide to Database Migration from MS Access using MySQL Workbench.

Metadata Lock Browser

MySQL uses metadata locking to manage access to objects (tables, triggers, and so forth). Sometimes that can be puzzling, as your query may block waiting on an object being manipulated by another connection from maybe another user. The Client Connections list was updated to take advantage of the metadata lock information provided in the performance_schema starting in MySQL 5.7.3, to show information about what locks a connection is waiting for and what it holds. client_connections_locks

Updated Client Connection Browser

Speaking of the Client Connection browser, a neat feature added in MySQL 5.6 is the connection attribute dictionary, which includes handy things like the name of the clients that are connected (as long as the client supports it). You can access that by clicking the Show Details button in the Client Connection screen. client_connections

Improved Visual Explain

In MySQL 5.7, the Optimizer Team has been doing great work in refactoring as well as innovation with the new Cost Model. The improved Visual Explain enables the DBA to now get deeper insights into Optimizer decision making, for improved performance tuning of queries. explain   The UI was also improved to allow easier navigation in large query plans.

Streamlined Query Results Panel

The query results panel was updated to centralize the many features related to result sets into a single location. Result Grid, Form Editor, Field Types, Query Stats, Execution Plan (including the traditional and Visual Explain) and the new Spatial Viewer are all easily accessible from a single interface. Screenshot 2014-09-05 14.35.39

Shared Snippets

SQL Snippets are useful to store queries and commands that are used often, but until now they could¬†only be stored locally. In 6.2, you can now store snippets in the MySQL server you’re connected to and anyone anywhere who¬†can access the .mysqlworkbench schema can also use these snippets.

MySQL Fabric Support

MySQL Fabric servers can now be added to the Workbench home screen. When clicked, these connections will dynamically query the Fabric server and individual connections for all the managed MySQL servers will be created. You can then connect to each instance as usual. Screenshot 2014-09-08 15.24.30

Run SQL Script

It often happens that people try to load gigantic SQL script files into the Workbench SQL editor just to execute them. That will rarely work, as loading files for editing uses a lot of memory and Workbench does a lot of processing in the editor (syntax highlighting, syntax checking, code folding etc). To execute arbitrarily large scripts easily, you can now use the dialog at File -> Run SQL Script: Screenshot 2014-09-05 14.48.35 ¬† The dialog lets you preview a part of the script, specify a default schema¬†(in case¬†it’s not already defined) and a default character set to use when importing it. The output window shows warnings, messages and a nice progress bar.

Include Model Scripts in Forward Engineering and Synchronization

Workbench modeling has always supported attaching SQL script files to the model, usually for documentation/organization purposes. You can now include these attachments to the output script when performing forward engineering or synchronization. Screenshot 2014-09-05 14.55.35

Direct Schema Tree Action Buttons

Screenshot 2014-09-05 15.00.21 The schema tree in the SQL Editor now has some very convenient buttons for accessing the most used functions for each object type:

  • Table or¬†Schema Inspector
  • Object structure¬†editor
  • Table data browser/editor
  • Call Stored Procedure or¬†Function

Format Note Objects in Diagrams

Note objects in diagrams can now be resized and have its contents automatically rearranged. You can also change style attributes like font, background color and text color. Screenshot 2014-09-05 15.07.27

Other improvements and bug fixes that make a difference

Resultset grid columns are now automatically resized to fit Рand if you manually resize a column, the customized size is remembered, so next time you run that query again, the columns will be back to the size you left them.

Customize font for resultset grid Рsome people want to cram more text in the resultset grid, some people prefer bigger, easier to read text. Now you can pick what you like in Preferences.

Improved state saving for the SQL Editor РOpened, closed and reordered tabs are now properly saved and restored. The scroll position and cursor location is also remembered.

MySQL password is remembered for the session, even if not stored in the keychain, so you don’t need to re-enter it when¬†a new connection is needed.

Keyboard shortcuts now work in the Scripting Shell.

Resume data copy in Migration Wizard. If a data copy fails during database migration (because of a timeout or network failure, for example), you can now click Resume to retry the data copy. Workbench will find the last row that was copied successfully and try to restart the copy from that row.

Platform Updates

MySQL Workbench 6.2 also finally adds native 64bit support for Windows. This should allow working with larger data sets and script files. Oracle Linux/RHEL 7 support was added. To improve quality and user experience, we will be providing 64-bit binaries for Linux. Linux users who want 32-bit binaries, can compile from source.