Fast intersection counts (thousands lines in sample files, millions in db)

count_intersections.py

import sys
import sqlite3
import json
import resource

def intersects(sample_file, db):
    conn = sqlite3.connect(db)
    c = conn.cursor()

    def check(id):
        c.execute("select * from data where twitter = ?", (id, ))
        res = c.fetchone()
        if res is None:
            return 0
        else:
            return 1

    with open(sample_file) as f:
        return reduce(lambda x,y: x+check(y), json.load(f), 0)

if __name__ == '__main__':
    print "Intesection: {0} accounts".format(intersects(sys.argv[1], 'twi.sqlite'))
    print "Memory usage: {0}mb".format(resource.getrusage(resource.RUSAGE_SELF).ru_maxrss / 1024**2)

init_db.py

import sqlite3
conn = sqlite3.connect('twi.sqlite')
c = conn.cursor()
c.execute("create table if not exists data (twitter text)")
conn.commit()
c.execute("create unique index if not exists twitter_uniq_idx on data (twitter)")

import json
f = open('tw_leaders.json')
it = json.load(f)

for t in it:
    try:
        c.execute('insert into data values (?)', (str(t),))
    except:
        pass

conn.commit()