Update SQLITE DB with multiple python lists

Question

I'm attempting to update my sqlite db with 2 python lists. I have a sqlite db with three fields. Name, number, date. I also have three python lists with similar names. I'm trying to figure out a way to update my sqlite db with data from these 2 lists. I can get the db created, and even get a single column filled, but I cant seem to update it correctly or at all. Is there a way to INSERT both lists at once? Rather than INSERT a single column and then UPDATE the db with the other?

Here is what I have so far:

name_list = []

number_list = []

date = now.date()
strDate = date.strftime("%B %Y")

tableName = strDate

sqlTable = 'CREATE TABLE IF NOT EXISTS ' + tableName + '(name text, number integer, date text)'

c.execute(sqlTable)

conn.commit()

for i in name_list:
    c.execute('INSERT INTO January2018(names) VALUES (?)', [i])
    conn.commit()

I can't seem to get past this point. I still need to add another list of data (number_list) and attach the date to each row.

Here's what I have on that:

for i in number_list:
    c.execute('UPDATE myTable SET number = ? WHERE name', [i])
    conn.commit()

Any help would be much appreciated. And if you need more information, please let me know.

At a guess, you want c.execute('INSERT INTO January2018(names) VALUES (?)', ([i],)). Does that work? — roganjosh
– roganjosh, Commented Jan 31, 2018 at 19:30
Would I need a second for loop to iterate through the second list? So like for i in name_list: for o in number_list: c.execute('INSERT INTO January2018 VALUES (?, ?)', ([i], [o])) conn.commit() — grigs
– grigs, Commented Jan 31, 2018 at 19:38
No. In those cases, I'd use the executemany method given by Ajax and supply as c.executemany('INSERT INTO January2018 VALUES (?, ?)', list(zip(name_list, number_list))) — roganjosh
– roganjosh, Commented Jan 31, 2018 at 19:40
That's a benefit of executemany because it allows you to provide nested lists and execute the same query for each set of values in one go. Also, an aside, there is no need for you to commit() on every iteration of your loop, just do it once at the end (unless there's some pressing reason not to) because it will speed up the execution significantly. — roganjosh
– roganjosh, Commented Jan 31, 2018 at 19:46

Ajax1234 · Accepted Answer · 2018-01-31 19:48:02Z

1

You can use executemany with zip:

c.executemany('INSERT INTO January2018 (name, number) VALUES (?, ?)', zip(name_list, number_list))
conn.commit()

edited Jan 31, 2018 at 19:48

answered Jan 31, 2018 at 19:35

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

grigs Over a year ago

Thanks for this. I tried running it with both lists: c.executemany('INSERT INTO January2018(name, number) VALUES (?, ?)', name_list, number_list) conn.commit() but I keep getting this error: TypeError: function takes exactly 2 arguments (3 given)

grigs Over a year ago

That was it. Thank you. I never knew you could use zip like that. Thanks again!

Ajax1234 Over a year ago

@grigs glad to help!

Collectives™ on Stack Overflow

Update SQLITE DB with multiple python lists

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related