pythonsqliteasynchronousmulticore

Is there a way to run SQLite queries from python asynchronously or in parallel?

How can sqlite3 statements be run from python in some kind of "non-sequential" way?

Here is some code, basically straight from the python docs, on sqlite3. (I am assuming this code is executing sequentially):

import sqlite3
conn = sqlite3.connect('::memory')
c = conn.cursor()

###First sequence
c.execute('''DROP TABLE IF EXISTS stocks''')
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','SOME_STOCK',100,35.14)")
conn.commit()

result_stocks = c.execute('''SELECT * FROM stocks''').fetchall()

### Second Sequence
c.execute('''DROP TABLE IF EXISTS bonds''')
# Create first table table
c.execute('''CREATE TABLE bonds
             (date text, trans text, symbol text, qty real, price real)''')
c.execute("INSERT INTO bonds VALUES ('2006-01-05','BUY','SOME_BOND',100,35.14)")
conn.commit()

result_bonds = c.execute('''SELECT * FROM bonds''').fetchall()

conn.close()

print(result_stocks)
print(result_bonds)

Is there a way to execute the "Second Sequence" without waiting for the "First Sequence" to finish?

Solution

  • No.

    SQLite databases may only be written to by one connection at a time; a write transaction will lock the entire database and prevent other threads and processes from reading or writing.

    So yes, you can create a thread for each sequence, but since only one sequence can write to the database at any time, they don't execute concurrently, which defeats the purpose of running them asynchronously.

    If however you are doing something else other than querying the database while your thread is running a query, then you will see a benefit, as the query thread will wait for the disk, freeing up the CPU to do other stuff. But in your example, there is no benefit to running the two sequences in separate threads.