Insert multiple rows using execute

Question

I wish to execute the following statement:

c.execute('INSERT INTO artists (name, score) VALUES("Buddy Rich", 10), ("Candido", 9), ("Charlie Byrd", 8);')

but instead of hardcoding the values as above, I have them in a variable:

scores = (("Buddy Rich", 10), ("Candido", 9), ("Charlie Byrd", 8))

how do I use scores with execute(), where scores could contain any number of values?

UPDATE

I wish to be able to guarantee that if the data is inserted into the table, that all the data, or none of the data, is inserted even if there is a program crash. If there is a program crash while running executemany, is there a risk that only some of the rows get added?

For example, can this result in only some of the rows being added:

try:
   c.executemany('INSERT INTO artists (name, score) VALUES (?, ?)', scores)
except:
   c.commit()

But you have still hard-coded them. `c.executemany('INSERT INTO artists (name, score) VALUES (?, ?)', scores)` — roganjosh, Feb 22 '18 at 21:21
@roganjosh Does this simply execute insert multiple times (executing execute in a for loop) or is it a "multi insert" as recently introduced to sqlite? — Baz, Feb 22 '18 at 21:24
you can & should use executemany from python, just wrap it in a transaction. the other option is to load data into a table from csv: https://stackoverflow.com/questions/14947916/import-csv-to-sqlite — Haleemur Ali, Feb 22 '18 at 21:33
I wouldn't really call an 8 yr old question "recently" :P But I can't find from the docs exactly how `executemany` is implemented in relation to that — roganjosh, Feb 22 '18 at 21:38

Haleemur Ali · Accepted Answer · 2018-02-22T22:29:37.257

To put things into transactions you should use cursors, and a try block which rolls back if an exception is raised.

import sqlite3   
scores = (("Buddy Rich", 10), ("Candido", 9), ("Charlie Byrd", 8))
con = sqlite3.connect(':memory:')
try:
    cur = c.cursor()
    cur.execute('create table artists (name text primary key, score int)')
    cur.executemany('INSERT INTO artists (name, score) VALUES (?, ?)', scores)
    con.commit()
except:
    con.rollback()

score 0 · Answer 2 · answered Feb 22 '18 at 21:28

0

You can use executemany:

scores = (("Buddy Rich", 10), ("Candido", 9), ("Charlie Byrd", 8))
c.executemany('INSERT INTO artists (name, score) VALUES (?, ?)', scores)

answered Feb 22 '18 at 21:28

Ajax1234

58,711
7
46
83

If there is a program crash while running executemany, is there a risk that only some of the rows get added? In this regard, would it be safe for me to catch exceptions in my program like KeyboardInterrupt and do a commit, or do I run the risk of only having added some rows? – Baz Feb 22 '18 at 21:32

Insert multiple rows using execute

2 Answers2