Delete data with SQL

Tablestore SDK for Python executes a DELETE statement to delete one row from 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 a DELETE statement and delete one row. The WHERE clause must specify all primary key columns of the row.

def exe_sql_query(self, query)

The following example deletes the row whose primary key is tenant_id=1 and pk='row1' from example_table.

query = (
    "DELETE FROM example_table "
    "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 `DELETE` statement to execute. The statement specifies the mapping table 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 deletes 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.