Use the installed version of libsqlite3 from Python

Question

Is it possible to use the installed version of SQLite3 from Python, with ctypes? If so, how?

On a Mac, the below works without error:

from ctypes import CDLL
libsqlite3 = CDLL("libsqlite3.dylib")

... but then from https://www.sqlite.org/c3ref/sqlite3.html

Each open SQLite database is represented by a pointer to an instance of the opaque structure named "sqlite3".

(emphasis mine)

which to me suggests you can't really make a ctypes.Structure for the database, say to then pass to sqlite3_open.

(Context: I want to use parts of SQLite from Python that are not exposed by the built-in sqlite3 module)

ead · Accepted Answer · 2021-08-17 08:53:19Z

The sqlite3-API uses an opaque pointer, so in the end there is no need to know its memory layout - one just could use a void-pointer.

For example, opening a sqlite3-database would create such a pointer:

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

i.e. the second parameter is a pointer to pointer. This function will create the structure and give its address to us - no need to know the exact layout of the the structur at all.

Later, we only need the address of this structure to be able to use further functionality, i.e.:

int sqlite3_close(sqlite3*);

The type-safety is something ensured by the compiler, once we have the machine code, the gloves are off and we can pass anything instead of sqlite3* to the function, but we have to ensure that it would work. Any pointer can be replaced by void* as long as it points to a valid memory (i.e. with correct memory layout). That leads to:

import ctypes
libsqlite3 = ctypes.CDLL("libsqlite3.dylib")
sqlite3_handle = ctypes.c_void_p()  # nullptr

# pass handle by reference:
res = libsqlite3.sqlite3_open(b"mydb.db", ctypes.byref(sqlite3_handle))
print("open result",  res)                 # check res == 0
print("pointer value:", sqlite3_handle)    # address is set

# do what ever needed...

# example usage of handle:
res = libsqlite3.sqlite3_close(sqlite3_handle)
print("close result",  res)# check res == 0

sqlite3_handle = None # make sure nobody accesses dangling pointer

This is somewhat quick and dirty: usually one needs to set argument-types and return-value-type. But in the functions above, defaults get correct behavior so I've skipped this (otherwise important) step.

"the second parameter is a pointer to pointer." this is the bit I missed... I thought the Python code had to allocate the memory for it. Thanks!

Michal Charemza · Accepted Answer · 2021-08-25 06:10:28Z

Based on ead's answer, this is a more complete example of how to use libsqlite3 from Python, which is also at https://gist.github.com/michalc/a3147997e21665896836e0f4157975cb

The below defines a (generator) function, query

from contextlib import contextmanager
from collections import namedtuple
from ctypes import cdll, byref, string_at, c_char_p, c_int, c_double, c_int64, c_void_p
from sys import platform


def query(db_file, sql, params=()):
    libsqlite3 = cdll.LoadLibrary({'linux': 'libsqlite3.so', 'darwin': 'libsqlite3.dylib'}[platform])
    libsqlite3.sqlite3_errstr.restype = c_char_p
    libsqlite3.sqlite3_errmsg.restype = c_char_p
    libsqlite3.sqlite3_column_name.restype = c_char_p
    libsqlite3.sqlite3_column_double.restype = c_double
    libsqlite3.sqlite3_column_int64.restype = c_int64
    libsqlite3.sqlite3_column_blob.restype = c_void_p
    libsqlite3.sqlite3_column_bytes.restype = c_int64
    SQLITE_ROW = 100
    SQLITE_DONE = 101
    SQLITE_TRANSIENT = -1
    SQLITE_OPEN_READWRITE = 0x00000002

    bind = {
        type(0): libsqlite3.sqlite3_bind_int64,
        type(0.0): libsqlite3.sqlite3_bind_double,
        type(''): lambda pp_stmt, i, value: libsqlite3.sqlite3_bind_text(pp_stmt, i, value.encode('utf-8'), len(value.encode('utf-8')), SQLITE_TRANSIENT),
        type(b''): lambda pp_stmt, i, value: libsqlite3.sqlite3_bind_blob(pp_stmt, i, value, len(value), SQLITE_TRANSIENT),
        type(None): lambda pp_stmt, i, _: libsqlite3.sqlite3_bind_null(pp_stmt, i),
    }

    extract = {
        1: libsqlite3.sqlite3_column_int64,
        2: libsqlite3.sqlite3_column_double,
        3: lambda pp_stmt, i: string_at(
            libsqlite3.sqlite3_column_blob(pp_stmt, i),
            libsqlite3.sqlite3_column_bytes(pp_stmt, i),
        ).decode(),
        4: lambda pp_stmt, i: string_at(
            libsqlite3.sqlite3_column_blob(pp_stmt, i),
            libsqlite3.sqlite3_column_bytes(pp_stmt, i),
        ),
        5: lambda pp_stmt, i: None,
    }

    def run(func, *args):
        res = func(*args)
        if res != 0:
            raise Exception(libsqlite3.sqlite3_errstr(res).decode())

    def run_with_db(db, func, *args):
        if func(*args) != 0:
            raise Exception(libsqlite3.sqlite3_errmsg(db).decode())

    @contextmanager
    def get_db(db_file):
        db = c_void_p()
        run(libsqlite3.sqlite3_open_v2, db_file.encode(), byref(db), SQLITE_OPEN_READWRITE, None)
        try:
            yield db
        finally:
            run_with_db(db, libsqlite3.sqlite3_close, db)

    @contextmanager
    def get_pp_stmt(db, sql):
        pp_stmt = c_void_p()
        run_with_db(db, libsqlite3.sqlite3_prepare_v3, db, sql.encode(), -1, 0, byref(pp_stmt), None)
        try:
            yield pp_stmt
        finally:
            run_with_db(db, libsqlite3.sqlite3_finalize, pp_stmt)

    with \
            get_db(db_file) as db, \
            get_pp_stmt(db, sql) as pp_stmt:

        for i, param in enumerate(params):
            run_with_db(db, bind[type(param)], pp_stmt, i + 1, param)

        row_constructor = namedtuple('Row', (
            libsqlite3.sqlite3_column_name(pp_stmt, i).decode()
            for i in range(0, libsqlite3.sqlite3_column_count(pp_stmt))
        ))

        while True:
            res = libsqlite3.sqlite3_step(pp_stmt)
            if res == SQLITE_DONE:
                break
            if res != SQLITE_ROW:
                raise Exception(libsqlite3.sqlite3_errstr(res).decode())

            yield row_constructor(*(
                extract[libsqlite3.sqlite3_column_type(pp_stmt, i)](pp_stmt, i)
                for i in range(0, len(row_constructor._fields))
            ))

which can be used as, for example:

for row in query('my.db', 'SELECT * FROM my_table WHERE a = ?;', ('b',)):
    print(row)

Collectives™ on Stack Overflow

Use the installed version of libsqlite3 from Python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related