AWS Lambda function can't connect to and query SQLite DB file from S3-CodePudding

My goal is to upload a SQLite database file to AWS S3, use AWS Lambda & python (sqlite3) to connect to the database, query it, and return some of its data.

Having uploaded the database file to S3, I wrote a python script (which is to become the Lambda function) that successfully downloads the database from S3, connects to it with sqlite3, and returns some query results.

The issue is that when I take the exact same code and put it in AWS Lambda, I get the following error:

{
  "errorMessage": "malformed database schema (message_idx_undelivered_one_to_one_imessage) - near \"where\": syntax error",
  "errorType": "DatabaseError",
  "stackTrace": [
    "  File \"/var/task/lambda_function.py\", line 11, in lambda_handler\n    print(cursor.execute(\"select mycolumn from message limit 1\").fetchone())\n"
  ]
}

Here is the python script/Lambda function:

import sqlite3
import boto3

def lambda_handler(event, context):
    s3 = boto3.resource("s3")
    bucket = s3.Bucket("my-bucket")
    bucket.download_file("my_database.db", "/tmp/my_database.db")

    conn = sqlite3.connect("/tmp/my_database.db")
    cursor = conn.cursor()
    print(cursor.execute("select mycolumn from message limit 1").fetchone())

Running this file locally works correctly. I have confirmed that both my local environment and Lambda are using these versions of the following:

boto3: 1.20.32
python: 3.9.13
sqlite3: 2.6.0

Here is the schema of the database I'm trying to read.

Why is Lambda producing this error while my local environment isn't? How can I go about getting python to connect to the database within the Lambda function?

CodePudding user response：

From pastebin extract here is your DDL in your database schema which triggers your error when opening your database

CREATE INDEX message_idx_undelivered_one_to_one_imessage
ON message(
    cache_roomnames,
    service,
    is_sent,
    is_delivered,
    was_downgraded,
    item_type
) where cache_roomnames IS NULL
    AND service = 'iMessage'
    AND is_sent = 1
    AND is_delivered = 0
    AND was_downgraded = 0
    AND item_type == 0;

The partial index feature has been integrated in sqlite since the 3.8.0 version https://www.sqlite.org/partialindex.html. The sqlite3 version on the lambda environment is probably to old to handle such king of schema. An version bump in your sqlite3 dependency in your lambda environment should fix your issue.