Python sqlite3 access db denied

Vialo
Hi!

I am using a python script to fetch information in zotero.sqlite while being offline (so Pyzotero doesn't work) and also while zotero is open.

I use sqlite3 to connect directly to zotero.sqlite but then I get this error: "sqlite3.OperationalError: database is locked"

If I close zotero then my script works but I really need to have zotero how when my script runs so close zotero isn't an option. How idea how I can solve this?

Thanks a lot
  • bwiernik
    It’s generally not safe to access a database that is in use by another program, hence why it is locked here. What exactly are you trying to do?
  • Vialo
    Thanks for your answer.

    Here is what I want to do: when I read a pdf that is stored in zotero (it attached under an item), I want to be able to select a sentence, trigger a python script that copies this sentence and append to it an HTML link like source (where ABCD1234 is the key under whihc the pdf I read is referenced and 99 is the page where the copied sentence is written). I can then paste everything in a zotero note and next time I read this I can simply click on the link and it will open the correct pdf to the correct page.
    So I need to get into the zotero.sqlite to get the correct key (in my example ABCD1234).
    So far my work around is to copy zotero.sqlite (in a temp folder). Then my script connects to this zotero -copy.sqlite. But it is slow (my .sqlite is heavy).
  • bwiernik
    Zotfile can do this natively without any scripting. The page numbers it places next to extracted highlights/notes have links to open the PDF to that page.
  • adamsmith
    But the answer to the sqlite question is that you cannot do this.
    sqlite doesn't allow simultaneous read/write access (writing requires an exclusive lock on the db) and some version of local caching/copying is the only way to go.
  • Vialo
    Thanks to both of you. Zotfile won't help because it retrieve all the highlights: I don't need all the highlight, I just want the sentence I am reading and retrieving all the highlight just for one sentence is an overkill.

    So it seems I don't have a choice: I must copy the .sqlite and delete this copy each time I need to access a pdf key.
  • bwiernik
    An easier option would probably be to extract all the highlights and then delete what you don’t need. You could also write making a temporary copy of the SQLite database and deleting it into your python script.
  • Vialo
    bwiernik, I think that your first solution is a bigger overkill (compared to my python solution) and your second solution is what I am doing now but.

    I wonder if Zotfile could be modified so that it can be trigger with a shortcut (I use autohotkey for my python script) even when reading a pdf on Acrobat reader and once zotfile is triggered with this shortcut, it retrieves only the last highlight.
  • bwiernik
    Zotfile is open source, and such a modification would likely be fairly straightforward to add. (One possible wrinkle is I’m not sure if Zotfile could be made to retrieve the temporally most recent highlight, versus the highlight that is farthest in the PDF in terms of location.) The Zotfile developer would likely be open to a pull request adding such a feature. The approach I would recommend you take is to add a new function to extract just the last annotation. Then ask the Zutilo developer to add that function to the list of available keyboard shortcut actions.
  • Vialo
    Thanks a lot for this information, I will see what I can do.
Sign In or Register to comment.