How to use a script to query android sqlite database

Question

Android saved settings in a database file which is /data/data/com.android.providers.settings/databases/settings.db.

Android use sqlite3 as the database. We can use adb to manage the database file. I want to know if there is a way to run all these commands in a perl/python script to automate the whole query process?

$adb shell 
$sqlite3 /data/data/com.android.providers.settings/databases/settings.db 

The above command will open the settings database. Then you will enter into sqlite3 command line.

First check how many tables existed in the database. Here lists the result.

sqlite> .tables

android_metadata   bookmarks          gservices        
bluetooth_devices  favorites          system  

The settings (such as volume_alarm) I want to get are in "system" table, a .dump command will list all items in the table.

sqlite> .dump system
BEGIN TRANSACTION;
CREATE TABLE system (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE ON CONFLICT REPLACE,value TEXT);
INSERT INTO "system" VALUES(3,’volume_system’,’5′);
INSERT INTO "system" VALUES(4,’volume_voice’,’4′);
INSERT INTO "system" VALUES(5,’volume_alarm’,’6′);
.....
$ select value from system where name ='volume_alarm';
select value from system where name ='volume_alarm'
6
$.quit;

Solution

To query a specific value:

adb shell sqlite3 /data/data/com.android.providers.settings/databases/settings.db "select value from 'system' where name = 'volume_alarm';"

Or to pull all records from a table:

adb shell sqlite3 /data/data/com.android.providers.settings/databases/settings.db "select name, value from 'system';"