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)