Using python in android to interface to sql

Question

I know you can use python and other scripting languages in android. But I haven't seen weather or not it was possible to use python as an interface to sqlite in android. Is this possible? This is the first android app where I've needed sqlite, and using the java api's is retarded.

If this isn't possible, can someone point me to a good tutorial on sqlite in android? I've found a bunch, but all of them are entirely different and I'm totally lost on which is the best way to do it.

It's just hard to picture how google expects you to use the sqlite database. It seems like you need like 10 different classes just to query a database.

Answer 1

Actually you just need 3 classes:

A ContentProvider, as found here: http://developer.android.com/guide/topics/providers/content-providers.html

Second you need is a SQLiteOpenHelper and last but not least a Cursor

Edit: Just noticed it's not obvious from the snippets what the db variable is. It's the SQLiteOpenHelper or better my extension of it (where I've only overridden the onCreate, onUpgrade and constructor. See below ^^

The ContentProvider is the one which will be communicating with the database and do the inserts, updates, deletes. The content provider will also allow other parts of your code (even other Apps, if you allow it) to access the data stored in the sqlite.

You can then override the insert/delete/query/update functions and add your functionality to it, for example perform different actions depending on the URI of the intent.

public int delete(Uri uri, String whereClause, String[] whereArgs) {
    int count = 0;

    switch(URI_MATCHER.match(uri)){
    case ITEMS:
        // uri = content://com.yourname.yourapp.Items/item
        // delete all rows
        count = db.delete(TABLE_ITEMS, whereClause, whereArgs);
        break;
    case ITEMS_ID:
        // uri = content://com.yourname.yourapp.Items/item/2
        // delete the row with the id 2
        String segment = uri.getPathSegments().get(1);
        count = db.delete(TABLE_ITEMS, 
                Item.KEY_ITEM_ID +"="+segment
                +(!TextUtils.isEmpty(whereClause)?" AND ("+whereClause+")":""),
                whereArgs);
        break;
    default:
        throw new IllegalArgumentException("Unknown Uri: "+uri);
    }

    return count;
}

The UriMatcher is defined as

private static final int ITEMS = 1;
private static final int ITEMS_ID = 2;
private static final String AUTHORITY_ITEMS ="com.yourname.yourapp.Items";
private static final UriMatcher URI_MATCHER;

static {
    URI_MATCHER = new UriMatcher(UriMatcher.NO_MATCH);
    URI_MATCHER.addURI(AUTHORITY_ITEMS, "item", ITEMS);
    URI_MATCHER.addURI(AUTHORITY_ITEMS, "item/#", ITEMS_ID);
}

This way you can decide if only 1 result shall be returned or updated or if all should be queried or not.

The SQLiteOpenHelper will actually perform the insert and also take care of upgrades if the structure of your SQLite database changes, you can perform it there by overriding

class ItemDatabaseHelper extends SQLiteOpenHelper {
    public ItemDatabaseHelper(Context context){
        super(context, "myDatabase.db", null, ITEMDATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        String createItemsTable = "create table " + TABLE_ITEMS + " (" +
            ...
        ");";

        // Begin Transaction
        db.beginTransaction();
        try{
            // Create Items table
            db.execSQL(createItemsTable);

            // Transaction was successful
            db.setTransactionSuccessful();
        } catch(Exception ex) {
            Log.e(this.getClass().getName(), ex.getMessage(), ex);
        } finally {
            // End transaction
            db.endTransaction();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String dropItemsTable = "DROP TABLE IF EXISTS " + TABLE_ITEMS;

        // Begin transaction
        db.beginTransaction();

        try {
            if(oldVersion<2){
                // Upgrade from version 1 to version 2: DROP the whole table
                db.execSQL(dropItemsTable);
                onCreate(db);
                Log.i(this.getClass().toString(),"Successfully upgraded to Version 2");
            }
            if(oldVersion<3) {
                // minor change, perform an ALTER query
                db.execSQL("ALTER ...");
            }

            db.setTransactionSuccessful();
        } catch(Exception ex){
            Log.e(this.getClass().getName(), ex.getMessage(), ex);
        } finally {
            // Ends transaction
            // If there was an error, the database won't be altered
            db.endTransaction();
        }
    }
}

and then the easiest part of all: Perform a query:

String[] rows = new String[] {"_ID", "_name", "_email" };
Uri uri = Uri.parse("content://com.yourname.yourapp.Items/item/2";

// Alternatively you can also use getContentResolver().insert/update/query/delete methods
Cursor c = managedQuery(uri, rows, "someRow=1", null, null); 

That's basically all and the most elegant way to do it as far as I know.