How do I delete multiple rows based on a 2 dimensional array in mysql with python?-CodePudding

Good morning all,

I current have a database with around 400000 rows. An update needs to be applied to this database every morning which adds some rows and also deletes around 20000 rows based on a 2 dimensional array. The array looks like this:

# array format [CIF_train_uid, start_date, CIF_stp_indicator]
['C85679', '2023-02-12', 'O'], ['C89316', '2023-02-12', 'C'], ['H37809', '2022-12-12', 'P']

The 3 items in each list are used to identify which row in the database needs to be deleted.

I have tried the following SELECT to obtain the row id for each row to be deleted, where toDelete is the 2 dimensional array, and IDtoDelete would be the array containing all the row ids to be deleted from the database;

for a, b in enumerate(toDelete):
    sql = "SELECT id FROM schedulev1 WHERE CIF_train_uid = %s AND start_date = %s AND CIF_stp_indicator = %s"
    mycursor.execute(sql, toDelete[a])

for x in mycursor:
  IDtoDelete.append(x)

But this doesn't work. All i get is an empty list. Could anyone offer any help on where I am going wrong?

Many thanks

CodePudding user response：

You need to fetch the data after your query, modify your code like this:

for a, b in enumerate(toDelete):
    sql = "SELECT id FROM schedulev1 WHERE CIF_train_uid = %s AND start_date = %s AND CIF_stp_indicator = %s"
    mycursor.execute(sql, toDelete[a])
    data = [x[0] for x in mycursor.fetchall()]
    IDtoDelete.extend(data)