Insert query for Foregin key mysql python Script-CodePudding

I want to write a script for data migration on another DB, my python script is first to read and then insert in bulk, but I have an issue with the foreign key, where incident_number is the primary key for the reading table and the foreign key for Insert table, how to insert proper for incident_number

mycursor.execute("SELECT incident_category_id,incident_number,org_id_id FROM `internal_incident_incidentcreationinfo`")
IncidentMaster=[]
columns=tuple([d[0] for d in mycursor.description])

for row in mycursor:
    IncidentMaster.append(dict(zip(columns, row)))


for incident_data in IncidentMaster:
    sql= "INSERT INTO internal_incident_historicalmultiplecategorylist(incident_category_id,incident_number_id,org_id_id) VALUES(%s,%s, %s)"
    
    values=[(str(incident_data['incident_category_id']), str(incident_data['incident_number']), str(incident_data['org_id_id']))]
    mycursor1.executemany(sql,values)
    connection1.commit()    

the problem within select table row field incident_number
while in INSERT TABLE row in incident_number_id

CodePudding user response：

You should collect all the values into a single list, then call executemany() once with that complete list. mycursor.fetchall() will do this automatically for you.

There's no need to convert the values to strings.

mycursor.execute("SELECT incident_category_id,incident_number,org_id_id FROM `internal_incident_incidentcreationinfo`")
values = mycursor.fetchall()

sql= "INSERT INTO internal_incident_historicalmultiplecategorylist(incident_category_id,incident_number,org_id_id) VALUES(%s,%s, %s)"
mycursor1.executemany(sql,values)
connection1.commit()