Read date and time data

Prerequisites

Install Tablestore SDK for Python and initialize a client.
SQL query requires version 5.4.2 or later. We recommend that you use the latest version.
For the data table that you want to access, create a mapping table.

Description

Call the exe_sql_query method to execute a SELECT statement and use SQL date and time functions to convert numeric values to DATETIME, TIME, or DATE. The from_unixtime function accepts Unix timestamps in seconds.

def exe_sql_query(self, query)

The following example converts the value in the event_time column of example_table. Tablestore SDK for Python parses all three SQL types as strings.

query = (
    "SELECT from_unixtime(event_time) AS datetime_value, "
    "time(from_unixtime(event_time)) AS time_value, "
    "date(from_unixtime(event_time)) AS date_value "
    "FROM example_table WHERE pk = 'row1' LIMIT 1"
)
row_list, _, _ = client.exe_sql_query(query)

for row in row_list:
    values = dict(row.attribute_columns)
    print(values["datetime_value"])
    print(values["time_value"])
    print(values["date_value"])

Parameters

The exe_sql_query method contains the following parameter.

Name	Type	Description
query (required)	String	The `SELECT` statement to execute. Use functions such as `from_unixtime`, `time`, and `date` to return the required date and time types.

Response

The exe_sql_query method returns a tuple that contains the following elements.

Element	Type	Description
`row_list`	List[Row]	A list of result rows. Column values of the `DATETIME`, `TIME`, and `DATE` types are parsed as Python `str` values.
`table_capacity_units`	List[Tuple]	Read and write CUs consumed by data tables. Each tuple contains a table name and a `CapacityUnit` object.
`search_capacity_units`	List[Tuple]	Read and write CUs consumed by search indexes. Each tuple contains a search index name and a `CapacityUnit` object.