The Geek Diary

How to Use External Python modules in MySQL Shell

by

MySQL Shell is a great tool for working with MySQL. One of the features that make it stand out compared to the traditional mysql command-line client is the support for JavaScript and Python in addition to SQL statements. This allows you to write code you otherwise would have had to write outside the client. This post will look into the use of Python and more specifically external modules.

External Python modules in MySQL Shell

Using Standard Modules

The simplest usage of modules in MySQL Shell involves using standard Python modules installed in the default location for your system. You can use the standard Python modules just as you would do in any other Python script. For example, if you need to create UUIDs you can use the uuid module:

mysql-py> import uuid
mysql-py> print(uuid.uuid1().hex)
9e8ef45ea12911e8a8a6b0359feab2bb

This on its own is great, but what about your own modules? Sure, that is supported as well. Before showing how you can access your own modules, let’s create a simple module to use as an example.

Example Module

For the purpose of this post, the following code should be saved in the file table_tools.py. You can save it in whatever directory you keep your Python libraries. The code is:

def describe(table):
    fmt = "{0:<11}   {1:<8}    {2:<4}   {3:<3}    {4:<9}    {5:<14}"
 
    # Create query against information_schema.COLUMNS
    session = table.get_session()
    i_s = session.get_schema("information_schema")
    i_s_columns = i_s.get_table("COLUMNS")
 
    query = i_s_columns.select(
        "COLUMN_NAME AS Field",
        "COLUMN_TYPE AS Type",
        "IS_NULLABLE AS `Null`",
        "COLUMN_KEY AS Key",
        "COLUMN_DEFAULT AS Default",
        "EXTRA AS Extra"
    )
    query = query.where("TABLE_SCHEMA = :schema AND TABLE_NAME = :table")
    query = query.order_by("ORDINAL_POSITION")
 
    query = query.bind("schema", table.schema.name)
    query = query.bind("table", table.name)
 
    result = query.execute()
 
    # Print the column names
    column_names = [column.column_name for column in result.get_columns()]
    print(fmt.format(*column_names))
    print("-"*67)
 
    for row in result.fetch_all():
        print(fmt.format(*row))

The describe function takes a Table object from which it works backwards to get the session object. It then queries the information_schema.COLUMNS view to get the same information about the table as the DESC SQL command. Both the table and schema name can be found through the table object. Finally, the information is printed.

The example is overly simplified for general usage as it does not change the width of the output based on the length of the data, and there is no error handling whatsoever. However, this is on purpose to focus on the usage of the code from within MySQL Shell rather than on the code.

Note: The same code works in a MySQL Connector/Python script except that the rows are returned as mysqlx.result.Row objects. So, the loop printing the rows look a little different:

for row in result.fetch_all():
    values = [row[name] or "" for name in column_names]
    print(fmt.format(*values))

With the function ready, it is time to look at how you can import it into MySQL Shell.

Importing Modules Into MySQL Shell

In order to be able to import a module into MySQL Shell, it must be in the path searched by Python. If you have saved table_tools.py into a location already searched, then that is it. However, a likely more common scenario is that you have saved the file in a custom location. In that case, you need to tell Python where to look for the files.

You modify the search path in MySQL Shell just as you would in a regular Python program. If you for example have saved the file to D:\MySQL\Shell\Python, then you can add that to the path using the following code:

import sys
sys.path.append("D:\MySQL\Shell\Python")

If this is something you need as a one off, then it is fine just to modify the path directly in MySQL Shell. However, if you are working on some utilities that you want to reuse, it becomes tedious. MySQL Shell has support for configuration files where commands can be executed. The one for Python is named mysqlshrc.py (and mysqlshrc.js for JavaScript).

MySQL Shell searches for the mysqlshrc.py file in four locations including global locations as well as user specific locations. You can see the full list and the search order in the MySQL Shell User Guide. The user specific file is %APPDATA%\MySQL\mysqlsh\mysqlshrc.py on Microsoft Windows and $HOME/.mysqlsh/mysqlshrc.py on Linux and macOS.

You can do more than just changing the search path in the mysqlshrc.py file. However, for this example nothing else is needed. An alternative is to set PYTHONPATH environment variable with the search path.

Using the Module

Now that MySQL Shell has been set up to search in the path where your module is saved, you can use it in MySQL Shell. For example to get the description of the world.city table, you can use the following commands:

mysql-py> import table_tools
mysql-py> \use world
Default schema `world` accessible through db.
 
mysql-py> table_tools.describe(db.city)
Field         Type        Null   Key    Default      Extra
-------------------------------------------------------------------
ID            int(11)     NO     PRI    None         auto_increment
Name          char(35)    NO
CountryCode   char(3)     NO     MUL
District      char(20)    NO
Population    int(11)     NO            0

The '\use world' command sets the default schema to the world database. As a side effect, it also makes the tables in the world database available as properties of the db object. So, it is possible to pass an object for the world.city table as db.city to table_tools.describe() function.

That is it. Now it is your turn to explore the possibilities that have been opened with MySQL Shell.

You May Also Like