MySQL Workbench Plugin: Execute Query to Text Output

In MySQL Workbench 5.2.26 a new query execution command is available, where query output is sent as text to the text Output tab of the SQL Editor. Some MySQL Workbench users liked the “Results to Text” option available in Microsoft SQL Server Management Studio. Cool thing is with a few lines of Python we implemented this command using the SQL Editor scripting API.

For full documentation on scripting and plugin development, refer to the documentation pointers page.

In this post, you will learn:

  • Python script for implementing “Results to Text”
  • How you can customize the script to deliver your own customized results format command.

Execute Query to Text (accessible from Query -> Execute (All or Selection) to Text), will execute the query you typed in textual form into the Output tab in the SQL Editor. The output is similar to that of the MySQL command line client and can be copy/pasted as plain text. But the command line client has a different, interesting output format, activated through the –vertical command line option. It changes the output from a tabular to a form-like format, where row values are displayed as column name/value pairs:

We will try emulating that format using our modified plugin.

The Original Plugin Code

The goals for the original plugin shipped with Workbench were:

  • Provide an alterntive to the Results Grid output
  • Provide MySQL CLI and MS SQL Server Studio Text Formatted results
  • Add “Execute to Text” to the Query Menu

You can locate the original code for the plugin we want to modify in the sqlide_grt.py file, in the MySQL Workbench distribution (in Windows it will be in the modules directory in the WB folder, in MacOS X it will be in MySQLWorkbench.app/Contents/PlugIns and in Linux, in /usr/lib/mysql-workbench/modules).

# 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= "SQLIDEUtils", author= "Oracle Corp.", version="1.0")

@ModuleInfo.plugin("wb.sqlide.executeToTextOutput", caption= "Execute Query Into Text Output", input= [wbinputs.currentQueryBuffer()], pluginMenu= "SQL/Utilities")
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer)
def executeQueryAsText(qbuffer):
  editor= qbuffer.owner
  sql= qbuffer.selectedText or qbuffer.script
  resultsets= editor.executeScript(sql)
  editor.addToOutput("Query Output:\n", 1)
  for result in resultsets:
    editor.addToOutput("> %s\n\n" % result.sql, 0)
    line= []
    column_lengths=[]
    ncolumns= len(result.columns)
    for column in result.columns:
      line.append(column.name + " "*5)
      column_lengths.append(len(column.name)+5)

    separator = []
    for c in column_lengths:
        separator.append("-"*c)
    separator= " + ".join(separator)
    editor.addToOutput("+ "+separator+" +\n", 0)

    line= " | ".join(line)
    editor.addToOutput("| "+line+" |\n", 0)

    editor.addToOutput("+ "+separator+" +\n", 0)

    rows = []
    ok= result.goToFirstRow()
    while ok:
      line= []
      for i in range(ncolumns):
        value = result.stringFieldValue(i)
        if value is None:
          value = "NULL"
        line.append(value.ljust(column_lengths[i]))
      line= " | ".join(line)
      rows.append("| "+line+" |\n")
      ok= result.nextRow()
    # much faster to do it at once than add lines one by one
    editor.addToOutput("".join(rows), 0)

    editor.addToOutput("+ "+separator+" +\n", 0)
    editor.addToOutput("%i rows\n" % len(rows), 0)

  return 0

Lines 1 to 6 import some Workbench specific Python modules:

  • wb, which contains various utility functions for creating plugins;
  • grt, for working with Workbench objects and interfacing with it and
  • mforms, for creating GUIs.
@ModuleInfo.plugin("wb.sqlide.executeToTextOutput", caption= "Execute Query Into Text Output", input= [wbinputs.currentQueryBuffer()], pluginMenu= "SQL/Utilities")
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer)
def executeQueryAsText(qbuffer):

@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer) declares the return type (grt.INT by convention) and argument types of the plugin function defined further down. In the line above it, a unique identifier for the plugin is given, followed by a default caption to use in places such as menus, the input values taken by the plugin and the location in the Plugins menu where it should be placed.

