MySQL Shell 9.1  /  ...  /  Result Data Specification

10.4.3 Result Data Specification

The data definition of a result is composed of the following elements:

  • Column metadata: the column names and their types. This can be defined automatically or manually.

  • Data: the result's records.

Automatically Defined Column Data

The first dictionary defined in data is used to automatically determine the column metadata. This dictionary includes the following:

  • name: defined by each key in the dictionary.

  • type: the value of each key.

Note

Automatic definition of the result metadata has the following limitations:

  • You cannot define the data types used for each column.

  • You cannot define the column order in the result.

  • Name and type are the only column metadata available.

The column type and value representation in the results are mapped by language support:

Table 10.2 JavaScript Column and Result Value Type Mapping

JavaScript Value Default Column Type Result Value Type

null

STRING

null

string

STRING

string

integer

BIGINT

integer

float

DOUBLE

float

boolean

TINYINT

0 or 1

array

JSON

string

dictionary

JSON

string

array buffer

BYTES

array buffer

date

DATETIME

date


Table 10.3 Python Column and Result Value Type Mapping

Python Value Default Column Type Result Value Type

None

STRING

null

string

STRING

string

integer

BIGINT

integer

float

DOUBLE

float

boolean

TINYINT

0 or 1

array

JSON

string

dictionary

JSON

string

binary string

BYTES

binary string

date

DATE

date

time

TIME

time

datetime

DATETIME

datetime


The following shows an example of automatic column definition:

@sql_handler(prefixes=["SHOW FILES"])
def show_files(session, sql):
files = [{'path': file, 'length': os.stat(file).st_size} for file in os.listdir()]
return mysqlsh.globals.shell.create_result({'data': files})

Usage:

SQL> show files;
+---------+----------------+
| length  | path           |
+---------+----------------+
| 420     | sample_file.tx |
| 50      | readme.txt     |
+---------+----------------+
3 rows in set (0.0000 sec)

Manually Defined Column Data

You can define the metadata with the columns attribute. This attribute defines the column order and can be used to override the default column types.

columns is defined as a list containing either a string defining the column name, or a dictionary containing one, or more, of the following:

  • name: (mandatory) the name for the column in the result.

  • type: (optional) the expected type of the column values. Permitted values are string, integer, float, json, date, time, datetime, and bytes

  • flags: (optional) comma-separated list of additional flags (string). blob, timestamp, unsigned, zerofill, binary, enum, and set.

  • length: (optional) length in bytes.

The following shows an example of manual column definition:

@sql_handler(prefixes=["SHOW FILES"])
def show_files(session, sql):
files = [{'path': file, 'length': os.stat(file).st_size} for file in os.listdir()]
metadata = [{'name': 'path', 'type': 'string'}, {'name': 'length', 'type': 'integer'}]
return mysqlsh.globals.shell.create_result({'columns', metadata, 'data': files})

Usage:

mysql-sql> show files;
+----------------+---------+
| path           | length  |
+----------------+---------+
| sample_file.tx | 420     |
| readme.txt     | 50      |
+----------------+---------+
3 rows in set (0.0000 sec)