Write data with SQL

Tablestore SDK for Python executes an INSERT statement to write one or more rows to 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 INSERT statement and write one row. If the specified primary key already exists, the request fails.

def exe_sql_query(self, query)

The following example writes one row to example_table.

query = (
    "INSERT INTO example_table (tenant_id, pk, name, score) "
    "VALUES (1, 'row1', 'example', 10)"
)
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 `INSERT` statement to execute. The statement specifies the mapping table, columns, and values.

Response

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

Element	Type	Description
`row_list`	List[Row]	SQL writes 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.

Scenario examples

Write multiple rows

An INSERT statement can write up to 200 rows. All rows must have the same partition key and are written as an atomic operation: all rows succeed or all rows fail.

query = (
    "INSERT INTO example_table (tenant_id, pk, name, score) VALUES "
    "(1, 'row1', 'example-a', 10), "
    "(1, 'row2', 'example-b', 20), "
    "(1, 'row3', 'example-c', 30)"
)
client.exe_sql_query(query)