[Python] DELTA_INSERT_COLUMN_ARITY_MISMATCH error while using PYODBC and DataBricks

I'm getting an error of [DELTA_INSERT_COLUMN_ARITY_MISMATCH] while trying to insert into DataBricks using PYODBC. If I run this query, everything works fine in both DataBricks and Python

‘INSERT INTO <Table> (LOAD_DT_TM, FILE_DT) VALUES(CURRENT_TIMESTAMP, 20250827)”


If I run this query, it works fine in Databricks, but not PYODBC

“INSERT INTO <Table> (LOAD_DT_TM, FILE_DT) SELECT CURRENT_TIMESTAMP, 20250827”


If I run the same query through Python PYODBC, it fails giving a [DELTA_INSERT_COLUMN_ARITY_MISMATCH] target table has 23 column(s) but the inserted data has 2 column(s). (There are actually 23 columns, but I only want to insert 2)

“INSERT INTO <Table> (LOAD_DT_TM, FILE_DT) SELECT CURRENT_TIMESTAMP, 20250827”


Then if I run the same query and my table only has 2 rows, but the FILE_DT column is the first column in the table, and the LOAD_DT_TM is the second column, it does the insert, but puts the CURRENT_TIMESTAMP into the FILE_DT column since it's the first one in the table, and puts the CURRENT_TIMESTAMP into the FILE_DT since it's the second column in the table. When I run the exact same query in databricks, it inserts them into the correct columns.

“INSERT INTO <Table> (LOAD_DT_TM, FILE_DT) SELECT CURRENT_TIMESTAMP, 20250827”


I tried the Inserts listed above, but I expect to be able to limit how many columns I'm inserting, and also that the order be done in the correct manner.

Continue reading...