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 <a href="http://wb.fabforce.eu/doc/globals/classdb__query___query_buffer.html">db_query_QueryBuffer</a>. You can read more about the available types and inputs in the relevant documentation.
The code itself is straightforward:
- it takes the query code,
- executes it through the SQL Editor object that owns the query buffer and
- 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.
- copy the plugin file from the Workbench plugins directory to some folder of yours (eg your home directory or Desktop);
- rename it to
verticalquery_grt.py; - 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
