Chaquopy SQLite3 Tutorial (Android Python Tutorial)

Chaquopy is a module that lets you run Python code in your Android app.

For my app, Shopshop Shopping List, I made a script that saves and retrieves my shopping lists in an SQLite database, but when I first connected this to my app with Chaquopy, I had a few problems getting SQLite to work.

And I can assume that many people trying out Chaquopy or perhaps even other Python-for-Android modules, would run into similar problems.

So this is going to be showing how I got through those problems, and in general, how I’ve done my SQLite Python setup, converting it into JSON, and passing that JSON into my Android code in Chaquopy.

Disclaimer: I am an Android Developer, but not very experienced as a Python developer. I don’t know the coding standards and best practices of Python, so take my Python code with a grain of salt. Nonetheless, I’d love if Python devs could throw suggestions down in the comments on how I can improve my Python code.

Connecting to the DB

import sqlite3

class Commands:
    connection = sqlite3.connect('shopping_lists.db')
    cursor = connection.cursor()

If we were in a purely Python environment, we can get away with calling connect('shopping_lists.db').

But if we tried to do this in the context of an Android app, even though we’re running it using Python code, we’ll get this error:

com.chaquo.python.PyException: OperationalError: unable to open database file
on `connection = sqlite3.connect("shopping_lists.db")`

(I got this error when trying to connect to SQLite: Unable to open database file)

This happens because the path above provided into sqlite3.connect doesn’t quite work in an Android environment. Fortunately, the workaround is an easy one. We have to provide an absolute path based on our package.

import os.path

package_dir = os.path.abspath(os.path.dirname(__file__))
db_dir = os.path.join(package_dir, 'shopping_lists.db')

connection = sqlite3.connect(db_dir)

(credits to woodphil for his answer on getting the absolute path to the db)

With this, we’ll be able to connect to our db and we can now perform all our db transactions.

Although it’s not required, I put the rest of my SQLite code in a Commands class for convenience. We get things running by making a connection, passing in the path to our database, and using that to get a cursor which will let us execute our SQL commands.

Creating Tables

TABLE_SHOPPINGLISTS = "shoppinglists"
TABLE_SHOPITEMS = "shopitems"

class Commands:

    connection = sqlite3.connect(db_dir)
    connection.row_factory = sqlite3.Row
    cursor = connection.cursor()

    def __init__(self):
        create_shoppinglists_table = f"""
        CREATE TABLE IF NOT EXISTS
        {TABLE_SHOPPINGLISTS}(id INTEGER PRIMARY KEY, name TEXT NOT NULL)
        """
        self.cursor.execute(create_shoppinglists_table)

        create_shopitems_table = f"""
            CREATE TABLE IF NOT EXISTS
            {TABLE_SHOPITEMS} (
            id INTEGER PRIMARY KEY,
            list_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            quantity INTEGER NOT NULL,
            checked INTEGER NOT NULL,
            FOREIGN KEY(list_id) REFERENCES {TABLE_SHOPPINGLISTS}(id)
            )
        """
        self.cursor.execute(create_shopitems_table)

In our class’s __init__, we execute commands to create our tables if they don’t exist. Once again, I have two tables: ShoppingLists and ShopItems.

Everything here is really just creating standard SQL statements as strings. We’re defining our tables, their names, and each of their columns and their data types, then using the cursor to execute them. The ShopItems table also uses a foreign key to associate shop items with a shopping list.

If you have a sharp eye, you may have noticed connection.row_factory = sqlite3.Row. We’re gonna make our get functions return as JSON, and this will allow us to do that. This makes it so that when we call json.dumps later, we’ll get JSON keys that correspond to the name of each column in our table.

An example of such for a shopping list json:

{
  id: 1,
  name: "Sample List"
}

Insert, Update, & Delete Commands

class Commands:
  
        # ...

    def insert_shoppinglist(self, name):
        self.cursor.execute(
            f"INSERT INTO {TABLE_SHOPPINGLISTS} VALUES (NULL, '{name}')"
        )
        self.connection.commit()


    def insert_shopitem(self, list_id, name, quantity, checked):
        self.cursor.execute(
            f"INSERT INTO {TABLE_SHOPITEMS} VALUES (NULL, {list_id}, '{name}', {quantity}, {checked})"
        )
        self.connection.commit()


    def update_shoppinglist(self, list_id, name):
        self.cursor.execute(
            f"UPDATE {TABLE_SHOPPINGLISTS} SET name = '{name}' WHERE list_id = {list_id}"
        )
        self.connection.commit()


    def update_shopitem(self, item_id, name, quantity, checked):
        self.cursor.execute(
            f"""
            UPDATE {TABLE_SHOPITEMS}
            SET name = '{name}', quantity = {quantity}, checked = {checked}
            WHERE item_id = {item_id}
            """
        )
        self.connection.commit()


    def delete_shoppinglist(self, list_id):
        self.cursor.execute(
            f"DELETE FROM {TABLE_SHOPPINGLISTS} WHERE list_id = {list_id}"
        )
        self.connection.commit()


    def delete_shopitem(self, item_id):
        self.cursor.execute(
            f"DELETE FROM {TABLE_SHOPITEMS} WHERE item_id = {item_id}"
        )
        self.connection.commit()


    def clear_all(self):
        self.cursor.execute(f"DROP TABLE {TABLE_SHOPPINGLISTS}")
        self.cursor.execute(f"DROP TABLE {TABLE_SHOPITEMS}")
        self.connection.commit()

