4

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?

Protect children of Donbas2014
  • 226
  • 3
  • 18
  • 1
    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](https://pypi.org/project/pyfakefs/4.5.1/). Up to you to know whether it is worth it, or whether using a true file is simpler... – Serge Ballesta Oct 11 '21 at 07: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 Oct 11 '21 at 07:49
  • 3
    Similar https://stackoverflow.com/questions/68054099/query-an-existing-downloaded-sqlite-database-in-python-in-memory-without-having – snakecharmerb Oct 11 '21 at 12:55
  • @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 Oct 12 '21 at 11:50

2 Answers2

3

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

Shwetha
  • 106
  • 8
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.

Victor Paléologue
  • 2,025
  • 1
  • 17
  • 27