Load sqlite3 extension in python3 sqlite

Question

How does one correctly load a sqlite extension into python sqlite import?

os: Windows 7 64bit
sqlite3 version: 3.14.1 64bit
python3 version: 3.5.2 64bit

Here is my process so far: compile extension-functions.c to libsqlitefunctions.dll using this command:

gcc -shared -I "C:\Software\sqlite3\sqlite-master" -o libsqlitefunctions.dll extension-functions.c

Then I can happily use these functions in sqlite3 command line using this command:

SELECT load_extension('libsqlitefunctions.dll');

However when trying in the python script:

import sqlite3 as lite
con = lite.connect(db_file)
con.enable_load_extension(True)
con.load_extension("<<path to file>>\\libsqlitefunctions.dll")

This error appears:

Error The specified module could not be found. :

The extension-functions.c file does include the COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE section, and in fact it loads fine when using command line sqlite3

Additional notes:
The python sqlite package is installed and working correctly.
I've also tried updating the sqlite3.dll in the python path to the latest version

Check if you have sqlite3 package installed . Else use pip install sqlite3 in your windows command prompt to install it ,and try running the code again. — Arihant
– Arihant, Commented Sep 20, 2018 at 3:36
@Arihant yes it is installed. All other commands work, just an issue loading the extension. — rayzinnz
– rayzinnz, Commented Sep 20, 2018 at 3:42
@Shawn - many different paths. I've tried every possiblity, including having the dll in the python3 PATH and the sqlite3 PATH and only putting the dll name in the call. But, by example, I would used something like this: "C:\\temp\\libsqlitefunctions.dll" — rayzinnz
– rayzinnz, Commented Sep 20, 2018 at 21:24

rayzinnz · Accepted Answer · 2018-09-20 04:58:39Z

1

This isn't a nice answer, but it provides a method to use the extension in python.

Output sql commands to a file, then use subprocess to run the file directly into the command line, like so:

import subprocess
import uuid
import os

db_file = "trial02.db"
sqlite_functions_file = "libsqlitefunctions.dll"

sql_file = uuid.uuid4() + ".sql"
with open(sql_file, 'w') as fsql:
    fsql.write('/* auto script */\n\n')
    #load extension
    sql = "SELECT load_extension('" + sqlite_functions_file + "');"
    fsql.write(sql + '\n')
    #sql scripts
    sql = "insert into t(c) values (log(2));"
    fsql.write(sql + '\n')

args = [
    'sqlite3'
    , db_file
    , '< ', sql_file
]
print(' '.join(args))
out1 = subprocess.run(' '.join(args), shell=True)
os.remove(sql_file)

answered Sep 20, 2018 at 4:58

rayzinnz

2,0761 gold badge21 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Annarfych · Accepted Answer · 2019-03-02 15:01:08Z

0

Place libsqlitefunctions.dll in a folder that is visible globally like c:\WINDOWS\ or some other folder from the PATH environment variable. Then you will be able to load the extension using simple command:

con.load_extension("libsqlitefunctions.dll")

answered Mar 2, 2019 at 15:01

Annarfych

5374 silver badges10 bronze badges

1 Comment

Annarfych Over a year ago

I unpacked sqlite3 source to the same directory as extension-functions.c and used this command: gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.dll to compile it, by the way. GCC came from tdm-gcc package.

Collectives™ on Stack Overflow

Load sqlite3 extension in python3 sqlite

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related