python - Make sqlite3 column update faster -

python - Make sqlite3 column update faster -

i have written python code iteratively reads info sqlite database, calculations on , writes results database new columns , on. however, part involving writing database updating column feels slower , slower time. there alternative way can same thing faster?

a relevant part of code brought below. in section of code, 2 new columns column_i1_ , column_i2_ made, each ending iteration number, column_i1_1, column_i2_1, , new results written these columns corresponding lists. problem is, considering high number of iterations , time-consuming nature of calculations, plus high number of rows (here defined num value, approximately 11000 rows), works slow...

is there way of doing instead of using update? because think updating, has re-create column each time adds new value , causes slow down. used utilize excel , much faster since there 256 column limit spreadsheet had switch database.

cur.execute("alter table c add together column_i1_%d integer"%iter) #makes new column indexed iteration number con.commit() e in range(0,num): # num given number input cur.execute("update c set column_i1_%d=? id=%d"%(iter,e+1),(w[e],)) # w list containing results con.commit() # cur.execute("alter table c add together column_i2_%d integer"%iter) con.commit() f in range(num,(2*num)): cur.execute("update c set column_i2_%d=? id=%d"%(iter,f-num+1),(w[f],)) con.commit()

.....

your comments much appreciated. plus, relatively python newbie, please go easy on me! :)

to speed lookups on id column, create index on it:

cur.execute("create index mylittleindex on c(id)");

alternatively, if values in id column unique, declare column primary key (which automatically creates index):

cur.execute("create table c(id primary key)");

if values integers, declaring column integer primary key little more efficient:

cur.execute("create table c(id integer primary key)");

python sqlite3