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
– roganjosh, Commented Feb 22, 2018 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
– Baz, Commented Feb 22, 2018 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: stackoverflow.com/questions/14947916/import-csv-to-sqlite — Haleemur Ali
– Haleemur Ali, Commented Feb 22, 2018 at 21:33

Haleemur Ali · Accepted Answer · 2018-02-22 22:29:37Z

2

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()

edited Feb 22, 2018 at 22:29

answered Feb 22, 2018 at 21:51

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

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

Comments

Ajax1234 · Accepted Answer · 2018-02-22 21:28:29Z

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, 2018 at 21:28

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

1 Comment

Baz Over a year ago

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?

Collectives™ on Stack Overflow

Insert multiple rows using execute

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related