6

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. :)

Improve this question
1
  • As a user requirement, this MUST run in the MySQL Workbench Scripting Shell environment. Commented Apr 4, 2013 at 22:29

3 Answers 3

Reset to default
4

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"

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

2 Comments

Thank you but this question was about executing an SQL query in a Python script from MySQL Workbench, not from cmd.exe or PowerShell.
Upvoted just because it's good to know how to execute from cmd
0

The (first) problem seems to be that you use a function called executeScript(), which you haven't defined or taken from anywhere. If it is in the grt module (which I am not familiar with) you have to do it as follows:

from grt import executeScript

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

10 Comments

I imported the entire module with "import grt"... Even so replacing that with "from grt import executeScript" produces "NameError: name 'executeScript' is not defined"
@JaneGoodall - The thing is that you either explicitly have to import that method by doing 'from grt import executeScript' OR use the grt-prefix for the method by doing 'grt.executeScript()'. That's just how Python works. I do find it interesting though, that even though you now explicitely import the method, it still appears to be undefined. I think you do need to show us some more of your code before we can judge it. Why not use pastebin to post the whole script?
My entire script is in the original question, in the first grey box. I think executeScript is an instance-level method (it's hard to tell because I don't understand the documentation, which I linked in the original question) so I need to create an instance of the correct type of object and call it like obj.executeScript(foo,bar). Normally, this object is a connection to the server a) but I'm already connected to the server in MySQL Workbench and b) calling it that way requires me to put the password into the Python file in plaintext which is really dangerous.
Also, changing my call to grt.executeScript(querystring,'sql') produces "AttributeError: 'module' object has no attribute 'executeScript'"
Ah, in that case I'm afraid I can't help you out here. But just a note about the password in the python file. I don't know if you will be distributing this script but as long as it just sits and works on your own computer, I don't think there's anything to worry about.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.