0

I have a small SQL script:

SET @userId=12345;

SET @roleId=5;

BEGIN;
INSERT INTO mydatabase.Users(userId, roleId) VALUES (@userId, @roleId);
COMMIT;

I am looking to run this same script, but for a specific list of userIds, like this pseudocode:

SET @userIdList=[12345, 23456, 34567];

SET @roleId=5;

FOR EACH id in @userIdList:
    BEGIN;
    INSERT INTO mydatabase.Users(userId, roleId) VALUES (@userId, @roleId);
    COMMIT;
END

What is the correct syntax to do something like this in MySQL?

KJ0797
  • 187
  • 1
  • 2
  • 14
  • If you have a list of 10-20 items, just change @userId and run. If you want to automate that, one way is to create a table to store a list of user ids, create a stored procedure with a cursor that loops through each user id from that test table and does the insert. Example: https://stackoverflow.com/questions/3224248/using-cursor-in-a-loop-of-a-stored-procedure – zedfoxus Jul 11 '23 at 15:20

1 Answers1

0

I'm not sure you are willing to do it differently, with a python mysql connector.

pip install mysql-connector-python

then in a python shell:

import mysql.connector

mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)

mycursor = mydb.cursor()
roleId = 5
sql = "INSERT INTO Users (userId, roleId) VALUES (%s, %s)"
val = [(12345,roleId), (), (),......] 

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "was inserted.")

Sources : https://www.w3schools.com/python/python_mysql_insert.asp

NOTE: make sure you have a python 3.8 or greater

ZAKARIA SEMLALI
  • 1