查找zotero中给定item所在的collection

阿军抱怨zotero这个功能缺失好久了～～今天兴起，想用SQL语句解决这个问题。

一番挣扎之后，写出来了：“select collectionName from collections where collectionID = (select collectionItems.collectionID from collectionItems where itemID = (select itemData.itemID from itemData where valueID = (select valueID from itemDataValues where value = "?")))”。其中?处填上item的全称，在SQLite Spy或SQLite Manager中执行该语句即可（当然在任何支持sqlite3的软件中执行皆可）。

但这样只能查找到item直接所在的collection，而collection有可能还有上一层的collection，写出这样的SQL语句已经超出了我的能力范围，只有借助于python了～。

Python语言: 在zotero中查找给定item的collection层次

01 #! /usr/bin/env python
02 #coding=utf-8
03
04 import sqlite3 as sqlite
05
06 def findCollectionNames(itemName):
07 conn = sqlite.connect('zotero.sqlite')
08 c = conn.cursor()
09 param = (itemName,)
10 # 查找collectionName和parentCollectionID
11 c.execute("select collectionName, parentCollectionID from collections where collectionID = \
12 (select collectionID from collectionItems where itemID = \
13 (select itemID from itemData where valueID = \
14 (select valueID from itemDataValues where value = ?)))", param)
15 collectionNames = []
16 for row in c:
17 # print row[0].encode('gbk')
18 collectionNames.append(row[0])
19 parentCollectionID = row[1]
20 while parentCollectionID:
21 cur = conn.cursor()
22 param = (parentCollectionID,)
23 cur.execute("select collectionName, parentCollectionID from collections where collectionID = ?", param)
24 res = cur.fetchall()[0]
25 # print res[0].encode('gbk')
26 collectionNames.append(res[0])
27 parentCollectionID = res[1]
28
29 # We can also close the cursor if we are done with it
30 c.close()
31
32 # 显示出来
33 collectionNames.reverse()
34 for name in collectionNames:
35 print ("%s - " % name.encode('gbk')),
36 print ("\"%s\"" % itemName)
37
38 if __name__ == '__main__':
39 findCollectionNames(u"Panda3D - Forums")