如何在MySQL工作台中使用Python脚本执行SQL查询?

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.

我想在MySQL Workbench中执行来自Python脚本环境的SQL查询。我查看了grt模块的MySQL Workbench文档，找到了executeScript方法，但似乎不能使用它进行查询。

Executing this Python code:

执行这个Python代码:

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.

我不理解虚拟grt::ListRef executeScript (const std:::string & sql)意味着什么，因此我无法正确格式化查询，然而，错误消息似乎表明无论如何executeScript方法都不存在。我查看的大多数文档都有格式正确的函数调用的示例，但我似乎找不到任何可执行文件。

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

我所要做的就是在MySQL工作台Python脚本环境中作为一个SQL查询运行我的字符串。

Thank you!

谢谢你！

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

我是Python和SQL的新手，所以请耐心点。:)

3 个解决方案

#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:

要运行executeScript函数，您需要与sqleditor对象进行交互。为了进行测试，请使用示例数据库在MS Windows上执行下一个操作:

Start MySQLWorkbench
开始MySQLWorkbench
connect to local database
连接到本地数据库
select sakila from SCHEMAS
从模式选择sakila
start scripting shell with Tools->scripting shell or (Ctrl+F3)
使用工具->脚本shell或(Ctrl+F3)启动脚本shell
Add new python script (test.py)
添加新的python脚本(test.py)
Save script with the content below
用下面的内容保存脚本
run script in scripting shell
在脚本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"

要了解如何在脚本中引用对象，很容易使用Globals Tree面板的类浏览器，并使用鼠标右键单击对象，选择“Python的复制路径”

#2

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

如果需要在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

#3

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:

(第一个)问题似乎是，您使用了一个名为executeScript()的函数，您没有在任何地方定义或删除它。如果它在grt模块中(我不熟悉)，您必须按照以下方式进行:

from grt import executeScript

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

#1