The plugin executes the current query, so the argument it requests is wbinputs.currentQueryBuffer() (the selected query buffer tab), which has a type of db_query_QueryBuffer. You can read more about the available types and inputs in the relevant documentation.

The code itself is straightforward:

  1. it takes the query code,
  2. executes it through the SQL Editor object that owns the query buffer and
  3. renders the output in the text Output tab.

Custom Plugin

The goals for the custom plugin are:

  • Provide an custom alterntive to the Results Grid output
  • Provide text results column name/value pairs formatted output
  • Add “Execute to Vertical Formatted Text” to the Query Menu

To create the modified version, we can copy the above plugin and make some changes.

  1. copy the plugin file from the Workbench plugins directory to some folder of yours (eg your home directory or Desktop);
  2. rename it to verticalquery_grt.py;
  3. open it in some text editor of your liking.

First, we change the module info:

ModuleInfo = DefineModule(name= "QueryToVerticalFormat", author= "WB Blog", version="1.0")

The plugin arguments are the same, so we only need to update its identifier and name:

@ModuleInfo.plugin("wbblog.executeToTextOutputVertical", caption= "Execute Query Into Text Output (vertical)", input= [wbinputs.currentQueryBuffer()], pluginMenu= "SQL/Utilities")
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer)
def executeQueryAsTextVertical(qbuffer):

You can see the body of the function in the complete sample module file here.

Trying it Out

To install the module, you can use the Scripting -> Install Module/Script File… menu command. Select the newly created plugin file (verticalquery_grt.py) from the file browser and click Open.
Once installed, restart Workbench and run it:

You can download the entire, modified sample plugin here

18 thoughts on “MySQL Workbench Plugin: Execute Query to Text Output”

  1. Thanks! I had been looking for some examples of scripting the new Workbench application with Python.

  2. Just curious – in what cases do people like to use this query to text output over the regular query interface (e.g., with CSV export)?

  3. I wondered the same thing at first, but it can be handy if you need to quickly copy/paste the whole resultset or a bunch of records to an email or something like that.

  4. Thanks for the update, the product is becoming stable for production use, I now use models heavily and it’s fantastic, one request please : when running a query the results for each tab should appear in there own result container for each tab (not in 1 result container, or the option to save each result), this is a real pain in heavy use of comparing or evaluating results, this is common practice in nearly every other query editor so why wreck it !

  5. @Nicola
    Yes, this is a bug. It will be fixed in the next release.

    @Jon
    You can click the “pin” button in the resultset toolbar, that will make the resultset to stay there when you run other queries. If you click the pin in the main toolbar, all resultsets will be pinned by default and you have to close them explicitly.

  6. Great example, thanks.

    But somehow this is not working with me. I first tried to write my own module, and then downloaded and installed your example file. MySQL workbench says that the installation went fine and requests to restart the application, but when I restart it, the new menu entry does not appear.

    I’m using “MySQL Workbench 5.2.26” with Ubuntu Linux 10.04.

  7. nice to pin result tabs.
    but i think query tabs and result tabs should be related to each other in some way.
    maybe the actions, text output and history on the output tab too.

  8. Remember you can also create new connection tabs (Query -> New Tab to Current Server) and always use only one query buffer. That way you get something similar to what QB looked like.

  9. Hi there
    its a great post but i am searching for ways to deal wiht mform.
    does somebody have an idea how to open form??

  10. Tried to execute “show engine innodb status” – result gets cutoff (8 lines and … at the end) – any idea what could be a problem? Thank!

  11. Hi all,

    I was looking for a function in MySQL Workbench which streams a very big result set (some GB) directly into a file (csv) on my local desktop.
    When I found the “Execute Query to text output” I was very happy cause I thought it would just do what I was looking for. Now I know, it has another purpose. However, do you know if there is such functionality which I am looking for or how to work around it?

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.