Entering multiple lists at once in SQLi Database-CodePudding

I am using the following command to enter list values in a SQLi Database.

    sql_comm = "INSERT INTO Revisions (Work_ID,Rev_No,Name,Date) VALUES (?,?,?,?);"
    data = (wid,i,rev_name,rev_date)
    crsr.execute(sql_comm,data)
    sql_com2 = "INSERT INTO Revisions (Field,New_Value) VALUES (?,?)"
    for elem in zip(changes,vals):
        crsr.execute(sql_com2,elem)
    connection.commit()

This is the resulting output.

Work_ID Rev_No  Name                                   Date               Field      New Value                                          
172      1      Raul Gitta <[email protected]> 2021-10-21T10:08:10.653Z   null       null
null    null    null    null                                      System.Rev            2
null    null    null    null                                      System.AuthorizedDate 2021-10-20T06:42:01.32Z
null    null    null    null                                      System.RevisedDate    2021-10-21T10:08:10.653Z
null    null    null    null                                      System.ChangedDate    2021-10-20T06:42:01.32Z
null    null    null    null                                      System.Watermark      198

Is there a way to avoid these null values?

CodePudding user response：

All the null values stem from that fact that they are not inserted in the appropriate INSERT query. It might be easier to use lists for the parameters instead of tuples, since lists are mutable.

Something like:

data = [wid,i,rev_name,rev_date]
sql_com2 = "INSERT INTO Revisions (Work_ID,Rev_No,Name,Date,Field,New_Value) 
VALUES (?,?,?,?,?,?)"
for elem in zip(changes,vals):
    crsr.execute(sql_com2,data   list(elem))
 connection.commit()