How to execute an SQL query with Python script in MySQL Workbench

Question

I want to execute SQL queries from the Python script environment in MySQL Workbench. I looked at the MySQL Workbench documentation for the grt module and found the executeScript method but I can't seem to use it to make queries.

Executing this Python code:

import grt

querystring = "select * from Purchases WHERE PurchaseAmount > 600 and PurchaseAmount < 2500"
executeScript(querystring)

produces the following error message:

Uncaught exception while executing [filepath]runquery.py:

File "[filepath]runquery.py", line 10, in <module>

executeScript(querystring)

NameError: name 'executeScript' is not defined

I don't understand what virtual grt::ListRef executeScript ( const std::string & sql ) means so I can't format my query properly, however, the error message seems to indicate that the executeScript method doesn't exist anyway. Most documentation I look at has examples of correctly-formatted function calls but I can't seem to find any for executeScript.

All I want to do is literally run my string as an SQL query within the MySQL Workbench Python scripting environment.

Thank you!

I am new to Python and SQL so please be patient. :)

Answer 1

To run executeScript function you need to interact with an sqleditor object. For testing, do the next on MS Windows with the example databases:

1. Start MySQLWorkbench
2. connect to local database
3. select sakila from SCHEMAS
4. start scripting shell with Tools->scripting shell or (Ctrl+F3)
5. Add new python script (test.py)
6. Save script with the content below
7. run script in scripting shell

Script content:

import grt

result = grt.root.wb.sqlEditors[0].executeScript("select * from actor limit 10;")

for col in result[0].columns:
    print col.name

To find out how to reference objects in the script, it is very easy to use the Globals Tree panel's class browser and using right mouse click on the object and choose "Copy Path for Python"

Answer 2

You can run something like the following command if you need to run your script from command line in Windows:

"C:\Program Files\MySQL\MySQL Workbench 6.1 CE\MySQLWorkbench.exe" -query "Local instance MySQL56" -run-python "execfile('c:\Users\Root\AppData\Roaming\MySQL\Workbench\scripts\script.py')" -log-to-stderr -v