Initializing multiple rows of an SQLite3 database

Question

I have a simple database tables of 2 columns, the first being a lightbulb id, and the 2nd being an integer status (0/1) denoting whether the light is on or not. By default all of my light switches are assumed to be off. The number of lightbulbs is fixed, I only need to change the id with each row.

Here is my table:

cursor.execute(
    '''
    CREATE TABLE IF NOT EXISTS info(
    id integer,
    status integer
    )
    '''

)

What is a better way to initialize the table than to manually list each row as follows? (I know the number of lights is fixed to 10).

c.execute('INSERT INTO info VALUES(1,0)')
c.execute('INSERT INTO info VALUES(2,0)')
c.execute('INSERT INTO info VALUES(3,0)')

....

....
c.execute('INSERT INTO info VALUES(10,0)')

https://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database — Masoud Rahimi, Jul 21 '19 at 03:25

Barbaros Özhan · Answer 1 · 2019-07-21T14:38:45.533

1

You don't need any operation within the Database, but just iterate within the application by using range(1,11) to generate integers starting from 1 upto 10 :

....
c = con.cursor()
....
for i in range(1,11):
    c.execute('INSERT INTO info VALUES(?,0)',(i,))

con.commit()

edited Jul 21 '19 at 14:38

answered Jul 21 '19 at 13:03

Barbaros Özhan

39,060
9
21
45

It's much better to use `VALUES(?)` and have the db interpolate as a habit. See docs: https://docs.python.org/3/library/sqlite3.html – chthonicdaemon Jul 21 '19 at 13:09
thank you @chthonicdaemon for the caveat. I think you especially mean vulnerability against injection ... – Barbaros Özhan Jul 21 '19 at 13:33

score 1 · Answer 2 · answered Jul 21 '19 at 13:26

Instead of this create statement:

CREATE TABLE IF NOT EXISTS info(
  id integer,
  status integer
)

since you want the ids to have values 1-10 make the id primary key (so it will be auto increment starting from 1) and the column status to have a default value of 0:

CREATE TABLE IF NOT EXISTS info(
  id integer primary key,
  status integer default 0
)

Now when you want to insert a new row all you have to do is execute this statement:

insert into info default values;

This inserts a row with the next id (for a new table the id will start from 1) and status=0.
So use a loop to insert 10 rows:

for i in range(1, 11):
    c.execute('insert into info default values')

Initializing multiple rows of an SQLite3 database

2 Answers2