Mensanator mensanator at
Wed Jun 10 20:53:59 EDT 2009

On Jun 10, 5:24 pm, David Wilson <d... at> wrote:
> Hi,
> During a fun coding session yesterday, I came across a problem that I
> thought was already solved by itertools, but on investigation it seems
> it isn't.
> The problem is simple: given one or more ordered sequences, return
> only the objects that appear in each sequence, without reading the
> whole set into memory. This is basically an SQL many-many join.

Why not use SQL?

import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
create table test1(p INTEGER);
create table test2(q INTEGER);
create table test3(r INTEGER);

for t in ((1,),(100,),(142,),(322,),(12312,)):
    cur.execute('insert into test1 values (?)', t)
for t in ((2,),(100,),(101,),(322,),(1221,)):
    cur.execute('insert into test2 values (?)', t)
for t in ((100,),(142,),(322,),(956,),(1222,)):
    cur.execute('insert into test3 values (?)', t)

FROM (test1 INNER JOIN test2 ON p = q)
INNER JOIN test3 ON p = r;

sqlintersect = cur.fetchall()

for i in sqlintersect:
   print i[0],


##   100 322

More information about the Python-list mailing list