Python azure function app not reading sqlite db file correctly

Question

I have a storage account with a container named abc360. There is a folder in this container named sqlite_db_file. Sqlite db files will be dropped in this folder. File name would like this:

ABC100102_2_20230202.db

Path to the file looks like:

abc360/sqlite_db_file/ABC100102_2_20230202.db

Storage account and container details:

Storage account: abc360stg
Container: abc360

I have an azure function app in python that is supposed to get triggered when file is dropped and copy data from file to a csv file and upload it back to same path.

So I created a function with a blob trigger, function.json looks like this:

{
  "scriptFile": "__init__.py",
  "bindings": [
   {
     "name": "myblob",
     "type": "blobTrigger",
     "direction": "in",
     "path": "abc360/sqlite_db_file/{name}.db",
     "connection": "abc360stg_STORAGE"
   }
 ]
}

For now I am just trying to fetch all the tables that are in db file and below is my code:

import logging
import sqlite3
import os
import csv
from azure.functions import InputStream

class DataMigrator:
    def __init__(self, fileName, connection_string):
        self.fileName = fileName
        self.connection_string = connection_string

    def connect_sqlite(self):
        return sqlite3.connect(self.fileName)

    def get_table_names(self, cursor_sqlite):
        logging.info(cursor_sqlite)
        cursor_sqlite.execute("SELECT name FROM sqlite_master;")
        return cursor_sqlite.fetchall()

def main(myblob: InputStream):
    try:
        blob_name = myblob.name.split("/")[-1]
        logging.info(blob_name)
        migrator = DataMigrator(blob_name, connection_string)

        conn_sqlite = migrator.connect_sqlite()
        logging.info("Connected to SQLite database successfully")
        cursor_sqlite = conn_sqlite.cursor()

        tables = migrator.get_table_names(cursor_sqlite)
        logging.info(f"Tables in SQLite file: {tables}")
    
    except Exception as e:
        logging.error(f"Error: {str(e)}")
    finally:
        # Close SQLite connection
        if conn_sqlite:
            conn_sqlite.close()

Code is working fine, it connects to sqlite db file fine, but it returns an emprt array for list of tables. When I connect locally with a folder in my local drive, it works fine and lists everything.

Output (when db file is in storage account):

Tables in SQLite file: []

Following code is used because myblob.name returns abc360/sqlite_db_file/ABC100102_2_20230303.db and all i want is the filename:

myblob.name.split("/")[-1]

I am wondering is there something else thats needed here for me to read db file that is residing in storage account?

Help would be really appreciated.

Answer 1

I tried the code below to obtain the list of tables from the .db file in an Azure storage blob.

Code :

import logging
import os
import tempfile
import sqlite3
from azure.functions import InputStream

class DataMigrator:
    def __init__(self, file_path):
        self.file_path = file_path

    def connect_sqlite(self):
        return sqlite3.connect(self.file_path)

    def get_table_names(self, cursor_sqlite):
        cursor_sqlite.execute("SELECT name FROM sqlite_master WHERE type='table';")
        return cursor_sqlite.fetchall()

def main(myblob: InputStream):
    try:
        blob_name = os.path.basename(myblob.name)
        logging.info(f"Processing blob: {blob_name}")

        temp_file_path = os.path.join(tempfile.gettempdir(), blob_name)
        with open(temp_file_path, "wb") as temp_file:
            temp_file.write(myblob.read())

        migrator = DataMigrator(temp_file_path)
        conn_sqlite = migrator.connect_sqlite()
        logging.info("Connected to SQLite database successfully")
        cursor_sqlite = conn_sqlite.cursor()

        tables = migrator.get_table_names(cursor_sqlite)
        logging.info(f"Tables in SQLite file: {tables}")

    except Exception as e:
        logging.error(f"Error: {str(e)}")
    finally:
        if conn_sqlite:
            conn_sqlite.close()
        if os.path.exists(temp_file_path):
            os.remove(temp_file_path)

local.settings.json :

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "<storage_conne>",
    "FUNCTIONS_WORKER_RUNTIME": "python",
    "kam11funcstr_STORAGE": "<storage_conne>"
  }
}

function.json :

{
  "scriptFile": "__init__.py",
  "bindings": [
    {
      "name": "myblob",
      "type": "blobTrigger",
      "direction": "in",
      "path": "abc360/sqlite_db_file/{name}.db",
      "connection": "kam11funcstr_STORAGE"
    }
  ]
}

Output :

The following blob trigger function code ran successfully, providing a list of tables in the .db file from the container.

 *  Executing task: .venv\Scripts\activate ; func host start 

Found Python version 3.11.8 (py).

Azure Functions Core Tools
Core Tools Version:       4.0.5382 Commit hash: N/A  (64-bit)
Function Runtime Version: 4.25.3.21264

[2024-03-26T09:35:56.884Z] 0.01s - Debugger warning: It seems that frozen modules are being used, which may
[2024-03-26T09:35:56.888Z] 0.00s - make the debugger miss breakpoints. Please pass -Xfrozen_modules=off
[2024-03-26T09:35:56.893Z] 0.00s - to python to disable frozen modules.
[2024-03-26T09:35:56.897Z] 0.00s - Note: Debugging will proceed. Set PYDEVD_DISABLE_FILE_VALIDATION=1 to disable this validation.       
[2024-03-26T09:35:57.750Z] Worker process started and initialized.

Functions:

        BlobTrigger1: blobTrigger

For detailed output, run func with --verbose flag.
[2024-03-26T09:36:02.866Z] Host lock lease acquired by instance ID '000000000xxxxxxxxxx'.
[2024-03-26T09:36:30.129Z] Executing 'Functions.BlobTrigger1' (Reason='New blob detected(LogsAndContainerScan): abc360/sqlite_db_file/ABC100102_2_20230202.db', Id=fb47edb3-xxxxxxxxxxxxxxxx)
[2024-03-26T09:36:30.133Z] Trigger Details: MessageId: 109019e4-xxxxxxxxxxx, DequeueCount: 1, InsertedOn: 2024-03-26T09:36:28.000+00:00, BlobCreated: 2024-03-26T09:36:26.000+00:00, BlobLastModified: 2024-03-26T09:36:26.000+00:00
[2024-03-26T09:36:30.244Z] Connected to SQLite database successfully
[2024-03-26T09:36:30.244Z] Processing blob: ABC100102_2_20230202.db
[2024-03-26T09:36:30.244Z] Tables in SQLite file: [('users',), ('orders',)]
[2024-03-26T09:36:30.289Z] Executed 'Functions.BlobTrigger1' (Succeeded, Id=fb47edb3xxxxxxxxxxxx, Duration=925ms)