MySQL Shell 9.0  /  ...  /  Scripting Sessions in JavaScript and Python Mode

4.2.3 Scripting Sessions in JavaScript and Python Mode

You can use functions available in JavaScript and Python mode to create multiple session objects of your chosen types and assign them to variables. These session objects let you establish and manage concurrent connections to work with multiple MySQL Server instances, or with the same instance in multiple ways, from a single MySQL Shell instance.

Functions to create session objects are available in the mysqlx and mysql JavaScript and Python modules. These modules must be imported before use, which is done automatically when MySQL Shell is used in interactive mode. The function mysqlx.getSession() opens an X Protocol connection to a MySQL Server instance using the specified connection data, and returns a Session object to represent the connection. The functions mysql.getClassicSession() and mysql.getSession() open a classic MySQL protocol connection to a MySQL Server instance using the specified connection data, and return a ClassicSession object to represent the connection. With these functions, the connection protocol that MySQL Shell uses is built into the function rather than being selected using a separate option, so you must choose the appropriate function to match the correct protocol for the port.

MySQL Shell provides the openSession() method in the shell global object, which can be used in either JavaScript or Python mode. shell.openSession() works with both X Protocol and classic MySQL protocol. You specify the connection protocol as part of the connection data, or let MySQL Shell automatically detect it based on your other connection parameters (such as the default port number for the protocol).

The connection data for all these functions can be specified as a URI-like connection string, or as a dictionary of key-value pairs. You can access the returned session object using the variable to which you assign it. This example shows how to open a classic MySQL protocol connection using the mysql.getClassicSession() function, which returns a ClassicSession object to represent the connection:

mysql-js> var s1 = mysql.getClassicSession('user@localhost:3306', 'password');
mysql-js> s1
<ClassicSession:user@localhost:3306>

This example shows how to use shell.openSession() in Python mode to open an X Protocol connection with compression required for the connection. A Session object is returned:

mysql-py> s2 = shell.open_session('mysqlx://user@localhost:33060?compression=required', 'password')
mysql-py> s2
<Session:user@localhost:33060>

Session objects that you create in JavaScript mode using these functions can only be used in JavaScript mode, and the same happens if the session object is created in Python mode. You cannot create multiple session objects in SQL mode. Although you can only reference session objects using their assigned variables in the mode where you created them, you can use the shell.setSession() method in any mode to set as the session global object a session object that you have created and assigned to a variable. For example:

mysql-js> var s3 = mysqlx.getSession('user@localhost:33060', 'password');
mysql-js> s3
<Session:user@localhost:33060>
mysql-js> shell.setSession(s3);
<Session:user@localhost:33060>
mysql-js> session
<Session:user@localhost:33060>
mysql-js> shell.status();
 shell.status()
MySQL Shell version 8.1.0-commercial

Connection Id:                9
Current schema:
Current user:                 root@localhost
SSL:                          Cipher in use: TLS_AES_256_GCM_SHA384 TLSv1.3
Using delimiter:              ;
Server version:               8.1.0-commercial MySQL Enterprise Server - Commercial
Protocol version:             Classic 10
Client library:               8.1.0
Connection:                   localhost via TCP/IP
TCP port:                     3306
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Result characterset:          utf8mb4
Compression:                  Disabled
Uptime:                       9 hours 42 min 5.0000 sec

Threads: 2  Questions: 61  Slow queries: 0  Opens: 176  Flush tables: 3  Open tables: 95  Queries per second avg: 0.001

The session object s3 is now available using the session global object, so the X Protocol connection it represents can be accessed from any of MySQL Shell's modes: SQL mode, JavaScript mode, and Python mode. Details of this connection can also now be displayed using the shell.status() method, which only displays the details for the connection represented by the session global object. If the MySQL Shell instance has one or more open connections but none of them are set as the session global object, the shell.status() method returns Not Connected.

A session object that you set using shell.setSession() replaces any existing session object that was set as the session global object. If the replaced session object was originally created and assigned to a variable using one of the mysqlx or mysql functions or shell.openSession(), it still exists and its connection remains open. You can continue to use this connection in the MySQL Shell mode where it was originally created, and you can make it into the session global object again at any time using shell.setSession(). If the replaced session object was created with the shell.connect() method and assigned to a variable, the same is true. If the replaced session object was created while starting MySQL Shell, or using the \connect command, or using the shell.connect() method but without assigning it to a variable, its connection is closed, and you must recreate the session object if you want to use it again.