Problem Creating One File exe with pyinstaller and pysqlcipher

Question

I'm trying to create a one file .exe to run on any windows machine but I've hit a problem with pysqlcipher. I've gone back to some basic code that just creates a simple database with a key, on my dev machine all works fine whether I use the python file or the compiled exe. I seem to be missing a library, path or both? I've tried adding vaious items using --add-data but have spent hours and made no progress. Here is the basic bit of python:-

from pysqlcipher3 import dbapi2 as sqlite
import os
import sys

def resource_path(relative_path):
    """ Get absolute path to resource, works for dev and for PyInstaller """
    try:
        base_path = sys._MEIPASS
    except Exception:
        base_path = os.path.abspath(".")

    return os.path.join(base_path, relative_path)

conn = sqlite.connect('test.db')
c = conn.cursor()
c.execute("PRAGMA key='password'")
c.execute('''create table stocks (date text, trans text, symbol text, qty real, price real)''')
c.execute("""insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)""")
conn.commit()
c.close()

When I run the exe on a different windows 10 PC I get this error

Traceback (most recent call last): File "testdb.py", line 1, in File "c:\users\xxx\appdata\local\programs\python\python38\lib\site-packages\PyInstaller\loader\pyimod03_importers.py", line 623, in exec_module File "site-packages\pysqlcipher3-1.0.3-py3.8-win-amd64.egg\pysqlcipher3\dbapi2.py", line 33, in ModuleNotFoundError: No module named 'pysqlcipher3._sqlite3' [9248] Failed to execute script testdb

The error references the path on my dev PC and also refers to line 33 in dbapi2.py which is:-

from pysqlcipher3._sqlite3 import *

I have tried adding various files when running pyinstaller but I am making no progress, I'm sure its nothing simple but need help please.

Answer 1

I encountered a similar problem a while back and one thing that worked for me is adding the sqlite3 dll in the spec file of pyinstaller. It is also possible to add it from command line. I had tried many other things before such as re-creating a conda environment and rebuilding my python package but that did not work. In your spec file where you add the pyinstaller binaries please try something like.

binaries=[('C:\\Users\\myname\\newfolder\\sqlite3.dll','.')]

You can download the DLL from https://www.sqlite.org/download.html depending on windows or linux platform . Once you add the dll and recompile it should be fine. I think this error is primarily happening as dbapi2 requests the sqlite3 dll which in my case atlease was missing from the DLL folder. I just downloaded and added it in my spec file, but you can also try to add it to the hookspath or ENV folder.