Geek asks and answers

Reading from remote sqlite3 databases

On my server, I am trying to read from several sqlite3 databases (sent from web clients) and process their data. The db files are in the S3 bucket and I have its url and I can open them in memory.

Now the problem sqlite3.connectaccepts only the absolute line of the path, and I can not pass the file in memory to it.

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
+3
source share
1 answer

SQLite requires the database files to be stored on disk (it uses various locks and swap methods). There is not enough file in memory.

I would create a temporary directory to store the database file, write it to this directory, and then connect to it. The reference provides SQLite space for writing commit logs.

, :

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))

:

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

, , , .

+7

More articles:


All Articles