Error when accessing 'num_inserted_rows' in Spark SQL (DBR 15.4 LTS)

Data Engineering

Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.

Hello Databricks Community,

I've encountered an issue while trying to capture the number of rows inserted after executing an SQL insert statement in Databricks (DBR 15.4 LTS). My code is attempting to access the number of inserted rows as follows:

rows_insert = spark.sql(insert_statement).first()['num_inserted_rows']

However, I am receiving the following error:

TypeError: 'NoneType' object is not subscriptable

Upon further investigation, I noticed that in the SQL execution result, the fields num_affected_rows and num_inserted_rows are shown, but no rows are actually returned. This can be seen in the screenshots I've attached:

The first image shows the result of my SQL insert, which displays the columns num_affected_rows and num_inserted_rows but with no actual rows returned.
The second image shows the subsequent Python error when I try to access num_inserted_rows from the result.