Update large sqlite database in chunks
<p>I have a sqlite database (appr. 11 GB) that has multiple tables including the tables <code>distance</code> and <code>vertices</code>. The table <code>distance</code> is pretty large (120 mio rows), <code>vertices</code> is smaller (15 000 rows). I want to use sqlite3 in python to update one column of <code>distance</code> by values of another column in <code>vertices</code>. The table vertices has an index on column <code>cat</code> and another index on <code>orig_cat</code>.</p>

<p>What I am doing:</p>

<pre><code>import sqlite3
db_path='path/to/db.db'

conn = sqlite3.connect(db_path)
cur = conn.cursor()

cur.execute('''UPDATE distance SET 
                from_orig_v = (SELECT orig_cat FROM vertices WHERE cat=distance.source)''')
</code></pre>

<p>However running that update statement on such a large database, causes a memory error. The memory usage is increasing steadily until it crashes. I am looking for advise to perform such a large update statement without running out of memory? Maybe processing the update in chunks (i.e. rows of <code>distance</code> table) and committing after e.g. 1000 updates to free memory? How would that be done in python/sqlite? </p>

<p>It should be possible to update chunks with statements like this:</p>

<pre class="lang-sql prettyprint-override"><code>UPDATE distance SET ... WHERE rowid BETWEEN 100000 AND 200000;
</code></pre>

<p>You don't need to use multiple transactions; the only thing that actually must be kept in memory is the list of rows to be updated in a single statement.</p>

python memory sqlite3 sql-update