Update data with SQL

Tablestore SDK for Python executes an UPDATE statement to update one row in a data table that does not have local transactions enabled.

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, and make sure that the data table does not have local transactions enabled.
If you use a RAM user, grant the SQL_DML permission to the RAM user.

Description

Call the exe_sql_query method to execute an UPDATE statement and update one row. The WHERE clause must specify all primary key columns of the row. Primary key columns cannot be updated.

def exe_sql_query(self, query)

The following example sets the name column of a row in example_table to updated.

query = (
    "UPDATE example_table SET name = 'updated' "
    "WHERE tenant_id = 1 AND pk = 'row1'"
)
row_list, table_capacity_units, _ = client.exe_sql_query(query)

Parameters

The exe_sql_query method contains the following parameter.

Name	Type	Description
query (required)	String	The `UPDATE` statement to execute. The statement specifies the mapping table, attribute columns and values to update, and the complete primary key of the target row.

Response

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

Element	Type	Description
`row_list`	List[Row]	SQL updates do not return data rows. This element is an empty list.
`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.

Examples

Atomically increment or decrement BIGINT columns

In the SET clause, use attribute_column = attribute_column + integer or attribute_column = attribute_column - integer to atomically increment or decrement a BIGINT attribute column. The following example atomically increments score by 5.

query = (
    "UPDATE example_table SET score = score + 5 "
    "WHERE tenant_id = 1 AND pk = 'row1'"
)
row_list, table_capacity_units, _ = client.exe_sql_query(query)