sqlite-s3-query

Python function to query a SQLite file stored on S3. It uses multiple HTTP range requests per query to avoid downloading the entire file, and so is suitable for large databases.

All the HTTP requests for a query request the same version of the database object in S3, so queries should complete succesfully even if the database is replaced concurrently by another S3 client. Versioning must be enabled on the S3 bucket.

Operations that write to the database are not supported.

Inspired by phiresky's sql.js-httpvfs, and dacort's Stack Overflow answer.

Installation

sqlite-s3-query depends on APSW, which is not available on PyPI, but can be installed directly from GitHub.

pip install sqlite_s3_query
pip install https://github.com/rogerbinns/apsw/releases/download/3.36.0-r1/apsw-3.36.0-r1.zip --global-option=fetch --global-option=--version --global-option=3.36.0 --global-option=--all --global-option=build --global-option=--enable-all-extensions

Usage

from sqlite_s3_query import sqlite_s3_query

results_iter = sqlite_s3_query(
    'SELECT * FROM my_table WHERE my_column = ?', params=('my-value',),
    url='https://my-bucket.s3.eu-west-2.amazonaws.com/my-db.sqlite',
)

for row in results_iter:
    print(row)

If in your project you use multiple queries to the same file, functools.partial can be used to make an interface with less duplication.

from functools import partial
from sqlite_s3_query import sqlite_s3_query

query_my_db = partial(sqlite_s3_query,
    url='https://my-bucket.s3.eu-west-2.amazonaws.com/my-db.sqlite',
)

for row in query_my_db('SELECT * FROM my_table WHERE my_col = ?', params=('my-value',)):
    print(row)

for row in query_my_db('SELECT * FROM my_table_2 WHERE my_col = ?', params=('my-value',)):
    print(row)

The AWS region and the credentials are taken from environment variables, but this can be changed using the get_credentials parameter. Below shows the default implementation of this that can be overriden.

import os
from functools import partial
from sqlite_s3_query import sqlite_s3_query

query_my_db = partial(sqlite_s3_query
    url='https://my-bucket.s3.eu-west-2.amazonaws.com/my-db.sqlite',
    get_credentials=lambda: (
        os.environ['AWS_DEFAULT_REGION'],
        os.environ['AWS_ACCESS_KEY_ID'],
        os.environ['AWS_SECRET_ACCESS_KEY'],
        os.environ.get('AWS_SESSION_TOKEN'),  # Only needed for temporary credentials
    ),
)

for row in query_my_db('SELECT * FROM my_table_2 WHERE my_col = ?', params=('my-value',)):
    print(row)

Python function to query SQLite files stored on S3

Related tags

Overview

sqlite-s3-query

Installation

Usage

Owner

Michal Charemza

A super easy, but really really bad DBMS

A fast ordered NoSQL database.

ClutterDB - Extremely simple JSON database made for infrequent changes which behaves like a dict

Mongita is to MongoDB as SQLite is to SQL

Python function to extract all the rows from a SQLite database file while iterating over its bytes, such as while downloading it

A Painless Simple Way To Create Schema and Do Database Operations Quickly In Python

A very simple document database

HTTP graph database built in Python 3

Python object-oriented database

TinyDB is a lightweight document oriented database optimized for your happiness :)

ChaozzDBPy - A python implementation based on the original ChaozzDB from Chaozznl with some new features

securedb is a fast and lightweight Python framework to easily interact with JSON-based encrypted databases.

Connect Django Project to PostgreSQL

A Modular MWDB Utility to Collect Fresh Malware Samples

Simpledb-py: Simple JSON database

AWS Tags As A Database is a Python library using AWS Tags as a Key-Value database.

EmployeeDB - Advanced Redis search functionalities on Python applied on an Employee management backend app

Code for a db backend that relies on bash tools (grep, cat, echo, etc)

Manage your sqlite database very easy (like django) ...

MyReplitDB - the most simplistic and easiest wrapper to use for replit's database system.