This answer is relevant for these versions:
python - up to 3.7 (but not higher)
pyinstaller 3.4
setuptools 41.0.1
ibm_db 3.0.1
ibm_db_sa 0.3.4
sqlalchemy 1.3.3
There are separate issues here.
The immediate issue (the ImportError) is the failure to load ibm_db.dll
The ImportError happens because pyinstaller does not copy external (non python) libraries into the bundle unless you explicitly request that to happen.
pyinstaller will also not copy a Db2-client into the bundle unless you explicitly tell it to do that, which means that if your target hostname to which you deploy your built-executable does not already have a preconfigured preinstalled Db2-client then you will also experience the failure to load ibm_db module.
The pyinstaller option --add-binary
gives a workaround for some kinds of ImportError , see example below. If you are not using SQLAlchemy just skip those parts of this answer.
The pyinstaller option --add-data
gives a workaround for adding directories (for example the clidriver directory for adding a Db2-driver) when your target environment lacks a Db2-driver.
Note that this answer does not require you to use SQLAlchemy, the answer is also relevant if you are only using ibm_db (or ibm_db_dbi), in which case just skip the SQLAlchemy parts.
If your python script uses SQLAlchemy to access Db2, then you may see a second symptom at run time after building with pyinstaller. The run time symptom is either:
"sqlalchemy.exc.NoSuchModuleError: Can't load plugin:
sqlalchemy.dialects:ibm_db_sa"
or
"sqlalchemy.exc.NoSuchModuleError: Can't load plugin:
sqlalchemy.dialects:db2.ibm_db"
(depending on the prefix for the url given to create_engine())
This symptom sqlalchemy.exe.NoSuchModuleError is not specific to Db2 but can impact other databases when used via SQLAlchemy with an external dialect ( Db2, teradata, snowflake, presto,...). Databases that use SQLAlchemy internal dialects may just work out of the box.
Here is one workaround for SQLAlchemy, other workarounds are possible.
SQLAlchemy external dialects use pkg_resources entry_points to allow SQLAlchemy to exploit them, but pyinstaller cannot yet handle these, without some assistance from you. Such entry point information is a kind of meta data about the module.
This workaround uses pyinstaller hooks to collect the metadata of the relevant modules , and tells pyinstaller the directory (or directories) that contain these hook files. For Db2 with SQLAlchemy, three hook files are needed, hook-ibm_db.py, hook-ibm_db_sa.py, hook-sqlalchemy.py
. I choose to put these hook files in the same directory as my source file python script.
The contents of each of these files is trivial two lines, and the contents differ only by the module name contained within. Here is an example of one of the files hook-sqlalchemy.py
(for the other 2 required files, just replace the module name appropriately):
from PyInstaller.utils.hooks import copy_metadata
datas = copy_metadata('sqlalchemy')
To add ibm_db.dll
via the --add-binary method
, you can either use a command line option to pyinstaller or edit the spec file.
For handling load failures of ibm_db.dll alone, just use the --add-binary additional option like this:
pyinstaller -y --add-binary %LOCALAPPDATA%ProgramsPythonPython37Libsite-packagesibm_db_dllsibm_db.dll;.ibm_db_dlls your_script.py
If you want to include clidriver in your bundle, first find the fully qualified pathname to its location via:
pip show ibm_db
and in the output of that command see the Location:
line which has the first part of the fully qualified pathname , so you append CLIDRIVER
to that path and use it in the --add-data
additional option like this:
--add-data="c:pathoclidriver;.clidriver"
If you do include clidriver in your bundle, there are additional considerations, see the notes section below.
For apps that also use SQLAlchemy, you need additional steps.
Suppose that the ibm_db.dll lives in this directory:
%LOCALAPPDATA%programspythonpython37libsite-packagesibm_db_dlls
and you make a variable in a CMD.EXE shell to point to that location:
> set ibm_db_path=%LOCALAPPDATA%programspythonpython37libsite-packagesibm_db_dlls
For an MS-Windows batch file (using ^ as line continuation character), the pyinstaller command line example to handle both of the workarounds mentioned above is:
pyinstaller -y ^
--additional-hooks-dir=. ^
--hidden-import ibm_db_sa.ibm_db ^
--hidden-import ibm_db_dbi ^
--hidden-import ibm_db ^
--add-binary %LOCALAPPDATA%ProgramsPythonPython37Libsite-packagesibm_db_dllsibm_db.dll;.ibm_db_dlls ^
your_script.py
Notes:
If your python script explicitly imports the SQLAlchemy modules then you do not
need to specify them via --hidden-import
options (buy you still need
the hooks for SQLAlchemy to operate after bundling).
For ibm_db versions up to 3.0.2, the ibm_db.dll
needs to be in subdirectory ibm_db_dlls
in your
bundle, which is the reason for specifying that destination on the
--add-binary
option.
If you are building for Linux/Unix, instead of ^. use as the line continuation character as usual.
If you intend to copy your built executable to a new hostname, and that new hostname does not already have a pre-installed Db2-client , and you do not wish to install a separate Db2-client on the target, then you can bundle clidriver with the pyinstaller output with the --add-data
option shown above.
If you bundle clidriver be aware that you may also need to bundle its configuration files (such as db2dsdriver.cfg
and db2cli.ini
) if they are in non-default locations, depending on whether your code uses externally configured DSNs or long connection-strings. If you do not bundle such configuration files (implicitly or explicitly) and you deploy your built environment to a different hostname than the build environment then you will need to reconfigure those files at the target hostname. The default location for these files is in the clidrivercfg
directory which will get included via --add-data
as mentioned earlier.
If you bundle clidriver, and if you are using encrypted connections to Db2 via TLS/SSL, be aware you may also need to bundle additional files such as certificates, keystore/stash files etc when you run the pyinstaller build.
If you bundle clidriver, be aware that IBM refreshes this component a couple of times per year with bug fixes and security fixes and new functions, so you may need to refresh your executables periodically to prevent them from becoming security holes by being frozen in time with old versions.
if you bundle clidriver and if you need to use odbcad32
on the target hostname for configuring Db2 DSNs, then following deployment on the target hostname remember to run the clidriverindb2cli install -setup
command on the target hostname.