Write Python in MySQL

Mysql Workbench Scripting Shell Everybody knows MySQL Workbench mysqlworkbench.png

The MySQL Workbench Scripting-> Scripting Shell item opens an editor that allows you to control SQL execution in Python.

mysqlworkbench2.png

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

Write Python in MySQL
Write beta distribution in Python
Write python in Rstudio (reticulate)
Write a binary search in Python
Write JSON Schema in Python DSL
Write an HTTP / 2 server in Python
Write AWS Lambda function in Python
Write A * (A-star) algorithm in Python
Write selenium test code in python
Write a pie chart in Python
Write a vim plugin in Python
How to use Mysql in python
Write a depth-first search in Python
Write C unit tests in Python
Quadtree in Python --2
Python in optimization
Metaprogramming in Python
Python 3.3 in Anaconda
Geocoding in python
SendKeys in Python
Meta-analysis in Python
Unittest in python
Epoch in Python
Discord in Python
Sudoku in Python
nCr in python
N-Gram in Python
Programming in python
Plink in Python
Constant in python
Lifegame in Python.
FizzBuzz in Python
Sqlite in python
StepAIC in Python
N-gram in python
LINE-Bot [0] in Python
Csv in python
Disassemble in Python
Reflection in Python
Constant in python
nCr in Python.
format in python
Scons in Python3
Puyo Puyo in python
python in virtualenv
PPAP in Python
Quad-tree in Python
Reflection in Python
Chemistry in Python
Hashable in python
DirectLiNGAM in Python
LiNGAM in Python
Flatten in python
flatten in python
Write documentation in Sphinx with Python Livereload
Write a short property definition in Python
Write O_SYNC file in C and Python
Write a Caesar cipher program in Python
Read and write JSON files in Python
Write a simple greedy algorithm in Python
Write python modules in fortran using f2py