From a Pythonista sqlite script, how to open somefile.db in Dropbox ?

tguillemin

I have created a sqlite script in Pythonista, and I wish to insert/update/append/delete/retrieve data stocked in an existing .db file which is in my Dropbox account.

If I try to use the EXTERNAL FILES/Open…, I can see my file, but I am unable to open it.

So, for the time being, I am obliged to re-create that file inside Pythonista…

How should I proceed ?
And if it is possible what path should I indicate ? I tried :

conn = sqlite3.connect('Dropbox/somefile.db')

and, of course, it did not work ("unable to open database file").

Thanks in advance```

cvp

@tguillemin In the Files app/Dropbox, you can share a file to Pythonista, select your script and it will receive a link to your db file, and so you can open it...
Of course, your script has to run in appex mode and use get_file_path to get this link.

tguillemin

@cvp : First of all, thank you for your answer. But I seem to be doing it all wrong…

in my Pythonista script "SQLiteHist.py" (it is a simple tool for a chronology database), I added : import appex. I understand I shall have to insert a appex.get_file_path() line in my script
Then I went to my Files app/Dropbox (btw I am on iPhone 7 plus) :

I selected the Hist.db (the aforementioned chronology database)
I pressed until appeared the menu (Copy…Duplicate…etc…Share)
I then selected Share, which brought me to the Share Menu…
…where I selected Run Pythonista script
I did not have the possibility to choose my SQLiteHist.py script : the process started downloading the file (endlessy), and I canceled it
To sum it up I could not select my script, thus it did not receive any link to my db file

Sorry to be so awkwardly precise.

Where did I go wrong ?

cvp

@tguillemin tap here and then select your script and run it

Edit: later, you will learn to add your script presented in the share menu of Pythonista (very easy)

cvp

@tguillemin

sqlite3.connect(appex.get_file_path())```

tguillemin

@cvp : Thanks again

I finally succeeded. Sort of…

I proceeded as indicated, was taken to my script, ran it and got the data I was looking for.
But :

a) this occurred in a light mode version of my script, although I have adopted the night mode in Pythonista

b) when I tried to run the script by opening Pythonista as usual, I got an error ("argument 1 must be str, not None")

c) I then tried something else : I imported the db file in my Pythonista SQLite folder. But when I tried to retrieve data in it, came an error "there is no such table as data", although this table had been acknowledged in the attempt a)

It seems I do not grok the process. Furthermore I do not understand why it is impossible to establish a permanent link between a Pythonista script and a file in Dropbox.
Would it be different in iCloud Drive ?

cvp

@tguillemin

a) no idea

b) appex.get_file_path only works in share mode, else it returns None, this is normal

c) how did you import the db file in Pythonista?

Q: why it is impossible to establish a permanent link between a Pythonista script and a file in Dropbox?
A: I think that you can access a file on Dropbox only if you give access, via share, not directly from an external app

Q: Would it be different in iCloud Drive ?
A: Yes, but only if you put the file in the Pythonista folder because this folder "belongs " to this app

tguillemin

@cvp : I finally went for the iCloud solution, and, after copying the database in the Pythonista3 folder, inserted in my script :

conn = sqlite3.connect('/private/var/mobile/Library/Mobile Documents/iCloud~com~omz-software~Pythonista3/Hist.db')

and it worked, albeit with a few preliminary quirks ("No such table", and later "No such column"…)

Thank you very much for your help