I'm trying to perform sqlite3_exec
from python to extract line by line the contents of a database. According to the C API, I need a Callback function which will perform the iteration. I have coded the following with a lot of help from internet:
UPDATED with @eryksun pièces of advice
import ctypes
def extractor(unused, num_columns, pcolumn, pcolumn_name):
for column in range(0,num_columns):
if pcolumn[i] != None:
print pcolumn[i]
sqlite3DLL = ctypes.CDLL("C:\\Python\\PYTHON\\DLLs\\sqlite3.dll")
SQLITE_OPEN_READONLY = 1
null_ptr = ctypes.c_void_p(None)
p_src_db = ctypes.c_void_p(None)
ctypes.CFUNCTYPE(ctypes.c_int, ctypes.c_void_p, ctypes.POINTER(ctypes.c_char_p), ctypes.POINTER(ctypes.c_char_p))
callback_func = callback_type(extractor)
connect = sqlite3DLL.sqlite3_open_v2(DatabasePath, ctypes.byref(p_src_db), SQLITE_OPEN_READONLY, null_ptr)
connect = sqlite3DLL.sqlite3_exec(DatabasePath, "SELECT * FROM *", callback_func, None, null_ptr)
sqlite3DLL.sqlite3_close(DatabasePath)
Before moving on to the python callback function I have some doubts:
- Is
"SELECT * FROM *"
a possible SQL statement to avoid providing the name of a table (because I do not know it)? - Is the first argument of both functions
sqlite3_open_v2
andsqlite3_exec
the path to the database?
If all that is ok we can move on to the callback function. According to what I found on the net, the C callback function should be somewhat similar to:
callback(void *NotUsed, int argc, char **argv, char **azColName)
for (int i = 0; i < argc; i++) {printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL")}
That is why I coded the CFUNCTYPE
you see on my code. How can I code a python function which matches the callback needed to maybe fill a list with the database contents?
I have added the proposed changes on the code, the callback function just prints the value to verify the output. But It wont work I get an error:
con = sqlite3DLL.sqlite3_exec(FastenerLibraryPath, "SELECT * FROM *", callback_func, None, null_ptr)
WindowsError: exception: access violation writing 0x0000000000000009
Many thanks in advance!
FINAL WORKING EDITION (@eryksun comments and @MarkTolonen solution)
import ctypes
def extractor(unused, num_columns, pcolumn, pcolumn_name):
print ','.join(["''" if x is None else "'"+x+"'" for x in pcolumn[:num_columns]])
return 0
sqlite3DLL = ctypes.CDLL("C:\\Python\\PYTHON\\DLLs\\sqlite3.dll")
SQLITE_OPEN_READONLY = 1
null_ptr = ctypes.c_void_p(None)
p_src_db = ctypes.c_void_p(None)
ctypes.CFUNCTYPE(ctypes.CFUNCTYPE(ctypes.c_int, ctypes.c_void_p, ctypes.c_int, ctypes.POINTER(ctypes.c_char_p), ctypes.POINTER(ctypes.c_char_p))
callback_func = callback_type(extractor)
connect = sqlite3DLL.sqlite3_open_v2(DatabasePath, ctypes.byref(p_src_db), SQLITE_OPEN_READONLY, None)
connect = sqlite3DLL.sqlite3_exec(p_src_db, b"SELECT * FROM Pin", callback_func, None, None)
sqlite3DLL.sqlite3_close(p_src_db)
This is working, however, I will look into argtypes
and the opaque type.
Thank you all!