Reading from sqlite3 remote databases

Question

In my server I'm trying to read from a bunch of sqlite3 databases (sent from web clients) and process their data. The db files are in an S3 bucket and I have their url and I can open them in memory.

Now the problem is sqlite3.connect only takes an absolute path string and I can't pass to it a file in memory.

conn=sqlite3.connect() #how to pass file in memory or url
c=conn.cursor()
c.execute('''select * from data;''')
res=c.fetchall()
# other processing with res

Answer 1

SQLite requires database files to be stored on disk (it uses various locks and paging techniques). An in-memory file will not suffice.

I'd create a temporary directory to hold the database file, write it to that directory, then connect to it. The directory gives SQLite the space to write commit logs as well.

To handle all this, a context manager might be helpful:

import os.path
import shutil
import sqlite3
import sys
import tempfile

from contextlib import contextmanager


@contextmanager
def sqlite_database(inmemory_data):
    path = tempfile.mkdtemp()
    with open(os.path.join(path, 'sqlite.db'), 'wb') as dbfile:
        dbfile.write(inmemory_data)
    conn = None
    try:
        conn = sqlite3.connect(os.path.join(path, 'sqlite.db'))
        yield conn
    finally:
        if conn is not None:
            conn.close()
        try:
            shutil.rmtree(path)
        except IOError:
            sys.stderr.write('Failed to clean up temp dir {}'.format(path))

and use that as:

with sqlite_database(yourdata) as connection:
    # query the database 

This writes in-memory data to disk, opens a connection, lets you use that connection, and afterwards cleans up after you.