Hey Forum!

I know this horse has probably been beaten to death, but i cannot for the life of me find a way to make the SQLite ICU extension to work on Windows in Python 3.9.9 . On Linux works flawlessly either installed from pip (sqlite-icu : https://pypi.org/project/sqlite-icu/) or compiled from source (https://www.sqlite.org/src/info/91c021c7e3e8bbba) into .so file. On Windows have managed to successfully compile it into .dll file using msys/mingw, using the command:

gcc -shared icu.c pkg-config --libs --cflags icu-uc icu-io -o libSqliteIcu.dll

when trying to load the extension using this code in python:

conn = sqlite3.connect('library.db', ) conn.enable_load_extension(True) conn.load_extension('./libSqliteIcu')

I get the following error:

sqlite3.OperationalError: The specified module could not be found.

After some troubleshooting i found out i should rename it from libSqliteIcu.dll to icu.dll because slqite3_icu_init. After renaming it and changing the line:

conn.load_extension('./libSqliteIcu') to conn.load_extension('./icu')

sqlite3.OperationalError: The specified procedure could not be found.

I tried compiling some test extensions (the carray and json1) to dll-s and using the same code (only with './carray' and './json1' changed) they work flawlessly.

I also replacing the python sqlite3.dll with the prebuild one from the site (https://www.sqlite.org/download.html) because i read it could be because python ships with an older version of sqlite. That didn't change anything. I also compiled my own sqlite3.dll-s from the amalgamation (some with icu and some without). All the ones without icu worked as normally and the ones with icu gave me an error. The two main compilations i did were done using the commands (without icu and with respectively):

gcc -shared -O2 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_COLUMN_METADATA sqlite3.c -o sqlite3.dll

and

gcc -shared -O2 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_ENABLE_ICU sqlite3.c pkg-config --libs --cflags icu-uc icu-io -o sqlite3.dll

When the original sqlite3.dll is replaced with the one with the icu i get the following error:

ImportError: DLL load failed while importing _sqlite3: The specified module could not be found.

Other things i should maybe try are using MSVC instead of mingw, but god does that seem complicated and maybe trying it outside of python somehow. Anyway sorry if this problem has already been solved ,if i missed including some crucial piece of information and sorry the question is not properly formatted haven't learned any markdown.

Update: For now I haven't found a way to make the original icu extension to work on Windows, but i have found this unicode extension (https://github.com/Zensey/sqlite3_unicode) which is very easy to compile and use. Sorry if linking to an external extension is against the rules i am just posting the solution i found. To compile it i just used:

gcc -shared sqlite3_unicode.c -o unicode.dll

tho there are probably some compile time options some of you would like to add to improve performance and what not. Anyway there is also this repo which has this extention prebuild (https://github.com/nalgeon/sqlean) for windows, mac and linux (dll, dylib and so files respectively). Anyway this is my solution it offers Upper Lower and case insensitive Like operators that work with unicode (some other stuff too probably but i haven't looked into it too much).

Use the Dependency Walker to find out which DLLs your sqlite3.dll or icu.dll depends upon. You might need to put those dependencies in the same directory as your DLL, put them somewhere on the %PATH%, or find a way to link them statically to make the DLL self-contained.

That seems like a good idea tho i don't know how to use dependency walker i think i have an idea of which dll-s i might need. For now tho i will give it some rest because i spend i little more time on this than i would like to admit and i found another solution (another extension that gets the job done). Thanks for the suggestion and i will update the thread if i find a way to make this extension work.

Yeah on linux it is hella easy. you just install several libraries and you are good to compile. Thank you for giving me the 2 dependencies straight away that is some great help. I will update the post if manage to make it work.