CODEWITHSUNDEEP
pythonsqldatabasesqlite
Milano
Milano

Reputation: 18735

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.

Upvotes: 1

Views: 64

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175796

Use pure SQL and one UPDATE:

UPDATE companies 
SET ico = REPLACE(ico, ' ', '');

There is no need for using loop.

You can update records that contains at least one space:

UPDATE companies 
SET ico = REPLACE(ico, ' ', '')
WHERE ico LIKE '% %';

Upvotes: 3

Related Questions