Open sqlite database from http in memory

Question

I have code:

from io import BytesIO as Memory

import requests

def download_file_to_obj(url, file_obj):
    with requests.get(url, stream=True) as r:
        r.raise_for_status()
        for chunk in r.iter_content(chunk_size=None):
            if chunk:
                file_obj.write(chunk)


def main(source_url):
    db_in_mem = Memory()
    print('Downloading..')
    download_file_to_obj(source_url, db_in_mem)
    print('Complete!')
    with sqlite3.connect(database=db_in_mem.read()) as con:
        cursor = con.cursor()
        cursor.execute('SELECT * FROM my_table limit 10;')
        data = cursor.fetchall()
        print(data)
    del(db_in_mem)

The my_table exits in source database. Error:

sqlite3.OperationalError: no such table: my_table

How to load sqlite database to memory from http?

The problem is that sqlite3 wants its database to be a true file, or the special :memory: in memory database. Even the backup API only knows how to copy between them, but AFAIK, none is able to use a Python file like object, nor a memory image. If you want to go that way, you will have to find a memory file system. You could have a look at pyfakefs. Up to you to know whether it is worth it, or whether using a true file is simpler... — Serge Ballesta
– Serge Ballesta, Commented Oct 11, 2021 at 7:19
Thank you. I think it's not worth "building a garden", it's easier to use a real temporary file. As stated in ZEN of python: Simple is better than complex. — Protect children of Donbas2014
– Protect children of Donbas2014, Commented Oct 11, 2021 at 7:49
@snakecharmerb It looks like it, but not at all, since there is no way to execute SQL queries. Just do a dump. — Protect children of Donbas2014
– Protect children of Donbas2014, Commented Oct 12, 2021 at 11:50

Shwetha · Accepted Answer · 2021-10-20 08:35:04Z

The most common way to force an SQLite database to exist purely in memory is to open the database using the special filename :memory:. In other words, instead of passing the name of a real disk file pass in the string :memory:. For example:

database = sqlite3.connect(":memory:")

When this is done, no disk file is opened. Instead, a new database is created purely in memory. The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

Note that in order for the special :memory: name to apply and to create a pure in-memory database, there must be no additional text in the filename. Thus, a disk-based database can be created in a file by prepending a pathname, like this: ./:memory:.

See more here: https://www.sqlite.org/inmemorydb.html

Victor Paléologue · Accepted Answer · 2021-10-19 15:03:00Z

0

You can build on top of this solution and insert the data into an in-memory SQLite database, created with db = sqlite3.connect(":memory:"). You should be able to perform queries from that database.

answered Oct 19, 2021 at 15:03

Victor Paléologue

2,3791 gold badge23 silver badges32 bronze badges

1 Comment

Chris van Chip Over a year ago

Try to be less dependent on another answer, as it could be deleted any time and that renders this answer useless.

Collectives™ on Stack Overflow

Open sqlite database from http in memory

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related