Query related items from the database to vizualize with python

datentaste
I want to extract related items from the database to make a network analysis. Basically I want to do something similar to what refScape [1,2] did, where you can see clusters of records on a map. Using the Zotero desktop app I can see only the related items per record but I need to see all of them at once. I believe that is useful to gain a deeper understanding of the research landscape, e.g. which works should be cited in one chapter and where are the boundaries between the works I read.

The reason why I want to use the database approach rather than the API is because I think its faster but I'm open to other approaches. Yet, I struggle with the database schema. Although I can view all the tables with a tool such as DB Browser for SQLite its hard for me to understand how they are entangled.

Can someone give me a kickstart to build a query that outputs a table like this?

itemID | itemAut | itemTitle | relitemID | reltemAut | relItemTitle
------   -------   -------   --------   ---------   ----------
960     fnam,lnam   title         961           fnam,lnam     tit961
-
961     fnam,lnam   title         960           fnam,lnam     tit960

My plan is to process the results with python, to build the graph e.g. with networkx [3]. I also had a look into zoviz[4] but despite basic db access it does not work for me, which might be because of changes to the db schema [5]?

Any help, examples or pointers to similar initiatives are highly appreciated.

Thanks,
Johannes

[1] https://src-online.ca/index.php/src/article/view/184/376
[2] https://github.com/inke-uofs/Ref-Scape
[3] https://zoviz.readthedocs.io/en/latest/index.html
[4] https://pypi.org/project/networkx/
[5] https://www.zotero.org/support/changelog
  • datentaste
    Oh and I forgot to add that it would be great to limit the results to a certain collectionName (folder in Zotero). I already have a query to get item values from a collection but I didn't managed it to yield the output mentioned above yet:

    SELECT value FROM itemDataValues WHERE valueID IN (
    SELECT valueID FROM itemData WHERE itemID IN (
    select itemID from collectionItems WHERE collectionID IN (
    select collectionID from collections where collectionName = "My_CollectionName"
    )))

This is an old discussion that has not been active in a long time. Before commenting here, you should strongly consider starting a new discussion instead. If you think the content of this discussion is still relevant, you can link to it from your new discussion.

Sign In or Register to comment.