vendredi 6 novembre 2015

Modify all rows in table

I'm looking for a more efficient way to modify each row in a table. The table contains 130 000 rows. Each row contains column called ico which has to be modified this way:

ico scheme: xx xxx xxx

modify: ico = ico.replace(' ','')

So ico is changed from xx xxx xxx to xxxxxxxx.

I tried this way:

icos = [x[0] for x in  cur.execute("""SELECT ico FROM companies""").fetchall()]
for ico in icos:
    print ico
    new_ico = ico.replace(' ','')
    cur.execute("""UPDATE companies SET ico=? WHERE ico=?""",(new_ico,ico))
conn.commit()

It is very slow (10 rows in 5 seconds). I'm looking for a more efficient way to modify each row from this table.

Aucun commentaire:

Enregistrer un commentaire