We define functions that use our cursor to execute SQL commands to insert, update, and delete items from our database. Every time we do, we’re commiting to our connection so the changes our saved outside the context of this cursor.

Get Commands

import json

class Commands:

    # ...
    
    def dict_from_row(self):
    		return [dict(row) for row in self.cursor.fetchall()]

    def get_shoppinglists(self):
        self.cursor.execute(
            f"SELECT * FROM {TABLE_SHOPPINGLISTS}"
        )
        return json.dumps(self.dict_from_row())


    def get_shopitems(self, list_id):
        self.cursor.execute(
            f"SELECT * FROM {TABLE_SHOPITEMS} WHERE list_id = {list_id}"
        )
        return json.dumps(self.dict_from_row())

I kept the get commands separate from the last section because there’s a bit more that’s interesting here. In these functions, we’re converting the result of the query (provided by cursor.fetchall()) into JSON format, and returning that. We’re using dict_from_row to convert the fetchall result into an array of dictionaries which ultimately forms our JSON array.

The reason for that is that the Python code can’t just make a class and hand it to our Android code. Almost like treating this like a network API, we need to convert our result to JSON, and pass it to our Android code that way.

Now using fetchall will return a JSON array, which works for getting multiple shopping lists or shop items. What if we only wanted to get one shopping list?

def get_shoppinglist_by_id(self, list_id):
    self.cursor.execute(
        f"SELECT * FROM {TABLE_SHOPPINGLISTS} WHERE id = {list_id}"
    )
    shopping_list = dict(self.cursor.fetchone())

To get a JSON object rather than a JSON array, we use fetchone. We call dict() on it directly instead of using the dict_from_row() function we defined earlier because we only need a single dictionary to form our JSON object, not an array of dictionaries.

Now we can get shopping lists and shop items with what we have, but what about a shopping list with an array of items that are its shop items?

We’ll modify our get_shoppinglist_by_id to do this.

def get_shoppinglist_by_id(self, list_id):
    self.cursor.execute(
        f"SELECT * FROM {TABLE_SHOPPINGLISTS} WHERE id = {list_id}"
    )
    shopping_list = dict(self.cursor.fetchone())

    self.cursor.execute(
        f"SELECT * FROM {TABLE_SHOPITEMS} WHERE list_id = {shopping_list['id']}"
    )
    shop_items = self.dict_from_row()
    shopping_list['items'] = shop_items

    return json.dumps(shopping_list)

After making our query for the shopping list, we make a second query to get an array of shop items, then manually set that to the shopping list dictionary we already have and that’s what we return as a JSON.

Cleanup

class Commands:
  
    # ...
  
  def cleanup(self):
    self.cursor.close()
    self.connection.close()

Don’t forget to define a function to close the cursor and the connection. We’ll need to call this in our app to avoid memory leaks.

Calling it in Android Code

private fun testDbThings() {
  val python = Python.getInstance()
  val pythonFile = python.getModule("commands")
  val commandsClass = pythonFile.callAttr("Commands")

  commandsClass.callAttr("insert_shoppinglist", "Test List")
  val getListsResult = commandsClass.callAttr("get_shoppinglists").toString()
  val resultJson = JSONArray(getListsResult)
  commandsClass.callAttr("cleanup")
  
  Toast.makeText(this, resultJson.toString(), Toast.LENGTH_LONG).show()
}

Using Chaquopy, we get our Python file and class, and test it by inserting an item by calling the insert_shoppinglist function in our Python code.

Then to get the results, we get our JSON result via get_shoppinglists as a String, and convert that to JSON. We now have our data.

The reason we need to first get it as a String and then convert it back to JSON is because Chaquopy doesn’t let you get the results of functions directly as JSON objects or arrays, but that’s a problem easily solved like we did above.

Do make sure you call Python.start() on the creation of your application or activity, and on termination or destruction of your application or activity, call the cleanup() function defined earlier to prevent memory leaks.

Improvements to be made

Of course, as I develop my app, testDbThings() isn’t going to be where I’m calling my Python code from to manage my SQLite database.

A good way to architect this code is to make a repository or service class that keeps an instance of the Python class and calls all the Python code within itself. This then abstracts the Python layer from other areas of code. Just remember to call cleanup before the Python class instance dies.

Get the Source Code

Fortunately, the above improvements have already been made in my app Shopshop Shopping List which you can find on Github. Search for ChaquopyDatabaseWrapper specifically.

Why do this over Room?

Gonna be real honest, I’m not too sure myself. Maybe if you had a Python-specific SQLite integration that you wanted to then relay into your app? Like I said, I’m not a Python Developer, but I find it cool nonetheless. I would like to know some situations in which managing SQLite code in a Python class over Room can be a good decision.

In any case, happy coding ༼ つ ◕_◕ ༽つ