CODEWITHSUNDEEP
pythonperformancesqlite
mk1337
mk1337

Reputation: 105

Speed up Python/SQLite update?

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()

Upvotes: 1

Views: 146

Answers (1)

user11044402
user11044402

Reputation:

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.

Upvotes: 1

Related Questions