MariaDB loop for items in a list

Question

I want to loop through a pre-generated list of values and for each item i want to insert some data into a table.

DELIMITER //
START TRANSACTION;
FOR myitem IN (1, 5, 6, 14, 73, 811)
DO
    INSERT INTO `TestTable` (`userid`, `info`) VALUES (myitem, 'test input')'
END FOR;
ROLLBACK;
//

I'm getting a syntax error at the start of the list. (using MariaDB 10.7 and for loops are working with 1..5)

I have checked the docs here but it does not show using a defined list.

Answer 1

FOR IN() loop only supports sequences, lists can be used only in IN() condition.

A simple pure SQL solution would be to move the values into WHERE condition:

INSERT INTO TestTable(userid, info) 
  SELECT seq, "test_input" FROM seq_1_to_811 
  WHERE seq IN (1, 5, 6, 14, 73, 811);

A better and fast approach would be to handle this in a client application using batch execution:

Python example:

ids= [(1,),(5,),(6,),(14,),(73,),(811,)];
cursor.executemany("INSERT INTO TestTable(userid,info) VALUES (?, 'test')", ids);