As well as interactive code execution, MySQL Shell provides batch code execution from:
A file loaded for processing.
A file containing code that is redirected to the standard input for execution.
Code from a different source that is redirected to the standard input for execution.
As an alternative to batch execution of a file, you can also control MySQL Shell from a terminal, see Section 5.8, “API Command Line Integration”.
In batch mode, all the command logic described at
Section 5.2, “Interactive Code Execution” is not
available, only valid code for the active language can be
executed. When processing SQL code, it is executed statement by
statement using the following logic: read/process/print result.
When processing non-SQL code, it is loaded entirely from the input
source and executed as a unit. Use the
-i) command-line option to configure MySQL Shell
to process the input source as if it were being issued in
interactive mode; this enables all the features provided by the
Interactive mode to be used in batch processing.
In this case, whatever the source is, it is read line by line and processed using the interactive pipeline.
The input is processed based on the current programming language
change the default programming language using the
defaultMode MySQL Shell configuration option.
Files with the extensions
.sql are always
processed in the appropriate language mode, regardless of the
default programming language.
$> mysqlsh --file code.js
$> mysqlsh < code.js
This example shows how to redirect SQL code to standard input for execution:
$> echo "show databases;" | mysqlsh --sql --uri firstname.lastname@example.org:33060
From MySQL Shell 8.0.22, the
--pym command line option is
available to execute the specified Python module as a script in
Python mode. The option works in the same way as Python's
-m command line option.
On Linux you can create executable scripts that run with
MySQL Shell by including a
#! line as the
first line of the script. This line should provide the full path
to MySQL Shell and include the
--file option. For example:
#!/usr/local/mysql-shell/bin/mysqlsh --file print("Hello World\n");
The script file must be marked as executable in the filesystem. Running the script invokes MySQL Shell and it executes the contents of the script.
SQL query execution for X Protocol sessions normally uses the
sql() function, which takes an SQL statement
as a string, and returns a SqlExecute object that you use to
bind and execute the query and return the results. This method
is described at Using SQL with Session. However, SQL query
execution for classic MySQL protocol sessions uses the
runSql() function, which takes an SQL
statement and its parameters, binds the specified parameters
into the specified query and executes the query in a single
step, returning the results.
If you need to create a MySQL Shell script that is independent
of the protocol used for connecting to the MySQL server,
MySQL Shell provides a
function for X Protocol, which works in the same way as the
runSql() function in classic MySQL protocol
sessions. You can use this function in MySQL Shell only in
sql(), so that your script works
with either an X Protocol session or a classic MySQL protocol
Session.runSql() returns a SqlResult
object, which matches the specification of the ClassicResult
object returned by the classic MySQL protocol function, so the
results can be handled in the same way.
Session.runSql() is exclusive to the
Python, and is not part of the standard X DevAPI.
To browse the query results, you can use the
fetchOneObject() function, which works for
both the classic MySQL protocol and X Protocol. This function
returns the next result as a scripting object. Column names are
used as keys in the dictionary (and as object attributes if they
are valid identifiers), and row values are used as attribute
values in the dictionary. Updates made to the object are not
persisted on the database.
For example, this code in a MySQL Shell script works with either an X Protocol session or a classic MySQL protocol session to retrieve and output the name of a city from the given country:
var resultSet = mySession.runSql("SELECT * FROM city WHERE countrycode = ' AUT'"); var row = resultSet.fetchOneObject(); print(row['Name']);