Insert into SQLite table by column name?

Question

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!

Could you elaborate on what exactly you are trying to do or wish to do? — Cow, Apr 01 '22 at 10:45
You can't store a list in a table column. You could loop over the list and insert each of them into a separate row. You could also convert it to a comma-separated string, but that's generally a bad idea, see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Barmar, Apr 01 '22 at 10:48
Yeah sorry, extremely tired. Ive tried to explain better above — isnberg 70, Apr 01 '22 at 10:49
Or you could store it in a JSON column. But SQL functions for working with JSON are difficult to use IMO. — Barmar, Apr 01 '22 at 10:49
yeah exactly, I tried to loop and insert, but couldn't make it work... here, I'll add that attempt above — isnberg 70, Apr 01 '22 at 10:51

Barmar · Accepted Answer · 2022-04-01T11:01:09.570

1

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.

edited Apr 01 '22 at 11:01

answered Apr 01 '22 at 10:55

Barmar

That might be it... Still throws ```sqlite3.OperationalError: table payroll_details has 25 columns but 1 values were supplied``` I only want the list items to go into rows in the first column, not sure how to get around this error... – isnberg 70 Apr 01 '22 at 10:59
1

You haver to specify the columns you're inserting into. – Barmar Apr 01 '22 at 11:01
Is there a way to specify column AND row? – isnberg 70 Apr 01 '22 at 11:16
What does that mean? `INSERT` is for creating new rows, there's no row to specify. – Barmar Apr 01 '22 at 11:20
You use `UPDATE` to modify existing rows, using the `WHERE` clause to specify the row. – Barmar Apr 01 '22 at 11:21

1 Answers1