Chaquopy doesn't recognize 'pyodbc' module. Any suggestions?

Question

recently I wanted to check if it's possible to make a connection with MS SQL Server using python script and use Chaquopy to call the python functions in java. I used SQLAlchemy library to connect to the database. Here's the python code:

from sqlalchemy import create_engine, text
def test():

    server = 'LAPTOP\SQLEXPRESS'
    database = 'test'
    driver = 'ODBC Driver 17 for SQL Server'

    dbconnection = f'mssql://@{server}/{database}?driver={driver}'

    engine = create_engine(dbconnection)

    connection = engine.connect()
    result = connection.execute(text("SELECT * FROM test_table"))
    test_result = ""
    for row in result:
        test_result += str(row)

    connection.close()

    return test_result

test()

As I said, I called the function from python in java code, the java code is placed below:

package com.example.test3;

import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.widget.TextView;

import com.chaquo.python.PyObject;
import com.chaquo.python.Python;
import com.chaquo.python.android.AndroidPlatform;

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        init();

        TextView test = findViewById(R.id.test);

        test.setText(test());
    }

    public String test(){

        Python python = Python.getInstance();
        PyObject pythonFile = python.getModule("test");

        Log.v("TAG", "funkcja test dziala");
        return pythonFile.callAttr("test").toString();
    }

    public void init(){
        if (! Python.isStarted()) {
            Python.start(new AndroidPlatform(this));
        }
    }
}

In gradle file, I imported SQLAlchemy properly and before embedding the script above, the application did not crash. Whenever I run python code in shell, it works as I expected. However, after adding the code to the project, my app starts to crash and occurs and error that my logcat displays:

FATAL EXCEPTION: main
Process: com.example.test3, PID: 28531
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.test3/com.example.test3.MainActivity}: com.chaquo.python.PyException: ModuleNotFoundError: No module named 'pyodbc'

Here's the Gradle config (module-level):

plugins {
    id 'com.android.application'
    id 'com.chaquo.python'
}

android {
    namespace 'com.example.test3'
    compileSdk 33

    defaultConfig {
        applicationId "com.example.test3"
        minSdk 24
        targetSdk 33
        versionCode 1
        versionName "1.0"

        testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
        ndk {
            abiFilters "armeabi-v7a", "arm64-v8a", "x86", "x86_64"
        }
        python {
            version "3.8"
            buildPython "C:\\Users\\48794\\AppData\\Local\\Programs\\Python\\Python38-32\\python.exe"
        }
        python {
            pip {
                install "SQLAlchemy"
            }
        }
        sourceSets {
            main {
                python.srcDir "src/main/python"
            }
        }

    }

    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
        }
    }
    compileOptions {
        sourceCompatibility JavaVersion.VERSION_1_8
        targetCompatibility JavaVersion.VERSION_1_8
    }
}

dependencies {

    implementation 'androidx.appcompat:appcompat:1.6.1'
    implementation 'com.google.android.material:material:1.5.0'
    implementation 'androidx.constraintlayout:constraintlayout:2.1.4'
    testImplementation 'junit:junit:4.13.2'
    androidTestImplementation 'androidx.test.ext:junit:1.1.5'
    androidTestImplementation 'androidx.test.espresso:espresso-core:3.5.1'
}

Am I missing something important or my vision is just way too abstract? Every suggestion is appreciated.

Answer 1

It seems that your application is missing a DBAPI implementation to connect to the database. To connect to SQL Server SQLAlchemy requires either PyODBC or pymssql. I'm guessing that one was already installed in your host system because your pure Python test did work. Apparently Chaquopy doesn't currently support PyODBC. There is no mention of pymssql that I could find. Unless you are willing to migrate these packages yourself, I would say it is unsupported today.

My recommendation would be to separate concerns to what would be better to handle in the Java side and the Python side. Seems to me that database access is more mature in the Java side.