Pyinstaller can't insert information to sqlite database file --onefile

Question

I'm trying to build one-file EXE file with Pyinstaller which is to include Sqlite database file, it can't work with --onefile but in normal case mean (many files) it's working 100%.

What I'm doing is in .py script :

import os, sys
def resource_path(relative_path):
    try:
        base_path = sys._MEIPASS
    except Exception:
        base_path = os.path.abspath(".")
    return os.path.join(base_path, relative_path)

I used it to include Sqlite file and it's working 100% And in .spec file I input this :

a.datas.append(('sqlite.db', 'sqlite.db', 'DATA'))

When I building it as many files it's working 100% and I can insert information from my app but as --onefile I can't

Can I do this with Sqlite database If I can't is there any database help me?

Answer 1

If you are using the --onefile option (i.e bundling everything into a single executable), when you run your_app_name.exe, Pyinstaller unpacks your data into a temporary folder and the path gets set as sys._MEIPASS.

def path_to_temp(relative_path):
    try:
        base_path = sys._MEIPASS
    except Exception:
        base_path = os.path.abspath(".")

    return os.path.join(base_path, relative_path)

So your data files that you are specifying in your spec file will also be extracted into this temporary location when you launch your application.

a.datas.append(('sqlite.db', 'sqlite.db', 'DATA'))

You can check the extracted files by visiting the temporary location. You can get its path by simply adding this to the above code

print  base_path

Now here you'll also see your database file.Now make some changes to it. Then quit your app and launch it again. Get the new temporary location and check whether the changes you made got reflected back or not. The answer is No. Because it is not repackaging the extracted file.

So data files can be files like images or sound files or even database files which is not going to be modified by the program.

You need not be including your database file inside the bundled exe. Instead create a folder at runtime inside Appdata by the name of your application and store your database file there.

An example for an app using sqlite3.

dir_path = os.path.join(os.environ['APPDATA'], 'YOUR_APP_NAME')
if not os.path.exists(dir_path):
     os.makedirs(dir_path)
file_path = os.path.join(dir_path, 'your_database_file.sqlite')
sqlite3.connect(file_path)

This will create a folder by the name of your app inside AppData\Roaming directory and your database file will be stored there.

Answer 2

You will be able to access the SQLite file within the package, but as read-only. Therefore new information cannot be added. You will run into this same issue with similar embedded databases.

You can either package the SQLite file separately or clone the file and read the file externally.