Apps How to run an sqlite script?

[bhuether]

I don't need to develop an app, I just need some advice on how best to do the following, with a script or however is best.

1) Back up a database file /data/data/someapp/databases/database.db
2) Open a database file /data/data/someapp/databases/database.db
3) run some queries
4) close database

That's it - simple. I need to periodically run this script to make some database updates.

Advice appreciated! I know I can use the sqlite3 shell, but I really don't feel like going through the process everytime of bringing up the shell, opening the database and running the sql file. I want to be able to simply have a script I can click on that will do everything.

thanks,

Brian

 

[dcooterfrog]

I am looking for the same thing.
second I can't start the sqlite3 shell on my ally

 

[bhuether]

Figured out how to do it...

1) Install the Android Scripting Environment on your phone.
2) Install the Python interpreter
3) Write python script. Here is example of what I did:

import sqlite3
import shutil
import android
import os

shutil.copyfile('/data/data/com.paragon.flash.noreg/databases/flash_cards.db', '/sdcard/flash_cards.db')
shutil.copyfile('/data/data/com.paragon.flash.noreg/databases/flash_cards.db', '/sdcard/flash_cards_backup.db')

con = sqlite3.connect('/sdcard/flash_cards.db')
cur = con.cursor()

#~ remove duplicates
cur.execute('DELETE FROM tag2fact WHERE fact_id IN (select _id from cards where expression_sound_uri IS NULL AND definition_uri NOT NULL)')
con.commit()

cur.execute('DELETE FROM cards WHERE expression_sound_uri IS NULL AND definition_uri NOT NULL')
con.commit()

con.close()

os.system('su -c "cat /sdcard/flash_cards.db > /data/data/com.paragon.flash.noreg/databases/flash_cards.db"')

This is a script that fixes a bug in this Flash Cards program I use. This program integrates with a SlovoEd dictionary and whenever I add a word from the dictionary to the Flash Cards, the Flash cards program adds some duplicate entries... Annoying and the developer won't fix it so I had to reverse engineer a little and figure out how to make the change.

Anyway, above example should get you started. The last part about using the os.system() function... That was the only way I could figure out how to do anything in that app data directory...

 

[rightback]

Excellent information bhuether. I've got about 800 flash cards now, and it's a real pain to organize them using the interface.

Thanks