Write Python in MySQL
Mysql Workbench Scripting Shell
Everybody knows MySQL Workbench
The MySQL Workbench Scripting-> Scripting Shell item opens an editor that allows you to control SQL execution in Python.
When you start the editor anew, the template already contains a Python script, but this line ʻimport grt` is important, which allows the Mysql Workbench editor to be controlled by Python.
For example, you can use the grt.root.wb.sqlEditors [0] .executeScript () function to execute a string in a Python script as SQL.
Since string format can be applied to strings, variables can be flexibly incorporated into SQL.
Script example
For example, for the top 100 records in table A, the process of inserting records corresponding to the county column of table A into table B can be written as follows.
import grt
#import mforms
query_select = "select * from table_A limit {};"
query_insert = "insert into table_B (country) {};"
result = grt.root.wb.sqlEditors[0].executeScript(query_select.format(100))
n = result[0].rowCount -1
for i in range(0, n):
country = result[0].stringFieldValueByName("country")
editor.executeScript(query_insert.format(country+"_duplicated"))
result[0].nextRow()
There are quite a few things that you can easily write if you write in Python even if you can not write unless you become a SQL master There is also a saying that the biggest bottleneck is the time to write code, so let's actively use Python with MySQL.
Recommended Posts