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

Question

我在python sqlite3数据库中有以下信息，该数据库有大约400万条记录。

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 id '是一个包含id列表的blob(由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循环"循环遍历每个项以获得交集是痛苦的。是否有有效的方法来做到这一点?"内存"one_answers"速度"之间会有取舍吗?

Answer 1

注释暗示了这一点，但是处理仍然在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')

Answer 2

您可以使用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)