For me, this simple insert works as long as there is no transaction.
But changing the prepared statement to
BEGIN TRANSACTION;
INSERT INTO table1 (id, description) VALUES ($id, $description);
COMMIT;
results in Error: SQLITE_RANGE: column index out of range
I hesitate to offer this as a work-around but at first sight it's working to commit or rollback both inserts
[{"id":"a2efb037919b24b4","type":"template","z":"27c79c7c9cdd6344","name":"SQL via python","field":"payload","fieldType":"msg","format":"handlebars","syntax":"plain","template":"# The entire python script wrapped in single quotes\n# So NO SINGLE QUOTES in values or names :(\npython -c '\nimport sqlite3\n\n# Connect to the database\nconn = sqlite3.connect(\"mytest.db\")\n# Create a cursor object\ncursor = conn.cursor()\n\n# Create a parameterized query for insertion\nquery = \"INSERT INTO table2 (id, name, description) VALUES (?, ?, ?)\";\n# User data to insert\nuser_data = (5, \"John Smith\", \"Historian\");\n# Execute the query with the user data\ncursor.execute(query, user_data);\n\n# another parameterized query for insertion\nquery = \"INSERT INTO table1 (id, description) VALUES (?, ?)\";\nuser_data = (5, \"Historian\");\ncursor.execute(query, user_data);\n\n# Commit the changes\nconn.commit();\n' // closing single quote","output":"str","x":1020,"y":440,"wires":[["4bb712f48228efa9","93642ce1173bfe83"]]},{"id":"c420499d359b1df5","type":"inject","z":"27c79c7c9cdd6344","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":980,"y":380,"wires":[["a2efb037919b24b4"]]},{"id":"93642ce1173bfe83","type":"exec","z":"27c79c7c9cdd6344","command":"","addpay":"payload","append":"","useSpawn":"false","timer":"","winHide":false,"oldrc":false,"name":"","x":1210,"y":440,"wires":[["15d27e6a29753e41"],["15d27e6a29753e41"],[]]},{"id":"4bb712f48228efa9","type":"debug","z":"27c79c7c9cdd6344","name":"python script","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1190,"y":380,"wires":[]},{"id":"15d27e6a29753e41","type":"debug","z":"27c79c7c9cdd6344","name":"exec output","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1370,"y":440,"wires":[]}]
You can pass values into that python script as message properties eg msg.name = "Bill Stickers"
and using mustache template format