title: Python counting words in large file
body: <p>I have a 6M row database(sqlite) containing 4 fields: id(integer)|title(text)|text(text)|tags(text).</p>
  
  <p>Now I need to count the occurrence of each word appeared in title for example and import into other tables such as word|count and tag|word|count.</p>
  
  <p>My code looks like this in Python 2.7:</p>
  
  <pre><code>from nltk.tokenize import wordpunct_tokenize
  from collections import Counter
  import sqlite3
  
  word_count = Counter()
  pair_count = Counter()
  
  conn = sqlite3.connect('database')
  c = conn.cursor()
  
  for query in c.execute('SELECT Tags, Title FROM data'):
      tags = query[0].strip().split()
          title = wordpunct_tokenize(query[1])
          for word in title:
              word_count[word] += 1
              for tag in tags:
                  pair_count[(tag, word)] += 1
  ...
  </code></pre>
  
  <p>The problem is that the counters goes so large that I got a memory error in 1M rows. I've tried to reinitialize the counters every 100K rows and adding the counts to the db file, but this approach seems to be very slow maybe due to the enormous number of tag-word pairs.</p>
  
  <pre><code>...
  for query in c.execute('SELECT Tags, Title FROM data'):
      i += 1
      if i % 100000 == 0:
          conn1 = sqlite3.connect('counts.db')
          c1 = conn1.cursor()
  
          # update word count
          for word in word_count:
              c1.execute('SELECT Count FROM word_count WHERE Word=?', (word,))
              count = c1.fetchone()
              # add to existing count and update
              if count:
                  count = word_count[word] + count[0]
                  c1.execute('UPDATE word_count SET Count=? WHERE Word=?', (count, word))
              # insert new row
              else:
                  c1.execute('INSERT INTO title_word_count VALUES (?,?)', (word, word_count[word]))
  
          # update pair count                
          for pair in pair_count:
              c1.execute('SELECT Count FROM pair_count WHERE Tag=? AND Word=?', pair)
              count = c1.fetchone()
              if count:
                  count = pair_count[pair] + count[0]
                  c1.execute('UPDATE pair_count SET Count=? WHERE Tag=? AND Word=?', (count, pair[0], pair[1]))
              else:
                  c1.execute('INSERT INTO pair_count VALUES (?,?,?)', (pair[0], pair[1], pair_count[pair]))
          conn1.commit()
          conn1.close()
  
          # reinitiate counters
          word_count = Counter()
          pair_count = Counter()
  ...
  </code></pre>
  
  <p>Is there any approach I can follow to resolve this problem without access to multiple machines? Also any suggestions on the code would be appreciated!</p>
  
  <hr>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I tried to index the <code>counts.db</code> and update each batch, but it's still too slow - It took 10 hrs to process 7 batches of 200000 rows each.</p>
  
  <p>I ended up by following my initial idea. But instead of updating the counts every 100K rows, I just insert them into table <code>subcounts</code> despite possible duplicate <code>Tag, Word</code> pair.</p>
  
  <p>Then <code>INSERT INTO pair_count SELECT Tag, Word, SUM(Count) FROM subcounts GROUP BY Tag, Word;</code> gave me the final result. It took me about 3 hrs total.</p>
  
  <p>I accidentally dropped the temporary table I got following @abernert suggestion, but I think it's doable.</p>
  
  <p>Thank @Steve and @abernert for your advices!</p>