Insert into SQLite table by column name?-CodePudding

Is there a way to pass a list such as

PersNr_List = ['00001', '00002','00003']

into the first column of an SQLite table, in this case called 'PersNr'? I can SELECT from a column, but can I also pass into a specific column?

Sorry if that's confusing. Right now I just want to achieve this:

PersNr    columnName1    columnName2 .....
00001
00002
00003
00004

I tried looping but couldn't make it work:

PersNr_List = ['00001', '00002','00003']

for x in PersNr_List: 
    cursor.executemany('INSERT INTO payroll_details VALUES (?)', PersNr_List)

Thanks so much for any help!

CodePudding user response：

You need to make persnNr_List a list of lists. Each nested list is the list of values for a row, even if you're only insert into one column.

PersNr_List = [['00001'], ['00002'],['00003']]

And when you use executemany() you don't need the loop, it automatically inserts all of them at once.

And if you're not insert into all the columns, you have to specify the column names in the query.

cursor.executemany('INSERT INTO payroll_details (PersNr) VALUES (?)', PersNr_List)

This is a good idea even if you are inserting into all columns, since it's error-prone to depend on the order of the columns.