为数百万个术语建立多对多关系

Question

我在一个包含大约400万条记录的Python sqlite3数据库中有以下信息。

Term         No of articles      Article Ids
Obama           300            [411,523,534, …. 846]
Gandhi         3900            [23,32,33…..4578]
Mandela        3900            [21,14,56,145 …4536]
George Bush     450            [230,310 … 700]
Tony Blair      350            [225,320 … 800]
Justin Bieber   25             [401 , 420, 690 …. 904]
Lionel Messi    150            [23, 78, …… 570]

这里的'Article Ids'是一个二进制大对象，里面存储着一系列的ID，这些ID是通过一个API返回的。

我的任务是从每个术语的ID列表中找出共同的ID，并把它们保存到'relationships.db'中。

我该如何建立关系，以找出哪些文章同时提到了甘地和曼德拉（也就是找出交集的文章ID）呢？

最终的relationships.db应该是这样的；

Term 1              Term 2          No of Common Article Ids    Common Article IDS
Obama               Gandhi                17                    [34,123,25 ...]
Obama               Mandela               43                    [145,111,234,456 ....]
Obama               George Bush           46
Obama               Tony Blair             2
Obama               Justin Bieber         36
Obama               Lionel Messi           3
Gandhi              Mandela               40
Gandhi              George Bush           41
Gandhi              Tony Blair            32
Gandhi              Justin Bieber         31
Gandhi              Lionel Messi          20
Mandela             George Bush           20
Mandela             Tony Blair            11
Mandela             Justin Bieber         19
Mandela             Lionel Messi          39
George Bush         Tony Blair            46
George Bush         Justin Bieber         49
George Bush         Lionel Messi           2
Tony Blair          Justin Bieber         50
Tony Blair          Lionel Messi           3
Justin Bieber       Lionel Messi           6

用'for循环'逐个遍历每个术语来获取交集实在是太麻烦了。有没有更高效的方法？这样做会在'内存'和'速度'之间有权衡吗？

Answer 1

你可以用pandas找到一些解决方法。

1) 首先，用pandas.read_sql创建一个pandas数据框（DataFrame）。

2) 然后，你可以像@logc建议的那样，获取一个交叉连接。

3) 接下来，你可以把列表转换成集合，并进行交集操作。

如果你需要帮助实现这些，我稍后可以帮你，现在有点急。

编辑：

好的，其实这很简单，但我不知道它是否能满足你的性能需求，可能你需要一步一步读取csv文件：

import pandas, sqlite3

conn = sqlite3.connect(databaseFilePath)
df=pandas.read_sql('SELECT * FROM Terms;',conn)
df['Article Ids'] = df['Article Ids'].apply(eval).apply(set)
df['key'] = False
df2 = pandas.merge(df,df,on='key')
df2 = df2[df2.Term_x!=df2.Term_y]
df2['Common Articles IDS'] = df2.apply(lambda row:set.intersection(row['Article Ids_x'], row['Article Ids_y']), axis=1)
df2['No of Common Articles Ids'] = df2['Common Articles IDS'].apply(len)
df2['Common Articles IDS'] = df2['Common Articles IDS'].apply(list).apply(str)
df2[['Term_x','Term_y', 'No of Common Articles Ids', 'Common Articles IDS']].to_sql(outputTableName, conn) 

Answer 2

评论中提到过这一点，但在信息还处于BLOB格式时，处理起来几乎是不可能的（或者说非常困难）。如果你重新设计一下数据库，可能会更有帮助：

Term      Article_id
Ghandi    33
Obama     411
Obama     523
Ghandi    23
Obama     846
...
Mandela   23

这种“扁平化”的表示方式有几个好处。首先，添加新文章变得简单，你不需要提取BLOB。其次，配合合适的索引，你可以轻松恢复原来的计数：

SELECT COUNT(*) FROM news WHERE Term="Ghandi"`

如果你愿意，这些数据可以存放在一个单独的表里。要提取联合文章，你可以搜索类似这样的内容：

SELECT A.Article_id, A.Term, B.Term FROM news AS A 
JOIN news AS B ON A.Article_id = B.Article_id 
AND A.Term != B.Term

需要注意的是，这样会导致重复计数，但这很容易纠正。下面是一个完整的最小工作示例，修改了表格以显示更多匹配项：

import sqlite3

conn = sqlite3.connect(":memory:")

raw_items = '''
Ghandi    33
Obama     411
Obama     521
Ghandi    23
Obama     21
Ghandi    411
Mandela   21'''

script = '''
CREATE TABLE news ( 
Term STRING,
Article_id INTEGER
);'''

conn.executescript(script)
items = [line.split() for line in raw_items.strip().split('\n')]
conn.executemany("INSERT INTO news VALUES (?,?)", items)

cmd = '''SELECT COUNT(*) FROM news WHERE Term="Obama"'''
print "Obama size: ", conn.execute(cmd).fetchone()

cmd = '''
SELECT A.Article_id, A.Term, B.Term FROM news AS A 
JOIN news AS B ON A.Article_id = B.Article_id 
AND A.Term != B.Term '''

for result in conn.execute(cmd).fetchall():
    print result

这样可以得到：

Obama size:  (3,)
(411, u'Obama', u'Ghandi')
(21, u'Obama', u'Mandela')
(411, u'Ghandi', u'Obama')
(21, u'Mandela', u'Obama')