Speed up Python/SQLite update?

Question

I have created a 200 row SQLite Database. Later on I need to update all of these rows.

To be exact I want to update two values (called Test2 and Test3 in my example) of each row by using their old value and add (+200 and +400 in the example) something to their value.

My Code seems to be pretty slow, so i added a timer, so see how Long my Code Needs to be executed. At the Moment for 200 rows around 0.7879 seconds.

To work with bigger DB is Need to Speed up that Code. Maybe you guys could help an python/sql beginner to make my Code more efficient!

Here is my Code so far (tried to make a minimum example):

import sqlite3
import time

def database_test():
    conn = sqlite3.connect('SQLite_Test.db')
    c = conn.cursor()

    i = 0

    for i in range(200):
        c.execute('SELECT Test2, Test3 FROM Test WHERE Test1 = ?',  (i,))

        DB_Values = []        
        DB_Values = c.fetchone()

        Value1 = DB_Values[0]+200
        Value2 = DB_Values[1]+400

        c.execute('''UPDATE Test SET Test2 = ?, Test3 = ? WHERE Test1= ?''', (Value1, Value2, i))
        i += 1  

    # Save (commit) the changes
    conn.commit() 


start_time = time.time()
database_test()
print("--- %s seconds ---" % round((time.time() - start_time),4))

I use the Code shown below to update 20k rows in my db (for testing at the Moment). I expected all 20k rows to have added 1000 to Test2 and Test3. But that doesnt happen. Row 1-3 and row 11-21 are updated between them nothing happend. 101 to 201 also updated properly but after that another huge gap.

Why does that happen?

Please see my Code below:

def database_update_test():
    # Open Database
    conn = sqlite3.connect('SQLite_Test.db')
    c = conn.cursor()

    c.execute('''UPDATE Test SET Test2 = Test2+1000, Test3 = Test3+1000 WHERE Test1 >= 0 and Test1 < 20000''')

    # Save (commit) the changes
    conn.commit()

score 1 · Accepted Answer · 2019-02-12T09:13:07.790

1

You can do this using a single SQL UPDATE. No loop and no SELECT is needed.

UPDATE Test
SET
  Test2 = Test2 + 200,
  Test3 = Test3 + 300
WHERE
  Test1 >= 0 and Test1 < 200;

See the SQLite documentation for UPDATE.

Edit

Since SQL is pretty powerful, you can use complex expressions in SET. If you want to add the current value of Test4 to Test4, do this:

SET
  Test2 = Test2 + Test4

This can be done like above for more than one row selected using WHERE. Every row will use the specific values for Test2 and Test4.

edited Feb 12 '19 at 09:13

answered Feb 12 '19 at 09:04

Thank you for your answer. I am pretty new to SQLite, how do i write your Piece of Code if +200 and +300 are values different for each row and in the Formular a value from the row, which will be updated, is needed. Lets call that needed value Test4. So Test2 would be a Value (Test4+x). – mk1337 Feb 12 '19 at 09:08
Got it, at first a python thread running in the Background was causing an error. – mk1337 Feb 12 '19 at 09:16
Yes, SQLite can only be written by one client at a time. See https://stackoverflow.com/questions/151026/how-do-i-unlock-a-sqlite-database – Feb 12 '19 at 09:20
The process is much faster, but i Encounter another Problem. I added my Code to the bottom of my post. Not every row is updated properly. There a some huge gaps where nothing happend. – mk1337 Feb 12 '19 at 09:32
Please ask a new question. – Feb 12 '19 at 09:33
I can only ask a question every 90 minutes. I added a better describtion to my post. – mk1337 Feb 12 '19 at 09:40
What do you get if you execute `SELECT * FROM Test WHERE Test1 >= 0 and Test1 < 20000`? Do you get all expected rows? – Feb 12 '19 at 09:41
Dont mind me.....stupid mistake on my side. Test1 was a String not Integer. Now everything is working fine !! – mk1337 Feb 12 '19 at 09:45
Then your data is not what you expect. Find the `WHERE` condition that matches all the rows you want to update. It is not possible to tell you what this condition is without having your complete data. – Feb 12 '19 at 09:47
See my comment below yours. Mistake on my side! Thanks for your help. I would appreciate if you could upvote my question if it was well asked. – mk1337 Feb 12 '19 at 09:48

Speed up Python/SQLite update?

1 Answers1