Search code examples
pythonpython-3.xsqlitectypes

Use the installed version of libsqlite3 from Python


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)


Solution

  • 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.